java - How to set data (number) format locale for specific cell using Apache POI (SXSSF)? -

the problem concrete: using apache poi, want create cell (done), assign number format (done), , set format's locale (stuck here).

the code looks this:

sxssfworkbook workbook = new sxssfworkbook(100); sheet sheet = workbook.createsheet(); row row = sheet.createrow(1); cell cell = row.createcell(0); cellstyle cellstyle = workbook.createcellstyle(); cellstyle.setdataformat(8); //currency thousands separator , 2 decimal places after period cell.setcellvalue(123456.78); //??? how set custom locale cell's number format? 

the problem i'm trying solve custom locale customizing thousands separator char (french's non-breaking space ok me).

xlsx workbooks allow such customization (update: mean setting format locale per cell), achievable both ms office , openoffice. want same in code.

(apache poi 3.12)

in offixe openxml (*.xlsx) currency number format currency symbol can localized not decimal separator. decimal separator comes windows system locale settings of windows system excel running on. , thousands delimiter defaults windows system locale settings of windows system excel running on.

in excel looks like:

as see currency symbol can localized.

at least thousands delimiter can set using format string. format string

"#\\ ###\\ ##0.00\\ [$€-40c];[red]\\-#\\ ###\\ ##0.00\\ [$€-40c]".

this currency number format having localized french euro currency symbol , space thousands delimiter. because faking thousands delimiter, have give digits needed in format string.

the decimal separator default, means comes windows system locale settings of windows system excel running on. dot . within format string not means use dot decimal delimiter use decimal delimiter comes windows system locale settings of windows system excel running on. , if had used comma , thousands delimiter in format string, had used thousands delimiter comes windows system locale settings of windows system excel running on. , had not need giving digits in format string because thousands delimiter settings repeat every thousands digits. so

"#,##0.00\\ [$€-40c];[red]\\-#,##0.00\\ [$€-40c]"

would enough.

example:

import java.io.fileoutputstream;  import org.apache.poi.ss.usermodel.*;  import org.apache.poi.xssf.streaming.sxssfworkbook; import org.apache.poi.xssf.streaming.sxssfsheet;  public class createexcelnumberformat {   public static void main(string[] args) throws exception {   sxssfworkbook workbook = new sxssfworkbook(100);    dataformat dataformat = workbook.createdataformat();   cellstyle cellstylecurrency = workbook.createcellstyle();   cellstylecurrency.setdataformat(dataformat.getformat("#\\ ###\\ ##0.00\\ [$€-40c];[red]\\-#\\ ###\\ ##0.00\\ [$€-40c]"));     sheet sheet = workbook.createsheet();   row row = sheet.createrow(1);   cell cell = row.createcell(0);    cell.setcellvalue(123456.78);   cell.setcellstyle(cellstylecurrency);    ((sxssfsheet)sheet).trackcolumnforautosizing(0);   sheet.autosizecolumn(0);    workbook.write(new fileoutputstream("createexcelnumberformat.xlsx"));   workbook.close();   workbook.dispose();  } } 

but not same localized currency format usable in libreoffice opendocument spreadsheet format. looks like:

as see here both, currency symbol , language of whole format, can localized.

but office openxml (*.xlsx) cannot store localized currency number formats. opendocument spreadsheet (*.ods), native format of openoffice/libreoffice, can save localized currency number formats, if excel open such file, localization lost.

the settings of "language" combo-box of openoffice/libreoffice cannot stored in *.xlsx, not openoffice/libreoffice. set else default there in openoffice/libreoffice, save file *.xlsx, close openoffice/libreoffice, open stored *.xlsx file in openoffice/libreoffice again. see "language" reseted default.