GetFormatCondition not available?

Aspose.Cells Product Family
1

Hi,


I would like to apply ‘Color Scale’ conditional formatting to a range and am having some problems. I begin by placing a datatable of data into the sheet like this:

Worksheet sheet = book.Worksheets[sheetIndex];
sheet.Name = sheetName;

// Place data
sheet.Cells.ImportDataTable(inputData, true, dataStartRow - 1, dataStartCol, inputData.Rows.Count,
inputData.Columns.Count, false, “dd/mm/yyyy”);

Now, I use an example snippet from here (Set Conditional Formats of Excel and ODS files.|Documentation) to apply the color scale:

// Add colour scale to highlight outliers

FormatConditionCollection conds = GetFormatCondition(“A9:C10”, Color.White);
int idx = conds.AddCondition(FormatConditionType.ColorScale);
FormatCondition cond = conds[idx];
cond.ColorScale.MidCfvo = null;
cond.ColorScale.MinColor = Color.Gold;
cond.ColorScale.MaxColor = Color.SkyBlue;
However it says ‘The name ‘GetFormatCondition’ does not exist in the current context.’ I’ve downloaded and referenced the latest build of the Aspose.Cells DLL zip file and it does not help. The closes I can see is in the ‘Cell’ object, a method called GetFormatConditions. Also the cond.ColorScale.MidCfvo is not recognised.

My desire is as follows:

a) Identify the range the ImportDatatable method used to place its data
b) Exclude the top row (headers), leftmost and rightmost columns, then apply ‘color scale’ conditional formatting to the remaining range.

I’d appreciate your help.

Thanks - Mark

2

Hi,


Thanks for your query.

Well, GetFormatCondition is just a custom method in the document/article that adds a new conditional formatting based on the provided cells area range, it also calls other custom methods in it e.g. FillCell, GetCellAreaByName etc. for applying cells’ shading color and calculate the area etc - these are just user defined custom methods and not the methods of Aspose.Cells API although the code segment used in these methods utilizes Aspose.Cells APIs regarding conditional formattings, see the screen shot below for your reference:
http://prntscr.com/5vxhwy

Moreover, Cells.ImportDataTable can return the Total number of rows imported, so you may get it into some variable object. For getting total number of columns, you may easily use DataTable.Columns.Count attribute for your needs. If you need to get the table range, you may easily evaluate it by yourself. For example you start importing the DataTable into A1 cell, so you may evaluate the starting cell to calculate the indexes (0 based) of the end cell for your requirements by yourself:
e.g
start_column = 0
start_row = 0
ending_row = DataTable.Rows.Count + start_row -1
ending_column = DataTable.Columns.Count + start_column -1
etc.
…
//Now specify the cells range based on the above variables’ values.
…

Hope, this helps you a bit.

Thank you.
3

Perfect - many thanks for your help Amjad!

4

Hi,


Good to know it is helpful for your reference. Feel free to write back if you have further comments or questions, we will be happy to assist you soon.

Thank you.