Articles in this section
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.

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment
Access denied
Access denied