How to force Excel to display values in Spanish locale using Apache POI

0
votes

I am using Apache POI to render few reports in Excel. My requirements are,

  1. Display the dates in Spanish format, the cell type in excel should be date.
  2. Display the decimal amount/price/currency value in Spanish format (e.g.: 12,23 or 1.454,23), the cell type in excel should be number even though it has ',' in the value and should be able to apply the number functions on those cells.

When I try setting the values using the following code with Spanish locale in the system, 

CellStyle style = workBook.createCellStyle();                
style.setDataFormat(workBook.createDataFormat().getFormat("##0.00%"));
cell.setCellStyle(style);
cell.setCellValue(new Double(23.45).doubleValue());

It displays the value as 23.45% (not respecting the system locale) and allows the number functions. To fix it, if I change the format to "##0,00", then the values are not displayed properly and it is not considering the cell as a number column, hence the number functions are not working. So, I either have to convert it to text and set the value (where I will lose the ability of using the number functions in the cells) or use the '.' format to display the number (which is not the requirement).

Questions:

  • How can I set the Spanish format number/currency and still make the cell type accept the number functions (even with ',' in the numbers)?
  • Is there a way to force this format in a cell without setting the value as text?
  • Is there a way to force the excel file locale to Spanish locale, so that wherever it is opened, it displays the values in Spanish format?
  • Using the above code, I generated the excel file with the standard format (##0.00) and expected it to change the format (to ##0,00) when the system's locale is changed, but it did not.

Any help would be greatly appreciated. Thanks.

1
javaexcelapacheapache-poilocale
What happens if you do what you'd have to do in Excel to force the same thing, and specify the Spanish locale at the start of your Format String? – Gagravarr
If in Windows system locale comma is set as the decimal separator and Excel nevertheless don't use this but uses the dot, then the only reason can be that Options-Advanced-Editing options-Use system separators check-box is unchecked and dot is explicitly set there as the decimal separator. – Axel Richter
@Gagravarr That displays number fine as expected. – KayKay
But the code you are showing does not fit the story you are telling. A value 23.45 set in an Excel cell and formatted using percent format ##0.00% must show either 2345.00% or 2345,00% dependent on the systems decimal separator. In Excel 1 == 100%. So 0.2345 will be 23.45% in Excel. So I suspect the problem you are facing is elsewhere in code you are not showing. – Axel Richter
@AxelRichter sorted it out. Actually it is not enough to just change the locale, but the number and date formats have to be changed too in the advanced settings. – KayKay

1 Answers

0
votes

The mentioned code works fine. The issue was that it was not enough to change just the locale of the system, but the number and date formats of the system as well. Once that has been changed, it works fine. Tested it in different locale machines as well.

Thanks