Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   Perl Misc (http://www.velocityreviews.com/forums/f67-perl-misc.html)
-   -   Writing row at a time in Excel using OLE (http://www.velocityreviews.com/forums/t903467-writing-row-at-a-time-in-excel-using-ole.html)

Ash 06-14-2007 05:49 PM

Writing row at a time in Excel using OLE
 
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@chello.nl 06-14-2007 06:05 PM

Re: Writing row at a time in Excel using OLE
 
On Jun 14, 7:49 pm, Ash <ashish...@gmail.com> wrote:
> 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 06-14-2007 06:32 PM

Re: Writing row at a time in Excel using OLE
 
On Jun 14, 2:05 pm, "anton.vanderst...@chello.nl"
<anton.vanderst...@chello.nl> wrote:
> On Jun 14, 7:49 pm, Ash <ashish...@gmail.com> wrote:
>
> > 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.
>
> #!


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


anno4000@radom.zrz.tu-berlin.de 06-15-2007 08:52 AM

Re: Writing row at a time in Excel using OLE
 
anton.vandersteen@chello.nl <anton.vandersteen@chello.nl> wrote in comp.lang.perl.misc:
> On Jun 14, 7:49 pm, Ash <ashish...@gmail.com> wrote:
> > 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@yahoo.com 06-15-2007 07:21 PM

Re: Writing row at a time in Excel using OLE
 
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 06-16-2007 03:23 PM

Re: Writing row at a time in Excel using OLE
 
#!/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("A6:D10")->{Value} = $aref;



All times are GMT. The time now is 11:13 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.