POI和EasyExcel
需求：
- 将 Excel 表中的信息录入到网站数据库。
- 对数据进行校验与处理
- 将导入失败的数据导出为 excel 表格。
市面上比较知名的开源组件有Apache 的POI 和 阿里巴巴的 EasyExcel。EasyExcel 也是对 POI 的改进和封装， 更加好用。
1. Apache POI
Apache POI 官网： http://poi.apache.org/index.html
结构：
- HSSF － 提供读写[Microsoft Excel](https://baike.baidu.com/item/Microsoft Excel)格式档案的功能。excel 2003 版本
- XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。excel 2007 版本
- HWPF － 提供读写[Microsoft Word](https://baike.baidu.com/item/Microsoft Word)格式档案的功能。
- HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
- HDGF － 提供读写[Microsoft Visio](https://baike.baidu.com/item/Microsoft Visio)格式档案的功能。
本次项目中需要用到HSSF和XSSF对Excel文件进行导入导出。
1.1 POI-Excel 读
-
引入依赖
<dependencies> <!-- xls03 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <!-- xlsx07 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency> <!-- 日期格式化工具 --> <dependency> <groupId>joda-time</groupId> <artifactId>joda-time</artifactId> <version>2.10.1</version> </dependency>需要注意：2003版本和2007版本存在兼容性问题，03版本最多只有 65535 行，两个版本的写操作，就是对象不一样，方法都是一样的。
-
实现代码
public ServerResponse importExcel(MultipartFile myFile) { //1. 使用HSSFWorkbook 打开或者创建 “Excel对象” //2. 用HSSFWorkbook返回对象或者创建sheet对象 //3. 用sheet返回行对象，用行对象得到Cell对象 //4. 对Cell对象进行读写 //创建数据对象 List<ReportInfo> reports = new ArrayList<>(); // 创建一个工作表 Workbook workbook = null; // 获取文件名 String fileName = myFile.getOriginalFilename(); logger.info("【fileName】{}", fileName); try { //根据后缀判断版本，创建工作薄 if (fileName.endsWith(XLSX)) { // 2007版本 workbook = new HSSFWorkbook(myFile.getInputStream()); } else if (fileName.endsWith(XLS)) { // 2003版本 workbook = new XSSFWorkbook(myFile.getInputStream()); }else if (fileName.isEmpty()){ return ServerResponse.createByErrorCodeMessage(FILE_IS_NULL.getCode(), FILE_IS_NULL.getDesc()); } else { return ServerResponse.createByErrorCodeMessage(FILE_IS_NOT_EXCEL.getCode(), FILE_IS_NOT_EXCEL.getDesc()); } } catch (IOException e) { throw new RuntimeException(e); } // 获取sheet对象 Sheet sheet = workbook.getSheet(SHEET_NAME); // 总数据量 int rows = sheet.getLastRowNum(); // 失败数据量 int errorTime = 0; logger.info("【rows】{}", rows); if (rows == 0) { return ServerResponse.createByErrorCodeMessage(DATA_IS_NULL.getCode(), DATA_IS_NULL.getDesc()); } //开始时间 long startTime = System.currentTimeMillis(); /*=========================行数据处理start=========================*/ //第一行为标题，i=1 for (int i = 1; i <= rows + 1; i++) { //表行数据数量 Row row = sheet.getRow(i); if (row != null) { ReportInfo report = new ReportInfo(); /*===============获取值start===============*/ // 身份证号码 String idNumber = getCellValue(row.getCell(0)); // 用户姓名 String username = getCellValue(row.getCell(1)); /*===============获取值end===============*/ /*===============数据校验start===============*/ //跳过空数据 if (idNumber.isEmpty()||username.isEmpty()){ errorTime++; continue; } //身份证号码与关系人相同 if (idNumber == spouseIdNumber){ return ServerResponse.createByErrorCodeMessage(RELATION_NUMBER_IS_SAME.getCode(), RELATION_NUMBER_IS_SAME.getDesc()); } // 身份证号码唯一性校验 int idNumberCount = reportMapper.selectIdNumberCount(idNumber); if (idNumberCount != 0) { return ServerResponse.createByErrorCodeMessage(IDNUMBER_IS_EXIST.getCode(), IDNUMBER_IS_EXIST.getDesc()); } // 关系人身份证号码唯一性校验 int spouseIdNumberCount = reportMapper.selectSpouseIdNumberCount(spouseIdNumber); if (spouseIdNumberCount != 0) { return ServerResponse.createByErrorCodeMessage(RELATION_IDNUMBER_IS_EXIST.getCode(), RELATION_IDNUMBER_IS_EXIST.getDesc()); } // 证件号码唯一性校验 int certificateNumberCount = reportMapper.selectCertificateNumberCount(certificateNumber); if (certificateNumberCount != 0) { return ServerResponse.createByErrorCodeMessage(CERTIFICATE_NUMBER_IS_EXIST.getCode(), CERTIFICATE_NUMBER_IS_EXIST.getDesc()); } /*===============数据校验end===============*/ report.setIdNumber(idNumber).setName(username).setDept(dept).setRelation(relation) .setSpouseName(spouseName).setSpouseIdNumber(spouseIdNumber).setUnit(unit) .setJob(job).setPoliticalOutlook(politicalOutlook).setOutsideChina(outsideChina) .setOutsideSituation(outsideSituation).setCertificateNumber(certificateNumber) .setCountryResidence(countryResidence).setResidenceCity(residenceCity).setStartTime(startsTime); // 单条数据加入list reports.add(report); } } /*===============行数据处理end===============*/ // 将处理结果批量插入 reportMapper.batchInsert(reports); //结束时间 long endTime = System.currentTimeMillis(); //消耗时间 long totaltime = endTime - startTime; logger.info("【消耗时间为】{}", totaltime); logger.info("【第一条数据为】{}", JSON.toJSON(reports.get(0))); logger.info("【总数据：】{}", rows); logger.info("【失败数据：】{}", errorTime); //导入成功 return ServerResponse.createBySuccessMessage("耗时"+totaltime+"秒,"+"总数据"+rows+"条"+","+"失败"+errorTime+"条"); } -
读取不同的数据类型
/** * 获取行对象 * @param cell * @return */ public String getCellValue(Cell cell) { String value = ""; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC:// 数字 value = cell.getNumericCellValue() + " "; if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { value = new SimpleDateFormat("yyyy-MM-dd").format(date); // 日期格式化 } else { value = ""; } } else { // 解析cell时候 数字类型默认是double类型的 但是想要获取整数类型 需要格式化 value = new DecimalFormat("0").format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: // 字符串 value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean类型 value = cell.getBooleanCellValue() + ""; break; case HSSFCell.CELL_TYPE_BLANK: // 空值 value = ""; break; case HSSFCell.CELL_TYPE_ERROR: // 错误类型 value = "非法字符"; break; default: value = "未知类型"; break; } } return value.trim(); }
1.2POI-Excel 写
public void outExcel () throws IOException {
// 时间
long begin = System.currentTimeMillis();
// 创建一个工作薄
Workbook workbook = new SXSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet();
// 写入数据
for (int rowNum = 0; rowNum < 65537; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/error.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
// 清除临时文件
((SXSSFWorkbook) workbook).dispose();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin) / 1000); // 1.859
}
SXSSF优点： 可以写非常大的数据量，如 100 万条甚至更多，写数据速度快，占用更少的内存。
注意：
过程总会产生临时文件，需要清理临时文件。默认由 100 条记录被保存在内存中，则最前面的数据被写入临时文件。如果想要自定义内存中数据的数量，可以使用 new SXSSFWorkbook (数量)。
2. EasyExcel
GitHub 地址： https://github.com/alibaba/easyexcel
EasyExcel 官网： https://www.yuque.com/easyexcel/doc/easyexcel
根据官方文档的测试代码： https://www.yuque.com/easyexcel/doc/write
EasyExcel 是阿里巴巴开源的一个 excel处理框架，以使用简单、节省内存著称。
EasyExcel 能大大减少内存占用的主要原因是在解析 Excel 时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。
-
引入依赖
<dependencies> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.0-beta2</version> </dependency> <!-- 日期格式化工具 --> <dependency> <groupId>joda-time</groupId> <artifactId>joda-time</artifactId> <version>2.10.1</version> </dependency> </dependencies> -
实体类demo
@Data public class DemoData { @ExcelProperty("字符串标题") private String string; @ExcelProperty("日期标题") private Date date; @ExcelProperty("数字标题") private Double doubleData; /** * 忽略这个字段 */ @ExcelIgnore private String ignore; }注解：
注解 说明 @ExcelProperty 必要注解，注解中有三个参数 value,index,converter分别代表列明，列序号，数据转换方式value和index只能二选一，通常不用设置converter
1.value 通过标题文本对应
2.index 通过文本行号对应
3.converter 转换器，通常入库和出库转换使用，如性别入库0和1，出库男和女@ExcelIgnore 不将该字段转换成Excel，根据实体类导出Excel时，加在不需要生成列的字段上 @ExcelIgnoreUnannotated 没有注解的字段都不转换 … 还有许多针对表格样式的注解… -
写入数据
public class EasyExcelTest { private List<DemoData> data() { List<DemoData> list = new ArrayList<DemoData>(); for (int i = 0; i < 10; i++) { DemoData data = new DemoData(); data.setString("字符串" + i); data.setDate(new Date()); data.setDoubleData(0.56); list.add(data); } return list; } // 根据list 写入 Excel /** * 最简单的写 * 1. 创建excel对应的实体对象 参照{@link DemoData} * 2. 直接写即可 */ public void simpleWrite() { String PATH = "F:\\"; String fileName =PATH + System.currentTimeMillis() + ".xlsx"; // 这里需要指定写用哪个class去写，然后写到第一个sheet， //名字为模板然后文件流会自动关闭 // 如果这里想使用03 则 传入excelType参数即可 EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data()); } }
easyExcel由于整合了POI，对数据处理的许多操作代码量大大减少，主要表现于各种注解的作用。
4605
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
