Hi, I am new to Perl scripting. I have an existing excel file and I have to export values from a text file to that excel file. The excel file is like a template that generates histogram based on the values exported from the text file. With the perl script I have, it deletes the existing histogram and create new worksheet. Can i get some help on how to edit the existing excel file via Perl scripting. Below is the Perl script i have for now.
use strict;
use warnings;
use lib "/nfs/png/disks/gdr_dv_6/users/chihweit/acc_script/Excel-Writer-XLSX-1.03/lib";
use Excel::Writer::XLSX;
use IO::Uncompress::Gunzip qw(gunzip $GunzipError);
{
my $output_fn = 'result.xlsx';
my $input_fn = 'accuracy_final.txt.gz';
my $workbook = Excel::Writer::XLSX->new( $output_fn );
my $worksheet = $workbook->add_worksheet();
my $zip = IO::Uncompress::Gunzip->new( $input_fn )
or die "gunzip failed: $GunzipError\n";
my $col = 0;
my $row = 0;
while (!$zip->eof()) {
my $line = $zip->getline();
chomp($line);
next if $line !~ /\S/; # skip empty lines
my $value = $line;
$worksheet->write( $row, $col, $value );
$row++;
}
$workbook->close();
}
Looks like you do not edit the existing Excel file directly, but you copy it as a new worksheet and operate on / edit the copy (according to the link above).
I edited the script according to the link you provided.
use strict;
use warnings;
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Const "Microsoft Excel";
use IO::Uncompress::Gunzip qw(gunzip $GunzipError);
$Win32::OLE::Warn = 3
{
my $output_fn = 'result.xlsx';
my $input_fn = 'accuracy_final.txt.gz';
my $workbook = $Excel->Activewindow;
my $worksheet = $workbook->Activesheet;;
my $zip = IO::Uncompress::Gunzip->new( $input_fn )
or die "gunzip failed: $GunzipError\n";
my $col = 0;
my $row = 1;
while (!$zip->eof()) {
my $line = $zip->getline();
chomp($line);
next if $line !~ /\S/; # skip empty lines
my $value = $line;
$worksheet->write( $row, $col, $value );
$row++;
}
$workbook->close();
}
But I am getting this error.
Can't locate Win32/OLE.pm in @INC (@INC contains:
I am under corporate firewall as I am finding some difficulties to install those packages. Is any other approach to the problem i mentioned above?
Is it possible to solve the problem using 'Spreadsheet::ParseExcel' and 'Excel::Writer::XLSX'? What I mean by that is, first read the template file using e.g. Spreadsheet::ParseXLSX. Then create a new sheet using this data combined with the data in the text file using e.g. Excel::Writer::XLSX. I have this idea but i do not know how to execute it. Can you help me with this?
I did not review the examples much as i was getting some error using the sample i mentioned above. This sample was related but it extracts data from excel to text file.
I am keep getting this error "Can't call method "AddCell" without a package or object reference at line 26" when i try out this example. Could you help me with this?
#!/usr/bin/perl
use strict;
use warnings;
use Cwd;
use Cwd qw(abs_path);
use File::Temp qw(tempfile);
use Excel::Writer::XLSX;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use IO::Uncompress::Gunzip qw(gunzip $GunzipError);
# Open an existing file with SaveParser
my $parser = Spreadsheet::ParseExcel::SaveParser->new();
my $template = $parser->Parse('result.xlsx');
# Get the first worksheet.
my $worksheet = 0;
my $row = 0;
my $col = 0;
# Overwrite the string in cell A1
$worksheet->AddCell( $row, $col, 'New string' );
# Add a new string in cell B1
$worksheet->AddCell( $row, $col + 1, 'Newer' );
# Add a new string in cell C1 with the format from cell A3.
my $cell = $worksheet->get_cell( $row + 2, $col );
my $format_number = $cell->{FormatNo};
$worksheet->AddCell( $row, $col + 2, 'Newest', $format_number );
# Write over the existing file or write a new file.
$template->SaveAs('newfile.xls');
I don't use PERL these days (I'm a Ruby fan) so I would have to update my PERL distribution and install the libs and it still might not help you because you have not told us your OS, what version of PERL you are running, etc.
PS: I already tried to install the libs on my Mac for you, and I cannot get them to work. I hesitate to do the same on Ubuntu until you are very clear about your OS, version of PERL, etc.
