Solution to memory and speed problems: Introduce Style tool object and concept of cell selection

Topics: Developer Forum
Developer
Nov 12, 2008 at 10:56 PM
Edited Nov 12, 2008 at 11:11 PM
Symptom:
PHPExcel uses large amounts of memory and large spreadsheets are slow to process.

Reason:
Inefficient use of shared styles, shared fonts, shared number formats etc.

Idea for solution not breaking API:
  1. Make it possible in PHPExcel to specify which cells are currently selected in the sheet.
  2. Introduce something like PHPExcel_StyleTool (think of this as the style toolbar in Excel)
  3. Modify various methods including getStyle()
  4. Use shared styles throughout PHPExcel

1)
$objWorksheet->setCellSelection('A1:C3');

Note, we already have method to set the single active cell setSelectedCell('A1'), but the above is not the same.

2)
class PHPExcel_StyleTool {
    // basically same methods as PHPExcel_style, this is the key point

    function getFont() {
        return PHPExcel_StyleTool_Font;
    }

    function getFill() {
        return PHPExcel_StyleTool_Fill;
    }
    ...
}

together with similar classes

PHPExcel_StyleTool_Borders
PHPExcel_StyleTool_Font
PHPExcel_StyleTool_Fill
etc.

Now, PHPExcel_StyleTool could work like this:

PHPExcel_StyleTool->getFont()
  • returns PHPExcel_StyleTool_Font (think of this as the font section in the style toolbar in Excel)
    
PHPExcel_StyleTool->getFont()->getName()
  • returns what one would see in Excel in the style toolbar, namely the font name of the active cell
    
PHPExcel_StyleTool->getFont()->setName('Times New Roman')
  • set the font name for all cells in the current selection (just like in Excel)
  • this will usually spawn the creation of one new PHPExcel_Style object, one new PHPExcel_Font object etc. shared by the selected cells
  • at this point, some logic takes place to detect which new objects need to be created / removed, PHPExcel_Style, PHPExcel_Style_Font etc

3)
Critical modification to getStyle() method. Instead of returning PHPExcel_Style, now let it return PHPExcel_StyleTool

function getStyle($cell) {
    $this->setSelectedCell($cell); // set 'A1' as the active cell

    return PHPExcel_StyleTool;
}


4)
Update readers so they result in PHPExcel objects using shared styles so memory consumption is reduced. Writers should also speed up.

Related threads:
http://www.codeplex.com/PHPExcel/Thread/View.aspx?ThreadId=25879
http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=6370
http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=6857