CODEWITHSUNDEEP
c#excelclosedxml
Johannes Krauser III
Johannes Krauser III

Reputation: 25

Setting style in cells with conditional formatting in ClosedXML C#

There was a need to make conditional formatting of a cell with a histogram. Used ClosedXML but it didn't give the desired result.

enter image description here

It is necessary to solve the problem with both the gradient and negative numbers. Has anyone encountered something similar? I am attaching the code.

form_sheet.Cell("D37")
   .AddConditionalFormat()
   .DataBar(XLColor.FromArgb(68, 114, 196), false)
      .Minimum(XLCFContentType.Number, -3)
      .Maximum(XLCFContentType.Number, 3);

Ready to consider alternative solutions not through ClosedXML. The program will generate several dozen reports. All histograms will be in the same cells, so I also considered vbs, but I don’t have enough experience to write such a script that would change styles immediately for a bunch of documents.

Upvotes: 0

Views: 1428

Answers (1)

locknies
locknies

Reputation: 1355

Bit late to answer, might be helpful for others..

I also gone through the same gradient issue. Currently using ClosedXML it is not possible to generate conditional DataBar with solid color.

What am doing to resolve my issue is to generate the Excel as of now with ClosedXML and re-open the Excel again in Interop and add the DataBar in the respective cells using Interop.Excel.

I haven't fully rewrote the code using interop because performance wise we can't fully rely on interop as compared to ClosedXML, atleast for me.

Sample code for adding Databar using Interop

var excel = new Microsoft.Office.Interop.Excel.Application();

var workBooks = excel.Workbooks;
var workBook = workBooks.Add();
var workSheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;

workSheet.Cells[1, "A"] = 10;
               
Microsoft.Office.Interop.Excel.Range range1 = workSheet.Cells[1, 1];

Microsoft.Office.Interop.Excel.Databar bar = (Microsoft.Office.Interop.Excel.Databar)range1.FormatConditions.AddDatabar();
bar.BarFillType = Microsoft.Office.Interop.Excel.XlDataBarFillType.xlDataBarFillSolid;

Thanks.

Upvotes: 1

Related Questions