Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Tue Aug 29, 2017 4:24 pm

Hello,

I have some questions about conditional formats.

1°) Average
I have find the formatType "Above Average" but i don't found "Below Average"

Code: Select all
var average = rangeSpire.ConditionalFormats.AddCondition();
average.FormatType = ConditionalFormatType.AboveAverage;
//average.FormatType = ConditionalFormatType.BelowAverage;


2°) Top 10
I don't find propertie to set up formatType "Top10" (Top, bottom, rank, percent...),
like DataBar to formatType "DataBar" or IconSet to formatType "IconSet".

Code: Select all
var top = rangeSpire.ConditionalFormats.AddCondition();
top.FormatType = ConditionalFormatType.Top10;


3°) TimePeriod
In formatType "TimePeriod", I do'nt know how set up DateOperator (yesterday, today, tomorrow...)

Code: Select all
var top = rangeSpire.ConditionalFormats.AddCondition();
top.FormatType = ConditionalFormatType.TimePeriod;


For the other conditions, I managed to do what I wanted :)

Thanks

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Wed Aug 30, 2017 7:19 am

Dear VincentB,

Thanks for your inquiry.
Sorry that Spire.XLS doesn't support the three settings at present. But they have been implemented, just not include into current hotfix. We will let you know when the hotfix is available.
In addition, for the third requirement, here is another way to achieve same result at present, that is formula.
Code: Select all
            ConditionalFormatWrapper format = sheet.Range["E1:E8"].ConditionalFormats.AddCondition();
            format.FormatType = ConditionalFormatType.Formula;
            //modify the formula according to your specific requirement.
            format.FirstFormula = "=$E1=TODAY()";
            format.FontColor = Color.Red;


Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Wed Aug 30, 2017 8:21 am

Hello,

Ok thanks for your reply,
Waiting for hotfix,

Thanks

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Fri Oct 20, 2017 7:32 am

Hi VincentB,

Thanks for waiting.
Now the new feature(TimePeriod) has been implemented in Spire.XLS Pack Hotfix Version:7.12.100. Sample code:
Code: Select all
            ConditionalFormatWrapper format1 = sheet.Range["A1:C50"].ConditionalFormats.AddCondition();
            format1.FormatType = ConditionalFormatType.TimePeriod;
            format1.SetTimePeriod(TimePeriodType.Yesterday);

As for the other two requirements, we found some issues after testing, we will fix them and let you know when the hotfix is available.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Tue Oct 24, 2017 8:42 am

Hello,

I have test TimePeriod and it's works

Thanks

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Mon Oct 30, 2017 4:40 pm

Hello

I have an other question about conditionals formats.
I don't find property "StopIfTrue" (it's Excel Interop name)

Code: Select all
var cond = cellRange.ConditionalFormats.AddCondition();
cond.StopIfTrue = true; // I don't find it


ConditionalFormat.PNG
ConditionalFormat.PNG (14.32 KiB) Viewed 6397 times


Other question :
In interop, it's possible to put a ConditionalFormat in first position:
Code: Select all
FormatCondition cond = rangeXL.FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlGreater, Alerte.GetValeurDetail1())
cond.SetFirstPriority(); // Put condition in top for the list

Is it possible to change the order of conditions in Spire?

I probably miss something for this 2 points, could you help me ?

Thanks

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Tue Oct 31, 2017 3:36 am

Hi VincentB,

Thanks for your inquiry.

1) The property "StopIfTrue".
Sorry that Spire.XLS doesn't support setting it while adding ConditionalFormats at present, it needs to get first then set, please refer to code below.
Code: Select all
            ConditionalFormatWrapper Cond = sheet.Range["A1:A5"].ConditionalFormats.AddCondition();
            Cond .FormatType = ConditionalFormatType.Formula;
            //get and convert to XlsConditionalFormat
            XlsConditionalFormat format = sheet.ConditionalFormats[0][0] as XlsConditionalFormat;
            format.StopIfTrue = true;


2) ConditionalFormat position
I found there is a property "Priority" which could change the position of the ConditionalFormat. But there is an issue, the property under ConditionalFormatWrapper doesn't work. The property under XlsConditionalFormat could work. I have posted the issue with ConditionalFormatWrapper to our Dev team, once there is any progress, we will let you know.
Code: Select all
            ConditionalFormatWrapper Cond = sheet.Range["A1:A5"].ConditionalFormats.AddCondition();
            //this doesn't work
            format.Priority = 0;           
            XlsConditionalFormat format2 = sheet.ConditionalFormats[1][0] as XlsConditionalFormat;
            //this could work.
            format2.Priority = 0;


Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Tue Oct 31, 2017 10:45 am

Hi,

Use XlsConditionalFormat seems works for StopIfTrue and Priority.

Thanks for your workaround.

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Tue Oct 31, 2017 2:26 pm

Hi,

I want to find a new ConditionalFormat("ConditionalFormatWrapper") in sheet.ConditionalFormats ("XlsConditionalFormat").
For that, i use "FormatType", "Operator" , "FirstFormula" and "Address" to identify a ConditionalFormat.
But in a new ConditionalFormat, "Address" is not set. ("A1" is default value i think)

Code: Select all
sheet.Range["B1:B3"].ConditionalFormats.AddCondition();
var address= sheet.ConditionalFormats[0].Address; // "A1"


I need a way to identify specificly a new ConditionalFormat make like that :
Code: Select all
sheet.Range[XXX].ConditionalFormats.AddCondition();

into sheet.ConditionalFormats.

Do you have a solution ?

Thanks

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Wed Nov 01, 2017 5:51 am

Hi VincentB,

Thanks for your inquiry.
After investigation, we found the Address returns correct ranges of ConditionalFormat in existing document, yet it returns "A1" when creating a new ConditionalFormat. And I have logged the issue in our bug system, once there is any progress, we will let you know.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Tue Nov 07, 2017 7:17 am

Hi VincentB,

Thanks for waiting.
We publish Spire.XLS Pack(Hotfix) Version:7.12.109 which implements the new features and solves some issues.

1)Average
Please refer to code below.
Code: Select all
            var cond2 = sheet.Range["C1:C3"].ConditionalFormats.AddCondition();
            cond2.FormatType = ConditionalFormatType.Average;
            cond2.Average.Type = AverageType.Above;
            cond2.FontColor = Color.Yellow;


2)Top10
Sample code for reference.
Code: Select all
            var cond3 = sheet.Range["D1:D10"].ConditionalFormats.AddCondition();
            cond3.FormatType = ConditionalFormatType.TopBottom;
            cond3.TopBottom.Type = TopBottomType.Top;
            cond3.TopBottom.Rank = 2;


3)The property "StopIfTrue" and ConditionalFormat position.
Now you could only set the value for ConditionalFormatWrapper.
Code: Select all
            ConditionalFormatWrapper format = sheet.Range["A1:A5"].ConditionalFormats.AddCondition();
            format.FormatType = ConditionalFormatType.Formula;
            format.StopIfTrue = true;
            format.Priority = 0;

As for the address issue, sorry that it is not fixed at present, we will let you know when it is fixed.
Looking forward to your feedback.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Fri Dec 01, 2017 8:30 am

Hi VincentB,

Thanks for waiting.
Glad to inform you that the address issue has been fixed in Spire.XLS Pack(Hotfix) Version:7.12.125.
Looking forward to your feedback.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Fri Dec 08, 2017 7:03 am

Hi VincentB,

Greetings from E-iceblue.
Do you use the hotfix ? Has your issue been resolved ?

Thanks,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Tue Dec 19, 2017 3:46 pm

Hello,

Thanks for your hotfix. It resolved some issues (StopIfTrue...) but it still one not easy to reproduce :)

In attachement, you will find 2 files : "Sample.xlsm" and "Sample - out.xlsm"
"Sample.xlsm" is the file source file to which I want to add conditional formats.
"Sample - out.xlsm" is the file update with Spire 7.12.125 and source code following.

Code: Select all
            var workbook = new Workbook();
            workbook.LoadFromFile(Chemin + "Sample.xlsm");
           
            #region CA N-N-1

            var worksheet = workbook.Worksheets[0];
            var range = worksheet.Range["C7:C18"];

            var cond = range.ConditionalFormats.AddCondition(ConditionalFormatType.CellValue, true);
            cond.Operator = ComparisonOperatorType.Less;
            cond.FirstFormula = "0";
            cond.FontColor = Color.Red;

            cond = range.ConditionalFormats.AddCondition(ConditionalFormatType.CellValue, true);
            cond.Operator = ComparisonOperatorType.Greater;
            cond.FirstFormula = "0";
            cond.FontColor = Color.Green;

            range = worksheet.Range["C24:C35"];
            cond = range.ConditionalFormats.AddCondition(ConditionalFormatType.CellValue, true);
            cond.Operator = ComparisonOperatorType.Less;
            cond.FirstFormula = "0";
            cond.FontColor = Color.Red;

            cond = range.ConditionalFormats.AddCondition(ConditionalFormatType.CellValue, true);
            cond.Operator = ComparisonOperatorType.Greater;
            cond.FirstFormula = "0";
            cond.FontColor = Color.Green;

            #endregion CA N-N-1

            #region CA OBJECTIF

            worksheet = workbook.Worksheets[1];
            range = worksheet.Range["C7:C18"];
            cond = range.ConditionalFormats.AddCondition(ConditionalFormatType.CellValue, true);
            cond.Operator = ComparisonOperatorType.Greater;
            cond.FirstFormula = "1";
            cond.FontColor = Color.Green;

            cond = range.ConditionalFormats.AddCondition(ConditionalFormatType.CellValue, true);
            cond.Operator = ComparisonOperatorType.Greater;
            cond.FirstFormula = "0.85";
            cond.FontColor = Color.Orange;

            cond = range.ConditionalFormats.AddCondition(ConditionalFormatType.CellValue, true);
            cond.Operator = ComparisonOperatorType.Less;
            cond.FirstFormula = "0.85";
            cond.FontColor = Color.Red;

            range = worksheet.Range["C24:C35"];
            cond = range.ConditionalFormats.AddCondition(ConditionalFormatType.CellValue, true);
            cond.Operator = ComparisonOperatorType.Greater;
            cond.FirstFormula = "1";
            cond.FontColor = Color.Green;

            cond = range.ConditionalFormats.AddCondition(ConditionalFormatType.CellValue, true);
            cond.Operator = ComparisonOperatorType.Greater;
            cond.FirstFormula = "0.85";
            cond.FontColor = Color.Orange;

            cond = range.ConditionalFormats.AddCondition(ConditionalFormatType.CellValue, true);
            cond.Operator = ComparisonOperatorType.Less;
            cond.FirstFormula = "0.85";
            cond.FontColor = Color.Red;

            #endregion CA OBJECTIF

            workbook.SaveToFile(Chemin + "Sample - Out.xlsm", ExcelVersion.Version2016);


Sheet 1 "ca-n-n-1" is correct !

My issues is in sheet 2 "ca-objectifs".
Number of conditional format is correct but not order, range source and colors.
You will find more information in "Sample - Out.

I think the problems are that the ranges are the same between the 2 sheets...

Said me if you do'nt reproduce the issues.

Thanks
Attachments
Sample.zip
(78.38 KiB) Downloaded 291 times

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Wed Dec 20, 2017 3:54 am

Hello,

Thanks for your inquiry. I have reproduced the issue on my side and logged it into our bug tracking system. Once it's fixed, we will inform you. We apologize for the inconvenience.

Best regards,
Simon
E-iceblue support team
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Return to Spire.XLS