We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Hint on XlsIO performance excel generation

Thread ID:

Created:

Updated:

Platform:

Replies:

116106 Apr 3,2014 04:18 AM Apr 7,2014 12:30 AM ASP.NET Web Forms (Classic) 1
loading
Tags: XlsIO
Nikolay
Asked On April 3, 2014 04:18 AM

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 [Syncfusion]
Replied On April 7, 2014 12:30 AM

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


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;