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

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

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


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

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


Now, PHPExcel_StyleTool could work like this:

  • returns PHPExcel_StyleTool_Font (think of this as the font section in the style toolbar in Excel)
  • 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

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;

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

Related threads: