Data Bars in Conditional Formatting

26 Aug 20245 minutes to read

Here, the values in each of the selected cells are compared, and a data bar is drawn in each cell representing the value of that cell relative to the other cells in the selected range. This bar provides a clear visual cue for users, making it easier to pick out larger and smaller values in a range.

The following code example illustrates how to apply data bars using IDataBar interface in XlsIO.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    FileStream inputStream = new FileStream("../../../Data/InputTemplate.xlsx", FileMode.Open, FileAccess.Read);
    IWorkbook workbook = application.Workbooks.Open(inputStream);
    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 the constraints
    dataBar.MinPoint.Type = ConditionValueType.LowestValue;
    dataBar.MaxPoint.Type = ConditionValueType.HighestValue;

    //Set color for Bar
    dataBar.BarColor = Color.FromArgb(156, 208, 243);

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

    //Saving the workbook
    FileStream outputStream = new FileStream("DataBar.xlsx", FileMode.Create, FileAccess.Write);
    workbook.SaveAs(outputStream);

    //Dispose streams
    outputStream.Dispose();
    inputStream.Dispose();
}
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 the constraints
    dataBar.MinPoint.Type = ConditionValueType.LowestValue;
    dataBar.MaxPoint.Type = ConditionValueType.HighestValue;

    //Set color for Bar
    dataBar.BarColor = Color.FromArgb(156, 208, 243);

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

    //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 the constraints
    dataBar.MinPoint.Type = ConditionValueType.LowestValue
    dataBar.MaxPoint.Type = ConditionValueType.HighestValue

    ' Set color for Bar
    dataBar.BarColor = Color.FromArgb(156, 208, 243)

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

    ' 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.