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. (Last updated on : Nov 16th 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

ExcelEngine with merge performance

Thread ID:

Created:

Updated:

Platform:

Replies:

123424 Mar 16,2016 12:58 PM UTC Mar 21,2016 05:12 AM UTC WPF 4
loading
Tags: XlsIO
Martin Tichovsky
Asked On March 16, 2016 12:58 PM UTC

Hello,

i have problem with large write to excel. With using IWorksheet is write very slow (aprox. 3 and more minutes for 12 columns and 700 rows, 2 tabs), because i using merge Cells. I try use IMigrantRange, but i can't find right way to merge cells. Can anyone help me with performance with this? Thanks

Martin Tichovsky
Replied On March 17, 2016 08:35 AM UTC

Here is example, that take about 3 minutes. I would like export excel with these functions and merged columns, but more faster. 


DateTime StartExport = DateTime.Now;

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook myWorkbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet mySheet = myWorkbook.Worksheets[0];

int colCount = 30,
    rowCount = 1000;

for (int column = 1; column <= colCount; column++)
{

    mySheet.Range[1, column].Text = "Column: " + column.ToString();

}

for (int row = 2; row <= rowCount; row++)
{
                    
    for (int column = 1; column <= colCount; column++)
    {

        mySheet.Range[row, column].Number = row + column;

        if ((row % 2 == 0) && (column % 2 == 1))
        {
            mySheet.Range[row, column, row + 1, column].Merge();
            mySheet.UsedRange.VerticalAlignment = ExcelVAlign.VAlignCenter;
            mySheet.UsedRange.HorizontalAlignment = ExcelHAlign.HAlignCenter;
            mySheet.UsedRange.WrapText = true;

        }

    }

}

TimeSpan endTime = (DateTime.Now - StartExport);
System.Console.WriteLine("Time: " + endTime.Minutes + "Mins : " + endTime.Seconds + "secs : " + endTime.Milliseconds + "msec");


Ishwarya Narayanan [Syncfusion]
Replied On March 17, 2016 12:59 PM UTC

Hi Martin,

 

Thank you for contacting Syncfusion supports.

 

Yes, you can achieve the improved performance by using IMigrantRange  instead of IRange while dealing with large data and styles. The following code example illustrates this behavior which will take reasonable time to write the date with the mentioned functionalities.

 

Code Sample:

DateTime StartExport = DateTime.Now;

 

ExcelEngine excelEngine = new ExcelEngine();

IApplication application = excelEngine.Excel;

IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);

IWorksheet mySheet = workbook.Worksheets[0];

 

IMigrantRange migrantRange = mySheet.MigrantRange;

 

//Adding Style

IStyle Style = workbook.Styles.Add("Style");

Style.BeginUpdate();

Style.VerticalAlignment = ExcelVAlign.VAlignCenter;

Style.HorizontalAlignment = ExcelHAlign.HAlignCenter;

Style.WrapText = true;

Style.EndUpdate();

 

int colCount = 30,

rowCount = 1000;

 

for (int column = 1; column <= colCount; column++)

{

migrantRange[1,column].Text = "Column: " + column.ToString();

migrantRange[1, column].CellStyle = Style;

}

 

for (int row = 2; row <= rowCount; row++)

{

for (int column = 1; column <= colCount; column++)

{

migrantRange[row, column].Number = row + column;

migrantRange[row, column].CellStyle = Style;

 

if ((row % 2 == 0) && (column % 2 == 1))

{

migrantRange[row, column, row + 1, column].Merge();

migrantRange[row, column, row, column].CellStyle = Style;

}

 

}

 

}

 

TimeSpan endTime = (DateTime.Now - StartExport);

System.Console.WriteLine("Time: " + endTime.Minutes + "Mins : " + endTime.Seconds + "secs : " + endTime.Milliseconds + "msec");

 

We have prepared a sample as per your requirement for your reference which can be downloaded from following link.

 

Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/XlsIO_Sample-1452259017

 

Please try this sample and confirm us whether performance has been improved or not. You can also refer the following UG link to know more about improving performance in XlsIO.

 

UG Documentation Link: http://help.syncfusion.com/file-formats/xlsio/improving-performance

 

Please let us know if you need any concerns.

 

Regards,

Ishwarya N

 


Martin Tichovsky
Replied On March 18, 2016 07:13 AM UTC

It is much more better! Thank You!

Ishwarya Narayanan [Syncfusion]
Replied On March 21, 2016 05:12 AM UTC

Hi Martin,

We are glad to know that the issue has been resolved. Please let us know if you need any further assistance on this.

Regards,
Ishwarya N


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.

Warning Icon 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.Close Icon

;