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

Source: Internet
Author: User
Tags mysql import
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.

  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.