Writing row at a time in Excel using OLE

[Ash]

Hi!

I need to write a row at a time or a whole sheet at a time, anything
faster than one cell at a time in Excel sheet using Win32::OLE. Can
anyone help me?

Spreadsheet::WriteExcel module have functions for writing row at a
time but I need to modify existing Excel file and the module does not
allow that.

Thank you in advance!

 

[anton.vandersteen]

Hi!

I need to write a row at a time or a whole sheet at a time, anything
faster than one cell at a time in Excel sheet using Win32::OLE. Can
anyone help me?

Spreadsheet::WriteExcel module have functions for writing row at a
time but I need to modify existing Excel file and the module does not
allow that.

Thank you in advance!

Hey,

You must look for the package Spreadsheet::WriteExcel
With this package you can create an Excel document realy fast.

With the Perl listing below I create an Excel file first and then I
write data in it based on an sql statement.
In this example I did use an connection to an Access database.

Have fun.


#!perl/bin/perl

use Spreadsheet::WriteExcel;
use DBI;

# Create a new Excel workbook called perl.xls

my $excel_file = $ent2 -> get();

my $workbook = Spreadsheet::WriteExcel->new($excel_file);

my $worksheet = $workbook->addworksheet();


$sqlstatement9= $txt1->get('0.0','end');

#open connection to Access database
$dbh9 = DBI->connect('dbi:ODBC:driver=microsoft access driver
(*.mdb);dbq=C:\dump9\MS_Access.mdb');

#prepare and execute SQL statement

$sth9 = $dbh9->prepare($sqlstatement9);
$sth9->execute ||
die "Could not execute SQL statement ... maybe invalid?";

my $i=0;

#output database results


while (@row=$sth9->fetchrow_array())
{
$worksheet->write($i, 0, @row[0]);
$worksheet->write($i, 1, @row[1]);
$worksheet->write($i, 2, @row[2]);
$worksheet->write($i, 3, @row[3]);
$worksheet->write($i, 4, @row[4]);
$worksheet->write($i, 5, @row[5]);
$worksheet->write($i, 6, @row[6]);
$worksheet->write($i, 7, @row[7]);
$worksheet->write($i, 8, @row[8]);
$worksheet->write($i, 9, @row[9]);
$worksheet->write($i, 10, @row[10]);
$worksheet->write($i, 11, @row[11]);
$worksheet->write($i, 12, @row[12]);
$worksheet->write($i, 13, @row[13]);
$worksheet->write($i, 14, @row[14]);
$worksheet->write($i, 15, @row[15]);
$worksheet->write($i, 16, @row[16]);
$worksheet->write($i, 17, @row[17]);
$worksheet->write($i, 18, @row[18]);
$worksheet->write($i, 19, @row[19]);
$worksheet->write($i, 20, @row[20]);
$worksheet->write($i, 21, @row[21]);
$worksheet->write($i, 22, @row[22]);
$worksheet->write($i, 23, @row[23]);
$worksheet->write($i, 24, @row[24]);
$worksheet->write($i, 25, @row[25]);
$worksheet->write($i, 26, @row[26]);
$worksheet->write($i, 27, @row[27]);
$worksheet->write($i, 28, @row[28]);
$worksheet->write($i, 29, @row[29]);
$worksheet->write($i, 30, @row[30]);

$i=$i+1;

};

 

[Ash]

Hey,

You must look for the package Spreadsheet::WriteExcel
With this package you can create an Excel document realy fast.

With the Perl listing below I create an Excel file first and then I
write data in it based on an sql statement.
In this example I did use an connection to an Access database.

Have fun.

#!

Thanks!But I need to modify existing Excel file and not create a new
one.

 

[anno4000]

Hi!

I need to write a row at a time or a whole sheet at a time, anything

[...]

In this example I did use an connection to an Access database.

Have fun.


#!perl/bin/perl

You're running without strict and warnings!

use Spreadsheet::WriteExcel;
use DBI;

[some code snipped]

#output database results


while (@row=$sth9->fetchrow_array())
{
$worksheet->write($i, 0, @row[0]);

"@row[ 0]" should be written "$row[ 0]". With "warnings" Perl
would have told you so. Also, the loop body should be indented.

$worksheet->write($i, 1, @row[1]);

[28 similar lines]

$worksheet->write($i, 30, @row[30]);

$i=$i+1;

};

About thirty lines of your code can be replaced with a one-line loop:

my $i = 0;
while (@row=$sth9->fetchrow_array()) {
$worksheet->write($i, $_, $row[ $_]) for 0 .. 30;
++ $i;
}

That's what loops are for! Use them.

Anno

 

[gimme_this_gimme_that]

Any solution that doesn't use write_row isn't inserting the data a row
at a time.

Did you perldoc Spreadsheet::WriteExcel ?


@array = ('awk', 'gawk', 'mawk');
$array_ref = \@array;

#stuff row of data starting from A1:
$worksheet->write_row(0, 0, $array_ref);

# The above example is equivalent to:
$worksheet->write(0, 0, $array[0]);
$worksheet->write(0, 1, $array[1]);
$worksheet->write(0, 2, $array[2]);

# which is equivalent to
$worksheet->write_row('A1', $array_ref);

# even better yet is to write N by M at a time:

@eec = (
['maggie', 'milly', 'molly', 'may' ],
[13, 14, 15, 16 ],
['shell', 'star', 'crab', 'stone']
);

$worksheet->write_row('A1', \@eec);

 

[Craig]

#!/C:/Perl/bin/perl.exe
use strict;
use warnings;
use DBI;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
$Win32::OLE::Warn = 3;

my $file = "C:\\TMP\\test.xls";
my %args = ( sample => [ 'dbi:ODBC:Sample_Access_db', "administrator",
"" ],
sql => q{SELECT product, last_name, first_name, comment FROM
sample_table;},);

my $dbh = DBI->connect(@{$args{sample}}) or die ($DBI::errstr . "
Connect string: " . join(" ", @{$args{sample}}));
my $sth = $dbh->prepare($args{sql}) or die $dbh->errstr;
$sth->execute or die $dbh->errstr;
my $aref = $sth->fetchall_arrayref();
$sth->finish();
$dbh->disconnect;

my $excel = Win32::OLE->GetActiveObject('Excel.Application') ||
Win32::OLE->new('Excel.Application', 'Quit');
my $book = $excel->Workbooks->Open($file) or die("Could not open
$file", $?, $!);
my $sheet1 = $book->Worksheets(1);
$sheet1->Range("A610")->{Value} = $aref;