Apache POI 导出Excel笔记

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

背景
导出数据的部门内容要指定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;
    }