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

Free Support Forum - aspose.com

Conditional formating problem


I use Aspose.Cells to insert data into sheet and use this data as a source of pivot table and other reports. I have a ‘.xls’ template with empty pivot (first sheet), second sheet is a data sheet and third sheet is a report sheet and I use this template to generate report.
I have two conditional formating rules in third sheet.
When I open this template with Aspose, insert data into second sheet (by sheet.Cells.ImportDataTable(dtData.ToTable(), true, 0, 0) and change named range. Next I save it with workbook.Save(filename) and I notice that first rule of conditional formating has no format defined.

I attach you a template file which I use

Could you give me an advice for this?


Could you provide us some more details, on which cell(s) in the third sheet, do you place conditional formatting rules? Do you implement conditional formattings dynamically using Aspose.Cells API or you have manually created conditional formatting in MS Excel in the template file. Could you post your generated file here to show the issue, we will check it soon.

Thank you.

I’m not author of this sheet, so I don’t know details now. I use Excel 2007, so I don’t have to know cells, because I have a list of all conditional formatings applied to sheet. I send you screenshot of this list (from generated file - you can check that first rule has no format set) and generated .xls file as attachment. If this information is insufficient for you I will send you cells with conditional formating applied at Monday.
I don’t implement any conditional formating dynamically - the formating is applied in template file I sent you at previous message. I only insert new data and change data range (named ‘PivotData’).

Can I easily check the format of conditional formating via C#?
If I debug code used to generate result file then I can check number of conditional formating rules and data ranges which it is applied to.
If I can check the format I will examine when the format is cleared.

Is there a method or property to check conditional formating format?


Yes, you can manipulate and read the conditional formattings attributes in the template file using Aspose.Cells APIs, I tried the following sample code with a simple template file (conditional formattings implemented in it) and it works fine, kindly check and consult it:

//Create a Workbook object
Workbook workbook = new Workbook();
//Open the Excel file
//Get the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

ConditionalFormattings cfs = worksheet.ConditionalFormattings;
FormatConditions fcs = cfs[0];
FormatCondition fc = fcs[0];
//Get the first cell area on which the conditional formattings is applied
CellArea ca = fcs.GetCellArea(0);
//Get the Formula1
string f1 = fc.Formula1;
//Get the Operator
OperatorType op1 = fc.Operator;
//Get the font color
Color c1 = fc.Style.Font.Color;
MessageBox.Show("Area: " + CellsHelper.ColumnIndexToName(ca.StartColumn).ToString() + CellsHelper.RowIndexToName(ca.StartRow).ToString()+ ":" + CellsHelper.ColumnIndexToName(ca.EndColumn).ToString() + CellsHelper.RowIndexToName(ca.EndRow).ToString());// displays A21:A26
MessageBox.Show(f1);// displays 20
MessageBox.Show(op1.ToString()); // displays GreaterThan
MessageBox.Show(c1.ToString()); // displays blue color with rgbs value.

Which version of Aspose.Cells for .NET you are using, could you try the latest fix (4.5.1): http://www.aspose.com/community/files/51/file-format-components/aspose.cells/entry145662.aspx

If you still find any issue, kindly post your template file with conditional formattings applied on its cells and output file (processed by Aspose.Cells), we will check it soon.

Thank you.

Thank you for fast replies. I use the latest fix you point and it fixes my problem.

It’s very good time synchronization :slight_smile: