Spreadsheet::WriteExcel::Big makes my .xls bigger than necessary...

[jtb1492]

So I write a .xls using Spreadsheet::WriteExcel::Big and it's 25-MB. Then I open and save it, and it's under 500-KB.

Wassup with that? Can I get perl to write the file in this more compact format?

Thanks,
Jason

 

[dmazzini]

I have been using this module in the past and I haven't noticed it.

I think you should use the Spreadsheet::WriteExcel::Big for files bigger than 17 Mb if I remember,if they are less than that just use the standard Spreadsheet::WriteExcel



dmazzini
GSM System and Telecomm Consultant

 

[dmazzini]

I have checked again. Just use ::Big for files > 7 Mb

dmazzini
GSM System and Telecomm Consultant

 

[jtb1492]

Yeah, I know that. What's I'm saying is that when you write with Spreadsheet::WriteExcel it makes the file huge. Then you open and save it in Excel and the files size reduces by A LOT.

 

[dmazzini]

Read it, may be it could help you???

Use of too many formats

Warning signs: Creating formats in a loop


Excel only allows roughly 6000 different formats in a given workbook.
S::WE will create a new format every time you call add_format(), even
if the format you're creating is identical to one you've already
created.


The fix is to create your format outside the loop and reuse it within the loop.




dmazzini
GSM System and Telecomm Consultant

 

[dmazzini]

Could you post some code??????????

Cheers

dmazzini
GSM System and Telecomm Consultant

 

[dmazzini]

I have done a test.

You are right, the file size is bigger before you save the file. SpreadsheetWriteExcel generate an xls 5.0/95. So when you save it will overwrite in the last excel format (small size)

One solution could be open and save the excel file by code. You could do it at the end of your script, it really the size matter

Using ParseExcel

use Spreadsheet:arseExcel::SaveParser;
my $oExcel = new Spreadsheet:arseExcel::SaveParser;
my $oBook = $oExcel->Parse('temp.xls');
$oExcel->SaveAs($oBook, 'temp.xls');

Using OLE:

use Win32::OLE;
$excel = new Win32::OLE ('Excel.Application', 'Quit');
$excel->{Visible} = 0;
$excel->Workbooks->Open("$file_name") or die ("Error: unable to
open document ", Win32::OLE->LastError());
$excel->SaveAs('$file_name');
undef $excel;




dmazzini
GSM System and Telecomm Consultant