Category / Section
How to set alternative row color in an Excel file using XlsIO?
1 min read
Alternative row color can be applied by using conditional format. Before applying the conditional format, values must be entered in the range where the alternative row style need to be applied.
Here is the sample code for applying colors on alternative rows using XlsIO.
C#
IWorkbook workbook = application.Workbooks.Open(fileName, ExcelOpenType.Automatic); IWorksheet sheet = workbook.Worksheets[0]; // Alternative row coloring for the Entire sheet can be achieved by sheet.Range["$1:$1048576"].conditionalFormats // Applying alternative row coloring for UsedRange. IConditionalFormats condition = sheet.UsedRange.ConditionalFormats; IConditionalFormat condition1 = condition.AddCondition(); condition1.FormatType = ExcelCFType.Formula; condition1.FirstFormula = "MOD(ROW(),2)=0"; condition1.BackColorRGB = Color.LightGray;
VB
Dim workbook As IWorkbook = application.Workbooks.Open(fileName, ExcelOpenType.Automatic) Dim sheet As IWorksheet = workbook.Worksheets(0) ' Alternative row coloring for the Entire sheet can be achieved by sheet.Range["$1:$1048576"].conditionalFormats ' Applying alternative row coloring for UsedRange Dim condition As IConditionalFormats = sheet.UsedRange.ConditionalFormats Dim condition1 As IConditionalFormat = condition.AddCondition() condition1.FormatType = ExcelCFType.Formula condition1.FirstFormula = "MOD(ROW(),2)=0" condition1.BackColorRGB = Color.LightGray
The sample illustrating this behavior can be downloaded here.
If you need to apply alternative row coloring from a particular row, you need to specify the particular range instead of used range as below.
C#
// Applying alternative row coloring for specific range. IRange range = sheet[3, 1, sheet.UsedRange.LastRow, sheet.UsedRange.LastColumn]; IConditionalFormats condition = range.ConditionalFormats; IConditionalFormat condition1 = condition.AddCondition(); condition1.FormatType = ExcelCFType.Formula; condition1.FirstFormula = "MOD(ROW(),2)=0"; condition1.BackColorRGB = Color.LightGray;