在数据仓库中，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种类型：
- CELL_TYPE_BLANK ：空值
- CELL_TYPE_BOOLEAN ：布尔型
- CELL_TYPE_ERROR ： 错误
- CELL_TYPE_FORMULA ：公式型
- CELL_TYPE_STRING：字符串型
- 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();
