近期有个需求，说是要用到excel导入导出，一般我们的想法都是按照行数，于是实现了，后面发现公司需求的是列读，甚至不规则的单个excel的读。于是就用poi自己写了按照单元格读的实现。
一、按行读
想起了之前用到的poi，经过搜索发现，开源的项目中有比较好的封装poi的框架，一个是阿里出的easyExcel，另一个是easypoi，感觉使用起来都很方便。网上说easyExcel能解决大文件内存溢出问题，于是项目中就使用easyExcel了。
简单普及下easyExcel原理，不做底层码农，了解点上层设计有好处:
easyExcel核心原理
写有大量数据的xlsx文件时，POI为我们提供了SXSSFWorkBook类来处理，这个类的处理机制是当内存中的数据条数达到一个极限数量的时候就flush这部分数据，再依次处理余下的数据，这个在大多数场景能够满足需求。
读有大量数据的文件时，使用WorkBook处理就不行了，因为POI对文件是先将文件中的cell读入内存，生成一个树的结构（针对Excel中的每个sheet，使用TreeMap存储sheet中的行）。
如果数据量比较大，则同样会产生java.lang.OutOfMemoryError: Java heap space错误。POI官方推荐使用“XSSF and SAX（event API）”方式来解决。
分析清楚POI后要解决OOM有3个关键.
读取的数据转换流程
easyexcel解析数据的 设计思想和相关角色。
根据上面官网给的信息，我们得有个模型来接收每行的数据，本例用CommonUser对象，该对象上在这上面也可以加数据校验，还需要个解析每个行的监听器CommonUserListener，可以来处理每行的数据，然后进行数据库操作读写。
来个小demo（用的mybatis-plus框架）
controler @RestController @RequestMapping("info/commonuser") public class CommonUserController { @Autowired private CommonUserService commonUserService; /** * excel导入（按照行读） * <p> * 1. 创建excel对应的实体对象 参照{@link CommonUser} * <p> * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link CommonUserListener} * <p> * 3. 直接读即可 */ @PostMapping("upload") @ResponseBody public String upload(MultipartFile file) throws IOException { EasyExcel.read(file.getInputStream(), CommonUser.class, new CommonUserListener(commonUserService)) .sheet() .doRead(); return "success"; } /** * 文件下载（失败了会返回一个有部分数据的Excel） * <p> * 1. 创建excel对应的实体对象 参照{@link CommonUser} * <p> * 2. 设置返回的 参数 * <p> * 3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大 */ @GetMapping("download") public void download(HttpServletResponse response) throws IOException { // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("用户表", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), CommonUser.class).sheet("模板").doWrite(data()); } /** * excel导出 * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel） * * @since 2.1.1 */ @GetMapping("downloadFailedUsingJson") public void downloadFailedUsingJson(HttpServletResponse response) throws IOException { // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman try { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("测试", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); // 这里需要设置不关闭流 EasyExcel.write(response.getOutputStream(), CommonUser.class).autoCloseStream(Boolean.FALSE).sheet("模板") .doWrite(data()); } catch (Exception e) { // 重置response response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); Map<String, String> map = new HashMap<String, String>(); map.put("status", "failure"); map.put("message", "下载文件失败" + e.getMessage()); response.getWriter().println(new Gson().toJson(map)); } } private List<CommonUser> data() { List<CommonUser> list = commonUserService.list(); return list; } }
CommonUserService （和读excel无关，业务需要）
/** * 用户Service * * @author hfl 690328661@qq.com * @date 2020-05-16 08:42:50 */ public interface CommonUserService extends IService<CommonUser> { }
CommonUserServiceImpl
@Service("commonUserService") public class CommonUserServiceImpl extends ServiceImpl<CommonUserMapper, CommonUser> implements CommonUserService { private final static Logger logger = LoggerFactory.getLogger(CommonUserServiceImpl.class); }
CommonUserListener（负责获取每行的数据，然后根据需要进行db保存）
public class CommonUserListener extends AnalysisEventListener<CommonUser> { private static final Logger LOGGER = LoggerFactory.getLogger(TestController.class); /** * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收 * mybatis-plus 默认1000 */ private static final int BATCH_COUNT = 1000; List<CommonUser> list = new ArrayList<>(); /** * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。 */ private CommonUserService commonUserService; public CommonUserListener(CommonUserService commonUserService) { // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数 this.commonUserService = commonUserService; } /** * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收 */ @Override public void invoke(CommonUser data, AnalysisContext context) { LOGGER.info("解析到一条数据:{}", new Gson().toJson(data)); list.add(data); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM if (list.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list list.clear(); } } @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据，确保最后遗留的数据也存储到数据库 saveData(); LOGGER.info("所有数据解析完成！"); } /** * 加上存储数据库 */ private void saveData() { LOGGER.info("{}条数据，开始存储数据库！", list.size()); commonUserService.saveBatch(list); LOGGER.info("存储数据库成功！"); } }
CommonUserMapper（数据库操作的）
/** * 用户表 * * @author hfl * @date 2020-05-16 08:42:50 */ @Mapper public interface CommonUserMapper extends BaseMapper<CommonUser> { }
实体对象
@Data @TableName("common_user") public class CommonUser extends BaseEntity { /** * 用户ID */ @TableId private String userId; /** * 用户名 */ @ExcelProperty("字符串标题") private String userName; /** * 真实姓名 */ private String userRealname; /** * 密码 */ private String userPassword; /** * 盐 */ private String userSalt; /** * 手机号码 */ private String userMobile; /** * 性别 */ private String userSex; /** * 头像url */ private String userAvatar; /** * 电子邮箱 */ private String userEmail; /** * 账号状态(0-正常,1-冻结) */ private Integer userStatus; /** * 扩展字段 */ private String userEx; @Override public String toString() { return "CommonUser{" + "userId='" + userId + '\'' + ", userName='" + userName + '\'' + ", userRealname='" + userRealname + '\'' + ", userPassword='" + userPassword + '\'' + ", userSalt='" + userSalt + '\'' + ", userMobile='" + userMobile + '\'' + ", userSex='" + userSex + '\'' + ", userAvatar='" + userAvatar + '\'' + ", userEmail='" + userEmail + '\'' + ", userStatus=" + userStatus + ", userEx='" + userEx + '\'' + '}'; } }
数据库表结构:
CREATE TABLE `common_user` ( `user_id` varchar(32) NOT NULL COMMENT '用户ID', `user_name` varchar(50) DEFAULT NULL COMMENT '用户名', `user_realname` varchar(50) DEFAULT NULL COMMENT '真实姓名', `user_password` varchar(50) DEFAULT NULL COMMENT '密码', `user_salt` varchar(50) DEFAULT NULL COMMENT '盐', `user_mobile` varchar(20) DEFAULT NULL COMMENT '手机号码', `user_sex` varchar(20) DEFAULT NULL COMMENT '性别', `user_avatar` varchar(255) DEFAULT NULL COMMENT '头像url', `user_email` varchar(50) DEFAULT NULL COMMENT '电子邮箱', `user_status` tinyint(1) DEFAULT NULL COMMENT '账号状态(0-正常,1-冻结)', `user_ex` text COMMENT '扩展字段', `creater` varchar(32) DEFAULT NULL COMMENT '创建者', `modifier` varchar(32) DEFAULT NULL COMMENT '修改者', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_time` datetime DEFAULT NULL COMMENT '更新时间', `is_deleted` tinyint(1) DEFAULT NULL COMMENT '0：未删除，1：删除', PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
启动项目:
使用postman先把本地的数据库数据导出到excel中试试:
浏览器中输入: localhost:7000/info/commonuser/downloadFailedUsingJson
可以看出数据库中的被导出来了: 标题头和实体中的@ExcelProperty对应:这里只写了字符串标题这个表头，其他的会默认显示英文字段。
按行读
将刚才数据库中的数据全部删除:导入刚才的Excel数据试试
使用post测试:
可以看到控制已经显示成功，
看下数据库:显示已经按照行读，并写入数据库中了
二、按列读(按单元格读：适合模板型的excel）
上面的导入excel数据是按照行读的，但是我的需求是这样的:
列形式，很多个sheet都不一样，怎么处理呢?
于是想到，easyExcel肯定是实现不了了，干脆使用poi自带的按照单元格自己去读。
思路: 每个模板excel对应一个实体，每个单元格的位置（行号和列号）在实体上通过注解对应好，这样我解析单元格，取出每个单元格的值，和位置，赋值给对应的实体的属性。
解析单元格的小demo
因为easyExcel默认引用poi，所以不需要引maven包，直接写就好了:
@Test public void AllExcelRead() throws IOException, InvalidFormatException { //1、指定要读取EXCEL文档名称 String filename="C:\\Users\\69032\\Desktop\\vechicleService.xlsx"; //2、创建输入流 FileInputStream input=new FileInputStream(filename); //3、通过工作簿工厂类来创建工作簿对象 Workbook workbook= WorkbookFactory.create(input); //4、获取工作表 (可以按照sheet名字，也可以按照sheet的索引) String sheetName = "车辆信息备案申请"; Sheet sheet=workbook.getSheet(sheetName); // Sheet sheet=workbook.getSheet("工作表1"); // Sheet sheet = workbook.getSheetAt(0); //5、获取行 Row row=sheet.getRow(53); //6、获取单元格 Cell cell=row.getCell(2); //7、读取单元格内容 System.out.println(cell.getStringCellValue()); }
运行结果如下:
完美找到excel对应单元格的数据:
实战：
定义个属性上的注解，指定某个单元格的属性:
RecordTemplate
@Target({ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface RecordTemplate { //行号 int rowNo(); //列号 int columnNo(); //是否必填 FillCommentEnum comment(); //单元格名称 String name(); //数据类型 }
是否必填的枚举:
FillCommentEnum
/** * Title: FillCommentEnum * Description: 单元格 填充枚举 * * @author hfl * @version V1.0 * @date 2020-05-29 */ public enum FillCommentEnum { FILL(0, "必填"), EMPTY(1, "非必填"); private int code; private String description; FillCommentEnum(int code, String description) { this.code = code; this.description = description; } public int getCode() { return code; } public void setCode(int code) { this.code = code; } public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } }
接下来，我需要定义实体和模板对应。
@Data @TableName("vehicle_static_info") public class VehicleStaticInfo extends BaseEntity { /** * 车辆ID(主键) */ @TableId private String vehicleId; /** * 最大基准扭矩 */ private String engineMaxTorque; /** * 车牌号码(（GB17691-2005必填，GB17691-2018选填）) * 此字段，数据库中已经移除，提取到车辆公共信息表中了 */ @RecordTemplate(rowNo = 3, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车牌号码") @TableField(exist = false) private String vehicleLicense; /** * 车牌颜色(车牌颜色) */ @RecordTemplate(rowNo = 4, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车牌颜色") private String licenseColor; /** * 车体结构(（编码见说明3）) */ @RecordTemplate(rowNo = 5, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车体结构") private Integer vehicleStructure; /** * 车辆颜色 */ @RecordTemplate(rowNo = 6, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车辆颜色") private String vehicleColor; /** * 核定载重 */ @RecordTemplate(rowNo = 7, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "核定载重") private Double vehicleLoad; /** * 车辆尺寸mm（长）(32960、17691等) */ @RecordTemplate(rowNo = 8, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车辆尺寸mm（长）") private Double vehicleLong; /** * 车辆尺寸mm（宽）(国六，新能源等字典) */ @RecordTemplate(rowNo = 9, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车辆尺寸mm（宽）") private Double vehicleWide; /** * 车辆尺寸mm（高） */ @RecordTemplate(rowNo = 10, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车辆尺寸mm（高）") private Double vehicleHigh; /** * 总质量 */ @RecordTemplate(rowNo = 11, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "总质量") private Double grossVehicleMass; /** * 车辆类型(（编码见说明1）) */ @RecordTemplate(rowNo = 12, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车辆类型（编码见右面1）") private Integer vehicleType; /** * 行业类型(（编码见说明8 http://www.stats.gov.cn/tjsj/tjbz/hyflbz/201905/P020190716349644060705.pdf）) */ @RecordTemplate(rowNo = 13, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "行业类型（编码见右面8）") private String industryType; /** * 车辆型号(（GB17691-2005必填，GB17691-2018选填）) */ @RecordTemplate(rowNo = 14, columnNo = 2, comment = FillCommentEnum.FILL, name = "车辆型号（GB17691-2005必填，GB17691-2018选填）") @NotEmpty(message = "车辆型号不能为空", groups = {ImportGroup.class}) private String vehicleModel; /** * 购买时间 */ @RecordTemplate(rowNo = 15, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "购买时间") private String buyingDate; /** * 车架号VIN(（必填）) */ @RecordTemplate(rowNo = 16, columnNo = 2, comment = FillCommentEnum.FILL, name = "车架号VIN（必填）") @NotEmpty(message = "车架号VIN不能为空", groups = {ImportGroup.class}) @TableField(exist = false) private String vehicleFrameNo; /** * 行驶证号 */ @RecordTemplate(rowNo = 17, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "行驶证号") private String drivingLicenseNo; /** * 发动机型号(（GB17691-2005必填，GB17691-2018选填）) */ @RecordTemplate(rowNo = 18, columnNo = 2, comment = FillCommentEnum.FILL, name = "发动机型号（GB17691-2005必填，GB17691-2018选填）") @NotEmpty(message = "发动机型号不能为空", groups = {ImportGroup.class}) private String engineModel; /** * 发动机编号 */ @RecordTemplate(rowNo = 19, columnNo = 2, comment = FillCommentEnum.FILL, name = "发动机编号") @NotEmpty(message = "发动机编号不能为空", groups = {ImportGroup.class}) private String engineNo; /** * 车籍地 */ @RecordTemplate(rowNo = 20, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车籍地") private String vehiclePlace; /** * 车辆技术等级(（编码见说明2）) */ @RecordTemplate(rowNo = 21, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车辆技术等级（编码见右面2）") private Integer technicalLevel; /** * 出厂日期 */ @RecordTemplate(rowNo = 22, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "出厂日期") private String productionDate; /** * 等级评定日期 */ @RecordTemplate(rowNo = 23, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "等级评定日期") private String gradeAssessmentDate; /** * 二级维护日期 */ @RecordTemplate(rowNo = 24, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "二级维护日期") private String twoMaintenanceDate; /** * 二级维护状态(（编码见说明5）) */ @RecordTemplate(rowNo = 25, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "二级维护状态（编码见右面5）") private Integer twoMaintenanceStatus; /** * 年审状态(（编码见说明4）) */ @RecordTemplate(rowNo = 26, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "年审状态（编码见右面4）") private Integer yearEvaluationStatus; /** * 年检有效期 */ @RecordTemplate(rowNo = 27, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "年检有效期") private String yearinspectionPeriod; /** * 保险有效期 */ @RecordTemplate(rowNo = 28, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "保险有效期") private String insurancePeriod; /** * 保养有效期 */ @RecordTemplate(rowNo = 29, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "保养有效期") private String maintenancePeriod; /** * 所属单位名称 */ @RecordTemplate(rowNo = 30, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "所属单位名称") private String enterpriseName; /** * 车辆联系人 */ @RecordTemplate(rowNo = 31, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车辆联系人") private String contactsName; /** * 车辆联系电话 */ @RecordTemplate(rowNo = 32, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车辆联系电话") private String contactPhone; /** * 车辆sim卡号 */ @RecordTemplate(rowNo = 33, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车辆sim卡号") private String terminalSim; /** * 车辆注册时间 */ @RecordTemplate(rowNo = 34, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车辆注册时间") private String registerDate; /** * 所属组织ID */ @RecordTemplate(rowNo = 35, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "所属组织ID") private String organizationId; /** * 环保局车辆类型(（编码见说明6）) */ @RecordTemplate(rowNo = 36, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "环保局车辆类型（编码见右面6）") private Integer epaVehicleType; /** * 运输局车辆类型(（编码见说明7）) */ @RecordTemplate(rowNo = 37, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "运输局车辆类型（编码见右面7）") private Integer transVehicleType; /** * 所有绑定的sim卡 */ @RecordTemplate(rowNo = 38, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "所有绑定的sim卡") private String terminalAllSim; /** * 所有者地址 */ @RecordTemplate(rowNo = 39, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "所有者地址") private String ownerAddress; /** * 车牌型号 */ @RecordTemplate(rowNo = 40, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车牌型号") private String licenseModel; /** * 行政区划 */ @RecordTemplate(rowNo = 41, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "行政区划") private String administrativeArea; /** * 行政地址 */ @RecordTemplate(rowNo = 42, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "行政地址") private String administrativeAddress; /** * 总客数 */ @RecordTemplate(rowNo = 43, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "总客数") private Integer totalnumberGuest; /** * 整备质量 */ @RecordTemplate(rowNo = 44, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "整备质量") private Double curbWeight; /** * 列车最大总质量 */ @RecordTemplate(rowNo = 45, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "列车最大总质量") private Double maximumTotalMassOfTrain; /** * 入网证号 */ @RecordTemplate(rowNo = 46, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "入网证号") private String netNumber; /** * 初次登记日期 */ @RecordTemplate(rowNo = 47, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "初次登记日期") private String initialRegistrationDate; /** * 年检日期 */ @RecordTemplate(rowNo = 48, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "年检日期") private String annualInspectionDate; /** * 强制报废日期 */ @RecordTemplate(rowNo = 49, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "强制报废日期") private String mandatoryScrapDate; /** * 所属企业简称 */ @RecordTemplate(rowNo = 50, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "所属企业简称") private String enterpriseShortName; /** * 车辆SN */ @RecordTemplate(rowNo = 51, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "车辆SN") private String vehicleSn; /** * 安全芯片型号(（车载终端含有安全芯片的必填）) */ @RecordTemplate(rowNo = 52, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "安全芯片型号（车载终端含有安全芯片的必填）") private String chipType; /** * 车载终端型号(（必填）) */ @RecordTemplate(rowNo = 53, columnNo = 2, comment = FillCommentEnum.FILL, name = "车载终端型号（必填）") @NotEmpty(message = "车载终端型号不能为空", groups = {ImportGroup.class}) private String tboxType; /** * 激活模式(（编码见说明9，必填）0-无需激活，1-需要激活) */ @RecordTemplate(rowNo = 54, columnNo = 2, comment = FillCommentEnum.FILL, name = "激活模式（编码见右面9，必填）") @NotEmpty(message = "激活模式不能为空", groups = {ImportGroup.class}) private Integer vehRegisterMode; /** * 排放水平(（编码见说明10，必填）(1-国六,2-国五,3-国四,4-国三,5-国二,6-排气后处理系统改装车辆)) */ @RecordTemplate(rowNo = 55, columnNo = 2, comment = FillCommentEnum.FILL, name = "排放水平（编码见右面10，必填）") @NotEmpty(message = "放水平不能为空", groups = {ImportGroup.class}) private Integer emissionlLevelType; /** * * 整车生产企业 */ @RecordTemplate(rowNo = 56, columnNo = 2, comment = FillCommentEnum.FILL, name = "整车生产企业") @NotEmpty(message = "整车生产企业不能为空", groups = {ImportGroup.class}) private String vehicleFirm; /** * 安全芯片编号(备案不需要) */ private String chipCode; /** * 备注 */ private String remark; /** * 车辆备案结果(0:草稿；1:待审核；2：未备案 3:审核通过4:审核未通过) * @return */ @TableField(exist = false) private Integer recordResult;
解析工具类（最重要）
思路: 遍历实体类上的有@ExcelTemplate注解的属性，有的话说明事由单元格和它对应的，把该属性的行号和列号传递给 解析单元格数据的方法，返回单元格的值，再通过java的 Filed的反射机制，给类赋值，就获取了excel中所有值了。在解析单元格的时候根据是否必填，也可以提前抛出异常。
解析单元格的相关方法:
/** * 获取sheet对象 */ public static Sheet getSheetByStream(InputStream inputStream, String sheetName) { Workbook workbook = null; try { workbook = WorkbookFactory.create(inputStream); } catch (Exception e) { throw new ServiceException("excel文件有误"); } Sheet sheet = null; if (StringUtils.isBlank(sheetName)) { //取第一个 sheet = workbook.getSheetAt(0); } else { sheet = workbook.getSheet(sheetName.trim()); } return sheet; } /** * 读取单个单元格的值 * * @param sheet * @param name * @param rowNo * @param columnNo */ public static String readCell(Sheet sheet, int rowNo, int columnNo, FillCommentEnum comment, String name) { //5、获取行 Row row = sheet.getRow(rowNo); //6、获取单元格 Cell cell = row.getCell(columnNo); //7、读取单元格内容 String stringCellValue = getCellValueByType(cell, name); if (comment.getCode() == 0 && StringUtils.isBlank(stringCellValue)) { throw new ServiceException(name + "不能为空"); } logger.info(stringCellValue); return stringCellValue; } public static String getCellValueByType(Cell cell,String name){ String cellValue = ""; if(cell.getCellTypeEnum() == CellType.NUMERIC){ if (HSSFDateUtil.isCellDateFormatted(cell)) { cellValue = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd"); } else { NumberFormat nf = NumberFormat.getInstance(); cellValue = String.valueOf(nf.format(cell.getNumericCellValue())).replace(",", ""); } logger.info(cellValue); }else if(cell.getCellTypeEnum() == CellType.STRING){ cellValue = String.valueOf(cell.getStringCellValue()); }else if(cell.getCellTypeEnum() == CellType.BOOLEAN){ cellValue = String.valueOf(cell.getBooleanCellValue()); }else if(cell.getCellTypeEnum() == CellType.ERROR){ cellValue = "错误类型"; throw new ServiceException("单元格"+name+": "+cellValue); } return cellValue; }
反射给对象赋值的相关方法:
/** * 解析excel备案数据到对象 * * @return * @throws ClassNotFoundException * @throws IllegalAccessException * @throws InstantiationException */ public static Object parseExcelToModel(String className, Sheet sheet) throws ClassNotFoundException, IllegalAccessException, InstantiationException { Class<?> clazz = Class.forName(className); Field[] declaredFields = clazz.getDeclaredFields(); Object o = clazz.newInstance(); //获取excel的流，前端传入 for (Field field: declaredFields) { field.setAccessible(true); if (field.isAnnotationPresent(RecordTemplate.class)) { RecordTemplate annotation = field.getAnnotation(RecordTemplate.class); Class<?> type = field.getType(); logger.info(type.getName()); //单元格的值 String value = ReadExcellCellUtils.readCell(sheet, annotation.rowNo(), annotation.columnNo(), annotation.comment(), annotation.name()); //通过反射把 单元格的值 给对象属性 setFieldValue(o, field, type, value); } } return o; } /** * 通过反射把 单元格的值 给对象属性 * @param o * @param field * @param type * @param value * @throws IllegalAccessException */ private static void setFieldValue(Object o, Field field, Class<?> type, String value) throws IllegalAccessException { Object targetValue = null; if (StringUtils.isEmpty(value)) { return; } if (type.equals(Integer.class)) { targetValue = Integer.parseInt(value); } else if (type.equals(Double.class)) { targetValue = Double.parseDouble(value); } else if (type.equals(Float.class)) { targetValue = Float.parseFloat(value); } else if (type.equals(Boolean.class)) { targetValue = Boolean.getBoolean(value); }else{ targetValue = value; } field.set(o, targetValue); } /** * 解析数据到model * * @param className 类名 * @param inputStream 输入流 * @param sheetName sheetname 可以为null，为null时取第一页 * @return 映射对象 */ public static Object parseExcelToModel(String className, InputStream inputStream, String sheetName) { Sheet sheetByStream = getSheetByStream(inputStream, sheetName); try { return parseExcelToModel(className, sheetByStream); } catch (Exception e) { e.printStackTrace(); throw new ServiceException("解析数据到model失败"); } }
如何使用:
提供这3个值就可以了。
举例说明:
测试:
使用postman请求： excel的数据都获取到了：
自此，按照单元格的例子就完成了，这种适合不规则的excel，而且行数确定，特别是针对模板excel的读都是很通用的。
总结:本文主要实现了easyExcel按照行数的例子，还有自己封装的针对通用不规则excel按照列（确切是按照单元格）读的例子。如有问题或者错误，欢迎留言讨论。
