Как читать большие листы из больших файлов Excel (более 27 МБ) с помощью PHPExcel?
У меня есть большие листы Excel, которые я хочу читать в MySQL, используя PHPExcel.
Я использую последний патч, который позволяет читать в Worksheets, не открывая весь файл. Таким образом, я могу читать по одному листу за раз.
Тем не менее, один файл Excel имеет размер 27 МБ. Я могу успешно читать на первом листе, так как он маленький, но второй лист настолько велик, что задание cron, которое запустило процесс в 22:00, не было завершено в 8:00, лист просто слишком большой.
Есть ли способ читать на листе построчно, например, что-то вроде этого:
$inputFileType = 'Excel2007';
$inputFileName = 'big_file.xlsx';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$worksheetNames = $objReader->listWorksheetNames($inputFileName);
foreach ($worksheetNames as $sheetName) {
//BELOW IS "WISH CODE":
foreach($row = 1; $row <=$max_rows; $row+= 100) {
$dataset = $objReader->getWorksheetWithRows($row, $row+100);
save_dataset_to_database($dataset);
}
}
добавление
@mark, я использовал опубликованный вами код для создания следующего примера:
function readRowsFromWorksheet() {
$file_name = htmlentities($_POST['file_name']);
$file_type = htmlentities($_POST['file_type']);
echo 'Read rows from worksheet:<br />';
debug_log('----------start');
$objReader = PHPExcel_IOFactory::createReader($file_type);
$chunkSize = 20;
$chunkFilter = new ChunkReadFilter();
$objReader->setReadFilter($chunkFilter);
for ($startRow = 2; $startRow <= 240; $startRow += $chunkSize) {
$chunkFilter->setRows($startRow, $chunkSize);
$objPHPExcel = $objReader->load('data/' . $file_name);
debug_log('reading chunk starting at row '.$startRow);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
var_dump($sheetData);
echo '<hr />';
}
debug_log('end');
}
Как показывает следующий файл журнала, он отлично работает на небольшом 8-килобайтном Excel-файле, но когда я запускаю его на 3-мегабайтном Excel-файле, он никогда не проходит первый блок, есть ли способ оптимизировать этот код для повышения производительности, иначе это не похоже на то, что это не достаточно эффективно, чтобы получить куски из большого файла Excel:
2011-01-12 11:07:15: ----------start
2011-01-12 11:07:15: reading chunk starting at row 2
2011-01-12 11:07:15: reading chunk starting at row 22
2011-01-12 11:07:15: reading chunk starting at row 42
2011-01-12 11:07:15: reading chunk starting at row 62
2011-01-12 11:07:15: reading chunk starting at row 82
2011-01-12 11:07:15: reading chunk starting at row 102
2011-01-12 11:07:15: reading chunk starting at row 122
2011-01-12 11:07:15: reading chunk starting at row 142
2011-01-12 11:07:15: reading chunk starting at row 162
2011-01-12 11:07:15: reading chunk starting at row 182
2011-01-12 11:07:15: reading chunk starting at row 202
2011-01-12 11:07:15: reading chunk starting at row 222
2011-01-12 11:07:15: end
2011-01-12 11:07:52: ----------start
2011-01-12 11:08:01: reading chunk starting at row 2
(...at 11:18, CPU usage at 93% still running...)
Приложение 2
Когда я комментирую:
//$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
//var_dump($sheetData);
Затем он анализирует с приемлемой скоростью (около 2 строк в секунду), есть ли в любом случае для повышения производительности toArray()?
2011-01-12 11:40:51: ----------start
2011-01-12 11:40:59: reading chunk starting at row 2
2011-01-12 11:41:07: reading chunk starting at row 22
2011-01-12 11:41:14: reading chunk starting at row 42
2011-01-12 11:41:22: reading chunk starting at row 62
2011-01-12 11:41:29: reading chunk starting at row 82
2011-01-12 11:41:37: reading chunk starting at row 102
2011-01-12 11:41:45: reading chunk starting at row 122
2011-01-12 11:41:52: reading chunk starting at row 142
2011-01-12 11:42:00: reading chunk starting at row 162
2011-01-12 11:42:07: reading chunk starting at row 182
2011-01-12 11:42:15: reading chunk starting at row 202
2011-01-12 11:42:22: reading chunk starting at row 222
2011-01-12 11:42:22: end
Приложение 3
Это, кажется, работает адекватно, например, по крайней мере для файла 3 МБ:
for ($startRow = 2; $startRow <= 240; $startRow += $chunkSize) {
echo 'Loading WorkSheet using configurable filter for headings row 1 and for rows ', $startRow, ' to ', ($startRow + $chunkSize - 1), '<br />';
$chunkFilter->setRows($startRow, $chunkSize);
$objPHPExcel = $objReader->load('data/' . $file_name);
debug_log('reading chunk starting at row ' . $startRow);
foreach ($objPHPExcel->getActiveSheet()->getRowIterator() as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
echo '<tr>';
foreach ($cellIterator as $cell) {
if (!is_null($cell)) {
//$value = $cell->getCalculatedValue();
$rawValue = $cell->getValue();
debug_log($rawValue);
}
}
}
}
4 ответа
Можно читать лист в виде "чанков", используя фильтры чтения, хотя я не могу дать никаких гарантий относительно эффективности.
$inputFileType = 'Excel5';
$inputFileName = './sampleData/example2.xls';
/** Define a Read Filter class implementing PHPExcel_Reader_IReadFilter */
class chunkReadFilter implements PHPExcel_Reader_IReadFilter
{
private $_startRow = 0;
private $_endRow = 0;
/** Set the list of rows that we want to read */
public function setRows($startRow, $chunkSize) {
$this->_startRow = $startRow;
$this->_endRow = $startRow + $chunkSize;
}
public function readCell($column, $row, $worksheetName = '') {
// Only read the heading row, and the rows that are configured in $this->_startRow and $this->_endRow
if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) {
return true;
}
return false;
}
}
echo 'Loading file ',pathinfo($inputFileName,PATHINFO_BASENAME),' using IOFactory with a defined reader type of ',$inputFileType,'<br />';
/** Create a new Reader of the type defined in $inputFileType **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
echo '<hr />';
/** Define how many rows we want to read for each "chunk" **/
$chunkSize = 20;
/** Create a new Instance of our Read Filter **/
$chunkFilter = new chunkReadFilter();
/** Tell the Reader that we want to use the Read Filter that we've Instantiated **/
$objReader->setReadFilter($chunkFilter);
/** Loop to read our worksheet in "chunk size" blocks **/
/** $startRow is set to 2 initially because we always read the headings in row #1 **/
for ($startRow = 2; $startRow <= 240; $startRow += $chunkSize) {
echo 'Loading WorkSheet using configurable filter for headings row 1 and for rows ',$startRow,' to ',($startRow+$chunkSize-1),'<br />';
/** Tell the Read Filter, the limits on which rows we want to read this iteration **/
$chunkFilter->setRows($startRow,$chunkSize);
/** Load only the rows that match our filter from $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);
// Do some processing here
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
var_dump($sheetData);
echo '<br /><br />';
}
Обратите внимание, что этот фильтр чтения всегда будет считывать первую строку таблицы, а также строки, определенные правилом чанков.
При использовании фильтра чтения PHPExcel по-прежнему анализирует весь файл, но загружает только те ячейки, которые соответствуют определенному фильтру чтения, поэтому он использует только память, требуемую этим количеством ячеек. Тем не менее, он будет анализировать файл несколько раз, по одному разу для каждого чанка, поэтому он будет работать медленнее. Этот пример читает 20 строк за раз: чтобы читать построчно, просто установите $chunkSize в 1.
Это также может вызвать проблемы, если у вас есть формулы, которые ссылаются на ячейки в разных "чанках", потому что данные просто недоступны для ячеек за пределами текущего "чанка".
В настоящее время читать .xlsx, .csv а также .ods лучший вариант - программа чтения электронных таблиц ( https://github.com/nuovo/spreadsheet-reader), потому что она может читать файлы, не загружая их в память. Для .xls Расширение имеет ограничения, потому что использует PHPExcel для чтения.
Это ChunkReadFilter.php:
<?php
Class ChunkReadFilter implements PHPExcel_Reader_IReadFilter {
private $_startRow = 0;
private $_endRow = 0;
/** Set the list of rows that we want to read */
public function setRows($startRow, $chunkSize) {
$this->_startRow = $startRow;
$this->_endRow = $startRow + $chunkSize;
}
public function readCell($column, $row, $worksheetName = '') {
// Only read the heading row, and the rows that are configured in $this->_startRow and $this->_endRow
if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) {
return true;
}
return false;
}
}
?>
И это index.php и не идеальная, но базовая реализация в конце этого файла.
<?php
require_once './Classes/PHPExcel/IOFactory.php';
require_once 'ChunkReadFilter.php';
class Excelreader {
/**
* This function is used to read data from excel file in chunks and insert into database
* @param string $filePath
* @param integer $chunkSize
*/
public function readFileAndDumpInDB($filePath, $chunkSize) {
echo("Loading file " . $filePath . " ....." . PHP_EOL);
/** Create a new Reader of the type that has been identified * */
$objReader = PHPExcel_IOFactory::createReader(PHPExcel_IOFactory::identify($filePath));
$spreadsheetInfo = $objReader->listWorksheetInfo($filePath);
/** Create a new Instance of our Read Filter * */
$chunkFilter = new ChunkReadFilter();
/** Tell the Reader that we want to use the Read Filter that we've Instantiated * */
$objReader->setReadFilter($chunkFilter);
$objReader->setReadDataOnly(true);
//$objReader->setLoadSheetsOnly("Sheet1");
//get header column name
$chunkFilter->setRows(0, 1);
echo("Reading file " . $filePath . PHP_EOL . "<br>");
$totalRows = $spreadsheetInfo[0]['totalRows'];
echo("Total rows in file " . $totalRows . " " . PHP_EOL . "<br>");
/** Loop to read our worksheet in "chunk size" blocks * */
/** $startRow is set to 1 initially because we always read the headings in row #1 * */
for ($startRow = 1; $startRow <= $totalRows; $startRow += $chunkSize) {
echo("Loading WorkSheet for rows " . $startRow . " to " . ($startRow + $chunkSize - 1) . PHP_EOL . "<br>");
$i = 0;
/** Tell the Read Filter, the limits on which rows we want to read this iteration * */
$chunkFilter->setRows($startRow, $chunkSize);
/** Load only the rows that match our filter from $inputFileName to a PHPExcel Object * */
$objPHPExcel = $objReader->load($filePath);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, false);
$startIndex = ($startRow == 1) ? $startRow : $startRow - 1;
//dumping in database
if (!empty($sheetData) && $startRow < $totalRows) {
/**
* $this->dumpInDb(array_slice($sheetData, $startIndex, $chunkSize));
*/
echo "<table border='1'>";
foreach ($sheetData as $key => $value) {
$i++;
if ($value[0] != null) {
echo "<tr><td>id:$i</td><td>{$value[0]} </td><td>{$value[1]} </td><td>{$value[2]} </td><td>{$value[3]} </td></tr>";
}
}
echo "</table><br/><br/>";
}
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel, $sheetData);
}
echo("File " . $filePath . " has been uploaded successfully in database" . PHP_EOL . "<br>");
}
/**
* Insert data into database table
* @param Array $sheetData
* @return boolean
* @throws Exception
* THE METHOD FOR THE DATABASE IS NOT WORKING, JUST THE PUBLIC METHOD..
*/
protected function dumpInDb($sheetData) {
$con = DbAdapter::getDBConnection();
$query = "INSERT INTO employe(name,address)VALUES";
for ($i = 1; $i < count($sheetData); $i++) {
$query .= "(" . "'" . mysql_escape_string($sheetData[$i][0]) . "',"
. "'" . mysql_escape_string($sheetData[$i][1]) . "')";
}
$query = trim($query, ",");
$query .="ON DUPLICATE KEY UPDATE name=VALUES(name),
=VALUES(address),
";
if (mysqli_query($con, $query)) {
mysql_close($con);
return true;
} else {
mysql_close($con);
throw new Exception(mysqli_error($con));
}
}
/**
* This function returns list of files corresponding to given directory path
* @param String $dataFolderPath
* @return Array list of file
*/
protected function getFileList($dataFolderPath) {
if (!is_dir($dataFolderPath)) {
throw new Exception("Directory " . $dataFolderPath . " is not exist");
}
$root = scandir($dataFolderPath);
$fileList = array();
foreach ($root as $value) {
if ($value === '.' || $value === '..') {
continue;
}
if (is_file("$dataFolderPath/$value")) {
$fileList[] = "$dataFolderPath/$value";
continue;
}
}
return $fileList;
}
}
$inputFileName = './prueba_para_batch.xls';
$excelReader = new Excelreader();
$excelReader->readFileAndDumpInDB($inputFileName, 500);
Если возможно использовать другую библиотеку, я рекомендую использовать Spout для чтения.xlsx, .ods, .csv
Он читает огромные файлы, использует мало памяти и работает быстро.
Из документации:
use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;
$reader = ReaderFactory::create(Type::XLSX); // for XLSX files
//$reader = ReaderFactory::create(Type::CSV); // for CSV files
//$reader = ReaderFactory::create(Type::ODS); // for ODS files
$reader->open($filePath);
foreach ($reader->getSheetIterator() as $sheet) {
foreach ($sheet->getRowIterator() as $row) {
// do stuff with the row
}
}
$reader->close();