We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Ambiguous Type and Operator return for Conditional Formatting on Date type

I have used conditional formatting on a date field of a excel file, which has condition as ( if Date Occurring - Yesterday) . On reading the type and operator of this conditional format object, it read as Type - 16 and Operator - 0. The type and operator read for other date conditions such as Today, Tomorrow, Last week, This week, Next week, Last month, This Month and Next Month, also returns the Type as 16 and operator as 0. So, I could not differentiate between the date conditional format used in the file. Is there any way to find the type of conditional format used , in the above said scenario?



PS: Conditional Formatting details:

Style : Classic
Option: Format only date cells that contains
Option:Date Occuring Option: Yesterday




Hi,


Thanks for providing us some details.

Please provide your template file and sample JAVA program (runnable) to show the issue on our end, we will check it soon.

Also. please try our latest version/fix: Aspose.Cells for Java v8.6.0.x (if you are not already using it).

Thank you.
In the attached file, I have added two conditional formatting and on running the below code, it produces type and operator value same in both the conditional formats. Is there any way to distinguish between the two conditional formatting without analysing the formula component attached.

Here is the sample code:

public static void main(String[] args) throws Exception

{


LoadOptions loadoptions = new LoadOptions(

FileFormatType.EXCEL_2007_XLSX);

Workbook workbook = new Workbook(

"date.xlsx", loadoptions);

int sheetIndex = workbook.getWorksheets().getActiveSheetIndex();

Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);

ConditionalFormattingCollection cfs = worksheet

.getConditionalFormattings();

int cfsCount = cfs.getCount();

for (int i = 0; i < cfsCount; i++)

{

FormatConditionCollection fcs = cfs.get(i);

int fcsRangeCount = fcs.getRangeCount();

int fcsCount = fcs.getCount();

for (int j = 0; j < fcsRangeCount; j++)

{

for (int k = 0; k < fcsCount; k++)

{

FormatCondition fc = fcs.get(k);

int operator = fc.getOperator();

int type = fc.getType();

String formula1 = fc.getFormula1();


System.out.println("type = " + type);

System.out.println("Operator = " + operator);

System.out.println("Formula =" + formula1);

}


}

}

}

Hi,

Thanks for your posting and using Aspose.Cells.

We think, FormatCondition.getTimePeriod() method will resolve your issue.

Please see the following sample code for your reference. Let us know your feedback.

Java
String filePath = “D:\Downloads\date.xlsx”;

Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.getWorksheets().get(0);

FormatCondition fc1 = worksheet.getConditionalFormattings().get(0).get(0);
FormatCondition fc2 = worksheet.getConditionalFormattings().get(0).get(1);

//First is Today
if (fc1.getTimePeriod() == TimePeriodType.TODAY)
System.out.println(true);

//Second is Last Week
if (fc2.getTimePeriod() == TimePeriodType.LAST_WEEK)
System.out.println(true);

Console Output
true
true

Thank you. This will resolve the issue.

Hi,


Good to know that your issue is sorted out by the suggested code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.