PHP batch imports data from excel files into the database

While working on projects these days, I encountered a situation where data needed to be imported in batches. After the user submitted the excel form, we needed our backend to insert all the contents of the excel form information into the data table. Of course, the premise is that the information in the excel table that the user gives us must correspond to the field information in our table. The following are the steps for batch importing data in my backend.

First we need to download phpExcel

phpExcel is a PHP class library used to operate Office Excel documents. It is based on Microsoft's OpenXML standard and PHP language. You can use it to read and write spreadsheets in different formats.

If you need this class library, please contact me. My email is 823410261@qq.com.

After downloading phpExcel, the following is the coding implementation part. First, let me talk about the overall idea of ​​​​my handling.

Firstly, we need to get the data in the excel table. Secondly, we need to make the data into a format that complies with SQL specifications. The detailed processing will be shown in the following code. Finally, we need to put the obtained data together with the SQL statement. Execute in the program and insert all results into the data table.

<?php
require_once &#39;../Classes/PHPExcel.php&#39;;

function getFileExcle($value){
    $cgsdiseasesinterface = new Cgs_diseases_interface();

    $filePath = $value;//$value为需要导入数据的excel文件

    $PHPExcel = new PHPExcel();

    /**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/
    $PHPReader = new PHPExcel_Reader_Excel2007();

    if(!$PHPReader->canRead($filePath)){
        $PHPReader = new PHPExcel_Reader_Excel5();
        if(!$PHPReader->canRead($filePath)){
            echo 'no Excel';
            return ;
        }
    }

    $PHPExcel = $PHPReader->load($filePath);

    /**读取excel文件中的第一个工作表*/
    $currentSheet = $PHPExcel->getSheet(0);
    /**取得最大的列号*/
    $allColumn = $currentSheet->getHighestColumn();
    /**取得一共有多少行*/
    $allRow = $currentSheet->getHighestRow();
    $row = array();
    /**从第二行开始输出，因为excel表中第一行为列名*/
    for($currentRow = 2;$currentRow <= $allRow;$currentRow++){
        /**从第A列开始输出*/
        $col = array();
        for($currentColumn= &#39;A&#39;;$currentColumn<= $allColumn; $currentColumn++){
            $val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue();/**ord()将字符转为十进制数*/
            array_push($col,$val);
        }
        array_push($row,$col);
    }
    foreach($row as $key=>$value){
        $row[$key][1] = &#39;\&#39;&#39;.$value[1].&#39;\&#39;&#39;;
    }
    foreach($row as $key=>$value){
        $row[$key] = implode(",",$value);
    }
    $row = implode("),(",$row);
    $row = &#39;(&#39;.$row.&#39;)&#39;;
    $res = $cgsdiseasesinterface->insertDiseasesInformation($row);//该函数将数据插入到数据库中
    if($res){
        echo json_encode([&#39;code&#39; => CODE_SUCCESS, &#39;result&#39; => &#39;批量导入成功&#39;]);
    }else{
        echo json_encode([&#39;code&#39; => CODE_ERROR, &#39;result&#39; => &#39;批量导入失败&#39;]);
    }
}

One point here is that the main thing is because $currentRow starts from 2, so the first line should be the explanation of the corresponding field, and starting from the second line is the data we need. You can follow your own program actual need to make changes.

Here I spliced ​​all the data into strings. The advantage is that when the database is a short connection, you only need to connect to the database once, which saves time and system resources. If the database is set to a long connection, then The advantages of using string concatenation are not obvious. Of course, you can also use loops to insert data into the table. The advantage of this method is that it does not require complex splicing of data, but the disadvantage is that as I just said, it increases the running time and reduces the cost. It consumes more system resources.