| Subject: | Writing a long formula (>255 chars) to the same cell twice causes Excel crash |
$ perl -v
This is perl, version 5.005_03 built for aix
$ uname -a
AIX 59H 3 4 0009388AA100
$ oslevel
4.3.3.0
Hi, I currently use the Spreadsheet::WriteExcel module to convert some manually produced HTML reports into Excel format - I've come across a problem when trying to write long strings to a cell repeatedly.
e.g.
$test = $worksheet->write(1,1,long_string($str));
$test = $worksheet->write(1,1,long_string($str));
When trying to load the produced spreadsheet using Excel, I consistently get a memory error, and a prompt crash of Excel (with "The instruction at 0x301ee91e referenced memory at 0x00000000. The memory could not be read").
I've attached a test perl script which should show this in more detail.
I'm attempting to create a workaround (i.e. Make sure I only write long strings once!), but this may be tricky considering my source information.
#!/usr/bin/perl -w
# Converts a HTML file into Excel format. (badly)
# Author: Jonathan Daniel - jon.daniel@intercity-comms.com
# Version: 1.0
# Date: 30/08/02
# Description:
# Takes a html file, and converts it into an excel file.
# Designed for where I have good control over the html file being produced,
# and if your data is not organised into tables (or the tables are nested)
# then it'll produce rather unsatisfactory results.
#
# Version 1.1, 07/05/03
# "use strict".
# Occasionally HTML::Parser would decide it was only going to spit out half
# of the detail between <td> tags, so I've had to buffer to prevent data loss.
# Now recognises the th tag, and handles hr better.
# Space stripping on number columns improved.
#
use strict;
use Spreadsheet::WriteExcel;
# define variables:
my $outputfile = "";
# Parse arguments
if(@ARGV < 1)
{
print "test.pl outputfile";
exit(1);
}
$outputfile = $ARGV[0];
# Define variables
# Setup spreadsheet
my $workbook = Spreadsheet::WriteExcel->new($outputfile);
my $prevtext = "";
my $worksheet = $workbook->addworksheet();
$worksheet->hide_gridlines(2);
# Initialise very long string...
$prevtext = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE";
# Attempt to write
my $test = 0;
$test = $worksheet->write_string(1,1,$prevtext);
if($test == -3)
{
# string was too long to be written use long string instead to write as a formula...
$test = $worksheet->write(1,1,long_string($prevtext));
}
# Ok, now try writing to the same cell again, with the same long string
# Excel will now crash when trying to load this spreadsheet
$test = $worksheet->write(1,1,long_string($prevtext));
# Note that my source sometimes splits large strings up, so I have to rewrite a
# cell occasionally - rewriting works fine, but not for long strings - I think
# most formulas are OK though, though I don't tend to use them except for
# long_string()
# Also fails, just attempting to write a long string twice - e.g.
#$test = $worksheet->write(1,1,long_string($prevtext));
#$test = $worksheet->write(1,1,long_string($prevtext));
# Note the following double write does not crash Excel!
#$test = $worksheet->write(2,2,"=A1");
#$test = $worksheet->write(2,2,"=A1");
#----------------------------------------
#sub tag
#{
# my($tag, $num, $attr) = @_;
# $inside{$tag} += $num;
# if(@_ eq "table")
# {
# $intable++;
# }
# if(@_ eq "/table")
# {
# $intable++;
# }
# if($tag eq "td")
# {
# if($tdcheck == 0)
# {
# $col++;
# if( $col > $maxcol)
# {
# $maxcol = $col;
# }
# $tdcheck++;
# }
# else
# {
# $tdcheck--;
# }
# }
# if($tag eq "br")
# {
# $row++;
# $col=0;
# }
# if($tag eq "tr")
# {
# if($col >= 1)
# {
# $row++;
# $col = 0;
# }
# }
# if($tag eq "th")
# {
# if($col >= 1)
# {
# $row++;
# $col = 0;
# }
# }
#
# if($tag eq "hr")
# {
# $row++;
# $col = 0;
## apply changes to column using format11
# $worksheet->set_row($row, undef, $format11);
# $row++;
# }
#
#
# if($tag eq "b")
# {
# return;
# }
#
# if($tag eq "td")
# {
# $prevtext = "";
# if($attr){
# $fieldtype = 0;
# if(exists($attr->{style}))
# {
# if($attr->{style} eq "vnd.ms-excel.numberformat:@")
# {
# $fieldtype = 1;
# }
# if($attr->{style} eq "vnd.ms-excel.numberformat:0")
# {
# $fieldtype = 2;
# }
# if($attr->{style} eq "vnd.ms-excel.numberformat:0.00")
# {
# $fieldtype = 3;
# }
# if($attr->{style} eq "vnd.ms-excel.numberformat:dd/mm/yy")
# {
# $fieldtype = 4;
# }
# }
# }
# }
#}
#
#sub text
#{
# return if(substr($_[0],0,9) eq "<!DOCTYPE");
# return if $inside{script} || $inside{style} || $inside{title};
# return if ($_[0] !~ /\S/);
# my $subs = "";
# my $test = 0;
# my $test2 = 0;
#
# if ( $inside{table} )
# {
# $prevtext = $prevtext . $_[0];
# if( $inside{b} )
# {
# if($fieldtype == 0)
# {
# $worksheet->write($row,$col,$prevtext, $format1);
# }
# if($fieldtype == 1)
# {
# $worksheet->write($row,$col,$prevtext, $format1);
# }
# if($fieldtype == 2)
# {
# $prevtext =~ s/ //g;
# $worksheet->write($row,$col,$prevtext, $format8);
# }
# if($fieldtype == 3)
# {
# $prevtext =~ s/ //g;
# $worksheet->write($row,$col,$prevtext,$format9);
# }
# if($fieldtype == 4)
# {
# $worksheet->write($row,$col,$prevtext,$format10);
# }
#
# if ($colarray[$col] < length($prevtext))
# {
# $colarray[$col] = length($prevtext);
# }
# }
# else
# {
# if($fieldtype == 1)
# {
# $test = $worksheet->write_string($row,$col,$prevtext);
# if($test == -3)
# {
# print "string too long - ";
# $test = $worksheet->write($row,$col,long_string($prevtext));
# print $test . " " . long_string($prevtext);
# }
# }
# if($fieldtype == 0)
# {
# $worksheet->write($row,$col,$prevtext);
# }
# if($fieldtype == 2)
# {
# $prevtext =~ s/ //g;
# $worksheet->write($row,$col,$prevtext,$format5);
# }
# if($fieldtype == 3)
# {
# $prevtext =~ s/ //g;
# $worksheet->write($row,$col,$prevtext,$format6);
# }
# if($fieldtype == 4)
# {
# $prevtext =~ s/ //g;
# $worksheet->write($row,$col,$prevtext,$format7);
# }
# if ($colarray[$col] < length($prevtext))
# {
# $colarray[$col] = length($prevtext);
# }
# }
# }
# else
# {
# $subs = $_[0];
# $subs =~ s/\n//g;
# if( $inside{h1} || $inside{h2} || $inside{h3} || $inside{h4} )
# {
# if( $inside{h1} )
# {
# $col = 0;
# $worksheet->write($row,$col,$subs, $format2);
# $row+=2;
# }
# if( $inside{h2} )
# {
# $col=0;
# $worksheet->write($row,$col,$subs, $format3);
# $row+=2;
# }
# }
# else
# {
# $col = 0;
# $worksheet->write($row,$col,$subs);
# $row++;
# }
# }
#}
######################################################################
#
# long_string($str)
#
# Converts long strings into an Excel string concatenation formula.
# The concatenation is inserted between words to improve legibility.
#
# returns: An Excel formula if string is longer than 255 chars.
# The unmodified string otherwise.
#
sub long_string {
my $str = shift;
my $limit = 255;
# Return short strings
return $str if length $str <= $limit;
# Split the line at word boundaries where possible
my @segments = $str =~ m[.{1,$limit}$|.{1,$limit}\b|.{1,$limit}]sog;
# Join the string back together with quotes and Excel concatenation
$str = join '"&"', @segments;
# Add formatting to convert the string to a formula string
return $str = qq(="$str");
}