Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Spreadsheet::WriteExcel Too Many Formats Problems

by water (Deacon)
on Jun 02, 2006 at 01:14 UTC ( #553196=perlquestion: print w/ replies, xml ) Need Help??
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?

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://553196]
Approved by davidrw
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (12)
As of 2015-12-01 22:35 GMT
Find Nodes?
    Voting Booth?

    My keyboard shows this many letters:

    Results (29 votes), past polls