Conditional Formatting: reading from a loaded .xlsx

Aspose.Cells Product Family
1

Hello,


I am trying to read dynamically created conditional formats from a loaded .xlsx file and am having no luck. Here is the code, first to generate the Excel, then to read the Excel and the conditional format.

Excel generation:

Workbook wb = new Workbook(FileFormatType.Excel2007Xlsx);
wb.Worksheets[0].Cells[0, 0].PutValue(100);

Style style = wb.Styles[wb.Styles.Add()];

style.Font.IsBold = true;
style.BackgroundColor = System.Drawing.Color.Silver;
style.Pattern = BackgroundType.Solid;

ConditionalFormattings cfc = wb.Worksheets[0].ConditionalFormattings;
int fcsId= cfc.Add();
FormatConditions fcs = cfc[fcsId];

CellArea ca = new CellArea();
ca.StartColumn = 0;
ca.EndColumn = 0;
ca.StartRow = 0;
ca.EndRow = 0;

fcs.AddArea(ca);
int fcId = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.NotEqual, “100”, “100”);
fcs[fcId].Style = style;
wb.Save(@“c:\temp\test.xlsx”);

So far, so good. I can open test.xlsx, change the value of cell[0,0] to 150, and the conditional format appears.

Now here is the code that loads the same Excel file, after the value has been changed:

Workbook wb = new Workbook(FileFormatType.Excel2007Xlsx);
wb.LoadData(@“c:\temp\test.xlsx”);

ConditionalFormattings cf = wb.Worksheets[0].ConditionalFormattings;

System.Diagnostics.Debug.Write(fcs.Count);

I’m getting 0 for the count of conditional formats. Am I loading this wrong, or using the wrong access methods? Or is reading in conditional formats even possible?

My ultimate goal, here, is to be able to read those conditional formats to determine if a value has changed and save to the database only if the value changed.

Thanks!
Jim
2

Hi Jim,

Aspose.Cells for .NET does support to read/write conditional formattings. You are using wrong method. Please use Workbook.Open instead of Workbook.LoadData() method. For your information, when you use Workbook.LoadData method, all the drawing objects, shapes, formatting etc would be lost, the data would be retrieved though.

So, kindly change/update your code to:



Workbook wb = new
Workbook(FileFormatType.Excel2007Xlsx);
wb.Open(@“c:\temp\test.xlsx”);


Also, kindly when you use Style.Pattern to specify solid pattern, Style.BackgroundColor would be discarded. Either use a pattern other than None or Solid. Or use Style.ForegroundColor attribute. See the document for reference:http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/colors-background-patterns.html


Thank you.