Data bars in Excel are a built-in type of conditional formatting that inserts colored bars in cells to compare the values within them. The length of a bar depends on the value of a cell and the longest bar corresponds to the largest value in a selected data range, which allows you to spot it at a glance. In this article, you will learn how to add data bars in a cell range using Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Add Data Bars in Excel in C# and VB.NET
Data bars are a great tool for visually comparing data within a selected range of cells. With Spire.XLS for .NET, you are allowed to add a data bar to a specified data range and also set its format. The following are the detailed steps.
- Create a Workbook instance.
- Load a sample Excel document using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worsheets[index] property.
- Add a conditional formatting to the worksheet using Worksheet.ConditionalFormats.Add() method and return an object of XlsConditionalFormats class.
- Set the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
- Add a condition using XlsConditionalFormats.AddCondition() method, and then set its format type to DataBar using IConditionalFormat.FormatType property.
- Set the fill effect and color of the data bars using IConditionalFormat.DataBar.BarFillType and IConditionalFormat.DataBar.BarColor properties.
- Save the result document using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
using Spire.Xls.Core;
using Spire.Xls.Core.Spreadsheet.Collections;
using Spire.Xls.Core.Spreadsheet.ConditionalFormatting;
using System.Drawing;
namespace ApplyDataBar
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load a sample Excel docuemnt
workbook.LoadFromFile("sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Add a conditional format to the worksheet
XlsConditionalFormats xcfs = sheet.ConditionalFormats.Add();
//Set the range where the conditional format will be applied
xcfs.AddRange(sheet.Range["C2:C13"]);
//Add a condition and set its format type to DataBar
IConditionalFormat format = xcfs.AddCondition();
format.FormatType = ConditionalFormatType.DataBar;
//Set the fill effect and color of the data bars
format.DataBar.BarFillType = DataBarFillType.DataBarFillGradient;
format.DataBar.BarColor = Color.Red;
//Save the result document
workbook.SaveToFile("ApplyDataBarsToDataRange.xlsx", ExcelVersion.Version2013);
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
