Example of reading excel and importing database based on ThinkPHP framework

Time:2021-1-15

This paper introduces the operation of reading excel and importing database realized by ThinkPHP framework. The details are as follows:

In the entry file:


require_once VENDOR_PATH.'PHPExcel/PHPExcel/IOFactory.php';
require_once VENDOR_PATH.'PHPExcel/PHPExcel.php';

PHP:

namespace Home\Controller;
class ExcelController extends CommonController
{
public function Import() {
// vendor('PHPExcel.PHPExcel.IOFactory');
vendor("PHPExcel.PHPExcel.PHPExcel");
vendor("PHPExcel.PHPExcel.Writer.Excel5");
vendor("PHPExcel.PHPExcel.Writer.Excel2007");
//$excel = new PHPExcel();
$fileName = './trans_rate.xlsx';
date_default_timezone_set('PRC');
//Read excel file
try {
$objPHPExcel = \PHPExcel_IOFactory::load($fileName);
$inputFileType = \PHPExcel_IOFactory::identify($fileName);
$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
// $objPHPExcel = $objReader->load($fileName);
//Determine the sheet to read $sheet = $objppexcel - > getsheet (0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
//Get a row of data
// $phone_str = '';
for ($row = 3; $row <= $highestRow; $row++) {
 $row_data = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
//Get array data of one row in Excel
//dump($row_data);
$row_data = $row_data[0];
$time = date('Y-m-d H:i:s', strtotime(trim($row_data[0])));
$start_province = trim($row_data[1]);
$start_city = trim($row_data[2]);
...
// $phone_str .= '"' . $phone . '",';
$where['phone'] = $phone;
$id_ Arr = m (data table name) - > where ($where) - > getfield ('id ');
$user_id = !empty($id_arr) ? $id_arr : 0;
$fields[] = [
'data table field '= > $user_ ID, // user ID
          ...
           ];
}
// dump($fields);
$rate_ Add = m (data table name) - > addall ($fields);
dump($rate_add);
echo M()->getLastSql();
if (!(0 < $rate_add)) {
Commoncontroller:: logprofile ('add Excel data, SQL: '. M() - > getlastsql()); $this - > endback (0);}
// echo $phone_str . '<br />';
// dump($user_id);
} catch (Exception $e) {
Die ('error loading file: '". Pathinfo ($file name, pathinfo)_ BASENAME) . '": ' . $e->getMessage()); } }}

PHP reads Excel data

<?php
include 'ThinkPHP/Library/Vendor/PHPExcel/PHPExcel/IOFactory.php';

$inputFileName = './trans_rate.xlsx';
date_default_timezone_set('PRC');
//Read excel file
try {
  $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
  $objReader = PHPExcel_IOFactory::createReader($inputFileType);
  $objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
  Die ('error loading file: '. Pathinfo' ($inputfile name, pathinfo)_ BASENAME).'": '.$e->getMessage());
}

//Determine the sheet to read
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();

//Get a row of data
for ($row = 1; $row <= $highestRow; $row++){
// Read a row of data into an array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
//The rowdata obtained here is a row of data, and it will be processed by itself after getting the data
var_dump($rowData);
echo "<br>";
}
//$data is the array obtained from excel
for ($i =0; $i<count($data);$i++){
  echo '<br>';
  $gettime= explode('-',$data[$i][0]);
  if (checkdate($month=$gettime[0],$day=$gettime[1],$year=$gettime[2])){
    echo gmdate('Y-m-d',gmmktime(0,0,0,$month,$day,$year));
  }else{
    echo ($data[$i][0]);
  }
  echo '-----------';
  echo $data[$i][1];
}
<?php
include 'ThinkPHP/Library/Vendor/PHPExcel/PHPExcel/IOFactory.php';

$inputFileName = './test.xlsx';
date_default_timezone_set('Asia/Shanghai');
//Read excel file
try {
  $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
  $objReader = PHPExcel_IOFactory::createReader($inputFileType);
  $objPHPExcel = $objReader->load($inputFileName);

  //Make sure to read the sheet, what is the sheet, look at the lower right corner of Excel, really do not understand Baidu
  $sheet = $objPHPExcel->getSheet(0);
  $highestrow = $sheet - > gethighestrow(); // max row
  $highestcolumn = $sheet - > gethighestcolumn(); // maximum column

  $data = array();
  For ($rowindex = 2; $rowindex < = $highestrow; $rowindex + +) {// loop to read the contents of each cell. Notice that the rows start with 1 and the columns start with a
    for($colIndex='A';$colIndex<=$highestColumn;$colIndex++){
      $addr = $colIndex.$rowIndex;
      If ($COLINDEX = = "a") {// specify h column as the time column
        $cell = gmdate("Y-m-d H:i:s", PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell($addr)->getValue()));
//        $cell = PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell($addr)->getValue());
//        var_dump($cell);die;
      }else{
        $cell = $sheet->getCell($addr)->getValue();
      }
//      if($cell instanceof PHPExcel_ Richtext) {// rich text conversion string
//        $cell = $cell->__toString();
//      }
      $data[$rowIndex][$colIndex] = $cell;
    }
  }
//  return $data;
  var_dump($data);
} catch(Exception $e) {
  Die ('error loading file: '. Pathinfo' ($inputfile name, pathinfo)_ BASENAME).'": '.$e->getMessage());
}

For more information about ThinkPHP, interested readers can check out the following topics: introduction to ThinkPHP, summary of operation skills of ThinkPHP template, summary of common methods of ThinkPHP, introduction to CodeIgniter, CI (CodeIgniter) framework advanced course, Zend framework introduction course and PHP template technology summary.

I hope this article will be helpful to the design of PHP program based on ThinkPHP framework.