Hint on XlsIO performance excel generation

[Nikolay]

Hi,

I'm currently investigating and playing around with the excel generation component in order to figure out if it would be suitable for our company needs. Basically it does the job but as far as performance is concerned I have some question.

We would need to export excel files (~5000 rows, ~50-100 columns).

Currently used dlls:

Syncfusion.Compression.Base v11.4400.0.26

Syncfusion.Core v11.4400.0.26

Syncfusion.XlsIO.Web v11.4400.0.26

Test were performed on a website to generate excel document running under .NET framework 4.0.

1. Data Population

I noticed that SetText(int iRow, int iColumn, string value) works much more faster than setting a property IRange.Text. Tests showed that using SetText for ~5000 rows executes almost instantly (0 seconds) and using IRange.Text property took around 2-4 seconds to populate the data on the worksheet. So far so good, but SetText method of the worksheet is not flexible enough and basically works only for text values while IRange has the options to set Number, DateTime, NumberFormat etc. which in some occasions we would need.

Can you guide me if there's any other optimized method for numbers, dates etc. that we can use or we have to create some kind of hybrid between text fields using SetText and going with the IRange when numbers and dates are concerned?

2. Styling of rows and columns

Now here is the bigger performance issues that my tests showed. Styling ~5000 rows (99% of them use one and the same style) turned out to take around 12-15 seconds. 

Currently I'm using the following piece of code to apply style to rows:

IStyle rowStyle = workSheet.Workbook.Styles.Add("RowStyleFormatterContent");

//some styling properties are set below

...............

..............

..............

//The loop below takes 12-15 seconds to execute. rowNumbers is int[] with ~5000 items

foreach (int rowNumber in rowNumbers)

{

        worksheet.SetRowHeight(rowNumber, rowHeight);

        worksheet.AutofitRow(rowNumber);

        worksheet.SetDefaultRowStyle(rowNumber, rowStyle);

}

Is there any method that I'm missing to set the styles I need at once without going through a loop or is there any optimized way to format the rows that would take less time?

Thank you in advance!

Best regards,

Niki Yocov.

[Prakash Kumar D]

Hi Nikolay,

1.Data Population:

We have optimized method for numbers and not for DateTime, NumberFormat in 11.4. The below code snippets illustrates on how to achieve this. Kindly try this at your end.

 

 sheet.SetNumber(1, 1, 4);

 sheet.SetRowHeight(1,15);

We have optimized method for numbers and date in our upcoming release 12.1m which will be rolled out by this week.

DateTime time = DateTime.Now;

IMigrantRange range = sheet.MigrantRange;range.ResetRowColumn(1,1);

range.SetValue("string");
range.SetValue(time);
range.SetValue(30.00);
range.SetValue(12);

 2.Styling of Rows and Columns:

We have optimized way to apply the styles for rows and columns. The below code snippets illustrates on how to achieve this. Kindly try this at your end.

sheet.UsedRange.AutofitRows();

 sheet.UsedRange.AutofitColumns();

IStyle rowStyle = workbook.Styles.Add("RowStyleFormatterContent");

 sheet.UsedRange.CellStyle = rowStyle;

 

 

 

 

Note: No need of using SetRow height method while using AutoFitRows method.

Regards,
Prakash