append mode

Topics: Developer Forum
Oct 20, 2010 at 9:24 AM

is it possible to write to an excel in append mode ?

scenario :

1) create new excel object and insert data

2) write object to an existing excel file by adding a new worksheet

so if file has worksheet : "Sheet 1" , "Sheet 2" , "Sheet 3"

the write will create "Sheet 4" but doesn't delete the previously written sheets


i need this solution to overcome performance issues , since each sheet contains lot

of data , and loading the whole file only to create new sheet is redundant and will

cause the performance load to increase on each write

Oct 22, 2010 at 9:23 AM

I'm not really sure I follow what you're trying to do here. If you want all the worksheets in the existing excel file to be deleted, and just to contain the new sheet you've created, why not simply overwrite the previous Excel file with your new excel object rather than having the overheads of loading and adding to it?

You can delete existing sheets within a workbook by using the disconnectWorksheets() method of the PHPExcel object (which will delete every worksheet in the object) before adding your new sheet; or use the removeSheetByIndex() method to delete existing worksheets individually (although this method hasn't been optimised to release the memory used by that worksheet yet).

Oct 22, 2010 at 9:28 AM

Ik think the problem is the following:

- entire data sheet consists of multiple worksheets

- adding all worksheets at once results in performance problems (probably memory)


proposed solution:

- add one worksheet, than write file, destroy phpexcel object.

- reopen file, add another worksheet, etc....

Oct 22, 2010 at 10:01 AM

I'm still not sure I understand your problem: do you want to keep the existing worksheets and add another, or do you want only the one worksheet in your workbook?

Oct 22, 2010 at 10:05 AM

I think the original poster wants to add worksheets. So he wants to keep existing worksheets. I think it is an attempt to lower the memory footprint of PHPExcel, but I'm not really sure if it would be effective... ;)

Oct 22, 2010 at 10:12 AM
Edited Oct 22, 2010 at 10:12 AM

If that is the case borft, then his first option is to try cell caching as described in the manual.

It's also going to be more efficient to load the existing file, and to create and populate a new worksheet directly in that; rather than to build a worksheet in one workbook, then copy/move it to the existing file. It negates the overhead of two PHPExcel objects being memory resident at the same time.

Oct 22, 2010 at 10:15 AM

Yeah, I guess that sounds logical. If you have a lot of data, I guess you just need to use cell caching, and/or beef up your server hardware ;)

Oct 22, 2010 at 10:39 AM


Cell caching is the best we can offer to reduce memory usage at the moment. I'm experimenting with restructuring the way the cache is held and (weirdly) a 2D array of rows and columns seems to reduce memory quite significantly compared with the way we hold a simple 1D array of cellAddress/cellObject pairs at present, but I've yet to translate these initial sandbox observations into practise within PHPExcel itself.

Oct 22, 2010 at 10:39 AM
Edited Oct 22, 2010 at 10:40 AM
borft wrote:

proposed solution:

- add one worksheet, than write file, destroy phpexcel object.

- reopen file, add another worksheet, etc....

this solution doesn't work , since the previous worksheets will get deleted

meaning if file "abcd.xlsx" had "sheet 1" , "sheet 2"

and creating a new workbook with "sheet 3" and writing it to "abcd.xlsx" will results in only "sheet 3" while "sheet 1" and "sheet 2" are lost

my purpose is this :

suppose each sheet had 1000 lines , then on each run i don't want to load and write all sheets since it will

results in loading n*1000 lines and saving them , instead i only want to create a new

sheet with 1000 lines and append it to the existing file making it with (n+1)*1000 lines

it should be clear that much performance is reduced by this procedure

if i want to only append new 1000 lines to excel , why should i load the whole n*1000 lines previously created ?


Oct 22, 2010 at 10:51 AM

Ah I see. In a more generic approach, would it be possible to have a streaming writer? That would solve a lot of memory issues. We currently have a server with 24GB because some of the excel reports have over 500K rows and 50columns, and thus require a lot of memory when writing to xlsx.

Oct 22, 2010 at 10:59 AM
Edited Oct 22, 2010 at 11:00 AM

here is what i am trying to do , but as i said above it doesn't work

include 'PHPExcel/PHPExcel.php';
$objPE=new PHPExcel();

$objPE->getActiveSheet()->SetCellValue('A1','first text');

$second->SetCellValue('A1','second text');



/*new run*/
$objReader = new PHPExcel_Reader_Excel2007();
/*don't all sheets , in order to reduce performance*/

$third->SetCellValue('A1','third text');


Oct 22, 2010 at 11:12 AM


It isn't practical to simply append a new worksheet to an existing xls/xlsx file without reading that file into PHPExcel in its entirety so that it can be rewritten in its entirety. PHPExcel simply isn't designed that way: and I'm not aware of any Excel reader/writer that is. The file structure isn't particularly suitable for working that way, although it could be done with the xlsx files. However, that entail a pretty extensive rewrite of the PHPExcel library, make creating a new workbook from scratch extremely complicated, disabling any functionality for converting workbooks to other formats (e.g. xls to xlsx). Effectively, it would entail completely rewriting PHPExcel as a dedicated xlsx file editor.



I've been contemplating reworking some of the writers (Excel2007 in particular, as it's XML-based) as streaming writers, because it would effectively halve the memory requirements for writing and almost certainly make it faster; but it's a major rewrite of that code that I simply don't have the time for at the moment. I'm hoping that I can target it as a bit of light recreation over the christmas break.

Oct 22, 2010 at 11:19 AM

That would be great. If I could help in some way please let me know, because such a boost would be very beneficial for our application of phpexcel ;)

Oct 22, 2010 at 11:36 AM

thanks for your replies , i hope streaming gets introduced to phpexcel and that it improves performance

i have one last idea i want to try , given tow excel objects and sheets ojb1 {firstsheet} ,obj2 {secondsheet} what is the fastest method

to copy obj2->firstsheet to obj1->secondsheet so that obj1 becomes {firstsheet,secondsheet} ?