PHPExcel, Excel worksheet - limit on number of styles?

Question

I'm trying to create an excel file, about 10 worksheets of 3 columns and roughly 30 rows each. I'm trying to highlight some groups of cells on each worksheet by setting some style properties (in addition to a couple cell mergers and column resizing). I'm finding that the document drops the styles after about the 4th worksheet.

My question is: can I do something to increase the number of styles I can apply to my document? Could it be that I am neglecting to do some cleanup? Some setting I'm missing?

I noticed some memory issue questions on SO that seemed related, so I checked the memory limits and tried cacheing. As far as I can tell, that doesn't seem to be the issue (please refute me if I'm wrong though!).

I created a toy example to demonstrate the problem. On my test server, the styles give out on the 3rd worksheet (about 50 applications). Toy example (EDIT: Due to this answer I changed the example a little so that the styles are clearly in disjoint regions).

EDIT: I tried the same thing on a different server (perhaps a slightly newer version of PHPExcel as well) and all the styles appear to be preserved in Excel5 formatted output, even after increasing the complexity and size.

<?php
require_once 'classes/PHPExcel.php';

ini_set('memory_limit','64M'); // The default memory_limit in php.ini is at least this as well
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings=array( 'memoryCacheSize'=>'32MB');
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

$o = new \PHPExcel();

$style1 = array(
'fill'=>array(
   'type'=> \PHPExcel_Style_Fill::FILL_SOLID,
   'color'=>array('rgb'=>'CCFFCC'),
),
'font'=>array( 
    'size'=>17, 
    'name'=>'Calibri Light',
    'bold'=>false
),
);
$style2 = array(
'fill'=>array(
   'type'=> \PHPExcel_Style_Fill::FILL_SOLID,
   'color'=>array('rgb'=>'FFCCCC'),
),
'font'=>array( 
    'size'=>17, 
    'name'=>'Calibri Light',
    'bold'=>false
),
);
$maxws = 10;
$maxrow=40;
for ($ws=0;$ws<$maxws;$ws++){
$o->setActiveSheetIndex($ws);
$o->getActiveSheet()->setTitle("TEST $ws");
for ($row=1;$row<$maxrow; $row++){
    if ($row % 2){
           $o->getActiveSheet()
            ->getCell("A$row")
            ->setValue("Styled!");
           $o->getActiveSheet() ->getStyle("A$row") ->applyFromArray($style1);
    }else{
           $o->getActiveSheet()
            ->getCell("A$row")
            ->setValue("Default");
    }
       $o->getActiveSheet()
            ->getCell("B$row")
            ->setValue("Default");
    if ( ! ($row % 2)){
           $o->getActiveSheet()
            ->getCell("C$row")
            ->setValue("Other style!");
           $o->getActiveSheet() ->getStyle("C$row") ->applyFromArray($style2);
    }else{
           $o->getActiveSheet()
            ->getCell("C$row")
            ->setValue("Default");
    }
}
if ($ws+1<$maxws) $o->createSheet($ws+1);
}
//echo "Peak memory usage ". (memory_get_peak_usage(true)/1024/1024) . " MB\r\n"; die();
$filename = 'export_test.xls';
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$filename.xls\"");
header("Cache-Control: max-age=0");
$objWriter = \PHPExcel_IOFactory::createWriter($o, "Excel5");
$objWriter->save("php://output");
exit;

Answer 1

Style limitations for OfficeOpenXML .xlsx files (Excel2007 Writer):

• Unique cell formats/cell styles: 64,000
• Fill styles: 256
• Line weight and styles: 256
• Unique font types: 1,024 global fonts available for use; 512 per workbook Number formats in a workbook: Between 200 and 250, depending on the language version of Excel that you have installed

Style limitations for BIFF .xls files (Excel5 Writer):

• Colours in a workbook: 56
• Cell styles in a workbook: 4,000
• Custom number formats: Between 200 and 250, depending on the language version of Excel you have installed.

Where possible, try to set styles in PHPExcel for a range of cells, rather than for individual cells; so rather than do

$o->getActiveSheet() ->getStyle("A$row") ->applyFromArray($style);

in your for loop, do

$o->getActiveSheet() ->getStyle("A1:A$maxrow") ->applyFromArray($style);

after the for loop has finished

Answer 2

This is not a complete answer but solves the issue for me.

The problem was the export format "Excel5" in the createWriter factory method. Switching to "Excel2007" fixes the issue (the styles I expect to see appear correctly). This works both for my real application and the toy example.

In code:

header("Content-Disposition: attachment; filename=\"$filename.xlsx\"");
header("Cache-Control: max-age=0");
$objWriter = \PHPExcel_IOFactory::createWriter($o, "Excel2007");