how to add rows beyond 65535 in excel using Perl?

Prev: FAQ 5.7 How do I make a temporary file name?
Next: FAQ 7.12 How can I tell if a variable is tainted?

From: IJALAB on 10 May 2010 05:54

---

Hi,

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 cumbersome..how 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
errors...
# get already active Excel application or open new
$Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{DisplayAlerts}=0;
if (-e "$ReportFile")
{
# open Excel file
$Excel->{Visible} = 0;
$workbook = $Excel->Workbooks->Open("$ReportFile");
$worksheet = $workbook->Worksheets(1);
}
else
{
$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);
}

From: Tad McClellan on 10 May 2010 08:20

---

IJALAB <balaji.draj(a)gmail.com> wrote:

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


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

http://office.microsoft.com/en-us/excel/ha101375451033.aspx


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

From: Rafael Koeppen on 10 May 2010 08:22

---

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:
> Hi,
>
> 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 cumbersome..how 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
> errors...
> # get already active Excel application or open new
> $Excel = Win32::OLE->GetActiveObject('Excel.Application')
> || Win32::OLE->new('Excel.Application', 'Quit');
> $Excel->{DisplayAlerts}=0;
> if (-e "$ReportFile")
> {
> # open Excel file
> $Excel->{Visible} = 0;
> $workbook = $Excel->Workbooks->Open("$ReportFile");
> $worksheet = $workbook->Worksheets(1);
> }
> else
> {
> $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);
> }

 | 
Pages: 1
Prev: FAQ 5.7 How do I make a temporary file name?
Next: FAQ 7.12 How can I tell if a variable is tainted?