PHP import excel file mysql database method_PHP tutorial-PHP Tutorial-php.cn

PHP import excel file mysql database method_PHP tutorial

WBOY

Release： 2016-07-13 10:46:33

Original

1001 people have browsed it

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

代码如下

复制代码

//循环读取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

代码如下

复制代码

Copy code

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