PHP operates Excel files and imports data in Excel into the database-PHP Tutorial-php.cn

PHP operates Excel files and imports data in Excel into the database

WBOY

Release： 2016-07-29 09:12:22

Original

1608 people have browsed it

Idea 1: Use an xml file to save the correspondence between the columns in Excel and the fields in the data table, and then add data through the xml fileIdea 2: The first row in Excel saves the field name, and the other rows save the data Three ideas:Save the correspondence between the columns in Excel and the fields in the data table in a .php fileHere we explain the second idea, using PHPExcel

1. Create objects

$ objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
2. Specify excel file
$objPHPExcel = $objReader->load($newFileName);
$sheet = $objPHPExcel->getSheet (0); //Get the data of the first sheet in Excel
$highestRow = $sheet->getHighestRow(); //Get the total number of rows and return the int type
$highestColumn = $sheet->getHighestColumn() ; // Get the total number of columns and return letters
$allFileds='';
$filedsSum='A'; //Set the default maximum value of valid columns
$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 sql Statement
for($j=2; $j<=$highestRow; $j++){
$inputData=array();
for ($i='A';$i<=$filedsSum;++$i) {
$inputData[]='''.$sheet->getCell($i.$j)->getValue().''';
//Because it is not sure whether the field is of type string or int, So if you add '', there will be no error
$inputDataSql.=',('.implode(',', $inputData).')';
}
$inputDataSql=substr($inputDataSql,1); / /Remove the first ',' comma
$sql='INSERT INTO ('.$allFileds.') VALUES '.$inputDataSql;

Finally call the corresponding database operation object and perform the insertion operation.

The above introduces how to use PHP to operate Excel files and import data in Excel into the database, including the content. I hope it will be helpful to friends who are interested in PHP tutorials.