Java开发小技巧（六）：使用Apache POI读取Excel

前言

在数据仓库中，ETL最基础的步骤就是从数据源抽取所需的数据，这里所说的数据源并非仅仅是指数据库，还包括excel、csv、xml等各种类型的数据接口文件，而这些文件中的数据不一定是结构化存储的，比如各种各样的报表文件，往往是一些复杂的表格结构，其中不仅有我们需要的数据，还有一些冗余的、无价值的数据，这时我们就无法直接用一般数据加载工具直接读取入库了。也许你会想，数据源导出文件前先处理好数据就行了。然而，实际开发中数据源往往是多个的，而且涉及到不同的部门甚至公司，这其间难免会出现各种麻烦，甚至有些数据文件还是纯手工处理的，不一定能给到你满意的数据格式。所以我们不讨论谁该负责转换的问题，这里主要介绍如何使用Apache POI来从Excel数据文件中读取我们想要的数据，以及用Bean Validation对数据内容按照预定的规则进行校验。

文章要点：

  • Apache POI是什么
  • 如何使用Apache POI读取Excel文件
  • 使用Bean Validation进行数据校验
  • Excel读取工具类
  • 使用实例

Apache POI是什么

Apache POI是用Java编写的免费开源的跨平台的Java API，提供API给Java程式对Microsoft Office格式档案进行读和写的操作。

如何使用Apache POI处理Excel文件

1、导入Maven依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>3.17</version>
</dependency>

2、创建Workbook实例

这里需要注意的是Excel文档的版本问题，Excel2003及以前版本的文档使用HSSFWorkbook对象，Excel2007及之后版本使用HSSFWorkbook对象

// Excel2003及以前版本
Workbook workbook = new XSSFWorkbook(new FileInputStream(path));
// Excel2007及之后版本
Workbook workbook = new HSSFWorkbook(new FileInputStream(path));

3、获取Sheet表格页对象

Sheet是Excel文档中的工作簿即表格页面，读取前要先找到数据所在页面，可以通过标签名或者索引的方式获取指定Sheet对象

// 按索引获取
Sheet sheet = workbook.getSheetAt(index);
// 按标签名获取
Sheet sheet = workbook.getSheet(label);

4、获取Cell单元格对象

// 行索引row和列索引col都是以 0 起始
Cell cell = sheet.getRow(row).getCell(col);

5、获取单元格内容

获取单元格的值之前首先要获知单元格内容的类型，在Excel中单元格有6种类型：

  1. CELL_TYPE_BLANK ：空值
  2. CELL_TYPE_BOOLEAN ：布尔型
  3. CELL_TYPE_ERROR ： 错误
  4. CELL_TYPE_FORMULA ：公式型
  5. CELL_TYPE_STRING：字符串型
  6. CELL_TYPE_NUMERIC：数值型

各种类型的内容还需要进一步判断其数据格式，例如单元格的Type为CELL_TYPE_NUMERIC时，它有可能是Date类型，在Excel中的Date类型是以Double类型的数字存储的，不同类型的值要调用cell对象相应的方法去获取，具体情况具体分析

public Object getCellValue(Cell cell) {
    if(cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {
            return cell.getNumericCellValue();
        }
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_FORMULA:
        return formula.evaluate(cell).getNumberValue();
    default:
        return null;
    }
}

6、关闭Workbook对象

workbook.close();

使用Bean Validation进行数据校验

当你要处理一个业务逻辑时，数据校验是你不得不考虑和面对的事情，程序必须通过某种手段来确保输入进来的数据从语义上来讲是正确的或者符合预定义的格式，一个Java程序一般是分层设计的，而不同的层可能是不同的开发人员来完成，这样就很容易出现不同的层重复进行数据验证逻辑，导致代码冗余等问题。为了避免这样的情况发生，最好是将验证逻辑与相应的模型进行绑定。

Bean Validation 规范的目标就是避免多层验证的重复性，它提供了对 Java EE 和 Java SE 中的 Java Bean 进行验证的方式。该规范主要使用注解的方式来实现对 Java Bean 的验证功能，从而使验证逻辑从业务代码中分离出来。

Hibernate Validator 是 Bean Validation 规范的参考实现，我们可以用它来实现数据验证逻辑，其Maven依赖如下：

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-validator</artifactId>
    <version>5.1.3.Final</version>
</dependency>
<dependency>
    <groupId>javax.el</groupId>
    <artifactId>javax.el-api</artifactId>
    <version>2.2.4</version>
</dependency>

关于Bean Validation的详细介绍可参考以下文章：
JSR 303 - Bean Validation 介绍及最佳实践
Bean Validation 技术规范特性概述

Excel读取工具类

我们要达到的效果是，模拟游标的方式构建一个Excel读取工具类ExcelReadHelper，然后加载Excel文件流来创建工具类实例，通过这个实例我们可以像游标一样设置当前的行和列，定好位置之后读取出单元格的值并进行校验，完成对Excel文件的读取校验操作。既然是读取还有校验数据，异常处理和提示当然是至关重要的，所以还要有人性化的异常处理方式，方便程序使用者发现Excel中格式或内容有误的地方，具体到哪一行哪一项，出现的问题是什么。

ExcelReadHelper工具类主体

public class ExcelReadHelper {
    private static ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
    //文件绝对路径
    private String excelUrl;
    private Workbook workbook;
    private Sheet sheet;
    //Sheet总数
    private int sheetCount;
    //当前行
    private Row row;
    private Validator validator;
    
    public ExcelReadHelper(File excelFile) throws ExcelException {
        validator = factory.getValidator();
        excelUrl = excelFile.getAbsolutePath();
        //判断工作簿版本
        String fileName = excelFile.getName();
        String suffix = fileName.substring(fileName.lastIndexOf("."));
        try {
            if(suffix.equals(".xlsx")) {
                workbook = new XSSFWorkbook(new FileInputStream(excelFile));
            } else if(suffix.equals(".xls")) {
                workbook = new HSSFWorkbook(new FileInputStream(excelFile));
            } else {
                throw new ExcelException("Malformed excel file");
            }
        } catch(Exception e) {
            throw new ExcelException(excelUrl, e);
        }
        sheetCount = workbook.getNumberOfSheets();
    }
    
    /**
     * 关闭工作簿
     * @throws ExcelException 
     * @throws IOException
     */
    public void close() throws ExcelException {
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {
                throw new ExcelException(excelUrl, e);
            }
        }
    }
    
    /**
     * 获取单元格真实位置
     * @param row 行索引
     * @param col 列索引
     * @return [行,列]
     */
    public String getCellLoc(Integer row, Integer col) {
        return String.format("[%s,%s]", row + 1, CellReference.convertNumToColString(col));     
    }
    
    /**
     * 根据标签设置Sheet
     * @param labels
     * @throws ExcelException
     */
    public void setSheetByLabel(String... labels) throws ExcelException {
        Sheet sheet = null;
        for(String label : labels) {
            sheet = workbook.getSheet(label);
            if(sheet != null) {
                break;
            }
        }
        if(sheet == null) {
            StringBuilder sheetStr = new StringBuilder();
            for (String label : labels) {
                sheetStr.append(label).append(",");
            }
            sheetStr.deleteCharAt(sheetStr.lastIndexOf(","));
            throw new ExcelException(excelUrl, sheetStr.toString(), "Sheet does not exist");
        }
        this.sheet = sheet;
    }
    
    /**
     * 根据索引设置Sheet
     * @param index
     * @throws ExcelException 
     */
    public void setSheetAt(Integer index) throws ExcelException {
        Sheet sheet = workbook.getSheetAt(index);
        if(sheet == null) {
            throw new ExcelException(excelUrl, index + "", "Sheet does not exist");
        }
        this.sheet = sheet;
    }

    /**
     * 获取单元格内容并转为String类型
     * @param row 行索引
     * @param col 列索引
     * @return
     */
    @SuppressWarnings("deprecation")
    public String getValueAt(Integer row, Integer col) {
        Cell cell = sheet.getRow(row).getCell(col);
        String value = null;
        if (cell != null) {
          switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue() + "";
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue().getTime() + "";
                } else {
                    double num = cell.getNumericCellValue();
                    if(num % 1 == 0) {
                        value = Double.valueOf(num).intValue() + "";
                    } else {
                        value = num + "";
                    }
                }
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = cell.getNumericCellValue() + "";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue()) + "";
                break;
          }
        }
        return (value == null || value.isEmpty()) ? null : value.trim();
    }
    
    /**
     * 获取当前行指定列内容
     * @param col 列索引
     * @return
     */
    public String getValue(Integer col) {
        return getValueAt(row.getRowNum(), col);
    }
    
    /**
     * 获取Sheet名称
     * @return
     */
    public String getSheetLabel() {
        String label = null;
        if(sheet != null) {
            label = sheet.getSheetName();
        }
        return label;
    }
    
    /**
     * 行偏移
     * @param offset 偏移量
     * @return
     */
    public Boolean offsetRow(Integer offset) {
        Boolean state = true;
        if(row == null) {
            row = sheet.getRow(offset-1);
        } else {
            row = sheet.getRow(row.getRowNum() + offset);
            if(row == null) {
                state = false;
            }
        }
        return state;
    }
    
    /**
     * 设置行
     * @param index 索引
     * @return
     */
    public Boolean setRow(Integer index) {
        row = sheet.getRow(index);
        return row != null;
    }
    
    /**
     * 偏移一行
     * @return
     */
    public Boolean nextRow() {
        return offsetRow(1);
    }
    
    /**
     * 偏移到下一个Sheet
     * @return
     */
    public Boolean nextSheet() {
        Boolean state = true;
        if(sheet == null) {
            sheet = workbook.getSheetAt(0);
        } else {
            int index = workbook.getSheetIndex(sheet) + 1;
            if(index >= sheetCount) {
                sheet = null;
            } else {
                sheet = workbook.getSheetAt(index);
            }
            
            if(sheet == null) {
                state = false;
            }
        }
        row = null;
        return state;
    }
    
    /**
     * 数据校验
     * @param obj 校验对象
     * @throws ExcelException
     */
    public <T> void validate(T obj) throws ExcelException {
        Set<ConstraintViolation<T>> constraintViolations = validator.validate(obj);
        if(constraintViolations.size() > 0) {
            Iterator<ConstraintViolation<T>> iterable = constraintViolations.iterator();
            ConstraintViolation<T> cv = iterable.next();
            throw new ExcelException(excelUrl, sheet.getSheetName(), row.getRowNum() + 1 + "", 
                    String.format("%s=%s:%s", cv.getPropertyPath(), cv.getInvalidValue(), cv.getMessage()));
        }
    }

    /**
     * 抛出当前Sheet指定行异常
     * @param row 异常发生行索引
     * @param message 异常信息
     * @return
     */
    public ExcelException excelRowException(Integer row, String message) {
        return new ExcelException(excelUrl, sheet.getSheetName(), row + 1 + "", message);
    }

    /**
     * 抛出当前行异常
     * @param message 异常信息
     * @return
     */
    public ExcelException excelCurRowException(String message) {
        return new ExcelException(excelUrl, sheet.getSheetName(), row.getRowNum() + 1 + "", message);
    }

    /**
     * 抛出自定义异常
     * @param message 异常信息
     * @return
     */
    public ExcelException excelException(String message) {
        return new ExcelException(excelUrl, message);
    }
}

ExcelException异常类

public class ExcelException extends Exception {

    public ExcelException() {
        super();
    }

    public ExcelException(String message) {
        super(message);
    }

    public ExcelException(String url, String message) {
        super(String.format("EXCEL[%s]：%s", url, message));
    }
    
    public ExcelException(String url, String sheet, String message) {
        super(String.format("EXCEL[%s],SHEET[%s]：%s", url, sheet, message));
    }
    
    public ExcelException(String url, String sheet, String row, String message) {
        super(String.format("EXCEL[%s],SHEET[%s],ROW[%s]：%s", url, sheet, row, message));
    }
    
    public ExcelException(String url, Throwable cause) {
        super(String.format("EXCEL[%s]", url), cause);
    }
    
}

使用实例

// 使用Excel文件对象初始化ExcelReadHelper
ExcelReadHelper excel = new ExcelReadHelper(file);
        
// 第一页
excel.setSheetAt(0);
        
// “Sheet1”页
excel.setSheetByLabel("Sheet1");
        
// 下一页
excel.nextSheet();
        
// 第一行（以 0 起始）
excel.setRow(0);
        
// 下一行
excel.nextRow();
        
// 偏移两行
excel.offsetRow(2);
        
// 当前行第一列的值
String value1 = excel.getValue(0);
        
// 第一行第一列的值
String value2 = excel.getValueAt(0,0);
        
// 获取单元格真实位置（如索引都为0时结果为[1,A]）
String location = excel.getCellLoc(0,0);
        
// 当前页标题（如“Sheet1”）
String label = excel.getSheetLabel();
        
// 校验读取的数据
try {
    excel.validate(obj);
} catch (ExcelException e) {
    // 错误信息中包含具体错误位置以及原因
    e.printStackTrace();
}

//抛出异常，结果自动包含出现异常的Excel路径
throw excel.excelException(message);

//抛出指定行异常，结果自动包含出现错误的Excel路径、当前页位置
throw excel.excelRowException(0, message);

//抛出当前行异常，结果自动包含出现错误的Excel路径、当前页、当前行位置
throw excel.excelCurRowException(message);

//关闭工作簿Workbook对象
excel.close();

本文为作者kMacro原创，转载请注明来源：https://zkhdev.github.io/2018/10/14/java-dev6/

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,029评论 6赞 492
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,395评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,570评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,535评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,650评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,850评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,006评论 3赞 408
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,747评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,207评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,536评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,683评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,342评论 4赞 330
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,964评论 3赞 315
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,772评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,004评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,401评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,566评论 2赞 349

推荐阅读更多精彩内容