如何覆盖PrimeFaces号码：dataExporter误出口数字在Excel中的文本？ [英] How to override PrimeFaces p:dataExporter wrongly exporting numbers as text in Excel?

问题描述

的 PrimeFace 的 号码：dataExporter 标签出口数值数据为默认文本，它产生的电池，在左上角绿色的三角形。这可以在 PrimeFaces展示作为例子的好，如果点击汽车桌子底下Excel导出。

The PrimeFace p:dataExporter tag exports numeric data as text by default, which results in a cell with a green triangle in the upper left corner. This can be seen in the PrimeFaces showcase example as well, if you click the Excel export under the cars table.

我怎么能覆盖此默认以确保我的数字列不导出为文本？我试着使用指着我的方法后处理属性，该属性设置Excel格式，使用 POI API的所有数据细胞，但未生效（没有改变任何东西）：

How can I override this default to make sure my numeric columns are not exported as text? I tried using the postProcessor attribute pointing to my method that sets the Excel format for all the data cells using POI API but that did not take effect (did not change anything):

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

因此​​，这意味着所有的数据都不加区别地导出为文本，然后你甚至无法事后更改。

So that means that all data is indiscriminately exported as text and then you can't even change it afterwards.

推荐答案

这是什么最终为我工作。它是远离优雅，但它的工作原理：

This is what ended up working for me. It is far from elegant but it works:

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);
        }
    }
}

这篇关于如何覆盖PrimeFaces号码：dataExporter误出口数字在Excel中的文本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！