Aspose Cell with Accounting Format is read as String instead of number

Aspose.Cells Product Family
1

Hi Team,

1# we have a cell with formatting set to Accounting and settings as shown in the screen shot - with decimal places 2

image.png737×290 5.75 KB
image.png (5.8 KB)

2# we have another cell with formatting set to Accounting and settings as shown in the screen shot - with decimal places 0

image.png745×273 5.95 KB
image.png (5.9 KB)

when the particular cell values are read by aspose cells,
Cell 1# is returned as NumberCategoryType.Number. whereas cell 2# is returned as NumberCategoryType.Text. As both of them carry the same formatting of Accounting and the difference is only w.r.t decimal places, expecting both to be returned as Number Typefor the cell data.

Is this a defect and has been fixed or is there any workaround?

Thanks

2

@Mveerabaghu
By only creating sample file and testing with the latest version v23.12, we can obtain the correct results. Please refer to the attachment (6.1 KB).

The sample code as follows:

Workbook wb = new Workbook(filePath + @"sample.xlsx");
Cells cells = wb.Worksheets[0].Cells;
Cell c4 = cells["C4"];
Cell c5 = cells["C5"];

Console.WriteLine("c4 number category type: " + c4.NumberCategoryType);
Console.WriteLine("c5 number category type: " + c5.NumberCategoryType);

The output result:

c4 number category type: Number
c5 number category type: Number

If you still have any questions or confusion, please provide your sample file and executable sample code, and we will check it soon.

3

@Mveerabaghu
Could you share your template file and sample project? We will check it soon.

4

Property I am using to check is cell.NumberCategoryType
Aspose.Cells Version=“22.2.0”

5

@Mveerabaghu
Please try our latest version Downloads —New Releases-aspose.cells-for-.net-23.11 to check whether it can work for you. If you still get the issue, please provide us your template file and code to reproduce the issue so we can look into it.

6

@Mveerabaghu,

Please try our latest version Aspose.Cells for .NET v23.12. If you still find the issue, kindly zip and attach your template Excel file, we will check it soon.

7

Hi,
attached sample with latest version of Aspose and the file used.
AsposeDemo_DataType.zip (6.6 MB)

8

@Mveerabaghu,

Thanks for the template file.

After an initial testing, I am able to reproduce the issue you mentioned by using your template file and following sample code. I found accounting format (with decimal places 0) is read as String/Text instead of Number. B1 cell is read as Number while B2 is read as Text.

string srcFileName = "g:\\test2\\DataTypeIssue.xlsx";

using (Workbook workbook = new Workbook(srcFileName))
{
    Cells cells = workbook.Worksheets[0].Cells;
    Cell b1 = cells["B1"];
    Cell b2 = cells["B2"];

    Console.WriteLine("B1 number category type: " + b1.NumberCategoryType);
    Console.WriteLine("B2 number category type: " + b2.NumberCategoryType);//Text

    Console.ReadLine();
}

We require thorough evaluation of your issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-54803

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

9

@Mveerabaghu

For cell’s formatting with combined conditional formatting patterns, the NumberCategoryType we return is corresponding to the part which will be used according to current value of this cell.

For B2 in given template file, the formatting pattern is “( #,##0* );( (#,##0);* (* “-”); (@)". When cell’s value is 0, the used pattern is " (* “-”*)”. This is just a simple text pattern so we return “Text” for it.

For B1, the custom pattern is “* (* #,##0.00_);( (#,##0.00);* (* “-”??); (@)" and the used part is "( “-”??* )” when cell’s value is 0. This is a number pattern so we return “Number” for it.

For such kind of combined patterns, some of them may be taken as accounting or other specific category, some others may be recognized as “custom” only. We are afraid we has to implement this api in current way and cannot handle all cases in the same way with ms excel because of the variety of the formatting patterns.