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

 

 

 

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