Data Bars in Conditional Formatting

24 Jul 20264 minutes to read

Data bars draw a horizontal bar in each cell whose length represents the value of that cell relative to the other cells in the selected range. This provides a clear visual cue that makes it easier to compare larger and smaller values at a glance.

The following code example illustrates how to apply data bars using the IDataBar interface in XlsIO. Key members of IDataBar include:

  • BarColor – sets the fill color of the bar.
  • ShowValue – when true (the default), the cell value is displayed; when false, only the bar is shown.
  • MinLength / MaxLength – the bar length as a percentage of the cell width (defaults are 10 and 90).
  • BarOnly – when true, hides the cell value and shows only the bar.
  • BarBorder – the IBorder applied to the data bar.
  • BarDirection – the direction of the bar (ExcelDataBarDirection.Context for the default left-to-right).
  • NegativeBarColor – the color used for negative values.

NOTE

Data-bar conditional formatting is supported in Excel 2007 and later formats (.xlsx, .xlsm).

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"), ExcelOpenType.Automatic);
	IWorksheet worksheet = workbook.Worksheets[0];

	//Create data bars for the data in specified range
	IConditionalFormats conditionalFormats = worksheet.Range["C7:C46"].ConditionalFormats;
	IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();
	conditionalFormat.FormatType = ExcelCFType.DataBar;
	IDataBar dataBar = conditionalFormat.DataBar;

	//Set color for the bar
	dataBar.BarColor = Color.Aqua;

	//Hide the values in data bar
	dataBar.ShowValue = false;

	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/Output.xlsx"));
	#endregion
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];

    //Create data bars for the data in specified range
    IConditionalFormats conditionalFormats = worksheet.Range["C7:C46"].ConditionalFormats;
    IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();
    conditionalFormat.FormatType = ExcelCFType.DataBar;
    IDataBar dataBar = conditionalFormat.DataBar;

    //Set color for the bar
    dataBar.BarColor = Color.Aqua;

    //Hide the values in data bar
    dataBar.ShowValue = false;

    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    ' Create data bars for the data in specified range
    Dim conditionalFormats As IConditionalFormats = worksheet.Range("C7:C46").ConditionalFormats
    Dim conditionalFormat As IConditionalFormat = conditionalFormats.AddCondition()
    conditionalFormat.FormatType = ExcelCFType.DataBar
    Dim dataBar As IDataBar = conditionalFormat.DataBar

    ' Set color for the bar
    dataBar.BarColor = Color.Aqua

    ' Hide the values in data bar
    dataBar.ShowValue = False

    ' Saving the workbook
    workbook.SaveAs("Output.xlsx")
End Using

A complete working example to apply data bars in C# is present on this GitHub page.