how to add rows beyond 65535 in excel using Perl?




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->{Visible} = 0;
$workbook = $Excel->Workbooks->Open("$ReportFile");
$worksheet = $workbook->Worksheets(1);
$Excel->{Visible} = 0;
$Excel->{SheetsInNewWorkBook} = 2;
$workbook = $Excel->Workbooks->Add();
$worksheet = $workbook->Worksheets(1);
$worksheet->{Name} = "results";

$worksheet->Range("A:J")->{HorizontalAlignment} = 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);



Rafael Koeppen

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

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question