Category / Section
How to create a global style in XlsIO?
1 min read
XlsIO supports to add styles globally that can be applied to one or more cells in a workbook. This is a recommended approach to apply single style in different rows and columns, which improves memory and performance considerably.
C#
ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Open(Server.MapPath(@"App_Data\Sample.xlsx")); IWorksheet sheet = workbook.Worksheets[0]; // Formatting // Global styles should be used when the same style needs to be applied to more than // one cell. This usage of a global style reduces memory usage. // Header Style IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); headerStyle.Color = Color.FromArgb(255, 174, 33); headerStyle.Font.Bold = true; headerStyle.Font.FontName = "Arial"; headerStyle.Font.Size = 14; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); // Body Style IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); bodyStyle.Color = Color.FromArgb(239, 243, 247); bodyStyle.Font.FontName = "Calibri"; bodyStyle.Font.Size = 10; bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; bodyStyle.EndUpdate(); // Apply Body Style. sheet.UsedRange.CellStyleName = "BodyStyle"; // Apply Header style. sheet.Rows[0].CellStyleName = "HeaderStyle"; workbook.Close(); excelEngine.Dispose();
VB
Dim excelEngine As ExcelEngine = New ExcelEngine() Dim application As IApplication = excelEngine.Excel Dim workbook As IWorkbook = application.Workbooks.Open(Server.MapPath("App_Data\Sample.xlsx")) Dim sheet As IWorksheet = workbook.Worksheets(0) ' Formatting ' Global styles should be used when the same style needs to be applied to more than ‘ one cell. This usage of a global style reduces memory usage. ' Header Style Dim headerStyle As IStyle = workbook.Styles.Add("HeaderStyle") headerStyle.BeginUpdate() headerStyle.Color = Color.FromArgb(255, 174, 33) headerStyle.Font.Bold = True headerStyle.Font.FontName = "Arial" headerStyle.Font.Size = 14 headerStyle.Borders(ExcelBordersIndex.EdgeLeft).LineStyle = ExcelLineStyle.Thin headerStyle.Borders(ExcelBordersIndex.EdgeRight).LineStyle = ExcelLineStyle.Thin headerStyle.Borders(ExcelBordersIndex.EdgeTop).LineStyle = ExcelLineStyle.Thin headerStyle.Borders(ExcelBordersIndex.EdgeBottom).LineStyle = ExcelLineStyle.Thin headerStyle.EndUpdate() ' Body Style Dim bodyStyle As IStyle = workbook.Styles.Add("BodyStyle") bodyStyle.BeginUpdate() bodyStyle.Color = Color.FromArgb(239, 243, 247) bodyStyle.Font.FontName = "Calibri" bodyStyle.Font.Size = 10 bodyStyle.Borders(ExcelBordersIndex.EdgeLeft).LineStyle = ExcelLineStyle.Thin bodyStyle.Borders(ExcelBordersIndex.EdgeRight).LineStyle = ExcelLineStyle.Thin bodyStyle.EndUpdate() ' Apply Body Style. sheet.UsedRange.CellStyleName = "BodyStyle" ' Apply Header style. sheet.Rows(0).CellStyleName = "HeaderStyle" workbook.Close() excelEngine.Dispose()
The sample illustrating this behavior can be downloaded here.