Hi,
I am using Aspose Cells version 4.7.1.18 and operating with Swedish locality settings (Country Sweden, Language Swedish).
I am doing the following in Excel 2007:
1. Create a new new Excel 2007 workbook.
2. Check the advanced options to make sure it is using system separators.
3. On first worksheet, put the value 6096 in Cell C29 and 19577 in Cell F29.
4. Select Cell F29, click Conditional Formatting, New Rule. Select Use a formula to determine which cells to format.
5. Enter =AND(IF($F$29<=0;ABS($F$29)>1000;ABS($F$29)>5000);IF($C$29=0;FALSE;IF($F$29<=0;ABS($F$29/$C$29)>0,1;ABS($F$29/$C$29)>0,1))) into formula box.
6. Using the Format box, I select the font to be bold.
7. Select OK button.
At this stage, Excel is happy with my formula and the cell F29 is highlighted in bold.
I do the same thing using Aspose Cells. Here is my code.
Workbook book = new Workbook();
book.Worksheets.Add(“Report”);
book.Worksheets[“Report”].Cells[“C29”].PutValue(6096);
book.Worksheets[“Report”].Cells[“F29”].PutValue(19577);
CellArea cellArea = new CellArea();
cellArea.StartColumn = 5;
cellArea.StartRow = 28;
cellArea.EndColumn = 5;
cellArea.EndRow = 28;
int conditionalFormattingIndex = book.Worksheets[“Report”].ConditionalFormattings.Add();
FormatConditions formatConditions = book.Worksheets[“Report”].ConditionalFormattings[conditionalFormattingIndex];
formatConditions.AddArea(cellArea);
int formatConditionIndex = formatConditions.AddCondition(FormatConditionType.Expression, OperatorType.None, “=AND(IF($F$29<=0;ABS($F$29)>1000;ABS($F$29)>5000);IF($C$29=0;FALSE;IF($F$29<=0;ABS($F$29/$C$29)>0,1;ABS($F$29/$C$29)>0,1)))”, null);
FormatCondition formatCondition = formatConditions[formatConditionIndex];
// copy style
formatCondition.Style.Font.IsBold = true;
book.Save(Path.Combine(Application.StartupPath, “swedishformula.xls”));
When I run this, I am getting the following Error:
"Invalid formula:“AND(IF($F$29<=0;ABS($F$29)>1000;ABS($F$29)>5000);IF($C$29=0;FALSE;IF($F$29<=0;ABS($F$29/$C$29)>0,1;ABS($F$29/$C$29)>0,1)))”."
When I run the above code in Australia/English (Australia) settings with “=AND(IF($F$29<=0,ABS($F$29)>1000,ABS($F$29)>5000),IF($C$29=0,FALSE,IF($F$29<=0,ABS($F$29/$C$29)>0.1,ABS($F$29/$C$29)>0.1)))” as the formula, it works. So the code is fine.
Your help is much appreciated.
Thanks.
lama
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Thank you for considering Aspose.
Well, Aspose.Cells does not support formula separators / delimiters (like ‘;’ semicolin in your case) depending on the local settings. So please use the same formula as you are using for "Australian English" local setting with delimiter “,” (comma). You may also check the following documentation link as per your scenario:
Thank You & Best Regards,