POI 电子表格本地化

Question

使用Apache POI OOXML 和XSSF 模式，创建一个新的工作簿，如何设置电子表格的语言（即英语（美国））？我在上面找不到任何东西。该设置不需要是特定于单元格的，应该适用于所有工作表。我正在使用 POI 4.1.0 版。

Answer 1

文件中工作表数据的存储Excel未本地化。工作表数据的存储在函数名称、列表定界符、小数点和千位定界符方面始终是美国英语。只有 Excel 应用程序是本地化的。Excel GUI从文件中读取数据，然后翻译函数名称、列表定界符和小数点以及千位定界符。让我们举个例子：import java.io.FileOutputStream;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.util.GregorianCalendar;class CreateExcel {&nbsp;public static void main(String[] args) throws Exception {&nbsp; try (Workbook workbook = new XSSFWorkbook();&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {&nbsp; &nbsp;Object[][] data = new Object[][] {&nbsp; &nbsp; new Object[] {"Value", "Date", "Formatted value", "Formula"},&nbsp; &nbsp; new Object[] {123.456789, new GregorianCalendar(2019, 0, 15), 123.456789, "ROUND(A2,2)"},&nbsp; &nbsp; new Object[] {1234.56789, new GregorianCalendar(2019, 5, 15), 1234.56789, "ROUND(A3,2)"}&nbsp; &nbsp;};&nbsp; &nbsp;DataFormat dataFormat = workbook.createDataFormat();&nbsp; &nbsp;CellStyle dateStyle = workbook.createCellStyle();&nbsp; &nbsp;dateStyle.setDataFormat(dataFormat.getFormat("DDDD, MMMM, DD, YYYY"));&nbsp; &nbsp;CellStyle numberStyle = workbook.createCellStyle();&nbsp; &nbsp;numberStyle.setDataFormat(dataFormat.getFormat("#,##0.00 \" Coins\""));&nbsp; &nbsp;Sheet sheet = workbook.createSheet();&nbsp;&nbsp; &nbsp;for (int r = 0; r < data.length; r++) {&nbsp; &nbsp; Row row = sheet.createRow(r);&nbsp; &nbsp; for (int c = 0; c < data[0].length; c++) {&nbsp; &nbsp; &nbsp;Cell cell = row.createCell(c);&nbsp; &nbsp; &nbsp;if (r == 0) cell.setCellValue((String)data[r][c]);&nbsp; &nbsp; &nbsp;if (r > 0 && c == 0) {&nbsp; &nbsp; &nbsp; cell.setCellValue((Double)data[r][c]);&nbsp; &nbsp; &nbsp;} else if (r > 0 && c == 1) {&nbsp; &nbsp; &nbsp; cell.setCellValue((GregorianCalendar)data[r][c]);&nbsp; &nbsp; &nbsp; cell.setCellStyle(dateStyle);&nbsp; &nbsp; &nbsp;} else if (r > 0 && c == 2) {&nbsp; &nbsp; &nbsp; cell.setCellValue((Double)data[r][c]);&nbsp; &nbsp; &nbsp; cell.setCellStyle(numberStyle);&nbsp; &nbsp; &nbsp;} else if (r > 0 && c == 3) {&nbsp; &nbsp; &nbsp; cell.setCellFormula((String)data[r][c]);&nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; }&nbsp; &nbsp;}&nbsp; &nbsp;for (int c = 0; c < data[0].length; c++) {&nbsp; &nbsp; sheet.autoSizeColumn(c);&nbsp; &nbsp;}&nbsp; &nbsp;workbook.write(fileout);&nbsp; }&nbsp;}}如您所见，代码中没有任何内容是本地化的。都是en_US。函数名ROUND在公式中，函数参数之间的分隔符是逗号，同样是列表分隔符，double值中的小数点分隔符是点。数字格式代码也是en_US.文件中存储的内容没有Excel.xlsx以任何方式本地化。但是如果我用Excel.xlsx我的 German打开Excel，那么它看起来像：注意公式=RUNDEN(A3;2)。函数名称翻译成德语，函数参数之间的分隔符是分号，同样是列表分隔符，double 值中的小数点分隔符是逗号，千位分隔符是点。此外，数字格式代码现在是德语：为什么是这样？主要是因为是德文Excel申请。但也因为Windows决定日期格式的区域设置...和十进制分隔符、列表分隔符和千位分隔符。