php excel导入mysql

要下载phpexcelreader,然后再看一个简单的把excel数据读取并显示出,是把excel数据导入到mysql数据,利用php 导入 csv到mysql数据库.

PHP实例代码如下:

require_once './includes/reader.php';  
// excelfile($filename, $encoding);  
$data = new spreadsheet_excel_reader();  
// set output encoding.  
$data->setoutputencoding('gbk');  
//"data.xls"是指要导入到mysql中(的)excel文件  
$data->read('date.xls');  
@ $db = mysql_connect('localhost', 'root', '1234') or  
die("could not connect to database.");//连接数据库  
mysql_query("set names 'gbk'");//输出中文  
mysql_select_db('wenhuaedu'); //选择数据库  
error_reporting(e_all ^ e_notice);  
for ($i = 1; $i <= $data->sheets[0][&#39;numrows&#39;]; $i++) {  
//以下注释(的)for循环打印excel表数据  
for ($j = 1; $j <= $data->sheets[0][&#39;numcols&#39;]; $j++) {  
echo $data->sheets[0][&#39;cells&#39;][$i][$j].",";  
}  
echo "n"; 
//http://sourceforge.net/projects/phpexcelreader 
//下面为导入程序，注明，mysql数据库结构要与xls文档的结构一样就可以了
$data = new spreadsheet_excel_reader();  
$data->setoutputencoding(&#39;utf-8&#39;);//设置编码  
$data->read(&#39;test.xls&#39;);//excel,csv文件位置  
error_reporting(e_all ^ e_notice); 
for ($i = 2; $i <= $data->sheets[0][&#39;numrows&#39;]; $i++) {//插入数据库开始  
$sql="insert into {$table}kehu (userid,sortid,danwei,xingming,zuncheng,tel,mobile,fax,email,website,qq,address,postcode,sex,beizhu,addtime) values(&#39;$_cookie[wecms_user_id]&#39;,&#39;$sortid&#39;,  
&#39;".$data->sheets[0][&#39;cells&#39;][$i][1]."&#39;,  
&#39;".$data->sheets[0][&#39;cells&#39;][$i][2]."&#39;,  
&#39;".$data->sheets[0][&#39;cells&#39;][$i][3]."&#39;,  
&#39;".$data->sheets[0][&#39;cells&#39;][$i][4]."&#39;,  
&#39;".$data->sheets[0][&#39;cells&#39;][$i][5]."&#39;,  
&#39;".$data->sheets[0][&#39;cells&#39;][$i][6]."&#39;,  
&#39;".$data->sheets[0][&#39;cells&#39;][$i][7]."&#39;,  
&#39;".$data->sheets[0][&#39;cells&#39;][$i][8]."&#39;,  
&#39;".$data->sheets[0][&#39;cells&#39;][$i][9]."&#39;,  
&#39;".$data->sheets[0][&#39;cells&#39;][$i][10]."&#39;,  
&#39;".$data->sheets[0][&#39;cells&#39;][$i][11]."&#39;,  
&#39;".$data->sheets[0][&#39;cells&#39;][$i][12]."&#39;,  
&#39;".$data->sheets[0][&#39;cells&#39;][$i][13]."&#39;,  
&#39;$nowtime&#39;)";  
$res=$db->query($sql); 
//插入数据库结束  
} 
//把csv导入到数据库。 
 
function  getmicrotime(){      
list($usec,   $sec)   =   explode("   ",microtime());      
return   ((float)$usec   +   (float)$sec);    
}    
$time_start   =   getmicrotime();    
include   ("connectdb.php");    
function   insert_data   ($id,$summary,$description,$additional_information,$category)    
{    
$my_query1   =   "insert   into   mantis_bug_text_table   (id,description,additional_information)      
values   (&#39;$id&#39;,&#39;$description&#39;,&#39;$additional_information&#39;)";    
$first   =   mysql_query($my_query1);        
$my_query2   =   "insert   into   mantis_bug_table   (id,project_id,summary,bug_text_id)   values   (&#39;$id&#39;,&#39;$category&#39;,&#39;$summary&#39;,&#39;$id&#39;)";    
$second   =   mysql_query($my_query2);        
return;    
}    
$fp   =   fopen("test.csv","r");    
while($data   =   fgetcsv($fp,&#39;1000&#39;,&#39;,&#39;)){    
  insert_data   ($data[0],$data[1],$data[2],$data[3],$data[4]);    
  echo   "<font   color   =   #ff0000   size   =   20>数据导入成功！</font><br><br>";        
}    
fclose   ($fp);    
$time_end   =   getmicrotime();    
//开源代码phprm.com 
$time   =   $time_end   -   $time_start;    
echo   "程序执行时间：".$time."秒";

本文链接:

收藏随意^^请保留教程地址.