1

My code works great for the XLSX file but it breaks at the XLSM input file.

Requirements

  1. Read Excel's whole row rather than cell value.
  2. I don't want to use my $parser = Spreadsheet::ParseXLSX->new(); my $workbook = $parser->parse($prvs_excel_file); because it only parses cell value.
#!/home/utils/perl
use strict;
use warnings;

sub usage { die "usage: $0 file.xlsx name [out.csv]\n"; }

my $xls = shift or usage;
my $sht = shift or usage;
my $out = shift // $xls;
   $out =~ s/\.xlsm$/.csv/i;

use Spreadsheet:: Read;
use Text::CSV_XS;

my $csv = Text::CSV_XS->new ({ binary => 1, eol => "\r\n", auto_diag => 1 });
open my $fh, ">", $out or die "$out: $!\n";

my $book  = ReadData ($xls) or die "$xls: $!\n";

my $sheet = $book->[$book->[0]{sheet}{$sht}];

foreach my $row (1 .. $sheet->{maxrow}) {
    $csv->print ($fh, [ Spreadsheet::Read::row ($sheet, $row) ]);
    }
close $fh;
himmat
  • 159
  • 5
  • 1
    Have you tried [Spreadsheet::Reader::ExcelXML](https://metacpan.org/pod/distribution/Spreadsheet-Reader-ExcelXML/README.pod) – Håkon Hægland Jul 06 '20 at 22:42
  • Yes, I did. it doesn't meet my requirement of row reading "Read Excel's whole row rather than cell value." – himmat Jul 06 '20 at 23:44
  • I am looking into it. Currently I am not even able to install the module Spreadsheet::Reader::ExcelXML (due to failed tests). – Håkon Hægland Jul 07 '20 at 07:50

1 Answers1

1

It's been a while since I reviewed the available stuff out there but one of the things that Spreadsheet::Reader::ExcelXML solves is access to xlsm files. This is partly because potentially dangerous macros can be stored in spreadsheets with the xlsm extension. Most other packages avoid this risk by intentionally not allowing that extension. Spreadsheet::Reader::ExcelXML avoids the risk of malicious macros by simply not providing access to the macro binary subfile through the interface. The package is old though and somewhat brittle at this point. However, It sounds like Håkon Hægland has expressed an interest in helping with the brittleness so good news!

To potentially solve the OP question see the code below: From Spreadsheet::Reader::ExcelXML::Worksheet
Also review the methods 'fetchrow_array' and 'fetchrow_hashref'

use strict;
use warnings;
use Data::Dumper;
 
use Spreadsheet::Reader::ExcelXML;
my $workbook =  Spreadsheet::Reader::ExcelXML->new( #similar style to Spreadsheet::XLSX
                                        file => 't/test_files/TestBook.xlsx',# in the test folder of this package
                                        group_return_type => 'value',
                                );
 
if ( !$workbook->file_opened ) {
        die $workbook->error(), ".\n";
}
 
my      $worksheet = $workbook->worksheet( 'Sheet5' );
        $worksheet->set_custom_formats( {
                2 =>'yyyy-mm-dd',
        } );
my $value;
while( !$value or $value ne 'EOF' ){
        $value = $worksheet->fetchrow_arrayref;
        print Dumper( $value );
}
 
###########################
# SYNOPSIS Output
# $VAR1 = [ 'Superbowl Audibles', 'Column Labels' ];
# $VAR1 = [         'Row Labels',     2016-02-06', '2017-02-14', '2018-02-03', 'Grand Total' ];
# $VAR1 = [               'Blue',            '10',          '7',           '',          '17' ];
# $VAR1 = [              'Omaha',              '',           '',          '2',           '2' ];
# $VAR1 = [                'Red',            '30',          '5',          '3',          '38' ];
# $VAR1 = [        'Grand Total',            '40',         '12',          '5',          '57' ];
# $VAR1 = 'EOF';
###########################
Jed Lund
  • 11
  • 4
  • Also another issue is that the package https://github.com/jandrew/p5-spreadsheet-reader-format must be installed from github before cpan can work it's magic. – Jed Lund Jul 08 '20 at 00:21