Community Learn Tools Library Leisure
search
English
Home > Backend Development > PHP Tutorial > body text

PHP import excel data to MYSQL mysql import excel file java excel import mysql mysql import excel

WBOY
Release： 2016-07-29 08:49:30
Original
924 people have browsed it

Here is an example of directly importing excel files into mysql. I spent one night testing it, and there was no garbled code when importing simplified or traditional Chinese. It was very easy to use.
PHP-ExcelReader, download address: http://sourceforge.net/projects/phpexcelreader
Instructions:
Test environment: MYSQL database uses utf8 encoding. The imported EXCEL document is in xls format. After testing, xlsx format [excel 2007] is also OK .
The red markings in the text are areas that need attention. Please replace them with your configured data, such as database configuration, etc. Run http://localost/test.php to import.
The following is the detailed code I posted, in which test.php is the test file I wrote, and the reader.php and oleread.inc files are downloaded from the URL provided above.
1. test.php
The code is as follows: 

<?<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>?>
Copy after login

Contained file
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>?>
Copy after login

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

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

The above introduces PHP to import excel data into MYSQL, including the content of importing EXCEL and Mysql. I hope it will be helpful to friends who are interested in PHP tutorials.

Related labels：
mysql
source：php.cn
Previous article：几种编程语言的Lambda Expression初探 expression的动词 gene expression expressionless Next article：Mac installation pecl ecl luminescence kit ecl developer ecl Zhang Qi
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
Vite/Vue 3: "Undefined requirement" when using image source as prop I switched from VueCLI to ViteCLI, and from Vue3's CompositionAPI to SFCScriptsetupAPI. Ho...
From 2024-04-06 09:50:25
0
1
353
Forced refresh is a consequence of accessing undefined paths or error pages in Next.js Accessing an undefined path or error page causes Next.js to do a hard refresh, causing my ...
From 2024-04-05 08:32:46
0
1
400
Display AWS PDF files in Bootstrap mode in Laravel I have downloaded aws url like https://xxx-xx-dev.s3.ap-south-1.amazonaws.com/std_check/65...
From 2024-04-04 22:16:18
0
1
1450
Cannot import objects from the current folder into another folder I'm trying to import files from other folders but getting this error./src/components/Body/...
From 2024-04-04 15:43:37
0
1
371
Retrieve payment method history for WooCommerce customers I found a script that helps me get the information I need: the payment methods a customer ...
From 2024-04-04 14:57:11
0
1
428
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template