This article describes how to import an excel file to a 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 both) and change it to the correct oleread. php path: require_once 'oleread. php ';
Create a new php file and introduce reader. php,
The Code is as follows:
| The Code is as follows: |
Copy code |
<? Php
Require_once 'excel/reader. php ';
$ Data = new Spreadsheet_Excel_Reader ();
$ Data-> setOutputEncoding ('gbk'); // encoding is set here, generally in the gbk mode.
$ Data-> read('Book1.xls '); // file path
Error_reporting (E_ALL ^ E_NOTICE );
// Here, I will output the content of the excel file cyclically. to store the content to the database, just write a mysql statement where the output is located ~
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 ";
}
?> |
Sample Code
| 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 (); // gets the total number of rows.
$ HighestColumn = $ sheet-> getHighestColumn (); // gets the total number of Columns
$ K = 0; // Read the excel file cyclically, read one, and insert one
For ($ j = 2; $ j <= $ highestRow; $ j ++)
{ $ A = $ objPHPExcel-> getActiveSheet ()-> getCell ("A". $ j)-> getValue (); // obtain the value of column
$ B = $ objPHPExcel-> getActiveSheet ()-> getCell ("B". $ j)-> getValue (); // obtain the value of Column B
$ SQL = "insert into table VALUES (". $ a. ",". $ B .")";
Mysql_query ($ SQL ); } |
Code instance cvs import to database
Import a csv file to 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, '20140901 ',',')){
Insert_data ($ data [0], $ data [1], $ data [2], $ data [3], $ data [4]);
Echo "<font color = # ff0000 size = 20> the data is imported successfully! </Font> <br> ";
}
Fclose ($ fp );
$ Time_end = getmicrotime ();
$ Time = $ time_end-$ time_start;
Echo "program execution time:". $ time. "seconds "; |
For more details, see http://www.bKjia. c0m/phper/php-database/excel-mysql.htm
