in reply to Re: Perl 2 Excel
in thread Perl 2 Excel

I think you're running out of space in whatever table is used internally for ->add_format(). Compare the results of these two files:

#!/usr/bin/perl

use warnings;
use strict;
use Spreadsheet::WriteExcel;

many_formats: {
    my $wb = Spreadsheet::WriteExcel->new('output_many.xls');
    my $sheet = $wb->add_worksheet("overview");
    foreach my $row (0..100) {
        local $\ = "\n";
        print my $line = join('', map { ('A'..'Z')[rand 26] } 0 .. ran
+d 16);
        my $header1 = $wb->add_format(bold=>0, color=>'green', size=>1
+0, merge=>0);
        $sheet->set_column(1,1,30);
        $sheet->write($row, 1, $line, $header1);
    }
    undef $wb;
}

shared_format: {
    my $wb = Spreadsheet::WriteExcel->new('output_shared.xls');
    my $header1 = $wb->add_format(bold=>0, color=>'green', size=>10, m
+erge=>0);
    my $sheet = $wb->add_worksheet("overview");
    foreach my $row (0..100) {
        local $\ = "\n";
        print my $line = join('', map { ('A'..'Z')[rand 26] } 0 .. ran
+d 16);
        $sheet->set_column(1,1,30);
        $sheet->write($row, 1, $line, $header1);
    }
    undef $wb;
}

[download]

The only difference between the code is that in the first, like you, I create a new format for every row. That stops formatting after about 40 rows. For the second, I create one format, and apply it to every row... and when I open output_shared.xls in Excel, all the rows are properly formatted.

(Also, since the documentation shows ->add_format(), that's what I used here, instead of the ->addformat() you used, though it didn't seem to affect my experiments, that I noticed.)

Replies are listed 'Best First'.
Re^3: Perl 2 Excel
by Aldebaran (Curate) on Aug 21, 2018 at 21:11 UTC

    I like responses like yours, which take the OP's code and try to breathe some life into it. In particular, you take some aim at providing some data set to work off of, which is critical if OP wants others to replicate their post. One thing I like about usenet is that we tried to do that on threads. (One thing I don't like about usenet was the meanspiritness and increased domination by topic trolls. I let my subscription lapse.)

    I did not see any difference in the 2 docs that pryrt's script produces. Everything seemed to be green. What follows are abridged output and then source:

     
$ ./1.excel.pl 
TUAROKUNSDSIG
JLUXOBCQYAIXDS
YAKB
...
UA
OJGFXQYPVIVO
UD
IABE
$ cat 1.excel.pl 
#!/usr/bin/perl -w

use 5.011;
use Spreadsheet::WriteExcel;

many_formats: {
    my $wb = Spreadsheet::WriteExcel->new('1.output_many.xls');
    my $sheet = $wb->add_worksheet("overview");
    foreach my $row (0..100) {
        local $\ = "\n";
        print my $line = join('', map { ('A'..'Z')[rand 26] } 0 .. ran
+d 16);
        my $header1 = $wb->add_format(bold=>0, color=>'green', size=>1
+0, merge=>0);
        $sheet->set_column(1,1,30);
        $sheet->write($row, 1, $line, $header1);
    }
    undef $wb;
}

shared_format: {
    my $wb = Spreadsheet::WriteExcel->new('1.output_shared.xls');
    my $header1 = $wb->add_format(bold=>0, color=>'green', size=>10, m
+erge=>0);
    my $sheet = $wb->add_worksheet("overview");
    foreach my $row (0..100) {
        local $\ = "\n";
        print my $line = join('', map { ('A'..'Z')[rand 26] } 0 .. ran
+d 16);
        $sheet->set_column(1,1,30);
        $sheet->write($row, 1, $line, $header1);
    }
    undef $wb;
}
__END__

    [download]

    My question is: given OP's script, what qualifies as a well-conditioned data set for this problem? OP says that his input is the output of a program: why is it unpostable between readmore tags?

[reply]
[d/l]

      Thanks.

      Like I said, I could reproduce the issue after about 40 rows: ie, first 40ish rows were green, the other 60ish were not formatted at all. It may be that it's different on different machines (32 vs 64bit Win; 32 vs 64bit Excel; Excel version; Spreadsheet::WriteExcel version, etc1). In this post, for example, Eily was able to "reproduce the defect with 300 lines of dummy data", so it may take a different number of rows for you or someone else.

      Since I was able to replicate the problem with 100 lines of random data, I didn't put much thought into "well-conditioned". But yes, for SSCCE-purposes, the OP should have been able to post example data in <readmore>data</readmore> tags. But given that the newerlbie has declared that the issue is fixed, apparently the OP was on a system where it was hitting a similar limit to what Eily and I found.

      And really, there's no good reason to have the $header1 format inside the loop, so putting it outside is arguably "better", even if you haven't hit the limit (ie, avoiding defining a large number of items which are all identical)

      1: My experiment was run with Spreadsheet::WriteExcel v2.40, Strawberry Perl v5.26.2 64bit, Win10.0.16299.611 64bit, Office 365 Excel 2016 MSO 16.0.9126.2259 32-bit

[reply]
[d/l]
[select]

        Actually I got the issue after 42 lines on my 300 lines input (just a bunch of lines containing a single letter). So the same as you. I didn't put much thought into the number I gave beyond the fact that 300 is pretty small (ie, it's not a defect that happens with absurdly large input data)

[reply]
Re^3: Perl 2 Excel
by newperlbie (Acolyte) on Aug 21, 2018 at 13:37 UTC
    yes,thanks again,moving the format out of the loop solved the issue!
[reply]
      Do you understand why it solves the issue?


      holli

      You can lead your users to water, but alas, you cannot drown them.
[reply]
[d/l]
        excel has this limitation that it cant support that many formats if I am trying to generate one each time in the loop? correct?
[reply]

In Section Seekers of Perl Wisdom