Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

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
[stevieb]: atcroft: "Make both hands into fists..."... is something my Ma taught me in our native lang, but I was to ignorant and young to pay attention. Thanks for that :)
[atcroft]: stevieb: Sad to say that I only recently learned that particular trick, but I have since found it very useful.... :)
[james28909]: ill be back with a solution eventually
[stevieb]: it's a reminder to re-inforce it :P
[atcroft]: james28909: That particular questions was a bit of trick, actually (depending on the country you are in). More interesting is, if you are trying to subtract from an epoch time, for instance, you might have to consider when/if DST occurs for a location,
[atcroft]: because you may have to adjust the number of seconds you change from an epoch from 86400 (not to mention leap seconds)....
[atcroft]: james28909: Although if your program is using a database, you might be able to "pass the buck" to the database and ask it to do the date change for you....

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (3)
As of 2017-04-29 04:37 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (531 votes). Check out past polls.