Spire.XLS is a professional Excel .NET library that can be used to any type of .NET( C#, VB.NET, ASP.NET, .NET Core) application and Java (J2SE and J2EE) application.

Thu Jun 10, 2021 5:59 pm

I am trying to apply conditional formatting for selected range(eg: A4 to E10), highlight only if selected range is empty.
I tried below options, but result is not as expected.
Option 1:
Code: Select all
ConditionalFormatWrapper format1 = sheet.Range["A4:E10"].ConditionalFormats.AddCondition();               
format1.FormatType = ConditionalFormatType.ContainsBlanks;

Option 2:
Code: Select all
ConditionalFormatWrapper format1 = sheet.Range["A4:E10"].ConditionalFormats.AddCondition();               
format1.FormatType = ConditionalFormatType.ContainsText;
format1.FirstFormula = "";
format1.Operator = ComparisonOperatorType.Equal;
format1.BackColor = Color.Red;

SivaGnet
 
Posts: 1
Joined: Thu Jun 10, 2021 4:13 pm

Fri Jun 11, 2021 8:30 am

Hello,

Thanks for your inquiry.
Please refer to the following code to meet your requirement.
Code: Select all
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Template_Xls_5.xlsx");
            Worksheet sheet = workbook.Worksheets[0];

            XlsConditionalFormats xcfs = sheet.ConditionalFormats.Add();
            xcfs.AddRange(sheet.Range["A4:E10"]);
            IConditionalFormat cf1 = xcfs.AddCondition();
            cf1.FormatType = ConditionalFormatType.ContainsBlanks;
            cf1.BackColor = Color.Red;

            workbook.SaveToFile("result.xlsx");

If this cannot help you, please provide your input file and the desired output for further investigation. Thanks in advance for your assistance.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1224
Joined: Mon Oct 19, 2020 3:04 am

Fri Jun 18, 2021 10:47 am

Hello,

How is your issue now? Did the code we provided work for you? Could you please give us some feedback at your convenience?

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1224
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.XLS