CODEWITHSUNDEEP
excelperl
MikG
MikG

Reputation: 1019

Perl writing to Excel worksheet using OLE

firstly thanks for reading this. I have a small script that collects various txt files and drops them into an Excel worksheet, I previously used Spreadsheet::WriteExcel to do this, and all worked fine. However as I developed the script it became more obvious that I should use Win::OLE to perform this function as I would prefer to use a template Excel file (with pre existing formulas) which I could add these txt files to as worksheets.

I am struggling with the change from Spreadsheet::WriteExcel to Win::OLE with the paste function to place the text files into a spreadsheet. Here's the code that did work (from the point of adding the txt file @ line >>>> $wrksheet->write_col)

        ......
        my $workbook = Spreadsheet::WriteExcel->new("resultsbook.xls");

        (my $sheetname = $file) =~s/\.\w+//; 
        my $wrksheet = $workbook->add_worksheet($sheetname);
        $wrksheet->write_col( 0, 0, readfile($file)); # <-- Old write line
    }
}

sub readfile {
    my @textfilecontent = ();
    open my $fh, '<', shift() or die "can't open file:$!";
    while (<$fh>) {
        chomp;
        push @textfilecontent, [split(/\t/)];
    }
    return \@textfilecontent;
}

But I have since changed to using OLE and I get as far as naming the Excel worksheet tabs but I am struggling to workout how to paste the extracted txt into a worksheet here (using the same subroutine above @ line >>>> $sheet->Cells(0,0)->{Value} = readfile($file); )...

        my $sheet = $book->Worksheets->Add({After => $book->Worksheets(1)})
        $sheet->{Name} = $sheetname;    

        $sheet->Cells(0,0)->{Value} = readfile($file); # <-- New write line

        ###$sheet->write_col( 0, 0, readfile($file));#### Old method
    }
}

sub readfile {
    my @textfilecontent = ();
    open my $fh, '<', shift() or die "can't open file:$!";
    while (<$fh>) {
        chomp;
        push @textfilecontent, [split(/\t/)];
    }
    return \@textfilecontent;
}

I get the following error whel I run this script...

Win32::OLE(0.1711) error 0x800a03ec in METHOD/PROPERTYGET "Cells" at line...

Any pointers/advice would be greatly appreciated.

Thanks, MikG

Upvotes: 2

Views: 1821

Answers (2)

imran
imran

Reputation: 1560

Take a look at the Range function. e.g. 

my $data = readfile($file);
# figure out size of data and adjust "A1:Z20" below accordingly
$sheet->Range("A1:Z20")->{Value} = $data;

Edit:

I haven't used Win32::OLE so this would be my hack: Use this version of your readfile function to pass back the number of rows and columns:

sub readfile {
    my @textfilecontent = ();
    open my $fh, '<', shift() or die "can't open file:$!";
    my ($rows, $cols) = (0,0);
    while (<$fh>) {
        chomp;
        my @row = split(/\t/);
        $cols = scalar(@row) if scalar(@row) > $cols;
        push @textfilecontent, \@row;
        $rows++;
    }
    return (\@textfilecontent, $rows, $cols);
}

You may want to modify this to pad (with "") rows that do not have the same number of columns as the one with the max number of columns. There is a function in Spreadsheet::WriteExcel::Utility that can convert row/columns to the A1 notation. Include it in your script like this:

use Spreadsheet::WriteExcel::Utility qw(xl_rowcol_to_cell);

Then your main routine would look like this:

my ($data, $rows, $cols) = readfile($file);
my $endCell = xl_rowcol_to_cell($rows, $cols);
$sheet->Range("A1:$endCell")->{Value} = $data;

Upvotes: 1

Miller
Miller

Reputation: 35198

I don't have Excel and use LibreOffice, so I've always used Spreadsheet::WriteExcel instead of Win32::OLE

However, you're attempting to assign a two dimensional array to a single cell. Win32::OLE #Examples shows a different way to do this:

# write a 2 rows by 3 columns range
$sheet->Range("A8:C9")->{Value} = [[ undef, 'Xyzzy', 'Plugh' ],
                                   [ 42,    'Perl',  3.1415  ]];

Upvotes: 0

Related Questions