PHP Excel memory question

Topics: Developer Forum, User Forum
Sep 1, 2010 at 7:48 AM

Good day,

I have a question regarding PHP Excels memory usage. I know this topic has come up in the past, but it mainly seems to deal with caching.

My question is more on 'why' each cell needs so much memory. I get that you'll want to give the user the ability to set a whole range of options (formatting, colour, borders, etc...), which i understand needs memory, but for people like me, who only use PHPExcel for mass up- and download of plain text data, this seems unnecessary. I'm sure I'm not the only one who uses PHP Excel but doesn't use any of the more advanced options. I don't need to merge cells, i don't need special colours, fonts or anything. Just plain text.

If you could please answer me these questions: Am i correct in this assumption on in how PHPExcel saves cell data? And if so, are there any plans for a 'no-formatting' means of saving excel data that might reduce memory load?

Thank you for your time,

-Maurits

 

Sep 1, 2010 at 8:01 AM

I guess PHPExcel implements all those styling options, because when you do not need to style, merge etc you can easily 

  • Send Excel-specific headers
  • echo your data as a table structure

(With some work [<td cellspan="x">] you can also "merge" cells)

Only when you start to need more advanced features you have to rely on Libraries like PHPExcel.

Coordinator
Sep 1, 2010 at 11:34 AM

@Mkersten

PHPExcel is written for a wide range of people who have different requirements when working with spreadsheets in PHP, which is why it  tries to provide facilities for manipulating all aspects of cells. Cell Caching is just one mechanism that allows us to make better use of restricted memory available to many user, but it is just one among many methods. You say that you don't need formatting information when reading your worksheets, so you could take advantage of the ReadDataOnly switch when loading a workbook into a PHPExcel object:

$objReader = PHPExcel_IOFactory::createReader('Excel2007'); 
$objReader->setReadDataOnly(true); 
$objPHPExcel = $objReader->load("test.xlsx"); 

When using this option, no formatting data is read from the workbook, so it won't use as much memory. The only drawback with this is that the only method to distinguish between a number and a date/time in Excel is the formatting... so PHPExcel can't automatically determine if a cell contains a number or a date if this option is used. (One day, I'll get round to modifying the loader logic to provide this date/number differentiation even when ReadDataOnly is used, but nobody has ever asked for it yet).

Other techniques are available within the loader for saving memory, such as reading in only the worksheets that you're interested in rather than every worksheet in the workbook, or only reading the cells you want from a worksheet using a read filter.

$objReader = PHPExcel_IOFactory::createReader('Excel2007'); 
$objReader->setLoadSheetsOnly( array("Worksheet1", "Worksheet2") ); 
$objPHPExcel = $objReader->load("test.xlsx"); 

or

class MyReadFilter implements PHPExcel_Reader_IReadFilter 
{ 
    public function readCell($column, $row, $worksheetName = '') { 
        // Read title row and rows 20 - 30 
        if ($row == 1 || ($row >= 20 && $row <= 30)) { 
            return true; 
        } 
 
        return false; 
    } 
} 
 
$objReader = PHPExcel_IOFactory::createReader('Excel2007'); 
$objReader->setReadFilter( new MyReadFilter() ); 
$objPHPExcel = $objReader->load("test.xlsx"); 

 

 

 

Sep 1, 2010 at 11:57 AM

Hey Time, Mark,

Thank you both for your replies. The setReadDataOnly sounds tailor-made for my needs. I ran some initial tests and the results look good.

Can i use this option for Writers too?

Coordinator
Sep 1, 2010 at 12:12 PM
Edited Sep 1, 2010 at 12:13 PM
Mkersten wrote:

Hey Time, Mark,

Thank you both for your replies. The setReadDataOnly sounds tailor-made for my needs. I ran some initial tests and the results look good.

Can i use this option for Writers too?

 The option isn't available for writers. If there is no formatting for cells in the PHPExcel object, then the writers will only write the minimum needed for the appropriate format (e.g. CSV, no formatting anyway; Excel 5 and Excel2007, just the basic defaults that are required by the MS Excel file format; HTML, minimal styling; etc). None of the writers should do any unnecessary writing if they can help it.