php excel导入mysql

发布:smiling　来源: PHP粉丝网　添加日期：2014-09-11 20:57:54　浏览: 评论：0　

要下载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]['numrows']; $i++) {

//以下注释(的)for循环打印excel表数据

for ($j = 1; $j <= $data->sheets[0]['numcols']; $j++) {

echo $data->sheets[0]['cells'][$i][$j].",";

}

echo "n";

//ttp://sourceforge.net/projects/phpexcelreader

//下面为导入程序，注明，mysql数据库结构要与xls文档的结构一样就可以了



$data = new spreadsheet_excel_reader();

            $data->setoutputencoding('utf-8');//设置编码

            $data->read('test.xls');//excel,csv文件位置

            error_reporting(e_all ^ e_notice);

            for ($i = 2; $i <= $data->sheets[0]['numrows']; $i++) {//插入数据库开始

                    $sql="insert into {$table}kehu (userid,sortid,danwei,xingming,zuncheng,tel,mobile,fax,email,website,qq,address,postcode,sex,beizhu,addtime) values('$_cookie[wecms_user_id]','$sortid',

   '".$data->sheets[0]['cells'][$i][1]."',

   '".$data->sheets[0]['cells'][$i][2]."',

   '".$data->sheets[0]['cells'][$i][3]."',

   '".$data->sheets[0]['cells'][$i][4]."',

   '".$data->sheets[0]['cells'][$i][5]."',

   '".$data->sheets[0]['cells'][$i][6]."',

   '".$data->sheets[0]['cells'][$i][7]."',

   '".$data->sheets[0]['cells'][$i][8]."',

   '".$data->sheets[0]['cells'][$i][9]."',

   '".$data->sheets[0]['cells'][$i][10]."',

   '".$data->sheets[0]['cells'][$i][11]."',

   '".$data->sheets[0]['cells'][$i][12]."',

   '".$data->sheets[0]['cells'][$i][13]."',

   '$nowtime')";

                    $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   ('$id','$description','$additional_information')";



          $first   =   mysql_query($my_query1);



          $my_query2   =   "insert   into   mantis_bug_table   (id,project_id,summary,bug_text_id)   values   ('$id','$category','$summary','$id')";



          $second   =   mysql_query($my_query2);



          return;

      }



  $fp   =   fopen("test.csv","r");



  while($data   =   fgetcsv($fp,'1000',',')){



              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();

  //开源代码phpfensi.com

  $time   =   $time_end   -   $time_start;



  echo   "程序执行时间：".$time."秒";