An example of reading multiple worksheets implemented by phpexcel

Time:2021-1-14

In this paper, an example of phpexcel implementation of the read multi worksheet operation. The details are as follows:

Recently, the CRM module of our company needs to optimize the customer import function. The previous requirements are: only need to obtain data from a single worksheet; now the requirements are: need to obtain corresponding data from multiple worksheets and import it into the database;

Fortunately, phpexcel has provided us with a way to get multiple sheet worksheets. Here is the code: [here I use tp3.2.3]

/**
 *Import excel file
 *@ param string $file excel file path
 *@ return array excel file content array
 */
function import_excel($file){
  //Determine the format of the file
  $type = pathinfo($file); 
  $type = strtolower($type["extension"]);
  if ($type=='xlsx') { 
    $type='Excel2007'; 
  }elseif($type=='xls') { 
    $type = 'Excel5'; 
  } 
  ini_set('max_execution_time', '0');
  Vendor('PHPExcel.PHPExcel');
  $objReader = PHPExcel_ Iofactory:: createreader ($type); // determine which format to use
  $objreader - > setreaddataonly (true); // only read data, will intelligently ignore all blank lines, this is very important!!!
  $objhpexcel = $objreader - > Load ($file); // load excel file
  $sheetcount = $objphpexcel - > getsheetcount(); // get the total number of sheet worksheets
  $rowData = array();
  $RowNum = 0;
  /*Read table data*/
  For ($I = 0; $I < = $sheetcount-1; $I + +) {// total number of circular sheet worksheets
    $sheet = $objPHPExcel->getSheet($i);
    $highestRow = $sheet->getHighestRow();
    $rownum + = $highestrow-1; // calculate the total number of all sheets
    $highestColumn = $sheet->getHighestColumn();
    //Get the data from line 1 of the $I sheet
    for($row = 1;$row <= $highestRow;$row++){
      //Each sheet is regarded as a new array element, and the key name is named by the index of the sheet, which is conducive to the extraction of the later array
      $rowData[$i][] = arrToOne($sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE));
    }
  }
  /*Delete header data of each row*/
  foreach($rowData as $k=>$v){
    array_shift($rowData[$k]);
  }
  echo '<pre>';
  print_ R ($rowdata); // print results
  echo '</pre>';
  return array("RowNum" => $RowNum,"Excel_Data" => $rowData);
}

The screenshot of Excel is as follows:

The print result is as follows: 0 corresponds to the first sheet; and so on, the third is the last sheet;

Note: when there is no data in a sheet, I will create an empty array, such as the third element of the following result; so when inserting the database operation, I need to judge whether it is empty again!

<pre>Array
(
    [0] => Array
        (
            [0] => Array
                (
[0] = > test data 001
                    [1] =>
[2] = > contact 1
[3] = > female
                    [4] =>
                    [5] =>
                    [6] =>
                    [7] =>
                    [8] =>
                    [9] =>
                    [10] =>
                    [11] =>
                    [12] =>
                )
 
            [1] => Array
                (
[0] = > test data 002
                    [1] =>
[2] = > contact 2
[3] = > female
                    [4] =>
                    [5] =>
                    [6] =>
                    [7] =>
                    [8] =>
                    [9] =>
                    [10] =>
                    [11] =>
                    [12] =>
                )
 
        )
 
    [1] => Array
        (
            [0] => Array
                (
[0] = > test data 014
                    [1] =>
[2] = > contact 13
[3] = > female
                    [4] =>
                    [5] =>
                    [6] =>
                    [7] =>
                    [8] =>
                    [9] =>
                    [10] =>
                    [11] =>
                    [12] =>
                )
 
        )
 
    [2] => Array
        (
            [0] => Array
                (
[0] = > test data 015
                    [1] =>
[2] = > contact 13
[3] = > female
                    [4] =>
                    [5] =>
                    [6] =>
                    [7] =>
                    [8] =>
                    [9] =>
                    [10] =>
                    [11] =>
                    [12] =>
                )
 
        )
 
    [3] => Array
        (
        )
 
)
</pre>

So far, we have successfully obtained the data of each sheet;

PS:The phpexcel file can be downloaded here https://www.jb51.net/codes/194070.html 。

More about PHP related content, interested readers can see the site topic: “PHP operation office document skills summary (including word, Excel, access, “Ppt”, “PHP array operation skills”, “PHP data structure and algorithm tutorial”, “PHP programming algorithm summary”, “PHP mathematical operation skills summary”, “PHP regular expression Usage Summary”, “PHP string Usage Summary” and “PHP common database operation skills summary”

I hope this article is helpful for PHP programming.