Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Spreadsheet::WriteExcel - using formats with multiple books

by nop (Hermit)
on Dec 06, 2000 at 19:09 UTC ( #45224=perlquestion: print w/replies, xml ) Need Help??
nop has asked for the wisdom of the Perl Monks concerning the following question:

Recently, I've run into some strange behavior when I use formats with multiple books. It seems that formats applied to subsequent books do not have any effect. Here's a simple case showing the behavior:
use strict; use Spreadsheet::WriteExcel; { my $workbook = Spreadsheet::WriteExcel->new("one.xls"); my $north = $workbook->addworksheet("one"); my $format = $workbook->addformat(); $format->set_bold(); $north->write(0, 0, "One -- this should be bold", $format); } { my $workbook = Spreadsheet::WriteExcel->new("two.xls"); my $north = $workbook->addworksheet("two"); my $format = $workbook->addformat(); $format->set_bold(); $north->write(0, 0, "Two -- this too should be bold", $format); }
In this example, each spreadsheet lives in its own scope, so they should not interact. What happens in this example on my machine that the format (in this case, bold) does not have any effect on the second sheet. This is true for more complicated formats, applied to more cells, etc, but this is a minimal case showing the behavior.

Anyone have any suggestions? Am I missing something here? Thanks!

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel - using formats with multiple books (Fixed)
by MeowChow (Vicar) on Dec 07, 2000 at 00:24 UTC
    After toying around with the code for this module for a bit, I've fixed the problem, hopefully without introducing any new bugs (my understanding of the module is not very complete). This bug is caused by the "class data" at the beginning of and The data is actually per-workbook data, but the author of the module mistakenly stored it into class variables, thus applying it to the set of all worksheets and formats. I've made this mistake so many times myself, it was quite easy to recognize :) Anyway, I've whipped up a quick patch to get things working. One caveat with the patch though, is that you have to explicitly close() your workbooks, because the order in which objects get DESTROY'd is random.
    diff -r c:\perl\site\lib\Spreadsheet/ C:\Perl\CPAN\Spreadshee +t-WriteExcel-0.22/ 32,33c32,33 < # my $xf_index = 15; < # my $font_index = 4; --- > my $xf_index = 15; > my $font_index = 4; 47,49c47 < _workbook => $_[1], < _xf_index => $_[1]->{_xf_index}, #MEOW < _font_index => $_[1]->{_font_index}, #MEOW --- > _xf_index => $xf_index, 50a49 > _font_index => $font_index, 89,90c88,89 < $self->{_workbook}{_xf_index}++; < $self->{_workbook}{_font_index}++; --- > $xf_index++; > $font_index++; diff -r c:\perl\site\lib\Spreadsheet/ C:\Perl\CPAN\Spreadsh +eet-WriteExcel-0.22/ 38,39d37 < bless $self, $class; < 49,52c47,48 < $self->{_xf_index} = 15; < $self->{_font_index} = 4; < $self->{_tmp_worksheet} = Spreadsheet::Worksheet->new('', 0, 0, + $self); < $self->{_tmp_format} = Spreadsheet::Format->new($self); --- > $self->{_tmp_worksheet} = Spreadsheet::Worksheet->new('', 0, 0) +; > $self->{_tmp_format} = Spreadsheet::Format->new(); 55a52 > bless $self, $class; 133d129 < $self, #MEOW 152c148 < my $format = Spreadsheet::Format->new($self); --- > my $format = Spreadsheet::Format->new(); diff -r c:\perl\site\lib\Spreadsheet/ C:\Perl\CPAN\Spreads +heet-WriteExcel-0.22/ 32,33c32,33 < #MEOW my $active_sheet = 0; < #MEOW my $first_sheet = 0; --- > my $active_sheet = 0; > my $first_sheet = 0; 53d52 < $self->{_workbook} = $_[3]; #MEOW 200c199 < $self->{_workbook}{_active_sheet} = $self->{_index}; #MEOW --- > $active_sheet = $self->{_index}; 212,214c211 < my $self = shift; #MEOW < < $self->{_workbook}{_active_sheet}; #MEOW --- > return $active_sheet; 229c226 < $self->{_workbook}{_first_sheet} = $self->{_index}; #MEOW --- > $first_sheet = $self->{_index}; 241,243c238 < my $self = shift; < < $self->{_workbook}{_first_sheet}; #MEOW --- > return $first_sheet; 341c336 < if ($self->{_workbook}{_active_sheet} == $self->{_index}) { --- > if ($active_sheet == $self->{_index}) {

      I put a comment in the code that said "# Class Data". perl mustn't have read it. ;-)

      I patched this bug a few days ago but I haven't uploaded it because I was busy adding other bugs. s/bugs/features/.

      I hope to upload a new version of Spreadsheet::WriteExcel this weekend. The main addition will be customisable numerical formats. The problem with close() and DESTROY is explained in more detail in the updated documentation.

Re: Spreadsheet::WriteExcel - using formats with multiple books
by Anonymous Monk on Nov 11, 2004 at 15:27 UTC
    Can I run this code in CGI? Well, I did it but I don't know where was the file .xls created? Can yo tell me .. please..

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://45224]
Approved by root
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (2)
As of 2018-08-17 00:17 GMT
Find Nodes?
    Voting Booth?
    Asked to put a square peg in a round hole, I would:

    Results (174 votes). Check out past polls.