php 导入excel文件mysql数据库方法

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

应该说介绍了利用phpexcel插件来实现数据库的导入与导入功能,本文章主要是告诉你把excel导入到mysql数据库的方法.

先下载,下载phpexcel文件,地址：phpexcel.codeplex.com/

在reader.php文件中找到以下类似代码,第一行既是,改成正确的oleread.php路径即可:require_once 'oleread.php';然后新建一个php文件引入reader.php,代码如下:

<?php

require_once 'Excel/reader.php';

$data = new Spreadsheet_Excel_Reader();

$data->setOutputEncoding('gbk');//此处设置编码，一般都是gbk模式

$data->read('Book1.xls');//文件路径

error_reporting(E_ALL ^ E_NOTICE);

//这里我就只循环输出excel文件的内容了，要入库，只要把输出的地方，写一段mysql语句即可~

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

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

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

}

echo "n";

}

?>

代码示例如下:

require_once 'phpexcel/Classes/PHPExcel.php';

require_once 'phpexcel/Classes/PHPExcel/IOFactory.php';

require_once 'phpexcel/Classes/PHPExcel/Reader/Excel5.php';

$objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format

$objPHPExcel = $objReader->load($filename);   //$filename可以是上传的文件，或者是指定的文件

$sheet = $objPHPExcel->getSheet(0);

$highestRow = $sheet->getHighestRow(); // 取得总行数

$highestColumn = $sheet->getHighestColumn(); // 取得总列数

$k = 0;

//循环读取excel文件,读取一条,插入一条

   for($j=2;$j<=$highestRow;$j++)

   {

     $a = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//获取A列的值

     $b = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//获取B列的值

     $sql = "INSERT INTO table VALUES(".$a.",".$b.")";

     mysql_query($sql);

   }

代码实例cvs导入到数据库,把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',',')){

   //开源代码phpfensi.com

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



  $time   =   $time_end   -   $time_start;



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