Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

Re^4: convert excel cell into "Text" format with Excel:: Write::xlsx modul

by sarf13 (Beadle)
on Oct 19, 2012 at 20:02 UTC ( #1000029=note: print w/replies, xml ) Need Help??

in reply to Re^3: convert excel cell into "Text" format with Excel:: Write::xlsx modul
in thread convert excel cell into "Text" format with Excel:: Write::xlsx modul

please find my test code

#!/usr/bin/perl use strict; use warnings; use Benchmark; my $t0 = Benchmark->new; #use Spreadsheet::WriteExcel; use Excel::Writer::XLSX; my $filePath = 'D:\work\test.txt'; open(FILE,"$filePath"); my $workbook = Excel::Writer::XLSX->new('tab.xls'); my $worksheet = $workbook->add_worksheet(); my $row = 0; while (my $line = <FILE>){ chomp($line); # Split on single tab #print "$line\n"; my @Fld = split('\|', $line); my $cnt = scalar(@Fld); my $col = 0; while ($cnt>= $col){ $worksheet->write_string($row, $col, $Fld[$col]); $col++; } $row++; } my $t1 = Benchmark->new; my $td = timediff($t1, $t0); print "the code took:",timestr($td),"\n";

it giving me this error --- Use of uninitialized value $str in hash element while i am using $worksheet->write_string($row, $col, $Fld[$col]);

its working fine with write subroutine $worksheet->write($row, $col, $Fld[$col]);

Replies are listed 'Best First'.
Re^5: convert excel cell into "Text" format with Excel:: Write::xlsx modul
by roboticus (Chancellor) on Oct 19, 2012 at 20:18 UTC


    It looks like a typical "fencepost" error. If your file has 10 columns, then $cnt will be set to 10. So far, so good. Then you're starting your columns at 0 for the worksheet. Again, that's good.

    But then your while loop tries to write all the way up to column 10 in the spreadsheet, which is the eleventh column in your array. Since it has only 10 columns, it's grabbing an uninitialized value and feeding it to the module.

    You can avoid errors like that by using a different idiom--Let perl detect the end of the array. You just do your part of the operation, namely writing the values to the spreadsheet and increment the column:

    #method 1: Perl will iterate over the array, so we just #increment the counter at each write $worksheet->write_string($row, $col++, $_) for @Fld; #method 2: If you need a loop (so you can do something #else in the code, too), you can do it like: for my $item (@Fld) { $worksheet->write_string($row, $col++, $item); # do something else here... } #method 3: If you don't mind destroying the @Fld array, #you could also: while (my $item = shift @Fld) { $worksheet->write_string($row, $col++, $item); # do something else here... }

    There are a few other ways to approach it, too.

    Note: I've also seen "off by one error", "edge case" and "boundary error" used in place of "fencepost error". I like the term fencepost error because it contains a nice visual: You need 11 fenceposts for 10 sections of fence. It's pretty easy to forget the fencepost that holds up the "other" end of the span when you're counting on each post to be the beginning of one and the end of the previous section.


    When your only tool is a hammer, all problems look like your thumb.

      roboticus thanks for your feedback.

      itís working pretty fine with this feedback solution. I am little worried about using "for" loop. Since my record no would be more than 6 lacks and it won't go out of memory with my system having 4 GB of RAM. Let me test this with high volume of records.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1000029]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (4)
As of 2018-06-19 06:02 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (111 votes). Check out past polls.