Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Excel how to open existing worksheet

by AtlasFlame (Novice)
on Jan 27, 2015 at 11:40 UTC ( #1114623=perlquestion: print w/replies, xml ) Need Help??

AtlasFlame has asked for the wisdom of the Perl Monks concerning the following question:

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?

Replies are listed 'Best First'.
Re: Excel how to open existing worksheet
by Utilitarian (Vicar) on Jan 27, 2015 at 12:21 UTC
    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.

    print "Good ",qw(night morning afternoon evening)[(localtime)[2]/6]," fellow monks."
Re: Excel how to open existing worksheet
by poj (Abbot) on Jan 27, 2015 at 14:49 UTC
    #!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
Re: Excel how to open existing worksheet
by bibliophile (Prior) on Jan 27, 2015 at 14:32 UTC
    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');
Re: Excel how to open existing worksheet
by jandrew (Chaplain) on Jan 27, 2015 at 22:27 UTC

    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.

Re: Excel how to open existing worksheet -- create and append to an XLS file
by Discipulus (Abbot) on Feb 26, 2016 at 12:24 UTC
    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*

    There are no rules, there are no thumbs..
    Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1114623]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (3)
As of 2022-11-28 00:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?