Excel的读取相比每个项目中都有用到，本篇有点标题党了，虽然解决了很多坑，但还是有很多不足和无法实现的地方。

本文讲述如何解决excel文件读取时会遇到的问题，使用的是apache的poi组件。

具体的代码示例请参考：https://github.com/kengdingxiaoman/hydralisk-common-toolkit 中的excel读取部分的代码。

跨过的坑

读取excel我遇到了这些坑：

如何判断excel的版本，是xls还是xlsx
大文件excel的读取
如何转换日期单元格
如何转换金额单元格
如何转换公式单元格
大文件读取方式如何获取总行数

接下来我们就一个一个来看看

如何判断excel的版本

excel分为2003和2007两个版本，很明显的区别就是文件名后缀，分别是： xls 和 xlsx

这个问题参考：org.apache.poi.ss.usermodel.WorkbookFactory#create(InputStream)方法仿照着来

InputStream is = FileMagic.prepareToCheckMagic(inputStream);
FileMagic fileMagic = FileMagic.valueOf(is);
//根据不同的文件类型(xls, xlsx)使用不同的处理类
if (FileMagic.OLE2.equals(fileMagic)) {
    return initHSSFReader(is, startRow, endRowIgnoredNum);
}
if (FileMagic.OOXML.equals(fileMagic)) {
    return initXSSFReader(is, startRow, endRowIgnoredNum);
}

注意

poi之前的版本并没有FileMagic类，但也是同样参考WorkbookFactory的create方法就可以了
判断后需要去初始化Reader，判断使用的是读取文件流的前几个字节，所以不使用inputStream去初始化Reader，会失败

大文件Excel的读取

如果excel文件比较大，那么使用Workbook模式就不太合适了

如果是xls文件，那么使用EventUserMode去读取，请参考gitHub例子中的HSSFEventUserModelReader类

如果是xlsx文件，那么使用Sax模式去读取，请参考gitHub例子中的XSSFSaxReader类

有一点需要说明的就是xlsx文件是可以解压的，解压后会发现原来excel中的所有数据都是以xml形式存放的，xls并不是这样的。

大文件如何获取总行数

会遇到这样的情况，文件末尾是一些合计统计的数据，所以要忽略跳过，大文件的读取没有像Workbook模式一样容易处理。

大文件不管通过哪种方式，都需要获取dimension值，也就是画布的值

excel文件中所有数据组成了一个长方形，通过获取长方形左上角和右下角的位置就可以知道数据有多少行多少列了

获得的dimension值类似：A1:H10，所以只需要解析H10，就可以得到一共有10行了

如何转换公式单元格

如果一个单元格是公式，直接使用 cell.getCellFormula() 获取到的是：F12+H12 这样的数据

要获得真实的数据，使用如下方式：

try {
    return convertToNumberSmart(cell.getNumericCellValue());
} catch (IllegalStateException e) {
    return String.valueOf(cell.getStringCellValue());
}

如何转换金额单元格

一个单元格是金额，遇到的情况也会很复杂

科学计数法需要进行转换
2222222.0需要转换成 2222222 有些数据其实是订单号等数据，并不是金额，不应该有小数点，这个时候也需要去除

代码如下，能转换大部分情况：

/**
 * 小数后两位是.00则忽略小数，否则保留小数
 *
 * 2.22222222222E11 -> 222222222222
 * 23.0 -> 23
 * 234.54 -> 234.54
 * 3.33333333389E9 -> 3333333333.89
 * 555555555 -> 555555555
 * 3.3333E-6 -> 0.0000033333
 * -888 -> -888
 *
 * @param numericCellValue
 * @return
 */
public static String convertToNumberSmart(double numericCellValue) {
    BigDecimal bigDecimal = new BigDecimal(String.valueOf(numericCellValue));
    bigDecimal.setScale(2, BigDecimal.ROUND_HALF_UP);
    String bigDecimalStr = bigDecimal.toEngineeringString();
    NumberFormat nf = NumberFormat.getNumberInstance();
    nf.setMaximumFractionDigits(2);
    nf.setRoundingMode(RoundingMode.HALF_UP);
    nf.setGroupingUsed(false);
    String result = nf.format(bigDecimal);
    if (Double.valueOf(result) == 0) {
        //处理类似0.00034的情况
        return bigDecimalStr;
    } else {
        return result;
    }
}

如何转换日期单元格

日期数据的单元格也是千奇百怪，形式各样，例如：2018-01-19 12:09:09，2018/1/19 12:09:09 获取后需要统一进行格式化。

也可能获取到的是excel date，类似于：43034.484143518515 或 43056

这时调用方通过判断后要进行转换：要调用

org.apache.poi.ss.usermodel.DateUtil.getJavaDate()方法进行转换

最后

总体的方法入口请参考：AbstractWorkbookReader的getCellValue()方法

private Object getCellValue(Cell cell) {
    if (cell == null) {
        return "";
    }
    CellType cellType = cell.getCellTypeEnum();
    switch(cellType) {
        case BLANK:
        case STRING:
            return cell.getRichStringCellValue().getString();
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return CellValueConvertUtils.format(cell.getDateCellValue());
            } else {
                return CellValueConvertUtils.convertToNumberSmart(cell.getNumericCellValue());
            }
        case FORMULA: return CellValueConvertUtils.convertFormulaCellValue(cell);
        case BOOLEAN: return cell.getBooleanCellValue();
        case ERROR: return cell.getErrorCellValue();
        default: return null;
    }
}

慢慢的成长之路

Excel读取看这一篇就够了