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

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.

Replies are listed 'Best First'.
Re: Appending data into an Excel Sheet
by marto (Cardinal) on Feb 27, 2008 at 13:36 UTC
[reply]
      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
[reply]
        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
[reply]
Re: Appending data into an Excel Sheet
by igelkott (Priest) on Feb 28, 2008 at 02:37 UTC
[reply]
      Thank you igelkott.
[reply]
Re: Appending data into an Excel Sheet
by Brutha (Friar) on Feb 28, 2008 at 13:06 UTC
    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);

    [download]
    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};

    [download]

    And it came to pass that in time the Great God Om spake unto Brutha, the Chosen One: "Psst!"
    (Terry Pratchett, Small Gods)

[reply]
[d/l]
[select]
      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
[reply]
      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.
[reply]
        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
[reply]

Back to Seekers of Perl Wisdom