I
IJALAB
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);
}
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);
}