CODEWITHSUNDEEP
perlfilespreadsheetcpan
Ganesh
Ganesh

Reputation: 515

unable to parse xlsx with Spreadsheet::Read

I am Trying to parse a .xlsx file using Spreadsheet::Read same code works for .xls files but throws an error .xlsx below is procedure followed:

  1. We are creating a file upload(browse) button in .cgi file with name "csv_path".
  2. when user submits the form (of course its multipart/form-data)

  3. code on next page

    use CGI;
use Spreadsheet::Read;
use Spreadsheet::ParseExcel;
use Data::Dumper qw(Dumper);

#will catch uploaded file.
my $fname = $query->param("csv_path");

print Dumper($fname); 
#just for confirmation we printed variable $fname
#when .xls is uploaded $fname contains:
#$VAR1 = bless( \*{'Fh::fh00001308_4_template.xls'}, 'Fh' );
#when .xlsx is uploaded $fname contains:
#$VAR1 = bless( \*{'Fh::fh00001308_4_template.xlsx'}, 'Fh' );

#now read the file with "Spreadsheet::Read"
my $data_xls  = ReadData ( $fname, "strip"=>3, "dtfmt" => "mm/dd/yyyy");#here if we pass .xlsx file name(stored on server) to ReadData() it works properly.


#print out of Spreadsheet::Read
print "<pre>";
print Dumper($data_xls);

#when .xls is uploaded $data_xls comes up with all required data

#when .xlsx is uploaded below error occurs
#   XLSX parser cannot parse data: Undefined subroutine Fh::opened

Please suggest if any changes required or any thing missing.

Upvotes: 1

Views: 3716

Answers (1)

Ganesh
Ganesh

Reputation: 515

Here instead of passing stream data directly we are suppose to store the file at temporary location, and then pass the file name with location to the ReadData() function.
Please refer below code:

use CGI;
use Spreadsheet::Read;
use Spreadsheet::ParseExcel;
use Data::Dumper qw(Dumper);

my $corrected_filename = $query->param('csv_path');
$corrected_filename =~ s/ /_/g;
# $corrected_filename .= "$username";
#store file locally
local $| = 1;
my ($bytesread,$buffer,$file);
my $fh = $query->upload('csv_path');
open(OUTF, '>' . "/tmp/upload-".$corrected_filename);
while ($bytesread = read($fh, $buffer, 1024)) {
    print(OUTF $buffer);
}
close(OUTF);
my $data_xls  = ReadData ("/tmp/upload-".$corrected_filename, "strip"=>3, "dtfmt" => "mm/dd/yyyy");#here if we pass .xlsx file name(stored on server) to ReadData() it works properly.


#print out of Spreadsheet::Read
print "<pre>";
print Dumper($data_xls);

And when code read finish we can unlink the file.

Upvotes: 1

Related Questions