How to read the number and date formats of an excel using Aspose.Cells

Aspose.Cells Product Family
1

Hi Team,

We have a requirement where we need to read the excel cell’s format to get the number or date formatting before using putValue method of Aspose.Cells to replace the value in the same cell.
Somehow, the putValue method is not using the cell’s format and not formatting accordingly.

Thanks,
Priyanka

2

@priyanka9
For the setting and getting date and number formats, please refer to the following document.

3

@priyanka9,
Also, see the following sample code on how to get formatting of the cell for your reference. You may use Style.getCustom()/getCultureCustom() method or other relevant method for your task.
e.g.
Sample code:

Workbook workbook = new Workbook("sample.xlsx");
//Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
//Get the cells in the worksheet
Cells cells = worksheet.getCells();
//Get your Date/Time cell. e.g., A1 cell
Cell cell = cells.get("A1");

//Read cell number format/style of the cell
Style style = cell.getStyle();

//Get Date formatting
System.out.println("Date formatting: " + style.getCustom());

Hope, this helps a bit.

4

Hi, the above doesn’t work for our scenario.

How can we get the above format using aspose.cells class
image.png (21.9 KB)

5

@priyanka9,

Thanks for the screenshot.

Click on the Custom in the dialog and you will get the same formatting by Aspose.Cells. If you still find any issue or confusion, kindly zip and attach your template Excel file and share sample code that you are using. We will check your issue soon.

6

@priyanka9

Or you are talking about the category types such as Number, Date, …? If so, please try Cell.NumberCategoryType.

If it is not the case, please provide us your sample file and the information you get from ms excel which you need to get from Aspose.Cells, we will provide assistance further.

7

Hi Amjad and Johnson,

Thanks for your response.

Pls note that we have some display expressions which are evaluated on runtime as strings from the excel cells.
But in the source excel formatting is defined.
When we are using cell.getType() method , it is always returning as string based on the content stored in the cell.
We really need to find a way to read the cell formatting.
tried numbercategory too and it is returning 3 for date values
image.jpg (173.5 KB)

image.png (26.2 KB)
Above is returning type as string based on the cell content but we need cell format.

8

@priyanka9
Please provide us your sample file and tell us those cells you need to get the formatting and what’s the expected result. For Cell.getType(), this api is designed to return current value type of its value. So if the cell’s value is text, the return type will be String. For Cell.getNumberCategoryType(), if the style set for this cell is datetime formatting, returning NumberCategoryType.DATE(the int value should be 3) should be the correct result.

9

Hi @johnson.shi ,

Here i am attaching sample excel.
inputExcel.zip (13.5 KB)

here as i mentioned in below screenshot how i can get cell number category. If you see cell C9 , actual data stored in that particular cell is string but under number category it is a Number. (right click on the cell->Format Cells->Number)
image.png (163.9 KB)

10

Hey,

I think i got the solution from chatGPT, we have a method cell.getNumberCategoryType() to get number category
and we can paas that value to NumberCategoryType.getName(cell.getNumberCategoryType()) to get cell number category type.

11

@priyanka9
Thank you for your feedback. I’m glad your issue has been resolved. If you have any questions or confusion, please feel free to contact us at any time.

12

@priyanka9,

Please note, NumberCategoryType is an enumeration class which has only constant values (i.e., DATE, FRACTION, GENERAL, NUMBER, SCIENTIFIC, TEXT, TIME). There is no NumberCategoryType.getName() method in the APIs. I think you need to write your own custom method which will get/accept constant values (e.g., from cell.getNumberCategoryType()) and returns relevant name of the category. You can easily write the method for your needs.

13

yes true, there is no method i got this wrong info from chatgpt.
Anyway we have written custom logic to hndle that and it worked.

Thanks,
Priyanka.

14

@priyanka9,

Alright, and it’s good to know that you have written a custom module to cope with it. Please feel free to write back to us if you have further queries or comments.