Improving Performance

This section gives you an idea for improving performance while developing with XlsIO.

UsedRange

Get UsedRange globally. It is recommended to get the UsedRange in loops as follows

int lastRow = sheet.UsedRange.LastRow;

for(int i=0;i<lastRow;i++)

{

//codes

}

//Do not use like below.

for(int i = 0;i<sheet.UsedRange.LastRow;i++)

{

//codes

}
Dim lastRow As Integer = sheet.UsedRange.LastRow

For i As Integer = 0 To lastRow - 1

'codes

Next

'Do not use like below.

For i As Integer = 0 To sheet.UsedRange.LastRow - 1

'codes

Next

Range Access

Use IMigrantRange instead of IRange to optimize performance while dealing with large data.

The IMigrantRange interface can be used to access and manipulate worksheet range. This is an optimal method of writing values with better memory performance.

The following code example illustrates how the IMigrantRange is accessed.

IMigrantRange migrantRange = workbook.Worksheets[0].MigrantRange; 

// Writing Data.
for (int row = 1; row <= rowCount; row++)
{
for (int column = 1; column <= colCount; column++)
{
// Writing values.

migrantRange.ResetRowColumn(row, column);

// Setting value of this migrant range which is similar to IRange object.

migrantRange.Value = "Syncfusion";        

}
}
'Writing Data.
Dim row As Integer
Dim migrantRange As IMigrantRange = workbook.Worksheets(0).MigrantRange
For row = 1 To rowCount Step row + 1
Dim column As Integer
For column = 1 To colCount Step column + 1
'Writing values.
migrantRange.ResetRowColumn(row, column)

' Setting value of this migrant range which is similar to IRange object.

migrantRange.Value = "Syncfusion"

Next
Next

IMigrantRange provides us a SetValue method in which different value for the range can be assigned. Following code snippet illustrates regarding this.

ExcelEngine excelEngine = new ExcelEngine();

excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013;

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

IWorksheet sheet = workbook.Worksheets[0];

IMigrantRange migrantRange = workbook.Worksheets[0].MigrantRange;

// Writing values.

migrantRange.ResetRowColumn(1, 1);

//Setting boolean value

migrantRange.SetValue(true);

migrantRange.ResetRowColumn(1, 2);

//Setting DateTime value

migrantRange.SetValue(DateTime.Now);

migrantRange.ResetRowColumn(1, 3);

//Setting double value

migrantRange.SetValue(5.5);

migrantRange.ResetRowColumn(1, 4);

//Setting int value

migrantRange.SetValue(5);

migrantRange.ResetRowColumn(1, 5);

//Setting string value

migrantRange.SetValue("Syncfusion");

workbook.Version = ExcelVersion.Excel2013;

workbook.SaveAs("MigrantRange.xlsx");

workbook.Close();

excelEngine.Dispose();
Dim excelEngine As New ExcelEngine()

excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013

Dim workbook As IWorkbook = excelEngine.Excel.Workbooks.Create()

Dim sheet As IWorksheet = workbook.Worksheets(0)

Dim migrantRange As IMigrantRange = workbook.Worksheets(0).MigrantRange

' Writing values.

migrantRange.ResetRowColumn(1, 1)

'Setting boolean value

migrantRange.SetValue(True)

migrantRange.ResetRowColumn(1, 2)

'Setting DateTime value

migrantRange.SetValue(DateTime.Now)

migrantRange.ResetRowColumn(1, 3)

'Setting double value

migrantRange.SetValue(5.5)

migrantRange.ResetRowColumn(1, 4)

'Setting int value

migrantRange.SetValue(5)

migrantRange.ResetRowColumn(1, 5)

'Setting string value

migrantRange.SetValue("Syncfusion")

workbook.Version = ExcelVersion.Excel2013

workbook.SaveAs("MigrantRange.xlsx")

workbook.Close()

excelEngine.Dispose()

Styles

Use global styles, rather than using different cell styles for each cell/range. See Applying global styles.

Use Begin and End call while using more than one global style for a worksheet.

//Defining body style

IStyle bodyStyle = workbook.Styles.Add("BodyStyle");

bodyStyle.BeginUpdate();

bodyStyle.Color = Color.FromArgb(239, 243, 247);

bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;

bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;

bodyStyle.EndUpdate();
'Defining body style

Dim bodyStyle As IStyle = workbook.Styles.Add("BodyStyle")

bodyStyle.BeginUpdate()

bodyStyle.Color = Color.FromArgb(239, 243, 247)

bodyStyle.Borders(ExcelBordersIndex.EdgeLeft).LineStyle = ExcelLineStyle.Thin

bodyStyle.Borders(ExcelBordersIndex.EdgeRight).LineStyle = ExcelLineStyle.Thin

bodyStyle.EndUpdate()

AutoFit

Minimize AutoFit manipulations which reduces the time consumption.

Importing DataTable

ImportDataTable overload method which has ImportOnSave argument allows you to import data with less memory consumption along with improved method performance by serializing the data directly on save method. This option is preferred for larger data that need to be imported in short time.

DataTable table = Worksheet.ExportDataTable(1, 1, Worksheet.UsedRange.LastRow, Worksheet.UsedRange.LastColumn, ExcelExportDataTableOptions.DetectColumnTypes); 

//Enabling the import on save options.

workbook.Worksheets[0].ImportDataTable(table, 1, 1, true);

workbook.Version = ExcelVersion.Excel2013; 

workbook.SaveAs("Output.xlsx");
Dim table As DataTable = Worksheet.ExportDataTable(1, 1, Worksheet.UsedRange.LastRow, Worksheet.UsedRange.LastColumn, ExcelExportDataTableOptions.DetectColumnTypes)

'Enabling the import on save options.

workbook.Worksheets(0).ImportDataTable(table, 1, 1, True)

workbook.Version = ExcelVersion.Excel2013

workbook.SaveAs("Output.xlsx")

Limitations

  • Cannot modify data dynamically
  • Styles cannot be applied
  • Table style cannot be applied
  • Existing sheet data will be lost

Data Validation

Use of BeginUpdate and EndUpdate methods for large blocks of Data Validation greatly improves the performance.

// List data validation for entire column

IDataValidation validation = sheet.Range["A3"].EntireColumn.DataValidation;

validation.BeginUpdate();

validation.DataRange = sheet.Range["D1:D56"];

validation.IsEmptyCellAllowed = true;

validation.IsListInFormula = false;

validation.EndUpdate();
' List data validation for entire column

Dim validation As IDataValidation = sheet.Range("A3").EntireColumn.DataValidation

validation.BeginUpdate()

validation.DataRange = sheet.Range("D1:D56")

validation.IsEmptyCellAllowed = True

validation.IsListInFormula = False

validation.EndUpdate()