Append row into existing excel file

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

Hello Monks,
Have a great day. I am trying to append an excel sheet with the following data servername,username and password. But I am not able to append the excel file using Spreadsheet::writeExcel module. I tried in the google and it says that "Spreadsheet::parseExcel::Saveparser can be used. But I do not find it will help to append the existing excel file. Could you please help me how to append data in the excel file?
I know that using Spreadsheet::parseExcel we can read all the rows and files, then we can write it to new excel file. But in my excel file it is having multiple sheets. I like to append only one excel sheet with only one row. so i am looking for appending the row in the sheet.
Here is the code,


use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel::Utility qw(ExcelLocaltime);
use DateTime::Format::Excel;


my @Array = ();
my $server = "localhost";
my $user = "lion";
my $pass = "tiger";

push(@Array, $server);
push(@Array, $user);
push(@Array, $pass);

my $inputfile = "C:\\perl\\bin\\input.xls";
$workbook = Spreadsheet::WriteExcel->new("$inputfile");
$sheet = $workbook->add_worksheet("serverdata");

print"File=> $inputfile worksheet=> $sheet\n";
# Add a Format
$format = $workbook->add_format();
$bgformat = $workbook->add_format();

for(my $i =0; $i <= 2; $i++){
$sheet1->write(0, $i, $writeArray[$i], $bgformat);
}

Existing File content(input.xls):
server    user    pass
red    lang    pian
green    weer    xisass


Expected output File content(input.xls):
server      user    pass
red      lang    pian
green      weer    xisass
loalhost  lion    tiger
[download]

Could any one help me to add this entry in the excel sheet.
THANKS.

Comment on Append row into existing excel file Download Code

Replies are listed 'Best First'.
Re: Append row into existing excel file by roboticus (Chancellor) on Apr 04, 2009 at 13:09 UTC
Parse the current Excel workbook. Add the data you want to the current representation in memory. Rename the old workbook to a backup copy. Write the new workbook with the name of the original workbook. Update: It's the first step that you seem to be missing. ...roboticus	[reply]
Re^2: Append row into existing excel file by Anonymous Monk on Apr 04, 2009 at 13:47 UTC
Thanks roboticus for the help. I am trying to do this operation without renaming the file. I mean without parsing the file, I want to update the excel file. Can you please help with any other alternate solution.	[reply]
Re^3: Append row into existing excel file by Corion (Patriarch) on Apr 04, 2009 at 14:01 UTC
See Spreadsheet::ParseExcel and Spreadsheet::WriteExcel, or Win32::OLE, together with the Microsoft Excel Object Model Documentation. You will need to do some research of your own, like, for example, searching on this site for Excel Win32::OLE.	[reply]
Re^3: Append row into existing excel file by roboticus (Chancellor) on Apr 05, 2009 at 18:17 UTC
If you don't want to parse & rewrite it, then you'll want to use Win32::OLE as mentioned by Corion. As you mention in Re^2: Append row into existing excel file, there's no function in Win32::OLE. That's because OLE is just a Microsoft-standard interface for talking to objects (not exactly the same as the objects in OOP, but not terribly dissimilar). The general procedure there would be to tell Win32::OLE to create an Excel application object for you, and then tell Excel to do what you want it to do. Note: You can turn on "macro recording" in Excel, and then add the row(s) you want. Then turn macro recording off, and you can then look at the VB code in the macro to see what you need to tell Excel to do. If you search around, you'll find plenty of links on 'convert VB to perl' which include some Excel-specific examples in the threads. ...roboticus	[reply]
Re: Append row into existing excel file by VinsWorldcom (Prior) on Apr 04, 2009 at 15:06 UTC
See Parse Excel Spreadsheets To Single for a working example. There are many sites that detail Win32::OLE for Excel - that's how I was able to cobble together my code.	[reply]
Re: Append row into existing excel file by Bloodnok (Vicar) on Apr 04, 2009 at 13:20 UTC
Personally, I favour Win32::OLE for this sort of job - it seems to be quite reliable despite the fact that it's an interface to Windoze 'technology'. A user level that continues to overstate my experience :-))	[reply]
Re^2: Append row into existing excel file by Anonymous Monk on Apr 04, 2009 at 13:43 UTC
Thanks for the suggestion. But I did not find the relative subroutine in Win32::OLE to append the excel file worksheet.	[reply]
Re^3: Append row into existing excel file by Bloodnok (Vicar) on Apr 04, 2009 at 15:39 UTC
AFAIR, there isn't a specific routine to do that since, given the worksheet is (ostensibly) infinite [sic], how on earth would the routine know where the end is ... and thus when to stop. It has to be home grown and typically (well, mostly :-) depends on searching down the worksheet to the first row that meets your criteria for an empty row e.g. the first row for which some unique identifier is not set, and appending the new data thereafter. A user level that continues to overstate my experience :-))	[reply]