Hi, I tried out this script but I am getting "Could not open source Excel file result.xlsx: at extract_acc_xls_2.pl line 14." error. Below is the Perl script I executed. Can someone help me with the error?
#!/usr/bin/perl
use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Data::Dumper;
my $sourcename = 'result.xlsx';
my $destname = 'output.xlsx';
my $source_excel = new Spreadsheet::ParseExcel;
my $source_book = $source_excel->Parse($sourcename) or die "Could not open source Excel file $sourcename: $!";
my $storage_book;
foreach my $source_sheet_number (0 .. $source_book->{SheetCount}-1)
{
my $source_sheet = $source_book->{Worksheet}[$source_sheet_number];
print "--------- SHEET:", $source_sheet->{Name}, "\n";
# sanity checking on the source file: rows and columns should be sensible
next unless defined $source_sheet->{MaxRow};
next unless $source_sheet->{MinRow} <= $source_sheet->{MaxRow};
next unless defined $source_sheet->{MaxCol};
next unless $source_sheet->{MinCol} <= $source_sheet->{MaxCol};
foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{MaxRow})
{
foreach my $col_index ($source_sheet->{MinCol} .. $source_sheet->{MaxCol})
{
my $source_cell = $source_sheet->{Cells}[$row_index][$col_index];
if ($source_cell)
{
print "( $row_index , $col_index ) =>", $source_cell->Value, "\n";
if ($source_cell->{Type} eq 'Numeric')
{
$storage_book->{$source_sheet->{Name}}->{$row_index}->{$col_index} = $source_cell->Value;
}
else
{
$storage_book->{$source_sheet->{Name}}->{$row_index}->{$col_index} = $source_cell->Value;
} # end of if/else
} # end of source_cell check
} # foreach col_index
} # foreach row_index
} # foreach source_sheet_number
print "Perl recognized the following data (sheet/row/column order):\n";
print Dumper $storage_book;
my $dest_book = Spreadsheet::WriteExcel->new("$destname") or die "Could not create a new Excel file in $destname: $!";
print "\n\nSaving recognized data in $destname...";
foreach my $sheet (keys %$storage_book)
{
my $dest_sheet = $dest_book->addworksheet($sheet);
foreach my $row (keys %{$storage_book->{$sheet}})
{
foreach my $col (keys %{$storage_book->{$sheet}->{$row}})
{
$dest_sheet->write($row, $col, $storage_book->{$sheet}->{$row}->{$col});
} # foreach column
} # foreach row
} # foreach sheet
$dest_book->close();
print "done!\n";
I'm not a Perl programmer myself, and so can't give Perl-specific advice (and nor have I seen this particular Perl module before either). But I'd start with checking the most basic fundamentals here, such as:
Does result.xlsx exist ?
Does it reside in the same directory as the script you're running ?
Does it contain data ?
Is that data valid Excel data, as far as the module Spreadsheet::ParseExcel is concerned ?
The module cannot read files in the Excel 2007 Open XML
XLSX format. See the Spreadsheet::XLSX module instead.
So that's my personal suspicion here: you're using a Perl module that's meant to parse Ye Olde Microsoft Office pre-2007 files, rather than the "modern" format favoured by more recent releases of Microsoft Office (which is what the .xlsx file extension would imply you're actually using here).
If you want to stay with your original libs, just change from .xlsx files to .xls files.
The docs and examples are clear on this.
You @rider seem to be in "copy the code, try to run it, and ask questions on a forum" mode of operation; versus looking at the code and understanding what it does and how you should apply it; and modifying code samples using some basic "read the docs and understand what you are doing" approach.
As mentioned a few time, the docs are clear on how to use (and how not to use) the libs you want to use.
Kindly read the docs versus expecting us to read the docs for you.
Post back when you have done this and written some code which, at least, matches the docs on how to use the libs you are wanting to use.
Just to let you know, i have tried my own script using writer and parse excel. But it didn’t work out. I was keep getting the same error. I googled and after reading some resources. I tried this,
then i did "return value of parse() and examine the error message: if ( !defined $workbook ) { die $parser->error(), ".\n"}" and the possible ways. After that only, i decided to try the example on the metacpan itself to see how it works or not. Turns out getting the same error. Thats when i posted this on this community. I have no scripting or programming background and its been just three weeks, im learning about perl scripting. Beginners like me only have google and community like this to learn from our mistake. Please dont misunderstand my intention on posting the question.
In that case, all I can really do is re-iterate the original points I made, since one of these is very likely to be the issue (otherwise your script would not produce this error):
Does result.xls exist ?
Does it reside in the same directory as the script you're running ?
Does it contain data ?
Is that data valid Excel data, as far as the module Spreadsheet::ParseExcel is concerned ?
One of the above things must not be the case, otherwise your script would not be producing this error.
Three other things that may be worth checking and/or remembering: the file must contain valid pre-Office 2007 Excel data (so in other words, it can't just be a .CSV or .XLSX that's simply had its extension re-named to .XLS, for example); also, do remember that UNIX-style OS's are case-sensitive (so result.XLS and result.xls would be two entirely different files, as far as your script is concerned); and lastly, please ensure that the ownerships and permissions on the file are such that the user running the script could actually read the spreadsheet file itself.
