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

Strange behaviour (Spreadsheet::WriteExcell)

by hawtin (Prior)
on Feb 28, 2005 at 10:14 UTC ( #435006=perlquestion: print w/replies, xml ) Need Help??

hawtin has asked for the wisdom of the Perl Monks concerning the following question:

Fellow monks: I am getting some behaviour that appears impossible to me. I have a script that is producing an Excell spreadsheet as follows:

# Send the last table in a form that Excel can use my($quest) = @_; my $buffer = ""; my $fh = new IO::Scalar \$buffer; my $book = Spreadsheet::WriteExcel->new($fh); my $sheet = $book->add_worksheet("data"); my %formats; ...Removed Code adds some formats with calls like... foreach my $f (list_formats()) { my $format = $book->add_format(); $format->set_bg_color('yellow') if($f =~ /yellow/i); $formats{$f} = $format; } for(my $y=0;$y<=$#last_table;$y++) { for(my $x=0;$x<=$#{$last_table[$y]};$x++) { my $val = ${$last_table[$y]}[$x]; my $format_name = "cell"; ...Work out the format name... my $r; $r = $sheet->write_string($y,$x, $val,$formats{$format_name}); # $r = $sheet->write_string($y,$x, # "$y,$x: Test "."1234567890"x30, # $formats{$format_name}); } } $book->close(); return $buffer;

As written the resulting spreadsheet will not open in Excell. If I grab only the first 136 characters of the string it works, if I send a long string (as shown in the commented out section) it works.

I have dumped the $val text out and there are no wide characters in it (i.e. it only contains bytes in the range 0x20-0x7A. I have written the data directly to a file (rather than via an IO::Scalar) and it behaves the same.

What is going on? What have I missed?

Replies are listed 'Best First'.
Re: Strange behaviour (Spreadsheet::WriteExcell)
by jmcnamara (Monsignor) on Feb 28, 2005 at 11:09 UTC

    There doesn't seem to be anything wrong in the code that you have shown.

    However it is hard to tell from just this code. It would be better if you could post a fully working example that demonstrates the problem. Use the bug_report program in the standard Spreadsheet::WriteExcel distro as an example.

    The main reasons that an Spreadsheet::WriteExcel file will fail to load in Excel are:

    • The file was corrupted in the transfer to a Windows system. Check if the excel file on the generating system is the same size as the file on the target system.
    • The Spreadsheet::WriteExcel file was corrupted by UTF8 data (sometimes happens in conjunction when extracting data from a XML file with Perl 5.6).
    • A merge format was used in a non-merged cell.


      Unfortunately the script is too big (and commercially sensitive) to easily produce a publishable version. As you can see from my comments I don't have enough of an understanding of the problem to be able to submit a bug report that would be of much help.

      The data being output did originate in an XML file, and the script is running here under 5.6, so your second suggestion looks like a good candidate. As I said I suspect some kind of utf8 type issue but I cannot see anything that looks wrong.

      Anyway I have found a fix, in my original code just before the write_string() I have inserted:

      if(length($val) > 100) { # For some reason we have to duplicate # the contents of the string via a process # that detaches from the original variable # if the string is longer than 136 characters # (I don't know why) my $t = pack("c".length($val), unpack("c".length($val),$val)); $val = $t; }

      Now it all seems to work :-O

        Yes, if that code fixes the problem then it is definitely a UTF8 issue. The Spreadsheet::WriteExcel docs also suggest the following workaround in the section Working with XML:
        $new_str = pack 'C*', unpack 'U*', $utf8_str;


        > the script is running here under 5.6

        Perhaps you could update to a modern version of Perl? There are many many utf8 bugs and problems fixed in the meantime.

Re: Strange behaviour (Spreadsheet::WriteExcell)
by hawtin (Prior) on Feb 28, 2005 at 10:55 UTC

    Further clarification: Just to test this out I have now changed the write_string() code to:

    if($y == 1 && $x == 1) { # In my test case only cell (1,1) causes a # problem my $t = "...Long string with the value I expect..."; if($val eq $t) { print "Equal\n"; } else { print "NOT EQUAL\n"; } if($fail_to_open) { $r = $sheet->write_string($y,$x, $val,$formats{$format_name}); } else { $r = $sheet->write_string($y,$x, $t,$formats{$format_name}); } } else { $r = $sheet->write_string($y,$x, $val,$formats{$format_name}); }

    The program asserts that $val and $t are eq. The spreadsheet fails if I use $val and works if I use $t.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://435006]
Approved by holli
Front-paged by grinder
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (2)
As of 2023-01-28 03:24 GMT
Find Nodes?
    Voting Booth?

    No recent polls found