VLOOKUP and performance

Topics: User Forum
Jan 10, 2011 at 10:01 AM

I found several topics about performance, so I am not sure, whether it is a good idea to post this. I use PHPExcel mainly to read files created by 3rd parties, containing nothing more than table data (in fact, they are nothing more than CSV-files, but people just send Excel files whatever you tell them to do). One of those files took several minutes to parse, which seemed way to long for a few hundred cells.

After some examination it turned out that:

a) the customer created a formula "=VLOOKUP(E...;$Tabelle3.B$1:C$65536;2)" for a full column in one worksheet

b) used 40 rows of the other worksheet ("Tabelle3") to define a mapping of codes to clear text and

c) PHPExcel is therefore creating a 65535-sized array for each of the 65535 rows in the first worksheet and analyzing the content of each of those cells.

 

There is no way I can prevent anyone from using Excel in this way (in fact, within Excel it seems to be quite clever). I did not dig deep enough into the code to be sure, if and where a cache could be helpful. Most of the work seems to be done in PHPExcel_Calculation::extractCellRange - maybe it would be possible to remember already-calculated ranges there without any side effects?