How can I modify an existing Excel workbook with Perl?

Question

With Spreadsheet::WriteExcel, I can create a new workbook, but what if I want to open an existing book and modify certain columns? How would I accomplish that?

I could parse all of the data out of the sheet using Spreadsheet::ParseExcel then write it back with new values in certain rows/columns using Spreadsheet::WriteExcel, however. Is there a module that already combines the two?

Mainly I just want to open a .xls, overwrite certain rows/columns, and save it.

score 14 · Accepted Answer · edited Sep 05 '09 at 23:02

14

Spreadsheet::ParseExcel will read in existing excel files:

my $parser   = Spreadsheet::ParseExcel->new();
# $workbook is a Spreadsheet::ParseExcel::Workbook object
my $workbook = $parser->Parse('Book1.xls');

But what you really want is Spreadsheet::ParseExcel::SaveParser, which is a combination of Spreadsheet::ParseExcel and Spreadsheet::WriteExcel. There is an example near the bottom of the documentation.

edited Sep 05 '09 at 23:02

cjm

61,471
9
126
175

answered Sep 02 '09 at 17:09

Ether

53,118
13
86
159

I don't see any way to write to an excel book using ParseExcel – user105033 Sep 02 '09 at 17:12
Spreadsheet::ParseExcel::SaveParser will do that. you beat my edit. :) – Ether Sep 02 '09 at 17:14

score 9 · Answer 2 · edited Sep 05 '09 at 23:05

If you have Excel installed, then it's almost trivial to do this with Win32::OLE. Here is the example from Win32::OLE's own documentation:

use Win32::OLE;

# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $ex) {
    $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
            or die "Oops, cannot start Excel";
}

# get a new workbook
$book = $ex->Workbooks->Add;

# write to a particular cell
$sheet = $book->Worksheets(1);
$sheet->Cells(1,1)->{Value} = "foo";

# write a 2 rows by 3 columns range
$sheet->Range("A8:C9")->{Value} = [[ undef, 'Xyzzy', 'Plugh' ],
                                   [ 42,    'Perl',  3.1415  ]];

# print "XyzzyPerl"
$array = $sheet->Range("A8:C9")->{Value};
for (@$array) {
    for (@$_) {
        print defined($_) ? "$_|" : "<undef>|";
    }
    print "\n";
}

# save and exit
$book->SaveAs( 'test.xls' );
undef $book;
undef $ex;

Basically, Win32::OLE gives you everything that is available to a VBA or Visual Basic application, which includes a huge variety of things -- everything from Excel and Word automation to enumerating and mounting network drives via Windows Script Host. It has come standard with the last few editions of ActivePerl.

Moreover, you can write whole arrays into columns\rows at once and it affects the speed of writing. If that's not enough, you can also write into an excel that has a macro in it and run with that module. — Vitali Pom, Jun 13 '13 at 13:52
Thank you. I noticed Spreadsheet::ParseXLSX::SaveParser doesn't exist so this works much better for me. — usr-bin-drinking, Dec 17 '14 at 21:17

score 3 · Answer 3 · edited Jun 20 '20 at 09:12

There's a section of the Spreadsheet::WriteExcel docs that covers Modifying and Rewriting Spreadsheets.

An Excel file is a binary file within a binary file. It contains several interlinked checksums and changing even one byte can cause it to become 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 required changes or additions and then write the file out again.

You can read and rewrite an Excel file using the Spreadsheet::ParseExcel::SaveParser module which is a wrapper around Spreadsheet::ParseExcel and Spreadsheet::WriteExcel. It is part of the Spreadsheet::ParseExcel package.

There's an example as well.

score 1 · Answer 4 · edited Apr 06 '14 at 04:56

1

The Spreadsheet::ParseExcel::SaveParser module is a wrapper around Spreadsheet::ParseExcel and Spreadsheet::WriteExcel.

I recently updated the documentation with, what I hope, is a clearer example of how to do this.

edited Apr 06 '14 at 04:56

szabgab

6,202
11
50
64

answered Sep 02 '09 at 19:23

jmcnamara

38,196
6
90
108

I still don't quite get this example. I can't find the documentation for what SaveAs accepts. Is is possible to pass file handles or scalar ref instead of a filename? – Myforwik Apr 19 '12 at 01:53
Oops I realised that John McNamara included a write_to_scalar.pl file in package. Using it I was able to make a memory to memory modification of an XLS file :-D – Myforwik Apr 19 '12 at 03:01

How can I modify an existing Excel workbook with Perl?

4 Answers4

Linked