Appending data into an Excel Sheet

[psr473]

Hello Monks,
I want automate my daily process, which is takes the data from text file and convert it into excel sheet. By using modules available in Perl I am able to create new excel sheet and writing data into it successfully and also I am able to read the data form excel sheet. But I want to append the data into an existing excel sheet, because my excel work book consists number of sheets and I want to update only two sheets and remaining sheets should be kept same.
Can any one help me in this aspect and tell me what are the modules available in the Perl to append data into an excel work book.

Thanks in advance for your solutions. 

[marto]

Hi psr473,

I notice that this is your first post, welcome to the Monastery, have you read the PerlMonks FAQ and How do I post a question effectively?? Regards your Excel question, take a look at Re: Perl module to write to an existing excel file in linux, and remember super search is your friend.

Hope this helps

Martin

[psr473]

Thank you Martin.
Ya I am new to Monastery. At a glance I saw PerlMonks FAQ.

I read that article already, in that article we are creating new Excel sheets(books). But I need data to be append on the some of excel sheet in the work book. I can preserve the name of work book by giving same name to the destination file name, but I am loosing all the graphs available on the remaining sheets of work book.

Thanks

Sudhakara

[marto]

Had you read rather than glanced at the PerlMonks FAQ and How do I post a question effectively? you may realised that you could do a lot to help us help you. You have not given us a lot of information. For example you fail to mention which operating system you are using? If it is Microsoft Windows you should be able to use Win32::OLE to control Excel (see Re: Excel VB Controls for some useful links on this topic).

Again, a super search would have shown this method of manipulating Excel.

Martin

[igelkott]

A search like "spreadsheet excel" at MetaCPAN is a place to start.

[psr473]

Thank you igelkott.

[Brutha]

As your question is rather vague, I just can guess what your platform is. I am successfully updating an Excel-Shit under Windows, I went directly through OLE hell and used Win32::OLE, Win32::OLE::Const 'Microsoft Excel', and the like. You can record your changes as a macro and translate the calls to Perl. I use something like:

my $Excel =  Win32::OLE->GetActiveObject('Excel.Application');
unless ($Excel) {
    $Excel = Win32::OLE->new('Excel.Application');
}
my $xls = $Excel->Workbooks->Open(...);
my $sheet = $xls->Worksheets(1);

Open the Excel Macro editor and open help, that gives you all the methods. You will go through hell with all those parameters, undebuggable objects and all those esoteric VBA paranormalia. One last hint, use something like this to find the last used row to append things:

 my $lastrow = $sheet->UsedRange->Find(
        {   What            => "*",
            SearchDirection => xlPrevious,
            SearchOrder     => xlByRows
        }
    )->{Row};

[psr473]

Thank you Brutha.

Now I am doing on Windows OS, If I am able to do successfully on Windows OS then I will move on to LINUX.

Sudhakara

[psr473]

Hi Brutha,
I was successful in opening existing excel file. But I need it on Linux. Can you suggest me a module, or if you have any code please tell me. The existing excel file contains graphs in some sheets.
Thanks in Advance Brutha.

Sudhakara.

[Anonymous Monk]

Hi Sudhakar, I have installed active perl 5.8.8.2.2 version in windows and I want to update the daily details in to spread sheet or excel sheet automatically using perl. which are the perl modules required to be installed to support the spread sheet for this existing perl version. Can you please provide the information on this... thanks & regards S S Maradi