Generated XLS-files are much too big

[Christian Nein]

Hello,

some files generated by XlsIO are huge in size compared to the same files that have been generated using MS Excel automation. In my attached example, the XLS-file generated with XlsIO is about 68(!) times bigger than the file with the same content generated by Excel automation. What makes the XlsIO-file so big?

Thanks & best regards
Christian

Export ExcelAutomation.zip

[Administrator]

Hi Christian,

Could you please send us the sample XlsIO and automation code that generated these files? Also, which version of XlsIO was used to generate this file?

Thanks,
Stephen.

>Hello,

some files generated by XlsIO are huge in size compared to the same files that have been generated using MS Excel automation. In my attached example, the XLS-file generated with XlsIO is about 68(!) times bigger than the file with the same content generated by Excel automation. What makes the XlsIO-file so big?

Thanks & best regards
Christian

Export ExcelAutomation.zip

[Christian Nein]

Hi Stephen,

we are using Version 5.1.0.51. It is not quite easy for me to send you the code because the generation process is quite complex. I would have to extract the code out of many pieces. But here is a short overview of what I am doing.
1) I am opening a template (XLT-file) that
contains 3 worksheets.
2) I make a copy of one of the 3 sheets.
3) On this copy, I insert the data that
you see in the file I attached
4) I delete the 3 template sheets

It is obvious that the file size is much too big. Can you somehow take a look into the binary structure of the file to get an idea where that big size could come from?

Best regards
Christian

[Administrator]

Christian,

I agree with your suggestion of figuring out what is wrong by analyzing the binary file but it makes the process a bit difficult without the accompanying code snippets and also the development team needs to be consulted regarding that so it might take a bit of time before we can get back to you. However it would be great if you can share some code snippets as is from your application so that we can use that information in addition to what we see in the binary file to figure out what is wrong. You can also open a Direct-Trac incident to share any data that cannot be shared on the forums

https://www.syncfusion.com/Support/DirectTrac/logon.aspx?URL=/Support/DirectTrac/default.aspx

Thanks,
Stephen.

>Hi Stephen,

we are using Version 5.1.0.51. It is not quite easy for me to send you the code because the generation process is quite complex. I would have to extract the code out of many pieces. But here is a short overview of what I am doing.
1) I am opening a template (XLT-file) that
contains 3 worksheets.
2) I make a copy of one of the 3 sheets.
3) On this copy, I insert the data that
you see in the file I attached
4) I delete the 3 template sheets

It is obvious that the file size is much too big. Can you somehow take a look into the binary structure of the file to get an idea where that big size could come from?

Best regards
Christian

[Ajish]

Hi Christian,

The possible cause may be because the EntireColumn or Row set to some Cell style. Setting some style properties causes creation of each cell and setting its style. MS Excel in such case sets default column style thats why file size change using MS Excel. In XlsIO setting styles using the following code

sheet.Range["A1:A3"].EntireColumn is equal to sheet.Range["A1:A65536"] and

sheet.Range["A1:A65536"].CellStyle.Font.FontName="Arial"; is equal to

for( int i = 1; i <= 65536; i++ )
{
sheet.Range[ i, 1 ].CellStyle.Font.FontName = "Arial";
}

If your requirement is to set styles that apply to a whole column then please use the alternative approach

//Custom Styles

IStyle threeColumnsStyle = workbook.Styles.Add("ThreeColumnStyle");
threeColumnsStyle.FillBackgroundRGB = Color.Red;

for(int i=1;i<=3;i++)
{

//Set Styles
sheet.SetDefaultColumnStyle(i,threeColumnsStyle);

}

Here is a sample for your reference,

Sample: http://websamples.syncfusion.com/samples/XlsIO.Windows/F66593/main.htm

kindly take a look and let me know if you have any other questions.

Regards,
Ajish.

[Christian Nein]

Hi Ajish,

that was a great hint. Indeed I was addressing huge ranges (whole rows, whole columns or even whole sheets) and then setting

range.NumberFormat = "@";

Now, as I avoid setting formats for such huge ranges, the file sizes of the generated XLS-files are pretty ok. They are quite similar to the sizes of the files generated by Excel automation.

Thanks again & best regards
Christian