Apache POI 导出Excel笔记

- 日理万妓 2022-04-06 06:42 269阅读 0赞

本文主要记录问题和解决方案，操作的的教程有时间再整理

**背景**  
导出数据的部门内容要指定Excel的格式，方便使用者筛选、分析、统计等  
**工作中的坑**  
导出工作基本接近尾声了，但是存在一种现状，导出的数值类型的数据信息，在Excel中是以文本呈现的，但是编辑当前单元格数据之后就变成正常的了，变化过程如下：

10000---》10,000.00

有个同事提出

在Excel中以文本写入的资源在呈现的时候就会出现这种情况，并且在Excel单元格的左上角有一个小三角图标的提示。

按着上述观点跟踪代码找问题原因。

protected void setCellValue(HSSFCell cell, Object value) {
            if (value == null)
                return;
            if (value instanceof Boolean) {
                cell.setCellValue(((Boolean) value).booleanValue());
            } else if (value instanceof Calendar) {
                cell.setCellValue((Calendar) value);
            } else if (value instanceof java.util.Date) {
                cell.setCellValue((java.util.Date) value);
            } else if (value instanceof Double) {
                cell.setCellValue(((Double) value).doubleValue());
            } else if (value instanceof RichTextString) {
                cell.setCellValue((RichTextString) value);
            } else if (value instanceof String) {
                cell.setCellValue((String) value);
            } else if (value instanceof BigDecimal) {
                cell.setCellValue(((BigDecimal) value).doubleValue());
            } else {
                cell.setCellValue(Conver.toString(value));
            }
        }

后来发现HSSFCell这个类支持的数据类型有限，而我们导出的数据使用的是BigDecimal类型，因此以文本形式写入到了数据中。把导出数据的BigDecimal转换成double类型后问题完美解决。

下面整理下Excel列数据类型的支持的实现方法  
需要构建的资源和设置关系

HSSFWorkbook hwb= new HSSFWorkbook(new FileInputStream(file));
    HSSFSheet s=hwb.getSheet(sheetName);
    HSSFRow row = s.createRow(0);
    HSSFCell cell=row.createCell(0);
    HSSFCellStyle cellstyle=hwb.createCellStyle();

设置值的方法

1、直接使用值
     cellstyle.setDataFormat((short) 4);
    2、动态创建
     cellstyle.setDataFormat(hwb.createDataFormat().getBuiltinFormat("m/d/yy h:mm"));
    3、使用静态方法
     cellstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
    4、使用自定义格式
     formatePattern = "yyyy-MM-dd HH:mm:ss";
     cellstyle.setDataFormat(hwb.createDataFormat().getFormat(formatePattern));

**需要注意的是写入的数据跟指定的数据类型要一致。**

推荐使用模板

时间
    m/d/yy h:mm
    数值
    #,##0.00
    货币
    \"$\"#,##0.00_);[Red](\"$\"#,##0.00)
    百分数
    0.00%
    分数
    # ??/??
    科学计数法
    0.00E+00

这个类org.apache.poi.ss.usermodel.BuiltinFormats提供了基本的数据格式

/*
    0 General General 18 Time h:mm AM/PM
    1 Decimal 0 19 Time h:mm:ss AM/PM
    2 Decimal 0.00 20 Time h:mm
    3 Decimal #,##0 21 Time h:mm:ss
    4 Decimal #,##0.00 2232 Date/Time M/D/YY h:mm
    531 Currency "$"#,##0_);("$"#,##0) 37 Account. _(#,##0_);(#,##0)
    631 Currency "$"#,##0_);[Red]("$"#,##0) 38 Account. _(#,##0_);[Red](#,##0)
    731 Currency "$"#,##0.00_);("$"#,##0.00) 39 Account. _(#,##0.00_);(#,##0.00)
    831 Currency "$"#,##0.00_);[Red]("$"#,##0.00) 40 Account. _(#,##0.00_);[Red](#,##0.00)
    9 Percent 0% 4131 Currency _("$"* #,##0_);_("$"* (#,##0);_("$"* "-"_);_(@_)
    10 Percent 0.00% 4231 33 Currency _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
    11 Scientific 0.00E+00 4331 Currency _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)
    12 Fraction # ?/? 4431 33 Currency _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
    13 Fraction # ??/?? 45 Time mm:ss
    1432 Date M/D/YY 46 Time [h]:mm:ss
    15 Date D-MMM-YY 47 Time mm:ss.0
    16 Date D-MMM 48 Scientific ##0.0E+0
    17 Date MMM-YY 49 Text @
    * */
    static {
            List<String> m = new ArrayList<String>();
            putFormat(m, 0, "General");
            putFormat(m, 1, "0");
            putFormat(m, 2, "0.00");
            putFormat(m, 3, "#,##0");
            putFormat(m, 4, "#,##0.00");
            putFormat(m, 5, "\"$\"#,##0_);(\"$\"#,##0)");
            putFormat(m, 6, "\"$\"#,##0_);[Red](\"$\"#,##0)");
            putFormat(m, 7, "\"$\"#,##0.00_);(\"$\"#,##0.00)");
            putFormat(m, 8, "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)");
            putFormat(m, 9, "0%");
            putFormat(m, 0xa, "0.00%");
            putFormat(m, 0xb, "0.00E+00");
            putFormat(m, 0xc, "# ?/?");
            putFormat(m, 0xd, "# ??/??");
            putFormat(m, 0xe, "m/d/yy");
            putFormat(m, 0xf, "d-mmm-yy");
            putFormat(m, 0x10, "d-mmm");
            putFormat(m, 0x11, "mmm-yy");
            putFormat(m, 0x12, "h:mm AM/PM");
            putFormat(m, 0x13, "h:mm:ss AM/PM");
            putFormat(m, 0x14, "h:mm");
            putFormat(m, 0x15, "h:mm:ss");
            putFormat(m, 0x16, "m/d/yy h:mm");
    
            // 0x17 - 0x24 reserved for international and undocumented
            for (int i=0x17; i<=0x24; i++) {
                // TODO - one junit relies on these values which seems incorrect
                putFormat(m, i, "reserved-0x" + Integer.toHexString(i));
            }
    
            putFormat(m, 0x25, "#,##0_);(#,##0)");
            putFormat(m, 0x26, "#,##0_);[Red](#,##0)");
            putFormat(m, 0x27, "#,##0.00_);(#,##0.00)");
            putFormat(m, 0x28, "#,##0.00_);[Red](#,##0.00)");
            putFormat(m, 0x29, "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)");
            putFormat(m, 0x2a, "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)");
            putFormat(m, 0x2b, "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)");
            putFormat(m, 0x2c, "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)");
            putFormat(m, 0x2d, "mm:ss");
            putFormat(m, 0x2e, "[h]:mm:ss");
            putFormat(m, 0x2f, "mm:ss.0");
            putFormat(m, 0x30, "##0.0E+0");
            putFormat(m, 0x31, "@");
            String[] ss = new String[m.size()];
            m.toArray(ss);
            _formats = ss;
        }