We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to set alternative row color in an Excel file using XlsIO?

Platform: ASP.NET Web Forms (Classic) |
Control: XlsIO |
Published Date: October 30, 2015 |
Last Revised Date: April 5, 2021

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;

 

 

 

ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon