water has asked for the wisdom of the Perl Monks concerning the following question:

Hi --

I am using Spreadsheet::WriteExcel to make a sheet with several tabs, each tab with many populated cells, and many cells with multiple formats (color, currency, etc).

I am applying the formats cell-wise, not row- or col- or region-wise.

If the sheet gets too big, it seems I am overflowing some sort of Excel max-num-formats-applied limit: an excel warning error gets issued on spreadsheet open, and the resulting sheet 'loses' formatting somewhere along the way in one of the later tabs.

Has anyone else encountered this over-use of Excel format problem, and have any suggestions to remedy it?



  • Comment on Spreadsheet::WriteExcel Too Many Formats Problems

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel Too Many Formats Problems
by Enlil (Parson) on Jun 02, 2006 at 01:27 UTC
    Are you hitting the 4000 format limit?

    There are some suggestions there to limit it.



Re: Spreadsheet::WriteExcel Too Many Formats Problems
by jmcnamara (Monsignor) on Jun 02, 2006 at 08:10 UTC

    Excel has a limit of approximately 4000 different cell formats, as pointed out above.

    I'd guess that you probably don't have 4000 unique cell formats in your workbook but instead have a large number of duplicate formats that are being generated by add_format() in a loop.

    The solution is to create your main formats as global objects and then reuse them as required rather then creating a new duplicate format each time.

    A future release of Spreadsheet::WriteExcel will remove duplicates automatically.


Re: Spreadsheet::WriteExcel Too Many Formats Problems
by dragonchild (Archbishop) on Jun 02, 2006 at 14:05 UTC
    Excel::Template correctly handles this by reusing formats wherever possible. You might want to look at that.

    The key item is that Spreadsheet::WriteExcel doesn't merge identical formats because you can change what a format does after you've applied it to a cell.

    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?