PHP导入excel数据到MYSQL，php导入excelmysql
这里介绍一个直接将excel文件导入mysql的例子。我花了一晚上的时间测试，无论导入简繁体都不会出现乱码，非常好用。
PHP-ExcelReader,下载地址: http://sourceforge.net/projects/phpexcelreader
说明：
测试环境：MYSQL数据库采用utf8编码.导入EXCEL文档是xls格式,经过测试，xlsx 格式[excel 2007]也OK.
文中红色标注为需要注意的地方，请替换成你配置好的数据，如数据库配置等。运行http://localost/test.php实现导入。
以下是我贴出的详细代码，其中test.php为我写的测试文件，reader.php和oleread.inc文件是从上面提供的网址中下载的。
1. test.php
代码如下:
<?<span>php
</span><span>require_once</span> './includes/reader.php'<span>;
</span><span>//</span><span> ExcelFile($filename, $encoding); </span>
<span>$data</span> = <span>new</span><span> Spreadsheet_Excel_Reader();
</span><span>//</span><span> Set output Encoding. </span>
<span>$data</span>->setOutputEncoding('gbk'<span>);
</span><span>//</span><span>”data.xls”是指要导入到mysql中的excel文件 </span>
<span>$data</span>->read('date.xls'<span>);
@ </span><span>$db</span> = <span>mysql_connect</span>('localhost', 'root', '1234'<span>) or
</span><span>die</span>("Could not connect to database.");<span>//</span><span>连接数据库 </span>
<span>mysql_query</span>("set names 'gbk'");<span>//</span><span>输出中文 </span>
<span>mysql_select_db</span>('wenhuaedu'); <span>//</span><span>选择数据库 </span>
<span>error_reporting</span>(<span>E_ALL</span> ^ <span>E_NOTICE</span><span>);
</span><span>for</span> (<span>$i</span> = 1; <span>$i</span> <= <span>$data</span>->sheets[0]['numRows']; <span>$i</span>++<span>) {
</span><span>//</span><span>以下注释的for循环打印excel表数据 </span><span>
/*</span><span>
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
echo """.$data->sheets[0]['cells'][$i][$j]."",";
}
echo "n";
</span><span>*/</span> <span>/*</span><span> 何问起 hovertree.com </span><span>*/</span>
<span>//</span><span>以下代码是将excel表数据【3个字段】插入到mysql中，根据你的excel表字段的多少，改写以下代码吧！ </span>
<span>$sql</span> = "INSERT INTO test VALUES('".
<span>$data</span>->sheets[0]['cells'][<span>$i</span>][1]."','".
<span>$data</span>->sheets[0]['cells'][<span>$i</span>][2]."','".
<span>$data</span>->sheets[0]['cells'][<span>$i</span>][3]."')"<span>;
</span><span>echo</span> <span>$sql</span>.'<br />'<span>;
</span><span>$res</span> = <span>mysql_query</span>(<span>$sql</span><span>);
</span>?>包含的文件
OLERead.php
<?<span>php
</span><span>define</span>('NUM_BIG_BLOCK_DEPOT_BLOCKS_POS', 0x2c<span>);
</span><span>define</span>('SMALL_BLOCK_DEPOT_BLOCK_POS', 0x3c<span>);
</span><span>define</span>('ROOT_START_BLOCK_POS', 0x30<span>);
</span><span>define</span>('BIG_BLOCK_SIZE', 0x200<span>);
</span><span>define</span>('SMALL_BLOCK_SIZE', 0x40<span>);
</span><span>define</span>('EXTENSION_BLOCK_POS', 0x44<span>);
</span><span>define</span>('NUM_EXTENSION_BLOCK_POS', 0x48<span>);
</span><span>define</span>('PROPERTY_STORAGE_BLOCK_SIZE', 0x80<span>);
</span><span>define</span>('BIG_BLOCK_DEPOT_BLOCKS_POS', 0x4c<span>);
</span><span>define</span>('SMALL_BLOCK_THRESHOLD', 0x1000<span>);
</span><span>//</span><span> property storage offsets </span>
<span>define</span>('SIZE_OF_NAME_POS', 0x40<span>);
</span><span>define</span>('TYPE_POS', 0x42<span>);
</span><span>define</span>('START_BLOCK_POS', 0x74<span>);
</span><span>define</span>('SIZE_POS', 0x78<span>);
</span><span>define</span>('IDENTIFIER_OLE', <span>pack</span>("CCCCCCCC",0xd0,0xcf,0x11,0xe0,0xa1,0xb1,0x1a,0xe1<span>));
</span><span>//</span><span>echo 'ROOT_START_BLOCK_POS = '.ROOT_START_BLOCK_POS."\n";
//echo bin2hex($data[ROOT_START_BLOCK_POS])."\n";
//echo "a=";
//echo $data[ROOT_START_BLOCK_POS];
//function log </span>
<span>function</span> GetInt4d(<span>$data</span>, <span>$pos</span><span>)
{
</span><span>$value</span> = <span>ord</span>(<span>$data</span>[<span>$pos</span>]) | (<span>ord</span>(<span>$data</span>[<span>$pos</span>+1]) << 8) | (<span>ord</span>(<span>$data</span>[<span>$pos</span>+2]) << 16) | (<span>ord</span>(<span>$data</span>[<span>$pos</span>+3]) << 24<span>);
</span><span>if</span> (<span>$value</span>>=4294967294<span>)
{
</span><span>$value</span>=-2<span>;
}
</span><span>return</span> <span>$value</span><span>;
}
</span><span>class</span><span> OLERead {
</span><span>var</span> <span>$data</span> = ''<span>;
</span><span>function</span><span> OLERead(){
}
</span><span>function</span> read(<span>$sFileName</span><span>){
</span><span>//</span><span> check if file exist and is readable (Darko Miljanovic) </span>
<span>if</span>(!<span>is_readable</span>(<span>$sFileName</span><span>)) {
</span><span>$this</span>->error = 1<span>;
</span><span>return</span> <span>false</span><span>;
}
</span><span>$this</span>->data = @<span>file_get_contents</span>(<span>$sFileName</span><span>);
</span><span>if</span> (!<span>$this</span>-><span>data) {
</span><span>$this</span>->error = 1<span>;
</span><span>return</span> <span>false</span><span>;
}
</span><span>//</span><span>echo IDENTIFIER_OLE;
//echo 'start'; </span>
<span>if</span> (<span>substr</span>(<span>$this</span>->data, 0, 8) !=<span> IDENTIFIER_OLE) {
</span><span>$this</span>->error = 1<span>;
</span><span>return</span> <span>false</span><span>;
}
</span><span>$this</span>->numBigBlockDepotBlocks = GetInt4d(<span>$this</span>->data,<span> NUM_BIG_BLOCK_DEPOT_BLOCKS_POS);
</span><span>$this</span>->sbdStartBlock = GetInt4d(<span>$this</span>->data,<span> SMALL_BLOCK_DEPOT_BLOCK_POS);
</span><span>$this</span>->rootStartBlock = GetInt4d(<span>$this</span>->data,<span> ROOT_START_BLOCK_POS);
</span><span>$this</span>->extensionBlock = GetInt4d(<span>$this</span>->data,<span> EXTENSION_BLOCK_POS);
</span><span>$this</span>->numExtensionBlocks = GetInt4d(<span>$this</span>->data,<span> NUM_EXTENSION_BLOCK_POS);
</span><span>/*</span><span>
echo $this->numBigBlockDepotBlocks." ";
echo $this->sbdStartBlock." ";
echo $this->rootStartBlock." ";
echo $this->extensionBlock." ";
echo $this->numExtensionBlocks." ";
</span><span>*/</span>
<span>//</span><span>echo "sbdStartBlock = $this->sbdStartBlock\n"; </span>
<span>$bigBlockDepotBlocks</span> = <span>array</span><span>();
</span><span>$pos</span> =<span> BIG_BLOCK_DEPOT_BLOCKS_POS;
</span><span>//</span><span> echo "pos = $pos"; </span>
<span>$bbdBlocks</span> = <span>$this</span>-><span>numBigBlockDepotBlocks;
</span><span>if</span> (<span>$this</span>->numExtensionBlocks != 0<span>) {
</span><span>$bbdBlocks</span> = (BIG_BLOCK_SIZE - BIG_BLOCK_DEPOT_BLOCKS_POS)/4<span>;
}
</span><span>for</span> (<span>$i</span> = 0; <span>$i</span> < <span>$bbdBlocks</span>; <span>$i</span>++<span>) {
</span><span>$bigBlockDepotBlocks</span>[<span>$i</span>] = GetInt4d(<span>$this</span>->data, <span>$pos</span><span>);
</span><span>$pos</span> += 4<span>;
}
</span><span>for</span> (<span>$j</span> = 0; <span>$j</span> < <span>$this</span>->numExtensionBlocks; <span>$j</span>++<span>) {
</span><span>$pos</span> = (<span>$this</span>->extensionBlock + 1) *<span> BIG_BLOCK_SIZE;
</span><span>$blocksToRead</span> = <span>min</span>(<span>$this</span>->numBigBlockDepotBlocks - <span>$bbdBlocks</span>, BIG_BLOCK_SIZE / 4 - 1<span>);
</span><span>for</span> (<span>$i</span> = <span>$bbdBlocks</span>; <span>$i</span> < <span>$bbdBlocks</span> + <span>$blocksToRead</span>; <span>$i</span>++<span>) {
</span><span>$bigBlockDepotBlocks</span>[<span>$i</span>] = GetInt4d(<span>$this</span>->data, <span>$pos</span><span>);
</span><span>$pos</span> += 4<span>;
}
</span><span>$bbdBlocks</span> += <span>$blocksToRead</span><span>;
</span><span>if</span> (<span>$bbdBlocks</span> < <span>$this</span>-><span>numBigBlockDepotBlocks) {
</span><span>$this</span>->extensionBlock = GetInt4d(<span>$this</span>->data, <span>$pos</span><span>);
}
} </span><span>/*</span><span> 何问起 hovertree.com </span><span>*/</span>
<span>//</span><span> var_dump($bigBlockDepotBlocks);
// readBigBlockDepot </span>
<span>$pos</span> = 0<span>;
</span><span>$index</span> = 0<span>;
</span><span>$this</span>->bigBlockChain = <span>array</span><span>();
</span><span>for</span> (<span>$i</span> = 0; <span>$i</span> < <span>$this</span>->numBigBlockDepotBlocks; <span>$i</span>++<span>) {
</span><span>$pos</span> = (<span>$bigBlockDepotBlocks</span>[<span>$i</span>] + 1) *<span> BIG_BLOCK_SIZE;
</span><span>//</span><span>echo "pos = $pos"; </span>
<span>for</span> (<span>$j</span> = 0 ; <span>$j</span> < BIG_BLOCK_SIZE / 4; <span>$j</span>++<span>) {
</span><span>$this</span>->bigBlockChain[<span>$index</span>] = GetInt4d(<span>$this</span>->data, <span>$pos</span><span>);
</span><span>$pos</span> += 4<span> ;
</span><span>$index</span>++<span>;
}
}
</span><span>//</span><span>var_dump($this->bigBlockChain);
//echo '=====2';
// readSmallBlockDepot(); </span>
<span>$pos</span> = 0<span>;
</span><span>$index</span> = 0<span>;
</span><span>$sbdBlock</span> = <span>$this</span>-><span>sbdStartBlock;
</span><span>$this</span>->smallBlockChain = <span>array</span><span>();
</span><span>while</span> (<span>$sbdBlock</span> != -2<span>) {
</span><span>$pos</span> = (<span>$sbdBlock</span> + 1) *<span> BIG_BLOCK_SIZE;
</span><span>for</span> (<span>$j</span> = 0; <span>$j</span> < BIG_BLOCK_SIZE / 4; <span>$j</span>++<span>) {
</span><span>$this</span>->smallBlockChain[<span>$index</span>] = GetInt4d(<span>$this</span>->data, <span>$pos</span><span>);
</span><span>$pos</span> += 4<span>;
</span><span>$index</span>++<span>;
}
</span><span>$sbdBlock</span> = <span>$this</span>->bigBlockChain[<span>$sbdBlock</span><span>];
}
</span><span>//</span><span> readData(rootStartBlock) </span>
<span>$block</span> = <span>$this</span>-><span>rootStartBlock;
</span><span>$pos</span> = 0<span>;
</span><span>$this</span>->entry = <span>$this</span>->__readData(<span>$block</span><span>);
</span><span>/*</span><span>
while ($block != -2) {
$pos = ($block + 1) * BIG_BLOCK_SIZE;
$this->entry = $this->entry.substr($this->data, $pos, BIG_BLOCK_SIZE);
$block = $this->bigBlockChain[$block];
}
</span><span>*/</span>
<span>//</span><span>echo '==='.$this->entry."==="; </span>
<span>$this</span>-><span>__readPropertySets();
}
</span><span>function</span> __readData(<span>$bl</span><span>) {
</span><span>$block</span> = <span>$bl</span><span>;
</span><span>$pos</span> = 0<span>;
</span><span>$data</span> = ''<span>;
</span><span>while</span> (<span>$block</span> != -2<span>) {
</span><span>$pos</span> = (<span>$block</span> + 1) *<span> BIG_BLOCK_SIZE;
</span><span>$data</span> = <span>$data</span>.<span>substr</span>(<span>$this</span>->data, <span>$pos</span>,<span> BIG_BLOCK_SIZE);
</span><span>//</span><span>echo "pos = $pos data=$data\n"; </span>
<span>$block</span> = <span>$this</span>->bigBlockChain[<span>$block</span><span>];
}
</span><span>return</span> <span>$data</span><span>;
}
</span><span>function</span><span> __readPropertySets(){
</span><span>$offset</span> = 0<span>;
</span><span>//</span><span>var_dump($this->entry); </span>
<span>while</span> (<span>$offset</span> < <span>strlen</span>(<span>$this</span>-><span>entry)) {
</span><span>$d</span> = <span>substr</span>(<span>$this</span>->entry, <span>$offset</span>,<span> PROPERTY_STORAGE_BLOCK_SIZE);
</span><span>$nameSize</span> = <span>ord</span>(<span>$d</span>[SIZE_OF_NAME_POS]) | (<span>ord</span>(<span>$d</span>[SIZE_OF_NAME_POS+1]) << 8<span>);
</span><span>$type</span> = <span>ord</span>(<span>$d</span><span>[TYPE_POS]);
</span><span>//</span><span>$maxBlock = strlen($d) / BIG_BLOCK_SIZE - 1; </span>
<span>$startBlock</span> = GetInt4d(<span>$d</span>,<span> START_BLOCK_POS);
</span><span>$size</span> = GetInt4d(<span>$d</span>,<span> SIZE_POS);
</span><span>$name</span> = ''<span>;
</span><span>for</span> (<span>$i</span> = 0; <span>$i</span> < <span>$nameSize</span> ; <span>$i</span>++<span>) {
</span><span>$name</span> .= <span>$d</span>[<span>$i</span><span>];
}
</span><span>$name</span> = <span>str_replace</span>("\x00", "", <span>$name</span><span>);
</span><span>$this</span>->props[] = <span>array</span><span> (
</span>'name' => <span>$name</span>,
'type' => <span>$type</span>,
'startBlock' => <span>$startBlock</span>,
'size' => <span>$size</span><span>);
</span><span>if</span> ((<span>$name</span> == "Workbook") || (<span>$name</span> == "Book"<span>)) {
</span><span>$this</span>->wrkbook = <span>count</span>(<span>$this</span>->props) - 1<span>;
}
</span><span>if</span> (<span>$name</span> == "Root Entry"<span>) {
</span><span>$this</span>->rootentry = <span>count</span>(<span>$this</span>->props) - 1<span>;
}
</span><span>//</span><span>echo "name ==$name=\n"; </span>
<span>$offset</span> +=<span> PROPERTY_STORAGE_BLOCK_SIZE;
}
}
</span><span>function</span><span> getWorkBook(){
</span><span>if</span> (<span>$this</span>->props[<span>$this</span>->wrkbook]['size'] <<span> SMALL_BLOCK_THRESHOLD){
</span><span>//</span><span> getSmallBlockStream(PropertyStorage ps) </span>
<span>$rootdata</span> = <span>$this</span>->__readData(<span>$this</span>->props[<span>$this</span>->rootentry]['startBlock'<span>]);
</span><span>$streamData</span> = ''<span>;
</span><span>$block</span> = <span>$this</span>->props[<span>$this</span>->wrkbook]['startBlock'<span>];
</span><span>//</span><span>$count = 0; </span>
<span>$pos</span> = 0<span>;
</span><span>while</span> (<span>$block</span> != -2<span>) {
</span><span>$pos</span> = <span>$block</span> *<span> SMALL_BLOCK_SIZE;
</span><span>$streamData</span> .= <span>substr</span>(<span>$rootdata</span>, <span>$pos</span>,<span> SMALL_BLOCK_SIZE);
</span><span>$block</span> = <span>$this</span>->smallBlockChain[<span>$block</span><span>];
}
</span><span>return</span> <span>$streamData</span><span>;
}</span><span>else</span><span>{
</span><span>$numBlocks</span> = <span>$this</span>->props[<span>$this</span>->wrkbook]['size'] /<span> BIG_BLOCK_SIZE;
</span><span>if</span> (<span>$this</span>->props[<span>$this</span>->wrkbook]['size'] % BIG_BLOCK_SIZE != 0<span>) {
</span><span>$numBlocks</span>++<span>;
}
</span><span>if</span> (<span>$numBlocks</span> == 0) <span>return</span> ''<span>;
</span><span>//</span><span>echo "numBlocks = $numBlocks\n";
//byte[] streamData = new byte[numBlocks * BIG_BLOCK_SIZE];
//print_r($this->wrkbook); </span>
<span>$streamData</span> = ''<span>;
</span><span>$block</span> = <span>$this</span>->props[<span>$this</span>->wrkbook]['startBlock'<span>];
</span><span>//</span><span>$count = 0; </span>
<span>$pos</span> = 0<span>;
</span><span>//</span><span>echo "block = $block"; </span>
<span>while</span> (<span>$block</span> != -2<span>) {
</span><span>$pos</span> = (<span>$block</span> + 1) *<span> BIG_BLOCK_SIZE;
</span><span>$streamData</span> .= <span>substr</span>(<span>$this</span>->data, <span>$pos</span>,<span> BIG_BLOCK_SIZE);
</span><span>$block</span> = <span>$this</span>->bigBlockChain[<span>$block</span><span>];
}
</span><span>//</span><span>echo 'stream'.$streamData; </span>
<span>return</span> <span>$streamData</span><span>;
}
}
}
</span>?>参考：http://hovertree.com/h/bjaf/to3l3tjm.htm
m18麦考林整站 for Ecshop
下载
m18麦考林整站 for Ecshop v2.7.3 安装方法： 1，解压rar包上传到网站根目录 2，导入sql数据库文件，到你的数据库里，可以phpmyadmin等软件 3，修改data里config.php里面的数据库 用户名 密码等信息 为你自己的数据库信息 4，安装完毕之后的后台用户名密码为： 后台地址：域名/admin 用户名：admin 密码：admin123 模板使用教程： htt
http://www.cnblogs.com/roucheng/p/phpmysql.html
立即学习“PHP免费学习笔记（深入）”；
