FAQ: Why does my Spreadsheet::WriteExcel not load correctly in Excel.
jmcnamara
it to crash?
In general it is hard to create a Spreadsheet::WriteExcel file will not
load in Excel. However, an Excel file is a binary file so it is
possible for it to become corrupt.
The following are the main reasons why this might happen.
1. The file was corrupted in the transfer to a Windows system.
Warning signs: Unix to Windows, Ftp.
Check if the excel file on the generating system is the same size as
the file on the target system. If you use the Windows ftp utility
ensure the transfer mode is set to binary using the "bin" command:
C:\files>ftp someserver
...
ftp> bin
200 Type set to I.
ftp> get myfile.xls
2. The Spreadsheet::WriteExcel file was corrupted by UTF8 data.
Warning signs: Perl 5.6, XML.
This usually happens when using Perl 5.6 and generally in connection
with XML data returned by XML::Parser (or some other parsing module).
The problem occurs due to the fact that data returned from an XML file
is in UTF8 format (even if it looks and prints like ASCII). When added
internally to other strings in Spreadsheet::WriteExcel it coerces them
to UTF8 which in turn causes corruption.
This doesn't happen with Perl 5.005, which doesn't try to handle UTF8
or Perl 5.8, which handles it more elegantly.
See also the Perl-XML faq, "What are some commonly encountered problems
with encodings?":
http://perl-xml.sourceforge.net/faq/#encoding_common
3. A merge format was used in a non-merged cell.
Warning signs: Merged cells, formatting not displaying.
Excel does not allow a format that is used in merged cells to be used
in non-merged cells. Spreadsheet::WriteExcel should shield the end user
from this implementation detail but currently it cannot.
Versions >= 2.13 of the module will warn users of this problem. A
future release will fix it completely.
In the meantime the recommended workaround is to use separate formats
for merged and non-merged cells.
John.
--
Rob Kinyon
Warning signs: A bunch of data
Spreadsheet::WriteExcel does not support workbooks larger than 7MB
total. Use Spreadsheet::WriteExcel::Big or
Spreadsheet::WriteExcel::XML instead.
5. Use of too many formats
Warning signs: Creating formats in a loop
Excel only allows roughly 6000 different formats in a given workbook.
S::WE will create a new format every time you call add_format(), even
if the format you're creating is identical to one you've already
created.
The fix is to create your format outside the loop and reuse it within the loop.
Rob
Valentijn
I'm programming a php/pear of perl excel report application.
I want to use a well formatted excel file that i can update or somebody
else
With Excel itself and leaving existing cells alone etc.
Bot php-pear as perl clears all data when making a new fileobject like
demo.xls etc. While writing to empty cells.... So I want to use a rich
formatted excel template [colors, borders, typo, chart! etc].
So the Excel file changes dynamiccally contentwise.... So no other
changes like I do now....
Do u have an idea?
Valentijn - in...@multimediaal.nl