PHP import Excel file MySQL database method _php Tutorial

It should be said that the use of Phpexcel plug-ins to implement the import and export of databases, this article is mainly to tell you to import Excel into the MySQL database method.

Download First

Download Phpexcel file, address: phpexcel.codeplex.com/

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

Then create a new PHP file to introduce reader.php,

The code is as follows:

copy code

"!--? php require_once ' Excel/reader. PHP '; $data = new Spreadsheet_excel_reader (); $data->setoutputencoding (' GBK ');//set encoding here, generally GBK mode $data->read (' Book1.xls ');//file path Error_reporting (e_all ^ e_notice); //Here I just loop out the contents of the Excel file, to the storage, as long as the output of the place, write a MySQL statement can ~ 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

< P>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 format $objPHPExcel = $ Objreader->load ($filename); $filename can be an uploaded file, or the specified file $sheet = $objPHPExcel->getsheet (0); $highestRow = $sheet->gethighestrow ();//Total number of rows $highestColumn = $sheet->gethighestcolumn ();//number of columns obtained $k = 0; //loops through the Excel file, reads a bar, inserts a for ($j =2; $j <= $highestRow; $j + +) { $a = $objPHPExcel Getactivesheet ()->getcell ("A". $j)->getvalue ();//Gets the value of column a $b = $objPHPExcel->getactivesheet () Getcell ("B". $j)->getvalue ();//Gets the value of column B $sql = "INSERT into table values (". $a. ",". $b. "); mysql_query ($sql); }

Code instance CVS import to database

Import the CSV into the database.

The code is as follows

Copy Code

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 "Data import successful! "; } Fclose ($FP); $time _end = Getmicrotime (); $time = $time _end-$time _start; echo "Program execution Time:". $time. " Seconds ";

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

http://www.bkjia.com/PHPjc/632936.html www.bkjia.com true http://www.bkjia.com/PHPjc/632936.html techarticle It should be said that the use of Phpexcel plug-in to implement the database import and import functions, this article is mainly to tell you to import Excel into the MySQL database method. Download the Phpexcel file First, ...

