PHP import excel file mysql database method_PHP tutorial

It should be said that it introduces the use of phpexcel plug-in to realize the import and import function of the database. This article mainly tells you how to import excel into the mysql database.

Download first

Download the phpexcel file at: phpexcel.codeplex.com/

Find the following similar code in the reader.php file (the first line is) and change it to the correct oleread.php path: require_once 'oleread.php';

Then create a new php file and introduce reader.php,

The code is as follows:

The code is as follows

Copy code

代码如下

复制代码

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 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();

$data->setOutputEncoding('gbk');//Set the encoding here, usually in gbk mode

代码如下

复制代码

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

$data->read('Book1.xls');//File path error_reporting(E_ALL ^ ​​E_NOTICE); //Here I will only loop the contents of the excel file. To store it in the database, just write a mysql statement in the output place~ 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"; } ?>

Code Example

The code is as follows

Copy code

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 can be an uploaded file or a specified file $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // Get the total number of rows $highestColumn = $sheet->getHighestColumn(); // Get the total number of columns $k = 0; //Loop through the excel file, read one item, and insert one item for($j=2;$j<=$highestRow;$j++)<🎜> {<🎜> <🎜> $a = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//Get the value of column A $b = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//Get the value of column B $sql = "INSERT INTO table VALUES(".$a.",".$b.")"; Mysql_query($sql); }

Import code example cvs into database

Import csv into database.

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, Descriptional_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 "& lt; font color = #ff0000 size = 20 & gt; data import is successful! & Lt; /font & lt; br & lt; br & gt;"
                                                                                     fclose ($fp);
​
$time_end = getmicrotime();
​
$time = $time_end - $time_start;
​
echo "Program execution time:".$time."seconds";

The code is as follows

代码如下

复制代码

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   "数据导入成功！ ";             }     fclose   ($fp);         $time_end   =   getmicrotime();         $time   =   $time_end   -   $time_start;         echo   "程序执行时间：".$time."秒";

Copy code

For more details, please see: http://www.bKjia.c0m/phper/php-database/excel-mysql.htm

http://www.bkjia.com/PHPjc/632936.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/632936.htmlTechArticleIt should be said that it introduces the use of phpexcel plug-in to realize the import and import functions of the database. This article mainly tells you how to use excel How to import into mysql database. First download the phpexcel file,...