99 lines
3.0 KiB
PHP
99 lines
3.0 KiB
PHP
|
<?php
|
||
|
|
/**
|
||
|
|
* Created by Glee Ltd.
|
||
|
|
* Date: 12-May-18
|
||
|
|
* Time: 12:02 PM
|
||
|
|
* Description: a class wrapper for reading an excel file and apply a callback on each rows
|
||
|
|
*/
|
||
|
|
|
||
|
|
namespace Hura8\System;
|
||
|
|
|
||
|
|
|
||
|
|
use PhpOffice\PhpSpreadsheet\IOFactory;
|
||
|
|
|
||
|
|
class ReadExcel
|
||
|
|
{
|
||
|
|
protected static $default_cols = [
|
||
|
|
'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
|
||
|
|
'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM',
|
||
|
|
'AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ',
|
||
|
|
//...
|
||
|
|
];
|
||
|
|
|
||
|
|
/* $objReader Reader\IReader */
|
||
|
|
protected $objReader;
|
||
|
|
|
||
|
|
public function __construct($ext="xlsx"){
|
||
|
|
// $inputFileType = 'Xls';
|
||
|
|
// $inputFileType = 'Xlsx';
|
||
|
|
// $inputFileType = 'Xml';
|
||
|
|
// $inputFileType = 'Ods';
|
||
|
|
// $inputFileType = 'Slk';
|
||
|
|
// $inputFileType = 'Gnumeric';
|
||
|
|
// $inputFileType = 'Csv';
|
||
|
|
$inputFileType = ucfirst($ext);
|
||
|
|
$this->objReader = IOFactory::createReader($inputFileType);
|
||
|
|
}
|
||
|
|
|
||
|
|
/*return [
|
||
|
|
'sheet_index' => [
|
||
|
|
'row_id' => [
|
||
|
|
//column name => content
|
||
|
|
'A' => '',
|
||
|
|
'B' => '',
|
||
|
|
],
|
||
|
|
],
|
||
|
|
//...
|
||
|
|
];*/
|
||
|
|
public function read(
|
||
|
|
$excel_file,
|
||
|
|
$sheet_start_row = 2,
|
||
|
|
array $column_list = ['A', 'B'],
|
||
|
|
$callback_on_each_row = '',
|
||
|
|
$return_content = false
|
||
|
|
) {
|
||
|
|
|
||
|
|
//$objReader->setReadDataOnly(true);
|
||
|
|
$objPHPExcel = $this->objReader->load($excel_file);
|
||
|
|
$apply_callback_on_rows = ($callback_on_each_row && function_exists($callback_on_each_row));
|
||
|
|
|
||
|
|
$number_sheet = $objPHPExcel->getSheetCount();
|
||
|
|
|
||
|
|
$all_sheet = [];
|
||
|
|
|
||
|
|
$map_column_list = (sizeof($column_list)) ? $column_list : self::$default_cols;
|
||
|
|
|
||
|
|
for($i=0; $i < $number_sheet; $i++){
|
||
|
|
|
||
|
|
$objPHPExcel->setActiveSheetIndex($i);
|
||
|
|
$current_sheet = $objPHPExcel->getActiveSheet();
|
||
|
|
$number_row = $current_sheet->getHighestRow();
|
||
|
|
|
||
|
|
$all_sheet_rows = [];
|
||
|
|
for($j = $sheet_start_row; $j <= $number_row; $j++){
|
||
|
|
$rows_content = [];
|
||
|
|
foreach ($map_column_list as $col) {
|
||
|
|
//$cell_value = $current_sheet->getCell($col . $j)->getFormattedValue();
|
||
|
|
$cell_value = $current_sheet->getCell($col . $j)->getValue();
|
||
|
|
$rows_content[$col] = ($cell_value) ? trim($cell_value) : '';
|
||
|
|
}
|
||
|
|
|
||
|
|
//check if we want to return data
|
||
|
|
if($return_content) $all_sheet_rows[$j] = $rows_content;
|
||
|
|
|
||
|
|
//check if we want to apply callback right away
|
||
|
|
if($apply_callback_on_rows) {
|
||
|
|
call_user_func($callback_on_each_row, $rows_content);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
if($return_content) $all_sheet[$i] = $all_sheet_rows;
|
||
|
|
}
|
||
|
|
|
||
|
|
//free resource
|
||
|
|
$objPHPExcel = null;
|
||
|
|
|
||
|
|
return ($return_content) ? $all_sheet : true;
|
||
|
|
}
|
||
|
|
}
|