Custom Format is removing -ve sign for a value field

Aspose.Cells Product Family
1

Hi Team,

I have a report which is having total field. Sometimes I will get totals with negative numbers. I am using below property which is removing -ve sign… I am this to show comma’s.

Style.Custom = “#,##0.00;#,##0.00”

Please help what is the alternative way to display both commas and sign of the value.

Thanks in advance.

2

Hi,

Thanks for your posting and using Aspose.Cells.

Please provide us your sample excel file which should contain numbers with desired formatting and you can create it manually using Microsoft Excel. We will look into your sample file and provide you a number format pattern string that can be used in Aspose.Cells API to provide the same functionality.

3

I have below line of code to display total value



objxlWs.Cells(lngFooterRow, i).Value = CDbl(FormatNumber((CDbl(arr_totals(1, i))), arr_columns(1, i), , , ))



at the time of debug above line of code is giving

-151306.29



after that I have below code for formatting



Dim GrandTotalStyle As Style





GrandTotalStyle = xlWb.CreateStyle()

GrandTotalStyle.Font.Name = “Arial”

GrandTotalStyle.Font.Size = 8

GrandTotalStyle.Font.IsBold = True

GrandTotalStyle.Font.Color = Color.Black

GrandTotalStyle.ForegroundColor = Color.Orange

GrandTotalStyle.Pattern = BackgroundType.Solid

GrandTotalStyle.Custom = “#,##0.00;#,##0.00”



Dim GrandTotalFlag As New StyleFlag()

GrandTotalFlag.NumberFormat = True

GrandTotalFlag.Font = True

GrandTotalFlag.Borders = True

GrandTotalFlag.CellShading = True



GrandTotalRange.ApplyStyle(GrandTotalStyle, GrandTotalFlag)





If I comment below code then valueis coming with sign but commas are missing. Pls help



GrandTotalStyle.Custom = “#,##0.00;#,##0.00”

4

Hi,

Thanks for your posting and using Aspose.Cells.

Please use the Style.Number property with number 4 to get the desired result instead of Style.Custom property.

Please see the following sample code. It adds a number -1234567 in cell A1 after setting the Style.Number property to 4 and the result is formatted as -1,234,567.00 which looks good.

I have also attached the output excel file generated by the code for your reference.

C#


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


Cell cell = worksheet.Cells[“A1”];


Style style = cell.GetStyle();

style.Number = 4;

cell.SetStyle(style);


cell.PutValue(-1234567);


worksheet.AutoFitColumns();


workbook.Save(“output.xlsx”);


5

Thanks for your reply… It is working…

6

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved with the above code change. Let us know if you encounter any other issue, we will be glad to look into it and help you further.