PHPExcel and large files
PHPExcel is an excellent library with a huge functionality for working with xls, xlsx formats. You can read, write, change formatting, set formulas, and from xlsx you can also pull pictures.
On Habré, there was already a post about this library - Universal Reading of Cells in PHPExcel . I will focus only on the main minus of PHPExcel - there is always not enough memory, all the time there are errors like “Fatal error: Out of memory”. This post is about how to get around this.
To read a large file (~ 25,000 lines), I used a complete solution.
')
First, we read the file not all, but several lines. This is done like this:
import_xls.php
Actually the chunkReadFilter class is what we need. We set it as a filter for reading the file, and the file will not be loaded as a whole, but only a certain number of lines.
Secondly, besides it, we still use such a useful option as ReadDataOnly. As the name suggests, it allows you to not load the formatting of the document, freeing up space for the data.
And, thirdly, of course, we use unset (). It will also help free up memory.
But besides the lack of memory there is another problem. On the majority of shared-hosting with php-scripts in addition to restrictions on the use of memory, there is also a limit on the execution time. And it is extremely likely that this time will not be missed. Personally, I avoided this problem with sessions and repeated ajax requests. In the code above, you have already seen the use of sessions and the final “The End”.
And here is the client code.
import_xls.html
import-xls.js
Those. we send an ajax request to our import_xls.php script, wait for a response, and, if we are not satisfied with the answer, send a new ajax request. I met another solution on the net without using AJAX - with the help of a redirect in php itself. File processing is divided into a small number of lines and after that the code is inserted:
But personally, I prefer the solution with AJAX, because here you can easily add a progress bar and some other thingies and buns. By the way, the attentive reader noticed that in my code the simplest progress bar has already been implemented. An additional important point: the solution with AJAX does not need to know in advance how many lines the script can process in one pass.
To write an xls file of about 25,000 lines in size, it is extremely useful to use the following code:
You can still play around with caching methods. In addition to caching in the php temporary directory, memcache is also supported:
As well as cache_to_discISAM .
UPDATE
I apologize to everyone for the fact that the code was presented terribly here. Immediately did not understand the syntax highlighting on Habré (apparently, the late time of the day affected) and hurried. From now on I will not rush and I will be smarter.
UPDATE2
Redid javascript. Now it sends a request on timeout in case the server did not respond.
On Habré, there was already a post about this library - Universal Reading of Cells in PHPExcel . I will focus only on the main minus of PHPExcel - there is always not enough memory, all the time there are errors like “Fatal error: Out of memory”. This post is about how to get around this.
Reading file
To read a large file (~ 25,000 lines), I used a complete solution.
')
First, we read the file not all, but several lines. This is done like this:
import_xls.php
<?php require_once 'path/to/PHPExcel/IOFactory.php'; class chunkReadFilter implements PHPExcel_Reader_IReadFilter { private $_startRow = 0; private $_endRow = 0; public function setRows($startRow, $chunkSize) { $this->_startRow = $startRow; $this->_endRow = $startRow + $chunkSize; } public function readCell($column, $row, $worksheetName = '') { if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) { return true; } return false; } } session_start(); if ($_SESSION['startRow']) $startRow = $_SESSION['startRow']; else $startRow = 13; $inputFileType = 'Excel5'; $objReader = PHPExcel_IOFactory::createReader($inputFileType); $chunkSize = 20; $chunkFilter = new chunkReadFilter(); while ($startRow <= 65000) { $chunkFilter->setRows($startRow,$chunkSize); $objReader->setReadFilter($chunkFilter); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($fileName); //- $startRow += $chunkSize; $_SESSION['startRow'] = $startRow; unset($objReader); unset($objPHPExcel); } echo "The End"; unset($_SESSION['startRow']); ?> Actually the chunkReadFilter class is what we need. We set it as a filter for reading the file, and the file will not be loaded as a whole, but only a certain number of lines.
Secondly, besides it, we still use such a useful option as ReadDataOnly. As the name suggests, it allows you to not load the formatting of the document, freeing up space for the data.
And, thirdly, of course, we use unset (). It will also help free up memory.
But besides the lack of memory there is another problem. On the majority of shared-hosting with php-scripts in addition to restrictions on the use of memory, there is also a limit on the execution time. And it is extremely likely that this time will not be missed. Personally, I avoided this problem with sessions and repeated ajax requests. In the code above, you have already seen the use of sessions and the final “The End”.
And here is the client code.
import_xls.html
<html> <head> <title> -</title> <script src="/media/js/jquery.js" type="text/javascript"></script> <script src="/media/js/import-xls.js" type="text/javascript"></script> </head> <body> <h1> -</h1> , ! <div id="progress-bar"> </div> <div id="content"> </div> </body> </html> import-xls.js
function repeat_import() { $.ajax({ url: "/import_xls.php", timeout: 50000, success: function(data, textStatus){ $("#progress-bar").append("I"); if (data == "The End") { $("#content").html("<h2> !</h2>"); } else { $("#content").html("<p>" + data + "</p>"); repeat_import(); } }, complete: function(xhr, textStatus){ if (textStatus != "success") { $("#progress-bar").append("I"); repeat_import(); } } }); } $(function (){ repeat_import(); }); Those. we send an ajax request to our import_xls.php script, wait for a response, and, if we are not satisfied with the answer, send a new ajax request. I met another solution on the net without using AJAX - with the help of a redirect in php itself. File processing is divided into a small number of lines and after that the code is inserted:
header ("Location: import_xls.php");). But personally, I prefer the solution with AJAX, because here you can easily add a progress bar and some other thingies and buns. By the way, the attentive reader noticed that in my code the simplest progress bar has already been implemented. An additional important point: the solution with AJAX does not need to know in advance how many lines the script can process in one pass.
Write file
To write an xls file of about 25,000 lines in size, it is extremely useful to use the following code:
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; $cacheSettings = array( 'memoryCacheSize ' => '256MB'); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); You can still play around with caching methods. In addition to caching in the php temporary directory, memcache is also supported:
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache; $cacheSettings = array( 'memcacheServer' => 'localhost', 'memcachePort' => 11211, 'cacheTime' => 600 ); As well as cache_to_discISAM .
UPDATE
I apologize to everyone for the fact that the code was presented terribly here. Immediately did not understand the syntax highlighting on Habré (apparently, the late time of the day affected) and hurried. From now on I will not rush and I will be smarter.
UPDATE2
Redid javascript. Now it sends a request on timeout in case the server did not respond.
Source: https://habr.com/ru/post/148203/
All Articles