经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » Java相关 » Java » 查看文章
Java开发小技巧：使用Apache POI读取Excel
来源：cnblogs　　作者：kMacro　　时间：2018/10/15 9:30:32　　对本文有异议

前言

在数据仓库中，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依赖

  1. <dependency>
  2.     <groupId>org.apache.poi</groupId>
  3.     <artifactId>poi</artifactId>
  4.     <version>3.17</version>
  5. </dependency>
  6. <dependency>
  7.     <groupId>org.apache.poi</groupId>
  8.     <artifactId>poi-ooxml</artifactId>
  9.     <version>3.17</version>
  10. </dependency>
  11. <dependency>
  12.     <groupId>org.apache.poi</groupId>
  13.     <artifactId>poi-ooxml-schemas</artifactId>
  14.     <version>3.17</version>
  15. </dependency>
  16. <dependency>
  17.     <groupId>org.apache.poi</groupId>
  18.     <artifactId>poi-scratchpad</artifactId>
  19.     <version>3.17</version>
  20. </dependency>

2、创建Workbook实例

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

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

3、获取Sheet表格页对象

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

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

4、获取Cell单元格对象

  1. // 行索引row和列索引col都是以 0 起始
  2. 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对象相应的方法去获取，具体情况具体分析

  1. public Object getCellValue(Cell cell) {
  2.     if(cell == null) {
  3.         return null;
  4.     }
  5.     switch (cell.getCellType()) {
  6.     case Cell.CELL_TYPE_STRING:
  7.         return cell.getRichStringCellValue().getString();
  8.     case Cell.CELL_TYPE_NUMERIC:
  9.         if (DateUtil.isCellDateFormatted(cell)) {
  10.             return cell.getDateCellValue();
  11.         } else {
  12.             return cell.getNumericCellValue();
  13.         }
  14.     case Cell.CELL_TYPE_BOOLEAN:
  15.         return cell.getBooleanCellValue();
  16.     case Cell.CELL_TYPE_FORMULA:
  17.         return formula.evaluate(cell).getNumberValue();
  18.     default:
  19.         return null;
  20.     }
  21. }

6、关闭Workbook对象

  1. workbook.close();

使用Bean Validation进行数据校验

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

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

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

  1. <dependency>
  2.     <groupId>org.hibernate</groupId>
  3.     <artifactId>hibernate-validator</artifactId>
  4.     <version>5.1.3.Final</version>
  5. </dependency>
  6. <dependency>
  7.     <groupId>javax.el</groupId>
  8.     <artifactId>javax.el-api</artifactId>
  9.     <version>2.2.4</version>
  10. </dependency>

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

Excel读取工具类

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

ExcelReadHelper工具类主体

  1. public class ExcelReadHelper {
  2.     private static ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
  3.     //文件绝对路径
  4.     private String excelUrl;
  5.     private Workbook workbook;
  6.     private Sheet sheet;
  7.     //Sheet总数
  8.     private int sheetCount;
  9.     //当前行
  10.     private Row row;
  11.     private Validator validator;
  12.     
  13.     public ExcelReadHelper(File excelFile) throws ExcelException {
  14.         validator = factory.getValidator();
  15.         excelUrl = excelFile.getAbsolutePath();
  16.         //判断工作簿版本
  17.         String fileName = excelFile.getName();
  18.         String suffix = fileName.substring(fileName.lastIndexOf("."));
  19.         try {
  20.             if(suffix.equals(".xlsx")) {
  21.                 workbook = new XSSFWorkbook(new FileInputStream(excelFile));
  22.             } else if(suffix.equals(".xls")) {
  23.                 workbook = new HSSFWorkbook(new FileInputStream(excelFile));
  24.             } else {
  25.                 throw new ExcelException("Malformed excel file");
  26.             }
  27.         } catch(Exception e) {
  28.             throw new ExcelException(excelUrl, e);
  29.         }
  30.         sheetCount = workbook.getNumberOfSheets();
  31.     }
  32.     
  33.     /**
  34.      * 关闭工作簿
  35.      * @throws ExcelException 
  36.      * @throws IOException
  37.      */
  38.     public void close() throws ExcelException {
  39.         if (workbook != null) {
  40.             try {
  41.                 workbook.close();
  42.             } catch (IOException e) {
  43.                 throw new ExcelException(excelUrl, e);
  44.             }
  45.         }
  46.     }
  47.     
  48.     /**
  49.      * 获取单元格真实位置
  50.      * @param row 行索引
  51.      * @param col 列索引
  52.      * @return [行,列]
  53.      */
  54.     public String getCellLoc(Integer row, Integer col) {
  55.         return String.format("[%s,%s]", row + 1, CellReference.convertNumToColString(col));     
  56.     }
  57.     
  58.     /**
  59.      * 根据标签设置Sheet
  60.      * @param labels
  61.      * @throws ExcelException
  62.      */
  63.     public void setSheetByLabel(String... labels) throws ExcelException {
  64.         Sheet sheet = null;
  65.         for(String label : labels) {
  66.             sheet = workbook.getSheet(label);
  67.             if(sheet != null) {
  68.                 break;
  69.             }
  70.         }
  71.         if(sheet == null) {
  72.             StringBuilder sheetStr = new StringBuilder();
  73.             for (String label : labels) {
  74.                 sheetStr.append(label).append(",");
  75.             }
  76.             sheetStr.deleteCharAt(sheetStr.lastIndexOf(","));
  77.             throw new ExcelException(excelUrl, sheetStr.toString(), "Sheet does not exist");
  78.         }
  79.         this.sheet = sheet;
  80.     }
  81.     
  82.     /**
  83.      * 根据索引设置Sheet
  84.      * @param index
  85.      * @throws ExcelException 
  86.      */
  87.     public void setSheetAt(Integer index) throws ExcelException {
  88.         Sheet sheet = workbook.getSheetAt(index);
  89.         if(sheet == null) {
  90.             throw new ExcelException(excelUrl, index + "", "Sheet does not exist");
  91.         }
  92.         this.sheet = sheet;
  93.     }
  94.  
  95.     /**
  96.      * 获取单元格内容并转为String类型
  97.      * @param row 行索引
  98.      * @param col 列索引
  99.      * @return
  100.      */
  101.     @SuppressWarnings("deprecation")
  102.     public String getValueAt(Integer row, Integer col) {
  103.         Cell cell = sheet.getRow(row).getCell(col);
  104.         String value = null;
  105.         if (cell != null) {
  106.           switch (cell.getCellType()) {
  107.             case Cell.CELL_TYPE_STRING:
  108.                 value = cell.getStringCellValue() + "";
  109.                 break;
  110.             case Cell.CELL_TYPE_NUMERIC:
  111.                 if (DateUtil.isCellDateFormatted(cell)) {
  112.                     value = cell.getDateCellValue().getTime() + "";
  113.                 } else {
  114.                     double num = cell.getNumericCellValue();
  115.                     if(num % 1 == 0) {
  116.                         value = Double.valueOf(num).intValue() + "";
  117.                     } else {
  118.                         value = num + "";
  119.                     }
  120.                 }
  121.                 break;
  122.             case Cell.CELL_TYPE_FORMULA:
  123.                 value = cell.getNumericCellValue() + "";
  124.                 break;
  125.             case Cell.CELL_TYPE_BOOLEAN:
  126.                 value = String.valueOf(cell.getBooleanCellValue()) + "";
  127.                 break;
  128.           }
  129.         }
  130.         return (value == null || value.isEmpty()) ? null : value.trim();
  131.     }
  132.     
  133.     /**
  134.      * 获取当前行指定列内容
  135.      * @param col 列索引
  136.      * @return
  137.      */
  138.     public String getValue(Integer col) {
  139.         return getValueAt(row.getRowNum(), col);
  140.     }
  141.     
  142.     /**
  143.      * 获取Sheet名称
  144.      * @return
  145.      */
  146.     public String getSheetLabel() {
  147.         String label = null;
  148.         if(sheet != null) {
  149.             label = sheet.getSheetName();
  150.         }
  151.         return label;
  152.     }
  153.     
  154.     /**
  155.      * 行偏移
  156.      * @param offset 偏移量
  157.      * @return
  158.      */
  159.     public Boolean offsetRow(Integer offset) {
  160.         Boolean state = true;
  161.         if(row == null) {
  162.             row = sheet.getRow(offset-1);
  163.         } else {
  164.             row = sheet.getRow(row.getRowNum() + offset);
  165.             if(row == null) {
  166.                 state = false;
  167.             }
  168.         }
  169.         return state;
  170.     }
  171.     
  172.     /**
  173.      * 设置行
  174.      * @param index 索引
  175.      * @return
  176.      */
  177.     public Boolean setRow(Integer index) {
  178.         row = sheet.getRow(index);
  179.         return row != null;
  180.     }
  181.     
  182.     /**
  183.      * 偏移一行
  184.      * @return
  185.      */
  186.     public Boolean nextRow() {
  187.         return offsetRow(1);
  188.     }
  189.     
  190.     /**
  191.      * 偏移到下一个Sheet
  192.      * @return
  193.      */
  194.     public Boolean nextSheet() {
  195.         Boolean state = true;
  196.         if(sheet == null) {
  197.             sheet = workbook.getSheetAt(0);
  198.         } else {
  199.             int index = workbook.getSheetIndex(sheet) + 1;
  200.             if(index >= sheetCount) {
  201.                 sheet = null;
  202.             } else {
  203.                 sheet = workbook.getSheetAt(index);
  204.             }
  205.             
  206.             if(sheet == null) {
  207.                 state = false;
  208.             }
  209.         }
  210.         row = null;
  211.         return state;
  212.     }
  213.     
  214.     /**
  215.      * 数据校验
  216.      * @param obj 校验对象
  217.      * @throws ExcelException
  218.      */
  219.     public <T> void validate(T obj) throws ExcelException {
  220.         Set<ConstraintViolation<T>> constraintViolations = validator.validate(obj);
  221.         if(constraintViolations.size() > 0) {
  222.             Iterator<ConstraintViolation<T>> iterable = constraintViolations.iterator();
  223.             ConstraintViolation<T> cv = iterable.next();
  224.             throw new ExcelException(excelUrl, sheet.getSheetName(), row.getRowNum() + 1 + "", 
  225.                     String.format("%s=%s:%s", cv.getPropertyPath(), cv.getInvalidValue(), cv.getMessage()));
  226.         }
  227.     }
  228.  
  229.     /**
  230.      * 抛出当前Sheet指定行异常
  231.      * @param row 异常发生行索引
  232.      * @param message 异常信息
  233.      * @return
  234.      */
  235.     public ExcelException excelRowException(Integer row, String message) {
  236.         return new ExcelException(excelUrl, sheet.getSheetName(), row + 1 + "", message);
  237.     }
  238.  
  239.     /**
  240.      * 抛出当前行异常
  241.      * @param message 异常信息
  242.      * @return
  243.      */
  244.     public ExcelException excelCurRowException(String message) {
  245.         return new ExcelException(excelUrl, sheet.getSheetName(), row.getRowNum() + 1 + "", message);
  246.     }
  247.  
  248.     /**
  249.      * 抛出自定义异常
  250.      * @param message 异常信息
  251.      * @return
  252.      */
  253.     public ExcelException excelException(String message) {
  254.         return new ExcelException(excelUrl, message);
  255.     }
  256. }

ExcelException异常类

  1. public class ExcelException extends Exception {
  2.  
  3.     public ExcelException() {
  4.         super();
  5.     }
  6.  
  7.     public ExcelException(String message) {
  8.         super(message);
  9.     }
  10.  
  11.     public ExcelException(String url, String message) {
  12.         super(String.format("EXCEL[%s]：%s", url, message));
  13.     }
  14.     
  15.     public ExcelException(String url, String sheet, String message) {
  16.         super(String.format("EXCEL[%s],SHEET[%s]：%s", url, sheet, message));
  17.     }
  18.     
  19.     public ExcelException(String url, String sheet, String row, String message) {
  20.         super(String.format("EXCEL[%s],SHEET[%s],ROW[%s]：%s", url, sheet, row, message));
  21.     }
  22.     
  23.     public ExcelException(String url, Throwable cause) {
  24.         super(String.format("EXCEL[%s]", url), cause);
  25.     }
  26.     
  27. }

使用实例

  1. // 使用Excel文件对象初始化ExcelReadHelper
  2. ExcelReadHelper excel = new ExcelReadHelper(file);
  3.         
  4. // 第一页
  5. excel.setSheetAt(0);
  6.         
  7. // “Sheet1”页
  8. excel.setSheetByLabel("Sheet1");
  9.         
  10. // 下一页
  11. excel.nextSheet();
  12.         
  13. // 第一行（以 0 起始）
  14. excel.setRow(0);
  15.         
  16. // 下一行
  17. excel.nextRow();
  18.         
  19. // 偏移两行
  20. excel.offsetRow(2);
  21.         
  22. // 当前行第一列的值
  23. String value1 = excel.getValue(0);
  24.         
  25. // 第一行第一列的值
  26. String value2 = excel.getValueAt(0,0);
  27.         
  28. // 获取单元格真实位置（如索引都为0时结果为[1,A]）
  29. String location = excel.getCellLoc(0,0);
  30.         
  31. // 当前页标题（如“Sheet1”）
  32. String label = excel.getSheetLabel();
  33.         
  34. // 校验读取的数据
  35. try {
  36.     excel.validate(obj);
  37. } catch (ExcelException e) {
  38.     // 错误信息中包含具体错误位置以及原因
  39.     e.printStackTrace();
  40. }
  41.  
  42. //抛出异常，结果自动包含出现异常的Excel路径
  43. throw excel.excelException(message);
  44.  
  45. //抛出指定行异常，结果自动包含出现错误的Excel路径、当前页位置
  46. throw excel.excelRowException(0, message);
  47.  
  48. //抛出当前行异常，结果自动包含出现错误的Excel路径、当前页、当前行位置
  49. throw excel.excelCurRowException(message);
  50.  
  51. //关闭工作簿Workbook对象
  52. excel.close();

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

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

Java热门文章

  • 【IDEA】Application context not configured for this file
  • [IDEA] chapter_reader - idea看小说插件 idea阅读插件 idea摸鱼插件
  • thrift常见异常及原因分析
  • Java 调用 PaddleDetection 模型
  • JVM 详解，大白话带你认识 JVM
  • spring-boot-2.0.3不一样系列之番外篇 - @Configuration、Condition与@Conditional
  • Java Comparator.comparing比较导致空指针异常的解决
  • Java使用EasyExcel进行单元格合并的问题详解
  • 死磕 java集合之ArrayBlockingQueue源码分析
  • Java基础之集合Set详解

    • Java推荐文章

  • 面试场景题：一次关于线程池使用场景的讨论。
  • 前端黑科技：使用 JavaScript 实现网页扫码功能
  • 聊一聊 Netty 数据搬运工 ByteBuf 体系的设计与实现
  • 简单设计一个JAVA并行处理工具类
  • 【杂谈】JPA乐观锁改悲观锁遇到的一些问题与思考
  • 认识netty的基本组件
  • Jenkins如何使用CrumbIssuer防御CSRF攻击
  • 如何平稳地从nacos迁移到r-nacos？
  • 线程池遇到父子任务，有大坑，要注意！
  • Java JVM——12. 垃圾回收理论概述

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号