PHP import Excel data into MySQL MySQL import Excel file Java Excel import mysql MySQL import Excel

Here is an example of importing an Excel file directly into MySQL. I spent the whole night testing, no matter the import of simple and traditional will not appear garbled, very useful.
Php-excelreader, download Address: Http://sourceforge.net/projects/phpexcelreader
Description
Test environment: MySQL database is UTF8 encoded. The import Excel document is in the XLS format, tested, and the XLSX format [Excel 2007] is OK.
If you want to be aware of the red label, replace it with your configured data, such as database configuration. Run http://localost/test.php to implement the import.
Here is the detailed code I posted, where test.php wrote the test files for me, reader.php and oleread.inc files are downloaded from the URLs provided above.
1. test.php
The code is as follows:

 Phprequire_once'./includes/reader.php '; //excelfile ($filename, $encoding);$data=NewSpreadsheet_excel_reader ();//Set output Encoding.$data->setoutputencoding (' GBK '); //"Data.xls" means an Excel file to import into MySQL$data->read (' Date.xls '); @ $db=mysql_connect(' localhost ', ' root ', ' 1234 ') or die("Could not connect to database.");//connecting to a databasemysql_query("Set names ' GBK '");//Output Chinesemysql_select_db(' wenhuaedu ');//Select Databaseerror_reporting(E_all^E_notice);  for($i= 1;$i<=$data->sheets[0][' NumRows '];$i++) { //The for loop of the following comments prints the Excel table data/*For ($j = 1; $j <= $data->sheets[0][' numcols '); $j + +) {echo "". $data->sheets[0][' cells '] [$i] [$j]. "", ";} E Cho "n"; *//*how to ask Hovertree.com*///The following code is to insert the Excel table Data "3 fields" into MySQL, according to the number of your Excel table field, rewrite the following code! $sql= "INSERT into Test VALUES ('".$data->sheets[0][' cells ' [$i][1]. "', '".$data->sheets[0][' cells ' [$i][2]. "', '".$data->sheets[0][' cells ' [$i][3]. "')"; Echo$sql.'
'; $res=mysql_query($sql); ?>

Included files
oleread.php

 PhpDefine(' Num_big_block_depot_blocks_pos ', 0x2c); Define(' Small_block_depot_block_pos ', 0x3c); Define(' Root_start_block_pos ', 0x30); Define(' Big_block_size ', 0x200); Define(' Small_block_size ', 0x40); Define(' Extension_block_pos ', 0x44); Define(' Num_extension_block_pos ', 0x48); Define(' Property_storage_block_size ', 0x80); Define(' Big_block_depot_blocks_pos ', 0x4c); Define(' Small_block_threshold ', 0x1000); //Property Storage OffsetsDefine(' Size_of_name_pos ', 0x40); Define(' Type_pos ', 0x42); Define(' Start_block_pos ', 0x74); Define(' Size_pos ', 0x78); Define(' Identifier_ole ',Pack("CCCCCCCC", 0xd0,0xcf,0x11,0xe0,0xa1,0xb1,0x1a,0xe1)); //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 LogfunctionGETINT4D ($data,$pos) { $value=Ord($data[$pos]) | (Ord($data[$pos+1]) << 8) | (Ord($data[$pos+2]) << 16) | (Ord($data[$pos+3]) << 24); if($value>=4294967294) { $value=-2; } return$value; } classOleread {var$data= ''; functionOleread () {}functionRead$sFileName){ //Check if file exist and is readable (Darko Miljanovic)if(!is_readable($sFileName)) { $this->error = 1; returnfalse; } $this->data = @file_get_contents($sFileName); if(!$this-data) { $this->error = 1; returnfalse; } //echo identifier_ole;//echo ' start ';if(substr($this->data, 0, 8)! =identifier_ole) { $this->error = 1; returnfalse; } $this->numbigblockdepotblocks = Getint4d ($this->data,Num_big_block_depot_blocks_pos); $this->sbdstartblock = Getint4d ($this->data,Small_block_depot_block_pos); $this->rootstartblock = Getint4d ($this->data,Root_start_block_pos); $this->extensionblock = Getint4d ($this->data,Extension_block_pos); $this->numextensionblocks = Getint4d ($this->data,Num_extension_block_pos); /*echo $this->numbigblockdepotblocks. " "; echo $this->sbdstartblock. " "; echo $this->rootstartblock. " "; echo $this->extensionblock. " "; echo $this->numextensionblocks. " "; *///echo "Sbdstartblock = $this->sbdstartblock\n";$bigBlockDepotBlocks=Array(); $pos=Big_block_depot_blocks_pos;//echo "pos = $pos";$bbdBlocks=$this-numbigblockdepotblocks;if($this->numextensionblocks! = 0) { $bbdBlocks= (Big_block_size-big_block_depot_blocks_pos)/4; }  for($i= 0;$i<$bbdBlocks;$i++) { $bigBlockDepotBlocks[$i] = Getint4d ($this->data,$pos); $pos+ = 4; }  for($j= 0;$j<$this->numextensionblocks;$j++) { $pos= ($this->extensionblock + 1) *big_block_size;$blocksToRead=min($this->numbigblockdepotblocks-$bbdBlocks, BIG_BLOCK_SIZE/4-1);  for($i=$bbdBlocks;$i<$bbdBlocks+$blocksToRead;$i++) { $bigBlockDepotBlocks[$i] = Getint4d ($this->data,$pos); $pos+ = 4; } $bbdBlocks+=$blocksToRead; if($bbdBlocks<$this-numbigblockdepotblocks) { $this->extensionblock = Getint4d ($this->data,$pos); } } /*how to ask Hovertree.com*///Var_dump ($bigBlockDepotBlocks);//Readbigblockdepot$pos= 0; $index= 0; $this->bigblockchain =Array();  for($i= 0;$i<$this->numbigblockdepotblocks;$i++) { $pos= ($bigBlockDepotBlocks[$i] + 1) *big_block_size;//echo "pos = $pos"; for($j= 0;$j< BIG_BLOCK_SIZE/4;$j++) { $this->bigblockchain[$index] = Getint4d ($this->data,$pos); $pos+ = 4 ; $index++; } } //var_dump ($this->bigblockchain);//echo ' =====2 ';//Readsmallblockdepot ();$pos= 0; $index= 0; $sbdBlock=$this-Sbdstartblock;$this->smallblockchain =Array();  while($sbdBlock! =-2) { $pos= ($sbdBlock+ 1) *big_block_size; for($j= 0;$j< BIG_BLOCK_SIZE/4;$j++) { $this->smallblockchain[$index] = Getint4d ($this->data,$pos); $pos+ = 4; $index++; } $sbdBlock=$this->bigblockchain[$sbdBlock]; } //ReadData (Rootstartblock)$block=$this-Rootstartblock;$pos= 0; $this->entry =$this->__readdata ($block); /*while ($block! =-2) {$pos = ($block + 1) * big_block_size; $this->entry = $this->entry.substr ($this->data, $pos, big_block_size); $block = $this->bigblockchain[$block]; } *///echo ' = = = '. $this->entry. " ==="; $this-__readpropertysets ();} function__readdata ($BL) { $block=$BL; $pos= 0; $data= '';  while($block! =-2) { $pos= ($block+ 1) *big_block_size;$data=$data.substr($this->data,$pos,big_block_size); //echo "pos = $pos data= $data \ n";$block=$this->bigblockchain[$block]; } return$data; } function__readpropertysets () {$offset= 0; //var_dump ($this->entry); while($offset<strlen($this-entry)) { $d=substr($this->entry,$offset,property_storage_block_size); $nameSize=Ord($d[Size_of_name_pos]) | (Ord($d[Size_of_name_pos+1]) << 8); $type=Ord($d[Type_pos]); //$maxBlock = strlen ($d)/big_block_size-1;$startBlock= Getint4d ($d,Start_block_pos); $size= Getint4d ($d,Size_pos); $name= '';  for($i= 0;$i<$nameSize;$i++) { $name.=$d[$i]; } $name=Str_replace("\x00", "",$name); $this->props[] =Array ( ' Name ' + =$name, ' type ' = =$type, ' startblock ' =$startBlock, ' size ' =$size); if(($name= = "Workbook") | | ($name= = "book")) { $this->wrkbook =Count($this->props)-1; } if($name= = "Root Entry") { $this->rootentry =Count($this->props)-1; } //echo "name = = $name =\n";$offset+=Property_storage_block_size;} } functionGetworkbook () {if($this->props[$this->wrkbook][' Size ' <small_block_threshold) { //Getsmallblockstream (propertystorage PS)$rootdata=$this->__readdata ($this->props[$this->rootentry][' Startblock ']); $streamData= ''; $block=$this->props[$this->wrkbook][' Startblock ']; //$count = 0;$pos= 0;  while($block! =-2) { $pos=$block*small_block_size;$streamData.=substr($rootdata,$pos,small_block_size); $block=$this->smallblockchain[$block]; } return$streamData; }Else{ $numBlocks=$this->props[$this->wrkbook][' size ']/big_block_size;if($this->props[$this->wrkbook][' size ']% big_block_size! = 0) { $numBlocks++; } if($numBlocks= = 0)return''; //echo "numblocks = $numBlocks \ n";//byte[] Streamdata = new byte[numblocks * Big_block_size];//print_r ($this->wrk book); $streamData= ''; $block=$this->props[$this->wrkbook][' Startblock ']; //$count = 0;$pos= 0; //echo "block = $block"; while($block! =-2) { $pos= ($block+ 1) *big_block_size;$streamData.=substr($this->data,$pos,big_block_size); $block=$this->bigblockchain[$block]; } //Echo ' stream '. $streamData;return$streamData; } } } ?>

Reference: http://hovertree.com/h/bjaf/to3l3tjm.htm

Http://www.cnblogs.com/roucheng/p/phpmysql.html

The above describes the PHP import of Excel data to MySQL, including the introduction of excel,mysql aspects of the content, I hope the PHP tutorial interested in a friend helpful.

