Writing row at a time in Excel using OLE

Discussion in 'Perl Misc' started by Ash, Jun 14, 2007.

  1. Ash

    Ash Guest

    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!
     
    Ash, Jun 14, 2007
    #1
    1. Advertising

  2. Ash

    Guest

    On Jun 14, 7:49 pm, Ash <> 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;

    };
     
    , Jun 14, 2007
    #2
    1. Advertising

  3. Ash

    Ash Guest

    On Jun 14, 2:05 pm, ""
    <> wrote:
    > On Jun 14, 7:49 pm, Ash <> 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.
     
    Ash, Jun 14, 2007
    #3
  4. Ash

    -berlin.de Guest

    <> wrote in comp.lang.perl.misc:
    > On Jun 14, 7:49 pm, Ash <> 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
     
    -berlin.de, Jun 15, 2007
    #4
  5. Ash

    Guest

    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);
     
    , Jun 15, 2007
    #5
  6. Ash

    Craig Guest

    #!/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;
     
    Craig, Jun 16, 2007
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Patrick.O.Ige

    Ole ole

    Patrick.O.Ige, Jul 16, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    495
    Patrick.O.Ige
    Jul 16, 2006
  2. eching

    ruby/ole excel delete row?

    eching, Dec 14, 2006, in forum: Ruby
    Replies:
    8
    Views:
    166
    Venkat r m Reddi
    Dec 30, 2009
  3. Replies:
    1
    Views:
    831
  4. mrichardson
    Replies:
    1
    Views:
    262
    Brian Helterline
    Sep 16, 2004
  5. TimmyD
    Replies:
    0
    Views:
    180
    TimmyD
    Apr 21, 2005
Loading...

Share This Page