Format Negative Number For Entire Column

Aspose.Cells Product Family
1

Hello,


I’m trying to format negative number to display in red with parentheses but not able to get it working. Please help.

Here is my attempted code:

Style style;
StyleFlag flag;
style = wb.Styles[wb.Styles.Add()];
style.Custom = “#,##0.00;Red”; //Negative numbers in parenthesis with red color
flag = new StyleFlag();
flag.NumberFormat = true;
wb.Worksheets[wsIndex].Cells.ApplyColumnStyle(colIndex, style, flag);
// I tried it like this too: wb.Worksheets[wsIndex].Cells.Columns[colIndex].ApplyStyle(style, flag);
wb.CalculateFormula();
wb.Save(filePath + “.out.pdf”);
2

Hi,


Thanks for your sample code and some details.

Well, your code is Ok but I suspect your underlying data in the column’s cell might be of string type and not numeric data. Take an example here. When you use Cell.PutValue("-123"), it would be inserted as string type and not as numeric data type into the cell. But when you use Cell.PutValue(-123), now it is entered as numeric data type into the cell.

For your information, I used the following sample code to test your scenario/ case a bit, it works fine here. When I open the output files (XLSX into MS Excel and PDF into Acrobat reader), both shows right (formatted) data.
e.g
Sample code:

Workbook wb = new Workbook();

//Input some negative number into A1 cell.
wb.Worksheets[0].Cells[“A1”].PutValue(-2423);

int colIndex = 0;
Style style;
StyleFlag flag;
style = wb.CreateStyle();
style.Custom = “#,##0.00;Red”; //Negative numbers in parenthesis with red color
flag = new StyleFlag();
flag.NumberFormat = true;
wb.Worksheets[0].Cells.ApplyColumnStyle(colIndex, style, flag);

wb.Save(“e:\test2\out1.xlsx”);
wb.Save(“e:\test2\out1.pdf”, SaveFormat.Pdf);


Thank you.
3

You are right. It’s text. I thought the custom format would first convert it to number and then apply the red styling with parenthesis. I guess it’s not the case. How can I convert the entire column to number? I want to do this first, and then run my code - it should work right?

4

Hi,


I am afraid, you have use your own code to convert your string data (numbers stored as text) to numeric data for the cells. You may also try to use static .NET methods e.g Convert.ToXXXX() for the purpose.

Thank you.