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

Tearing appart complex spreadsheets ??

by Wiggins (Hermit)
on Dec 20, 2020 at 16:18 UTC ( #11125500=perlquestion: print w/replies, xml ) Need Help??

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

My last posting here was 3yrs, 3 months, 2 days ago; so please be understanding if my question is simply obvious.

Working with SpreadSheet::Read . Starting with a working sample program and trying to determine how to split the opening of the spreadsheet from subroutines that do the examination and extraction. That is failing.

#!/usr/bin/perl use strict; use warnings; use 5.010; use Spreadsheet::Read qw(ReadData); my $book = ReadData('simple.xlsx'); say 'A1: ' . $book->[1]{A1}; my @row = Spreadsheet::Read::row($book->[1], 1); for my $i (0 .. $#row) { say 'A' . ($i+1) . ' ' . ($row[$i] // ''); } my @rows = Spreadsheet::Read::rows($book->[1]); foreach my $i (1 .. scalar @rows) { foreach my $j (1 .. scalar @{$rows[$i-1]}) { say chr(64+$i) . " $j " . ($rows[$i-1][$j-1] // ''); } } link : https://stackoverflow.com/questions/19782080/how-to-read-data +-from-xlsx-in-perl [2]

Inline is simple. I wanted to test a more complex program with subroutines or modules working on the data structure returned by SpreadSheet::Read(). In every case, code that was outside the scope of the SpreadSheet::Read opening the file, the knowledge of what was at that address seemed to be lost. That makes sense.

#!/usr/bin/perl #https://stackoverflow.com/questions/19782080/how-to-read-data-from-xl +sx-in-perl use strict; use warnings; use 5.010; use Spreadsheet::Read; #my $workbook; # try a global array ref to excel structure -failed ###################################################################### +###### # function input : file in xlsx format with absolute path # function output : prints 1st worksheet content if exist ###################################################################### +###### sub print_xlsx_file{ print ">>print_xlsx_file\n"; my $REFworkbook = shift; my $PageNum; for $PageNum (1..7) { my $worksheet = $REFworkbook->sheet($PageNum); my $max_rows = $worksheet->{'maxrow'}; my $max_cols = $worksheet->{'maxcol'}; print "Stats Page $PageNum ". "$max_rows x $max_cols\n"; } } #===================== MAIN # call above function my $file_path = shift; # passed full path my $workbook = ReadData($file_path,cells => 0 ); my $PageNum=1; if(defined $workbook->[0]{'error'}){ print "Error occurred while processing $file_path:". $workbook->[0]{'error'}."\n"; exit(-1); } print_xlsx_file ($workbook);

As I write this, I am thinking more that it will have to be a huge linear script.

It is always better to have seen your target for yourself, rather than depend upon someone else's description.

Replies are listed 'Best First'.
Re: Tearing appart complex spreadsheets ??
by BillKSmith (Prior) on Dec 21, 2020 at 00:25 UTC
    Welcome back Wiggins:

    The oo methods in your function require an object. Create that object with the method 'new'.

    #my $workbook = ReadData($file_path); my $workbook = Spreadsheet::Read->new($file_path);
    Bill
      Thanks a million. That worked (of course) Thanks!!

      It is always better to have seen your target for yourself, rather than depend upon someone else's description.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11125500]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (4)
As of 2021-05-18 14:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Perl 7 will be out ...





    Results (180 votes). Check out past polls.

    Notices?