Like word, data stored in Excel has format. Sometimes, we need to change the format of the data. Spire.XLS, an easy but powerful Excel .NET component can meet your need both in data dealing and format settings.
In this article, we will introduce you how to alternate row colors with conditional formatting.
Step 1: Select the range that you want to format
CellRange dataRange = sheet.AllocatedRange;
Step 2: Set conditional formation
Call the method AddCondition to add a new conditional formatting instance format1.
- FirstFormula : It determines which cells to format.
- FormatType : Set conditional formatting type
- BackColor : Set as LightSeaGreen.
- format1 sets the backcolor of the even rows as LightSeaGreen.
ConditionalFormatWrapper format1 = dataRange.ConditionalFormats.AddCondition(); format1.FirstFormula = "=MOD(ROW(),2)=0"; format1.FormatType = ConditionalFormatType.Formula; format1.BackColor = Color.LightSeaGreen;
format2 sets the backcolor of the odd rows as Yellow.
Full code:
using Spire.Xls;
using System.Drawing;
namespace AlternateRowColors
{
class Program
{
static void Main(string[] args)
{
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
CellRange dataRange = sheet.AllocatedRange;
ConditionalFormatWrapper format1 = dataRange.ConditionalFormats.AddCondition();
format1.FirstFormula = "=MOD(ROW(),2)=0";
format1.FormatType = ConditionalFormatType.Formula;
format1.BackColor = Color.LightSeaGreen;
ConditionalFormatWrapper format2 = dataRange.ConditionalFormats.AddCondition();
format2.FirstFormula = "=MOD(ROW(),2)=1";
format2.FormatType = ConditionalFormatType.Formula;
format2.BackColor = Color.Yellow;
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
}
}
}
}
Screenshot:
