excel - 如何覆盖 PrimeFaces p :dataExporter wrongly exporting numbers as text in Excel?

Question

PrimeFace p:dataExporter默认情况下，标记将数字数据导出为文本，这会导致单元格的左上角带有绿色三角形。这可以在 PrimeFaces showcase example 中看到同样，如果您单击汽车表下的 Excel 导出。

如何覆盖此默认值以确保我的数字列不会导出为文本？我尝试使用 postProcessor 属性指向我的方法，该方法使用 POI API 为所有数据单元格设置 Excel 格式，但没有生效(没有改变任何东西):

public void formatExcel(Object doc) {
    HSSFWorkbook book = (HSSFWorkbook)doc;
    HSSFSheet sheet = book.getSheetAt(0); 

    HSSFRow header = sheet.getRow(0);
    int colCount = header.getPhysicalNumberOfCells();
    int rowCount = sheet.getPhysicalNumberOfRows();

    HSSFCellStyle numStyle = book.createCellStyle();
    numStyle.setDataFormat((short)1);

    for(int rowInd = 1; rowInd < rowCount; rowInd++) {
        HSSFRow row = sheet.getRow(rowInd);
        for(int cellInd = 1; cellInd < colCount; cellInd++) {
            HSSFCell cell = row.getCell(cellInd);
            String val = cell.getStringCellValue();

            cell.setCellStyle(numStyle);

        }
    }
}

我也试过

cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

但这给了我

java.lang.IllegalStateException: Cannot get a numeric value from a text cell

这意味着所有数据都被不加区别地导出为文本，之后您甚至无法更改它。

Answer 1

这就是最终对我有用的东西。它远非优雅，但它有效:

HSSFCellStyle intStyle = book.createCellStyle();
intStyle.setDataFormat((short)1);

HSSFCellStyle decStyle = book.createCellStyle();
decStyle.setDataFormat((short)2);

HSSFCellStyle dollarStyle = book.createCellStyle();
dollarStyle.setDataFormat((short)5);


for(int rowInd = 1; rowInd < rowCount; rowInd++) {
    HSSFRow row = sheet.getRow(rowInd);
    for(int cellInd = 1; cellInd < colCount; cellInd++) {
        HSSFCell cell = row.getCell(cellInd);

        //This is sortof a hack to counter PF exporting all data as text
        //We capture the existing value as string, convert to int, 
        //then format the cell to be numeric and reset the value to be int
        String strVal = cell.getStringCellValue();

        //this has to be done to temporarily blank out the cell value
        //because setting the type to numeric directly will cause 
        //an IllegalStateException because POI stupidly thinks
        //the cell is text because it was exported as such by PF...
        cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

        strVal = strVal.replace(",", StringUtils.EMPTY);

        if(strVal.indexOf('.') == -1) {
            //integer
            //numStyle.setDataFormat((short)1);
            int intVal = Integer.valueOf(strVal);

            cell.setCellStyle(intStyle);
            cell.setCellValue(intVal);
        } else {
            //double
            if(strVal.startsWith("$")) {
                strVal = strVal.replace("$", StringUtils.EMPTY);
                //numStyle.setDataFormat((short)5);
                cell.setCellStyle(dollarStyle);
            } else {
                //numStyle.setDataFormat((short)2);
                cell.setCellStyle(decStyle);
            }

            double dblVal = Double.valueOf(strVal);
            cell.setCellValue(dblVal);
        }
    }
}