Intereting Posts
тройное шифрование не дает одинаковых результатов в PHP и C # Скрыть время в транзакционных письмах с помощью {{var order.getCreatedAtFormated ('' short '')}} Есть ли причина, по которой мне нужно использовать stripslashes для представленных пользователем данных? Аутентификация с использованием PHP для использования wsdl Как я могу заставить этот скрипт работать в WordPress? Cron Job in Laravel чтение страницы SSL с помощью CURL (php) Предотвращение подачи формы дважды Загрузить изображение с помощью jquery MySQL JOIN таблицы с двойными именами столбцов ошибка в разбиении на страницы пользовательских таблиц в wordpress PHP обнаруживает (или удаляет) текущую букву диска? Echo 'string', в то время как каждая итерация длинного цикла (flush () не работает) Отображение справа налево подключенных языков (таких как персидский и арабский) в GD – Возможная ошибка PHP Простой поиск базы данных MySQL с использованием php

Как читать большие листы из больших файлов Excel (27 МБ +) с помощью PHPExcel?

У меня большие рабочие листы Excel, которые я хочу читать в MySQL с помощью PHPExcel.

Я использую недавний патч, который позволяет читать в рабочих листах без открытия всего файла. Таким образом, я могу читать один рабочий лист за раз.

Однако один файл 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'); } 

Как показывает следующий файл журнала, он отлично работает на небольшом 8K- файле Excel, но когда я запускаю его в файле Excel объемом 3 МБ , он никогда не проходит мимо первого фрагмента, есть ли способ оптимизировать этот код для производительности, иначе это не похоже на то, что он недостаточно эффективен, чтобы получить куски из большого файла 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); } } } } 

Можно прочитать рабочий лист в «кусках» с использованием Read Filters, хотя я не могу гарантировать никаких гарантий эффективности.

 $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);