Excel how to open existing worksheet

[AtlasFlame]

How to write to existing worksheet?

use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new( 'sample.xlsx' );

$worksheet = $workbook->add_worksheet("new");
$worksheet->write(2,    2, 'Name');

$worksheet = $workbook->add_worksheet("old");
$worksheet->write(2,    2, 'Name');

$worksheet = $workbook->add_worksheet("latest");
$worksheet->write(2,    2, 'Name');

How to open worksheet "new" for write again?

[Utilitarian]

From the documentation on the old Spreadsheet::WriteExcel module

MODIFYING AND REWRITING EXCEL FILES 

An Excel file is a binary file within a binary file. It contains sever
+al interlinked checksums and changing even one byte can cause it to b
+ecome corrupted.

As such you cannot simply append or update an Excel file. The only way
+ to achieve this is to read the entire file into memory, make the req
+uired changes or additions and then write the file out again.

You can read and rewrite an Excel file using the Spreadsheet::ParseExc
+el::SaveParser module which is a wrapper around Spreadsheet::ParseExc
+el and Spreadsheet::WriteExcel. It is part of the Spreadsheet::ParseE
+xcel package: http://search.cpan.org/search?dist=Spreadsheet-ParseExc
+el.

However, you can only rewrite the features that Spreadsheet::WriteExce
+l supports so macros, graphs and some other features in the original 
+Excel file will be lost. Also, formulas aren't rewritten, only the re
+sult of a formula is written.

The xlsx format is somewhat better documented, but the problem appears to remain.

[poj]

#!perl
use strict;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'sample.xlsx' );
$workbook->add_worksheet("new");
$workbook->add_worksheet("old");

# select the sheet
my $worksheet = $workbook->sheets(0);
$worksheet->write(2, 2, 'This is Sheet 1');

# or directly
$workbook->sheets(1)->write(2, 2, 'This is Sheet 2');;

poj

[bibliophile]

I've never used it, but I would try something like:

use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new( 'sample.xlsx' );

$worksheet1 = $workbook->add_worksheet("new");
$worksheet1->write(2,    2, 'Name');

$worksheet2 = $workbook->add_worksheet("old");
$worksheet2->write(2,    2, 'Name');

$worksheet3 = $workbook->add_worksheet("latest");
$worksheet3->write(2,    2, 'Name');

$worksheet1->write(3,    3, 'Something else');

[jandrew]

Win32::OLE but it's a bad idea. You will also need some strongish VBscript-ing skills to go with it since Many Win32::OLE options are not documented and knowing the VBscript equivalent will allow you to find functions that you need. The actual process is to open the Microsoft Excel application, read the file into Excel with your script (Don't manually try and interact with Excel when the script has it open), make your changes, save the file, and then close Excel again.

From a data-safe standpoint there is very little keeping you from overwriting something accidentally using this process. Additionally these scripts are not very portable since they rely heavily on the Excel settings embedded in each computers Excel installation.

The alternative is to export your perl data with a .csv file using the excellent Text::CSV and either import the data to the existing Excel manually (Data/From Text/FileName.csv) or do the same with a Macro or PowerShell.

[Discipulus]

Ok i came acroos this thread because i wanted to edit an existing xlsx file. I jumped directly onto Excel::Writer::XLSX as is the new standard to write xlsx files. After some misleading errors i read in the docs of the module:

This module cannot, as yet, be used to write to an existing Excel XLSX file.

Ouch! when i realized was not possible i choosed that writing to an existing xls (old format file) would suffice for my tests and i ended with the following code. A mix of Spreadsheet::ParseExcel::SaveParser and Spreadsheet::WriteExcel worked out fine. I put it here for future memory.

use strict;
use warnings;

use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel;

# check for right parameters
my ($file,$howlong,$sleep) = @ARGV;
unless (  defined $file &&  $file =~/.+\.xls/ &&
          defined $howlong &&  $howlong =~/^\d+$/ &&
          defined $sleep &&   $sleep  =~/^\d+$/){
     die "USAGE: $0 filename.xls minutes_of_duration sleep_seconds",
         "\n\n\t$0 file.xls 5 1\n\n",
         "will creates file.xls and each seconds writes to it, for 5 m
+inutes.\n";

}
$howlong *=60;
$howlong = $^T+$howlong;
print "\tNB: $0 will run until ",scalar localtime($howlong),"\n";

my ($col,$row);
$col = $row = 0;
# initialize ta new XLS file
my $workbook_init = Spreadsheet::WriteExcel->new($file);
my $worksheet_init = $workbook_init->add_worksheet();
$worksheet_init->write( $row, $col, "XLS initialized at ".scalar local
+time(time));
$workbook_init->close();
$row++;

# modify the same xls in the while loop
while (time <= $howlong){
    print "writing row $row\n";
    my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
    my $template = $parser->Parse($file);
    my $worksheet = $template->worksheet(0);
    $worksheet->AddCell( $row, $col, scalar localtime (time) );
    $template->SaveAs($file);
    ++$row;
    sleep $sleep;
}
print "\treached ",(scalar localtime(time)),"\n\tscheduled termination
+"

L*