ExcelEngine with merge performance

[Martin Tichovsky]

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]

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]

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]

It is much more better! Thank You!

[Ishwarya Narayanan]

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