: This article mainly introduces how to use php to operate Excel files and import Excel data to a database. For more information about PHP tutorials, see. Idea 1: Use an xml file to save the correspondence between columns in Excel and fields in the data table, and then use an xml file to add data. Idea 2: save the field name in the first row of Excel, train of Thought 3 for saving data in other rows: Associate columns in Excel with fields in the data table, using one. the PHP file is saved here to explain the idea 2, using PHPExcel
1. create an object
$ ObjReader = PHPExcel_IOFactory: createReader ('excel5'); // use excel2007 for 2007 format
2. specify an excel file
$ ObjPHPExcel = $ objReader-> load ($ newFileName );
$ Sheet = $ objPHPExcel-> getSheet (0); // obtain the data of the first table in the Excel operation.
$ HighestRow = $ sheet-> getHighestRow (); // retrieves the total number of rows and returns the int type.
$ HighestColumn = $ sheet-> getHighestColumn (); // gets the total number of columns and returns a letter
$ AllFileds = '';
$ FiledsSum = 'a'; // you can specify the default maximum value for A valid column.
$ DataInfo = '';
$ TidIndex = 0;
For ($ I = 'a'; $ I <$ highestColumn; ++ $ I ){
$ CurFiled = $ sheet-> getCell ($ I. '1')-> getValue ();
If (! Empty ($ curFiled) {// filter invalid columns
$ FiledsSum = $ I;
$ AllFileds. = ','. $ curFiled .''';
}
}
$ InputDataSql = ''; // Insert an SQL statement
For ($ j = 2; $ j <= $ highestRow; $ j ++ ){
$ InputData = array ();
For ($ I = 'a'; $ I <= $ filedsSum; ++ $ I ){
$ InputData [] = '\ ''. $ sheet-> getCell ($ I. $ j)-> getValue ().'\'';
// Because you are not sure whether the field is of the string or int type, adding ''will not cause an error.
$ InputDataSql. = ', ('. implode (',', $ inputData ).')';
}
$ InputDataSql = substr ($ inputDataSql, 1); // remove the ',' comma
$ SQL = 'Insert INTO ('. $ allFileds.') VALUES '. $ inputDataSql;
Finally, call the corresponding database operation object and execute the insert operation.
The above describes how to use php to operate Excel files and import Excel data to the database, including some content. if you are interested in PHP tutorials.
