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

Home

Backend Development

PHP Tutorial

PHP import excel file mysql database method_PHP tutorial

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jul 13, 2016 am 10:46 AM

excelmysqlphpphpexceluseaccomplishimportplug-indatabasedocumentmethod

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 sheets[0]['numRows']; $i++) {
for ($j = 1; $j 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 {

     $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 sheets[0]['numRows']; $i++) { for ($j = 1; $j 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 { $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

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn