Set Display Formats of Numbers and Dates

Aspose.Cells Product Family
1

Excel allow one to specify the number of decimals in number cells. Also, it allow a fairly large choice for date formatting.



The Style class, however, seems to offer only a limited choice of formats. Why? Why not offer the complete and flexible options of Excel?

2

Hi Mario,

Thanks for your posting and considering Aspose.Cells.

Aspose.Cells also allows you to specify number of display formats for numbers and dates. You can specify the built-in formats using the Style.setNumber() property. If you do not want to specify built-in display formats, then you can specify the custom formats using Style.setCustom() property.

Please see the following documentation article that explains how to make use of these properties and what numbers to be used for the built-in formats.


Besides, you can also set you desired format in some MS-Excel file and then using Aspose.Cells, you can read the format using Style.getNumber() or Style.getCustom() properties and then use at your end.

Let us know if you encounter any issue in this regard, we will look into it and help you asap.

As an example, suppose you want to show number 23 as 23.000. You can set this style in some Excel file and read the custom value as below. I have attached the source file for your reference.

Java

String filePath = “F:\Shak-Data-RW\Downloads\Source.xlsx”;


//Create workbook object

Workbook workbook = new Workbook(filePath);


//Access the first worksheet

Worksheet worksheet = workbook.getWorksheets().get(0);


//Access the cell A1

Cell cell = worksheet.getCells().get(“A1”);


//Access its style

Style style = cell.getStyle();


//Print the custom value of style.

System.out.println(style.getCustom());

Console Output:
0.000

3

Fantastic!

This is exactly what I need.



The getCustom()/setCustom() methods in Style should be documented much better. I couldn’t figure out what they were good for.



Thanks

4

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that Style.Custom property fulfills your need. We will improve the documentation article soon. Let us know if you face any other issue, we will be glad to look into it and help you further.

5

I was referring to the Javadoc documentation, not the article. The article is quite clear.

6

Hi,

Thanks for clarification and using Aspose.Cells.

Here is the Javadoc description of the property. It looks good to me.

getCustom/setCustom

 
public java.lang.String getCustom() / public void setCustom(java.lang.String value)
Represents the custom number format string of a cell. If the custom number format is not set,we will return "". It should be InvariantCulture .

7

It is clear when one knows how the string parameter should be composed. I found the documentation on this in the Excel online help but not in the Javadoc.



For example, there’s nothing in the Javadoc that explains that the day of the week can be specified as “ddd” for the abbreviation (e.g., Wed) and “dddd” for the full name (e.g., Wednesday).



At least, the Javadoc should suggest to consult the Excel documentation.



Anyway, thanks for helping.

8

Hi,

Thanks for your suggestion and using Aspose.Cells.

You are right. It is not mentioned in the Javadocs how to create custom formats. We think, we should add a reference to the documentation article so that user could be redirected to the article for understanding about custom formats. We will look into it and fix this issue.