Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

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?

Thanks!

water

Comment on Spreadsheet::WriteExcel Too Many Formats Problems
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

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.

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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://553196]
Approved by davidrw
help
Chatterbox?
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: (3)
As of 2015-07-05 19:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (67 votes), past polls