標籤：style blog http os io ar for 檔案 資料
PHP-ExcelReader,: http://sourceforge.net/projects/phpexcelreader
注意點：
reader.php 中的下面這行要修改
1、將 require_once ‘Spreadsheet/Excel/Reader/OLERead.php’;改為 require_once ‘oleread.inc’;
2、require_once ‘oleread.inc’也可以和拷貝出來放到reader的檔案最前面合并為一個檔案
3、$data->setOutputEncoding(‘utf-8’)也可以這樣用，我的項目都是utf-8的編碼，開始用的就是上面的寫法$data->setOutputEncoding(’CP936′);結果是，匯入資料庫老是說編碼錯誤。最後設成utf-8解決了。
上傳Excel介面代碼：upExcel.php
<?phpheader("Content-Type:text/html;charset=utf-8"); //設定字型編碼，避免中文亂碼?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=gb2312" /><title>匯入測試</title> </head> <body> <script> function import_check(){ var f_content = form1.file.value; var fileext=f_content.substring(f_content.lastIndexOf("."),f_content.length) fileext=fileext.toLowerCase() if (fileext!='.xls') { alert("對不起，匯入資料格式必須是xls格式檔案哦，請您調整格式後重新上傳，謝謝 ！"); return false; } } </script> <table width="98%" border="0" align="center" style="margin-top:20px; border:1px solid #9abcde;"> <form id="form1" name="form1" enctype="multipart/form-data" method="post" action="insert.php"> <tr> <td width="18%" height="50"> 選擇你要匯入的資料表:</td> <td width="82%"><label> <input name="file" type="file" id="file" size="50" /> </label> <label> <input name="button" type="submit" class="nnt_submit" id="button" value="匯入資料" onclick="import_check();"/> </label> </td> </tr> </form></table> </body> </html>
<?phpheader("Content-Type:text/html;charset=utf-8"); //設定字型編碼，避免中文亂碼require_once("../db_config.php");require_once 'Excel/reader.php'; error_reporting(E_ALL ^ E_NOTICE); //ini_set('max_execution_time', '100'); //php已耗用時間為30秒，當資料量大時，會出現逾時而導致無法全部匯入的情況。if($_POST){ $Import_TmpFile = $_FILES['file']['tmp_name']; //$Import_TmpFile = 'http://test998-merchphoto.stor.sinaapp.com/test.xls'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('utf-8'); $data->read($Import_TmpFile); $count =0; for($i= 1; $i<= $data->sheets[0]['numRows']; $i++){ $sql= "INSERT INTO test_xls(id,tm,name) VALUES('". $data->sheets[0]['cells'][$i][1]."','". $data->sheets[0]['cells'][$i][2]."','". $data->sheets[0]['cells'][$i][3]."')"; echo $sql."</br>";if(mysql_query($sql)){$count++;}} echo "<script>alert('成功匯入".$count."條資料');</script>"; } ?>
mysql 表：
excel：
參考資料：
1、http://jason2016.blog.51cto.com/892969/289411
2、http://www.cnblogs.com/phpzxh/archive/2009/09/16/1568133.html
3、http://blog.csdn.net/china_skag/article/details/7098473
PHP匯入Excel到MySQL的方法
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
