Gemeinschaft Lernen Tools-Bibliothek Freizeit
suchen
Deutsch
Heim > Backend-Entwicklung > PHP-Tutorial > Hauptteil

PHP导入excel数据到MYSQL mysql导入excel文件 java excel导入mysql mysql 导入excel

WBOY
Freigeben： 2016-07-29 08:49:30
Original
923 Leute haben es durchsucht

这里介绍一个直接将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> $data->sheets[0]['numRows']; <span>$i</span>++<span>) { 
</span><span>//</span><span>以下注释的for循环打印excel表数据 </span><span>/*</span><span>
for ($j = 1; $j 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>?>
Nach dem Login kopieren

包含的文件 
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]) ord(<span>$data</span>[<span>$pos</span>+2]) ord(<span>$data</span>[<span>$pos</span>+3]) ); 
<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> $bbdBlocks; <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> $this->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> $bbdBlocks + <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> $this-><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> $this->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> $j++<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> $j++<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> strlen(<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]) ); 
<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> $nameSize ; <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']  SMALL_BLOCK_THRESHOLD){ 
<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>?>
Nach dem Login kopieren

参考：http://hovertree.com/h/bjaf/to3l3tjm.htm

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

以上就介绍了PHP导入excel数据到MYSQL，包括了导入EXCEL,Mysql方面的内容，希望对PHP教程有兴趣的朋友有所帮助。

Verwandte Etiketten：
mysql
Quelle：php.cn
Vorheriger Artikel：几种编程语言的Lambda Expression初探 expression的动词 gene expression expressionless Nächster Artikel：mac 安装 pecl ecl发光试剂盒 ecl显影液 ecl张琪
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Neueste Artikel des Autors
Aktuelle Ausgaben
Vite/Vue 3: „Undefinierte Anforderung' bei Verwendung der Bildquelle als Requisite Ich bin von VueCLI zu ViteCLI und von Vue3s CompositionAPI zu SFCScriptsetupAPI gewechselt...
Aus 2024-04-06 09:50:25
0
1
353
Eine erzwungene Aktualisierung ist eine Folge des Zugriffs auf undefinierte Pfade oder Fehlerseiten in Next.js Der Zugriff auf einen undefinierten Pfad oder eine Fehlerseite führt dazu, dass Next.js ei...
Aus 2024-04-05 08:32:46
0
1
400
Zeigen Sie AWS PDF-Dateien im Bootstrap-Modus in Laravel an Ich habe die AWS-URL wie https://xxx-xx-dev.s3.ap-south-1.amazonaws.com/std_check/65571220...
Aus 2024-04-04 22:16:18
0
1
1450
Objekte aus dem aktuellen Ordner können nicht in einen anderen Ordner importiert werden Ich versuche, Dateien aus anderen Ordnern zu importieren, erhalte jedoch die folgende Fehl...
Aus 2024-04-04 15:43:37
0
1
371
Rufen Sie den Verlauf der Zahlungsmethoden für WooCommerce-Kunden ab Ich habe ein Skript gefunden, das mir dabei hilft, die benötigten Informationen zu erhalte...
Aus 2024-04-04 14:57:11
0
1
428
verwandte Themen
Mehr>
Beliebte Empfehlungen
Beliebte Tutorials
Mehr>
Verwandte Tutorials
Beliebte Empfehlungen
Aktuelle Kurse
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage