Spreadsheet::WriteExcel Too Many Formats Problems

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?

Thanks!

water

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. HTH -enlil	[reply]
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. -- John.	[reply]
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: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]