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.

 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;

 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.

// 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;

Did you find this information helpful?

Yes

Comments

Please sign in to leave a comment