Create Databar with max value C#

[MonikaK]

Dear Iceblue Team :)

i need to create a Databar in my Excelsheet.
How can i set the max value for the Databar programmatically in C#?

for example:
Maxvalue = 1000;
My input value = 500;
-> The Databar in my excelsheet should be filled by 50%


Best regards
Monika

[Betsy.jiang]

Hi,

Thanks for your inquiry.
Please refer to following code:

Code: Select all

            Workbook workbook = new Workbook();
            workbook.LoadFromFile(FilePath +"sample.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            XlsConditionalFormats conditional = sheet.ConditionalFormats.Add();
            conditional.AddRange(sheet.Range["A1:A5"]);
            IConditionalFormat format = conditional.AddCondition();
            format.FormatType = ConditionalFormatType.DataBar;
            format.DataBar.MinPoint.Type = ConditionValueType.Number;
            format.DataBar.MinPoint.Value = "500";
            format.DataBar.MaxPoint.Type = ConditionValueType.Number;
            format.DataBar.MaxPoint.Value = "1000";
            workbook.SaveToFile("18918.xlsx", ExcelVersion.Version2010);

If you still have the issue, please provide your input Excel file and desired result for further investigation.

Sincerely,
Betsy
E-iceblue support team

[MonikaK]

Dear Iceblue Team :)

Thank you Betsy for your help, your solution worked fine for me.

I have another question, how can i set the Databar border?
Right now I have the style, wheres no border, so you can't see, how much the databar is exactly filled.

Best regards
Monika

[Betsy.jiang]

Hi Monika,

Thanks for your feedback.
Sorry at present Spire.XLS doesn't support setting Databar border. We will consider adding the new feature in our future upgrade. Once there is any good news, we will let you know.

Sincerely,
Betsy
E-iceblue support team

[MonikaK]

Ok. Thank you, for your help.

Best regards,
Monika

[Doris.Liu]

Hi,

Thanks for your patience.
Glad to inform you that we just released Spire.XLS Pack(Hotfix) 13.5.0, which contains the fix of SPIREXLS-1805.
Please download the new version from the following links and refer to code below to test.
Download links:
Website: https://www.e-iceblue.com/Download/down ... t-now.html
Nuget: https://www.nuget.org/packages/Spire.XLS/13.5.0

Code: Select all

            Workbook workbook = new Workbook();
            workbook.LoadFromFile(inputFile);
            Worksheet sheet = workbook.Worksheets[0];
            XlsConditionalFormats cfs = sheet.ConditionalFormats[0];
            IConditionalFormat cf = cfs[0];
            Spire.Xls.DataBar dataBar1 = cf.DataBar;
            dataBar1.BarBorder.Type = Spire.Xls.Core.Spreadsheet.ConditionalFormatting.DataBarBorderType.DataBarBorderNone;
            dataBar1.BarBorder.Color = Color.Blue;
            XlsConditionalFormats cfs2 = sheet.ConditionalFormats.Add();
            cfs2.AddRange(sheet.Range["C2"]);
            sheet.Range["C2"].NumberValue = 12;
            IConditionalFormat cf2 = cfs2.AddCondition();
            cf2.FormatType = ConditionalFormatType.DataBar;
            cf2.DataBar.BarBorder.Type = Spire.Xls.Core.Spreadsheet.ConditionalFormatting.DataBarBorderType.DataBarBorderSolid;
            cf2.DataBar.BarBorder.Color = Color.Red;
            cf2.DataBar.BarColor = Color.GreenYellow;

            workbook.SaveToFile(outputFile, FileFormat.Version2013);
            workbook.Dispose();


Sincerely,
Doris
E-iceblue support team