Writing to an existing Excel file 2

[sloppyhack]

Does anyone know of a way (module?) to write to an existing Excel file? I am very familiar with the Write-Excel module, but with that module you have to generate a new Excel file every time. The Excel file I need to write to is too complicated for the Write-Excel module (drop down lists, data validation, etc). I want to just write data to an existing template but I don't think there is anything out there that can do that...thought I'd check here before throwing in the towel. Any info would be greatly appreciated!!!

Cheers,

Sloppyhack

 

[EBGreen]

It is simple using VBScript. Which you should have access to unless you are on a Mac.

 

[uida1154]

try the following:

http://search.cpan.org/~jdb/Win32-OLE-0.17/lib/Win32/OLE.pm

Although I aggree with TomThumbKP, use the right tool for the right thing..

 

[sloppyhack]

Agreed...It's an unusual request. Here's the problem though..the processing to produce the results to go into the Excel file is VERY textually based (ie - lots of regexs). I could generate the results in Perl..write to a db..and have VB read the data and write it into the spreadsheet template. Just wondering if there was an easy way to go straight from Perl to Excel. This module looks very interesting!!! Let the fun begin.

Cheers,

Sloppyhack

 

[EBGreen]

VBScript supports regexes as well. Granted I like them implementation in Perl better, but the support is there in VBScript as well.

 

[dmazzini]

Hi man

Here I go with an example how to write to an existing excel file:

#Write to the existing excel file

use strict;
use Spreadsheet:arseExcel::SaveParser;
my $oExcel = new Spreadsheet:arseExcel::SaveParser;
my $oBook = $oExcel->Parse('temp.xls');
#1.Update and Insert
$oBook->AddCell(0, 0, 0, 'No(UPD)',
$oBook->{Worksheet}[0]->{Cells}[0][0]->FormatNo});
$oBook->AddCell(0, 1, 0, '304', 0);
$oBook->AddCell(0, 1, 1, 'Kawai,Takanori', 0);
$oBook->AddCell(0, 1, 2, 'Engineer', 0);
$oBook->AddCell(0, 1, 3, 'Konaka', 0);
$oBook->AddCell(0, 1, 4, '1990-06-01', 0);
$oBook->AddCell(0, 1, 5, '', 0);
$oBook->AddCell(0, 1, 6, '', 0);
$oBook->AddCell(0, 1, 7, 31, 0);



#2.add new worksheet
my $iWkN = $oBook->AddWorksheet('Test');
#2.1 set names
my @aNames = qw{ test1 test2 test3 };
for(my $i = 0; $i<=$#aNames; $i++) {
$oBook->AddCell($iWkN, 0, $i, $aNames[$i], 0);
}
#3. Save as the same name
$oExcel->SaveAs($oBook, 'temp.xls');


#1. Write an Excel file with previous data
use strict;
use Spreadsheet:arseExcel::SaveParser;
my $oExcel = new Spreadsheet:arseExcel::SaveParser;
my $oBook = $oExcel->Parse('temp.xls');
#1.1.Update and Insert Cells
my $iFmt = $oBook->{Worksheet}[0]->{Cells}[0][0]->{FormatNo};
$oBook->AddCell(0, 0, 0, 'No(UPD)',
$oBook->{Worksheet}[0]->{Cells}[0][0]->{FormatNo});
$oBook->AddCell(0, 1, 0, '304', $oBook->{Worksheet}[0]->{Cells}[0][0]);
$oBook->AddCell(0, 1, 1, 'Kawai,Takanori', $iFmt);
#1.2.add new worksheet
my $iWkN = $oBook->AddWorksheet('Test');
#1.3 Save
$oExcel->SaveAs($oBook, 'temp.xls'); # as the same name
$oExcel->SaveAs($oBook, 'temp1.xls'); # another name

#2. Create new Excel file (most simple)
use strict;
use Spreadsheet:arseExcel::SaveParser;
my $oEx = new Spreadsheet:arseExcel::SaveParser;
my $oBook = $oEx->Create();
$oBook->AddFormat;
$oBook->AddWorksheet('NewWS');
$oBook->AddCell(0, 0, 1, 'New Cell');
$oEx->SaveAs($oBook, 'new.xls');

#3. Create new Excel file(more complex)
#!/usr/local/bin/perl
use strict;
use Spreadsheet:arseExcel::SaveParser;
my $oEx = new Spreadsheet:arseExcel::SaveParser;
my $oBook = $oEx->Create();
my $iF1 = $oBook->AddFont(
Name => 'Arial',
Height => 11,
Bold => 1, #Bold
Italic => 1, #Italic
Underline => 0,
Strikeout => 0,
Super => 0,
);
my $iFmt =
$oBook->AddFormat(
Font => $oBook->{Font}[$iF1],
Fill => [1, 10, 0], # Filled with Red
# cf. ParseExcel (@aColor)
BdrStyle => [0, 1, 1, 0], #Border Right, Top
BdrColor => [0, 11, 0, 0], # Right->Green
);
$oBook->AddWorksheet('NewWS');
$oBook->AddCell(0, 0, 1, 'Cell', $iFmt);
$oEx->SaveAs($oBook, 'new.xls');


#new interface...

use strict;
use Spreadsheet:arseExcel::SaveParser;
$oBook =
Spreadsheet:arseExcel::SaveParser::Workbook->Parse('Excel/Test97.xls');
my $oWs = $oBook->AddWorksheet('TEST1');
$oWs->AddCell(10, 1, 'New Cell');
$oBook->SaveAs('iftest.xls');