how to add rows beyond 65535 in excel using Perl?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View


I need to process huge data from text and output to excel file.
Whenever the output row count hits more than 65535, i get an
exception., i had been splitting my input files and handling it, but
it is do i handle automatically whenver row count is
more than 65535, my excel setup is called as follows:
sub setup_excel()
    $Win32::OLE::Warn = 3;                                # die on
    # get already active Excel application or open new
    $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');
    if (-e "$ReportFile")
        # open Excel file
        $Excel-> = 0;
        $workbook = $Excel->Workbooks->Open("$ReportFile");
        $worksheet = $workbook->Worksheets(1);
        $Excel-> = 0;
        $Excel-> = 2;
        $workbook = $Excel->Workbooks->Add();
        $worksheet = $workbook->Worksheets(1);
        $worksheet-> = "results";

     $worksheet->Range("A:J")-> = xlCenter;
     with($worksheet->Columns(1), ColumnWidth => 12);
     with($worksheet->Columns(2), ColumnWidth => 15);
     with($worksheet->Columns(3), ColumnWidth => 12);
     with($worksheet->Columns(4), ColumnWidth => 17);
     with($worksheet->Columns(5), ColumnWidth => 17);
     with($worksheet->Columns(6), ColumnWidth => 17);
     with($worksheet->Columns(7), ColumnWidth => 17);
     with($worksheet->Columns(8), ColumnWidth => 12);
     with($worksheet->Columns(9), ColumnWidth => 12);

Re: how to add rows beyond 65535 in excel using Perl?

Quoted text here. Click to load it

That limit is built-in to Excel, nothing you can do about it other
than upgrade to a newer version of Excel.

Tad McClellan
email: perl -le "print scalar reverse qq/moc.liamg0cm.j.dat/"
The above message is a Usenet post.
I don't recall having given anyone permission to use it on a Web site.

Re: how to add rows beyond 65535 in excel using Perl?

AFAIK this is a limit concerning the number of rows per file
(*not* per sheet) for Excel versions 2003 and before.
Try Excel 2007, it should support up to 1M rows ...

BR Rafael

IJALAB schrieb:
Quoted text here. Click to load it

Site Timeline