Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Re: Spreadsheet::ParseExcel vs alternative Cell Handler

by jmcnamara (Monsignor)
on Jan 20, 2005 at 16:54 UTC ( #423738=note: print w/replies, xml ) Need Help??

in reply to Spreadsheet::ParseExcel vs alternative Cell Handler

What I am trying to do is just use Perl to convert a .xls table to a tab-delimited .txt file.

Writing a converter that uses the cell_handler() callback is trickier than writing a converter based on the standard interface because you have to keep track of your position in the workbook yourself.

Here is a sample xls2tab converter.

#!/usr/bin/perl -w use strict; use Spreadsheet::ParseExcel; use Data::Dumper; my $parse_excel = new Spreadsheet::ParseExcel(CellHandler => \&cell_ha +ndler, NotSetCell => 1); my $prev_index = -1; my $prev_row = 0; my $prev_col = 0; my $workbook = $parse_excel->Parse('book1.xls'); # Define our own cell handler to reduce S::PE's memory use. # This function will be called each time a cell is encountered. # However, it will ignore blank cells so we have to keep track # of our location in the worksheet so that we can pad out blank # cells and rows. # sub cell_handler { my $workbook = $_[0]; my $sheet_index = $_[1]; my $row = $_[2]; my $col = $_[3]; my $cell = $_[4]; # Only process the first worksheet if ($sheet_index > 0) { $workbook->ParseAbort(1); return; } # Reset the col counter between rows $prev_col = 0 if $row != $prev_row; # Add tabs between fields and newlines between rows. Also pad # any missing rows or columns. # print "\n" for $prev_row +1 .. $row; print "\t" for $prev_col +1 .. $col; # Print the formatted value of the cell print $cell->{_Value}; # Keep track of where we are $prev_row = $row; $prev_col = $col; }


Replies are listed 'Best First'.
Perfect now!
by Andre_br (Pilgrim) on Jan 22, 2005 at 19:35 UTC
    Hey John,

    Thanks a lot for the help. Itīs working just perfect now. The only complain is not mine. Itīs Perlīs, that keeps saying all the time:

    "Variable "$prev_col" will not stay shared at methods.cgi line 524. Variable "$prev_row" will not stay shared at methods.cgi line 524".

    This .cgi is an auxiliar script where Iīve put our code. I call it, as usual, with "require methods.cgi" and call the sub parse_xls() with two arguments - the names of the $infile and $outfile. The conversion is running smoothly, (even with the 15k lines file it was crashing before your cell handler! Great!) but Perl is now complaining with this message every time I run the code. I thought putting a "return 1" would calm down Perl, I say, tell her I am not worried about these variables staying shared, but it didnīt change anything.

    Any ideas?



      This is one of Perl's stranger messages, but it is a common one. I assume one of the following is true
      • You are requireing this code from within the body of a subroutine in your main code
      • You are running it with mod_perl under Apache::Registry
      The result is that you are declaring a nested subroutine within another one, and the inner sub accesses a lexical variable declared in the outer one. This is a bad thing, because Perl will not be able to figure out which "instance" of that lexical variable the inner sub should be working with when you call it. Solutions to this problem are explained here.
        Hey, Errto.

        Thatīs the first one, in fact! Thanks for the help!


      Checking out perldoc perldiag can be really handy for these. As you don't have 524 lines of code here, I can't really be sure what it's referring to, but I find the diagnostic info to be incredibly useful. So useful that our coding standards say "use diagnostics" must be in our perl code, up with "use strict" and "use warnings".

Re^2: Spreadsheet::ParseExcel vs alternative Cell Handler
by Anonymous Monk on Apr 09, 2007 at 19:54 UTC
    Hi John, I am new in Unix and I am trying to convert xls to tab delimited file. I have installed SpreadSheet ParseExcel but I don`t have any utility to work accordingly. Please suggest me at Thanks very much.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://423738]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2021-09-16 10:43 GMT
Find Nodes?
    Voting Booth?

    No recent polls found