Php: How to import an excel file mysql database. This article describes how to import an excel file to a mysql database. download and download the phpexcel file first. it should be said that the phpexcel plug-in is used to import and import databases. This article mainly tells you how to import excel to 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 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: |
|
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: |
|
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: |
|
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 "data imported successfully!
"; } 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
Examples. download the phpexcel file first ,...