Issue with Aspose.Cells conditional formatting

Good night,


We are trying Aspose here to decide if our company will buy it or not to generate PDF from XLSX files and we are not being able to conditional format numbers in the way we need, which is:

#,##0.00;RED

Does Aspose support it? Does it not?

If so how can I achieve to have it done that way since whenever I put that on Excel conditional formatting it will still display negative values with a minus and in red.

Regards

Hi Felipe,


Thank you for considering Aspose products, and welcome to Aspose.Cells support forum.

You may use the Style’s Custom property to set the desired custom format for a given cell or use the built-in number formats by using the Number property of the Style object.

Note: Built-in number format 7 is equal to the custom format #,##0.00;RED

Please check the following code snippets and attached resultant spreadsheet & PDF file for your review. We also recommend you to go through the detailed article on Setting Display Formats of Numbers and Dates.

C#

//Create an instance of Workbook
Workbook book = new Workbook();

//Create a new Style object and it to the Workbook’s style collection
Style style = book.Styles[book.Styles.Add()];

//Set the custom property of style object to desired patern/format
style.Custom = “#,##0.00;RED”;

//Access the first worksheet
Worksheet sheet = book.Worksheets[0];

//Access cells of first worksheet
Cells cells = sheet.Cells;

//Access A1 cells to put a number value
Cell cell = cells[“A1”];
cell.PutValue(12345);

//Set the style of cell, while passing StyleFlag object that
//specifies which format(s) to be applied
cell.SetStyle(style, new StyleFlag() { NumberFormat = true });

//Access B1 cell and insert a string value and convert it to number at run-time
cell = cells[“B1”];
cell.PutValue(“-12345”, true);

//Apply style to B1
cell.SetStyle(style, new StyleFlag() { NumberFormat = true });

//Auto fit columns as cell values may be larger than current column width
sheet.AutoFitColumns();

//Save spreadsheet to disk
book.Save(myDir + “output.xlsx”, SaveFormat.Xlsx);

//Save spreadsheet as PDF
book.Save(myDir + “output.pdf”, SaveFormat.Pdf);


Please feel free to write back in case you need our further assistance.

Hi,


Well, you should simply apply numbers formattings accordingly instead of applying conditionally formatting to the cells for your needs. Please see the sample code below for your reference:
e.g
Sample code:

Workbook workbook = new Workbook();
Cell cell = workbook.Worksheets[0].Cells[“A1”];
cell.PutValue(-3435.4376);
//Get the style of the cell.
Style style = cell.GetStyle();
//Apply the custom numbers formattings
style.Custom = "#,##0.00;Red ";
//Apply the style to the cell.
cell.SetStyle(style);
workbook.Save(“e:\test2\testcustomformat.xlsx”);


Also, please see the documents for your complete reference:
http://www.aspose.com/docs/display/cellsnet/Setting+Display+Formats+of+Numbers+and+Dates
http://www.aspose.com/docs/display/cellsnet/Named+Ranges

Hey guys, thanks!


Both answers were helpful and I’ve managed to get it working.

Regards

Hi Felipe,


Thank you for the confirmation. It’s good to know that you are up & running again. Please feel free to write back in case you need our further assistance.