Introduction to the specific method of importing EXCEL into MYSQL using PHP

Introduction to the specific method of importing EXCEL into MYSQL using PHP_PHP Tutorial

WBOY

Release： 2016-07-15 13:30:37

Original

924 people have browsed it

I searched a lot of information on this on the Internet and found that all excel files are saved as csv files and then imported from the csv file. 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

Description:

PHP imports EXCEL into the MYSQL test environment: MYSQL database adopts UTF8 encoding. The imported EXCEL document is in xls format. After testing, the 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. PHP code example test.php for importing EXCEL into MYSQL

<ol class="dp-xml">
<li class="alt"><span><span class="tag"><</span><span> ?php  </span></span></li><li><span>require_once 'reader.php';  </span></li><li class="alt"><span>// ExcelFile($filename, $encoding);  </span></li><li><span>$</span><span class="attribute">data</span><span> = </span><span class="attribute-value">new</span><span> Spreadsheet_Excel_Reader();  </span></li><li class="alt"><span>// Set output Encoding.  </span></li><li><span>$data-</span><span class="tag">></span><span>setOutputEncoding('gbk');  </span></span></li>
<li class="alt"><span>//”data.xls”是指要导入到mysql中的excel文件  </span></li>
<li>
<span>$data-</span><span class="tag">></span><span>read('data.xls');  </span>
</li>
<li class="alt">
<span>@ $</span><span class="attribute">db</span><span> = </span><span class="attribute-value">mysql_connect</span><span>('localhost', 'root', '123456') or  </span>
</li>
<li><span>die("Could not connect to database.");//连接数据库  </span></li>
<li class="alt"><span>mysql_query("set names 'gbk'");//输出中文  </span></li>
<li><span>mysql_select_db('mydb'); //选择数据库  </span></li>
<li class="alt"><span>error_reporting(E_ALL ^ E_NOTICE);  </span></li>
<li>
<span>for ($</span><span class="attribute">i</span><span> = </span><span class="attribute-value">1</span><span>; $i </span><span class="tag"><</span><span>= $data-</span><span class="tag">></span><span>sheets[0]['numRows']; $i++) {  </span>
</li>
<li class="alt"><span>//以下注释的for循环打印excel表数据  </span></li>
<li><span>/*  </span></li>
<li class="alt">
<span>for ($</span><span class="attribute">j</span><span> = </span><span class="attribute-value">1</span><span>; $j </span><span class="tag"><</span><span> = $data-</span><span class="tag">></span><span>sheets[0]['numCols']; $j++) {  </span>
</li>
<li>
<span>echo """.$data-</span><span class="tag">></span><span>sheets[0]['cells'][$i][$j]."",";  </span>
</li>
<li class="alt"><span>}  </span></li>
<li><span>echo "n";  </span></li>
<li class="alt"><span>*/  </span></li>
<li><span>//以下代码是将excel表数据【3个字段】插入到mysql中，<br>根据你的excel表字段的多少，改写以下代码吧！  </span></li>
<li class="alt">
<span>$</span><span class="attribute">sql</span><span> = </span><span class="attribute-value">"INSERT INTO test VALUES('"</span><span>.  </span>
</li>
<li>
<span>$data-</span><span class="tag">></span><span>sheets[0]['cells'][$i][1]."','".  </span>
</li>
<li class="alt">
<span>$data-</span><span class="tag">></span><span>sheets[0]['cells'][$i][2]."','".  </span>
</li>
<li>
<span>$data-</span><span class="tag">></span><span>sheets[0]['cells'][$i][3]."')";  </span>
</li>
<li class="alt">
<span>echo $sql.'</span><span class="tag"><</span><span> </span><span class="tag-name">br</span><span> </span><span class="tag">/></span><span>';  </span>
</li>
<li>
<span>$</span><span class="attribute">res</span><span> = </span><span class="attribute-value">mysql_query</span><span>($sql);  </span>
</li>
<li class="alt"><span>}  </span></li>
<li>
<span class="tag">?></span><span> </span>
</li>
</ol>

Copy after login

The above is an introduction to the relevant methods of importing EXCEL into MYSQL through PHP. I hope it will be helpful to friends who need it.