Re: Scripting data extraction from excel files in a directory.

Hello
The following code snippet might help you in basic manipulation of the excel sheet

    use strict;
    use Win32::OLE;
    # write a value in the first empty ro of the first column
    # an excel workbook containing a sheet called Versions 
    my $projsheet = 'c:\temp\t.xls'; 
    my $val = 123; 
    my $ex = new Win32::OLE 'Excel.Application' or die;
    eval ("\$ex->Workbooks->Open(\'$projsheet\')") or die;
    
    #
    # find empty row
    #
    my $rowcounter        = 1;
    my $rowval        = 1;
    while (($rowval) && ($rowcounter < 1000)) {
        $rowval    = eval "\$ex->Workbooks(1)->Worksheets(\'Versions\'
+)->Cells($rowcounter,1)->{Value}" ;
        $rowcounter++;        
    }
    $rowcounter--;        
    #
    # fill in the values in the sheet
    #
    
    eval "\$ex->Workbooks(1)->Worksheets(\'Versions\')->Cells($rowcoun
+ter,1)->{Value}= \'$val\'" ;
    
    #
    # save and quit
    #
    $ex->Save;
    $ex->Quit;
    
    return 1;
[download]

You will further need to do some ad hoc scripting to avoid message boxes generated by Excel. A good starting point is to record the macro in Excel, look at it in VB and then mimic it in perl.
Tabari

Comment on Re: Scripting data extraction from excel files in a directory. Download Code

Replies are listed 'Best First'.
Re^2: Scripting data extraction from excel files in a directory. by Corion (Patriarch) on Jul 10, 2007 at 08:56 UTC
Why are you using the `eval` statements, and why are you using the string form of the `eval` statements when calling Excel methods? In my experience, the direct way works just as well and looks far less ugly: `... $rowval = $ex->Workbooks(1)->Worksheets('Versions')->Cells($rowcounter +,1)->{Value} ...` [download]	[reply] [d/l] [select]
Re^3: Scripting data extraction from excel files in a directory. by Tabari (Monk) on Jul 10, 2007 at 13:47 UTC
I used the eval method to be sure that $rowcounter was evaluated before the operation on the object was executed. As to the string form of the eval , it is the only one known to me, are there any others? Tabari	[reply]
Re^4: Scripting data extraction from excel files in a directory. by Corion (Patriarch) on Jul 10, 2007 at 13:54 UTC
You can be assured that Perl is sane in that respect. String-eval should be avoided in most cases, and in your case especially, as Perl works exactly how one would imagine, evaluating the expressions one after another. If you really are paranoid about the order Perl does things in, it helps to split up such long method chains into separate statements: `# $rowval = eval "\$ex->Workbooks(1)->Worksheets(\'Versions\')->Cel +ls($rowcounter,1)->{Value}" ; my $wb = $ex->Workbooks(1); my $ws = $wb->Worksheets('Versions'); my $c = $ws->Cells($rowcounter,1); my $rowval = $c->{Value};` [download]	[reply] [d/l]