Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
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 cooling their heels in the Monastery: (4)
As of 2014-10-25 11:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (143 votes), past polls