java - Apache poi 单元格格式

Question

我正在尝试使用 Apache poi dataformatter 将数字 2852196 格式化为 $2,852,196。

NumberFormat numberFormat = NumberFormat.getCurrencyInstance();
dataFormatter.addFormat("($#,##0_);($#,##0)", numberFormat);
//after adding the above format, i see this format @ index 0, so i have taken 0 in the next line  

String formatCellValue = dataFormatter.formatRawCellContents((Long) cols.get(jj).getValue(), 0, "($#,##0_);($#,##0)");

但我得到的产出是 2,852,196 美元。我遗漏了一些东西或者使用了不正确的方法。

--谢谢。

Answer 1

此方法更改列数组中的所有元素

您将通过以下方式调用:

protected final static int TYPE_PORCENTAJE = 3;

setTypeColumn(sheet, Arrays.asList(19, 20, 21), TYPE_PORCENTAJE, null);


protected void setTypeColumn(Sheet sheet, List<Integer> columns, int type, Integer format) {
    CellType cellType = CellType.STRING;
    CellStyle style = sheet.getWorkbook().createCellStyle();
    CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();

    switch (type) {
    case TYPE_NUMBER:
        style.setDataFormat(sheet.getWorkbook().createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(1)));
        break;
    case TYPE_MONEY:
        style.setDataFormat(
                sheet.getWorkbook().createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(format)));
        break;
    case TYPE_DATE:
        style.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd"));
        break;
    case TYPE_PORCENTAJE:
        style.setDataFormat(sheet.getWorkbook().createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(0xa)));
        break;
    case TYPE_CUSTOM:
        style.setDataFormat(
                sheet.getWorkbook().createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(format)));
        break;
    case TYPE_TEXT:
        style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text"));
        break;
    default:
        break;
    }
    cellType = CellType.NUMERIC;
    final int lastRow = sheet.getLastRowNum();
    for (int i = 1; i <= lastRow; i++) {
        for (int j = 0; j < columns.size(); j++) {
            Cell cell = sheet.getRow(i).getCell(columns.get(j));
            String value = null;
            if (type == TYPE_TEXT) {
                value = cell.getStringCellValue();
            }
            cell.setCellType(cellType);
            cell.setCellStyle(style);
            if (type == TYPE_PORCENTAJE) {
                cell.setCellValue(cell.getNumericCellValue() / 100);
            }
            if (type == TYPE_TEXT) {
                style.setWrapText(true);
                cell.setCellStyle(style);
                cell.setCellValue(value);
            }
        }
    }
}

参数格式请查看此链接 https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html