API change with GETFormatConditions

Aspose.Cells Product Family
1
--->FormatConditionCollection collection = localCell.GetFormatConditions();
Int32 index = localSheet.ConditionalFormattings.Add();
FormatConditionCollection clN = localSheet.ConditionalFormattings[index];
CellArea areaN = new CellArea(); areaN.StartRow = cStyleStartCellRow; areaN.EndRow = endRowPosition; areaN.StartColumn = cStyleStartCellCol; areaN.EndColumn = startColPosition - 1;

clN.AddArea(areaN);
for (Int32 i = 0; i < collection.count; i++)
{
Style ss = new Style(); Style ssX = new Style();
if (i == 0)
{
ss.BackgroundColor = System.Drawing.Color.White;
ss.Font.Color = System.Drawing.Color.White;
}
else if ( i == 1 )
{
//ss.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.None;
}
else
{
ss.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thick;
ss.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Red;
}

---> Int32 conditionIndexN = clN.AddCondition(FormatConditionType.Expression, OperatorType.None, collection.Formula1, collection.Formula2);
FormatCondition fcN = clN[conditionIndexN]; fcN.Style = ss;
}


After updating from Aspose 6.x to Aspose 8.x the above code breaks pointed by arrow. How do i do this in the latest version.

Now the GetFormatConditions() is returning a collection. And also collection.Formula1 and Formula2 are not exposed any more.
2

Hi Naren,


Thank you for contacting Aspose support.

Please check the following piece of code that demonstrates the usage of latest Aspose.Cells for .NET APIs for your presented scenario. Please note, I have made some changes in order to test the code so you should amend it according to your application requirements. Please find the attachment for the source & resultant spreadsheets for your reference.

C#

var book = new Workbook(dir + “book1.xlsx”);
var sheet = book.Worksheets[0];
var cell = sheet.Cells[“A1”];
FormatConditionCollection [] formatConditions = cell.GetFormatConditions();
var formatCondition = formatConditions[0];
int index = sheet.ConditionalFormattings.Add();
var collection = sheet.ConditionalFormattings[index];
index = collection.AddCondition(FormatConditionType.CellValue, OperatorType.Between, formatCondition[0].Formula1, formatCondition[0].Formula2);
collection.AddArea(CellArea.CreateCellArea(“B1”, “B2”));
collection[0].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thick;
collection[0].Style.Borders[BorderType.BottomBorder].Color = Color.Red;
book.Save(dir + “output.xlsx”);
3
I have updated the code.
I am able to see the conditional formatting on the excel sheet but it is not working..
The formula values are :

formatCondition [0].Formula1 =AND(UPPER(INDIRECT(ADDRESS(63,COLUMN()-((COLUMN()-13)-(FLOOR((COLUMN()-(13-1))/4,1)*4))-1)))="M",UPPER(INDIRECT(ADDRESS(66,COLUMN()))="X"),UPPER(INDIRECT(ADDRESS(ROW(),COLUMN()-((COLUMN()-13)-(FLOOR((COLUMN()-(13-1))/4,1)*4)))))="X")

formatCondition [1].Formula1
=AND(UPPER(INDIRECT(ADDRESS(63,COLUMN()-((COLUMN()-13)-(FLOOR((COLUMN()-(13-1))/4,1)*4))-1)))="M",UPPER(INDIRECT(ADDRESS(66,COLUMN())))<>"X")

formatCondition [2].Formula1
=AND(UPPER(INDIRECT(ADDRESS(63,COLUMN()-((COLUMN()-13)-(FLOOR((COLUMN()-(13-1))/4,1)*4))-1)))<>"M",UPPER(INDIRECT(ADDRESS(ROW(),COLUMN()-((COLUMN()-13)-(FLOOR((COLUMN()-(13-1))/4,1)*4)))))="X")

See code below,

FormatConditionCollection[] formatConditions = localCell.GetFormatConditions();
var formatCondition = formatConditions[0];

if (null != formatCondition && template.DisplayTags["DR"].ApplicableValue)
{
Int32 index = localSheet.ConditionalFormattings.Add();
var clN = localSheet.ConditionalFormattings[index];
CellArea areaN = new CellArea(); areaN.StartRow = cStyleStartCellRow; areaN.EndRow = endRowPosition; areaN.StartColumn = cStyleStartCellCol; areaN.EndColumn = startColPosition - 1;

for (Int32 i = 0; i < formatCondition.Count; i++)
{

Style ss = new Style();
if (i == 0)
{
ss.BackgroundColor = System.Drawing.Color.White;
ss.Font.Color = System.Drawing.Color.White;
}
else if (i == 1)
{
//Do nothing
}
else
{
ss.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thick;
ss.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Red;
}


index = clN.AddCondition(FormatConditionType.Expression, OperatorType.None, formatCondition[i].Formula1, formatCondition[i].Formula2);
clN.AddArea(areaN);
clN[index].Style = ss;
}
}
4

Hi Joy,


Please note, the solution shared in my previous response is generic and it is working fine against the sample of my own. If you are not getting the desired results with the mentioned changes then please share your input spreadsheet and a standalone executable sample application along with the current output and desired results (that you may create manually in Excel application) here for further investigation. Unfortunately, we will not be able to assess the problem cause unless you share the above requested.

Thank you for your cooperation & understanding.
5

After reworking the code, there was an issue with XLSX conditional formatting using “AND” and “OR” when used in the formula. Somehow Microsoft Excel in xlsx and xlsm format is not recognizing it only in conditional formatting.


This is not an aspose issue.

6

Hi Joy,


It is good to know that you are able to sort out the problem cause. We will consider this incident as closed. Please feel free to contact us back in case you need our further assistance with Aspose APIs.