公司的日常事务中经常需要使用excel进行数据汇总,导入导出进行归类统计分析。
因为没有广泛流行的单元行到类转换/属性绑定工具,在功能开发之初或者很长一段时间内,
业务系统中我们处理普通excel数据的方法如下:
例如我们公司工程项目中需要到现场部署设备,
1、从问题域出发我们大概可以建立具有以下属性的类,用以描述每一行记录的所具有的属性以及性状特征
public class Device { private String device_id; //设备编号 private String name; //设备名称 private String model; //设备型号 private String location; //存放位置 private String serial; //序列号 private String ipv4; //IP地址 private String project_number; //项目编号 private String project_name; //项目名称 private String constructor_name;//建设人 private String customer_name; //客户单位名称 private String project_manager_name; //负责人 private Date lastExamine; //最后一次检查/巡查日期 }
通过对设备的基本特性、特征、用途分析,我们粗略地将他的属性分为3大类
抽取了一些代表性的属性,如下:
1、自有属性
通常是出厂设置或者与生俱来的:名称、型号、序列号等、
2、业务属性
项目编号、项目名称、建设人、客户单位名称
3、维保属性
巡检日期 等
当然比较合理的设计方法应该建立多个对象,用于描述业务对象(设备)在系统
功能中参与的一系列行为、事件等类。本文为了配合框架的使用,将属性糅合到一个类
当中,数据类型也只用了简单的字符串类型来阐述问题。
经过以上构造,一个崭新鲜活的类,如新生儿般,轻装上阵,便参与到OOP世界的业务系统中。
编写代码操作
... public static Object getCellValue(Cell cell) { return getCellValue(cell,NULL); } public static String getCellValue(Cell cell, Object defaultValue) { if(cell==null) return defaultValue; cell.setCellType(CellType.STRING); String value = cell.getStringCellValue(); if(StringUtils.isNotEmpty(value)) { return value.trim().replaceAll("\\s+", ""); } return defaultValue; } ... List<Device> list = LinkedList<Device>(); for(int i = 1; i< iRowNums; i++) { Device dev = new Device(); //设备编号 Cell celldevice_id = row.getCell(0); String device_id = getCellValue(celldevice_id); dev.setDevice_di(device_id); //设备名称 ... //项目编号 Cell cellproject_number = row.getCell(6); String project_number = getCellValue(cellproject_number); dev.setProject_number(project_number); list.add(dev); }
当然偷懒的方法也是有的,通过工具类来帮助我少写代码commons-beanutil.jar
中的属性工具类
public static String getProperty(Object bean,String propertyName) { try { String value = (String)PropertyUtils.getSimpleProperty(bean,propertyName); if(StringUtils.isEmpty(value)) return ""; return value; }catch (Exception ex) { logger.error("getProperty failed:{}",ex); return ""; } } public Cell enumerateCell(Row row, Cell copyOfdev, int offet, int length,String []fields) { for(int i = offet; i <length;i++){ Cell cell = row.getCell(i); String value = getCellValue(cell); String propertyName = fields[i-(offet-1)]; setProperty(copyOfMessage,propertyName,value); } return copyOfMessage; } String fields[] = { "device_id", "name","model", "location","serial", "ipv4", "project_number", "project_name","constructor_name","customer_name","project_manager_name","lastExamine" };
我们给他列名对应的索引值让他去跑,也能拿到他的属性,这样的缺陷是要维护属性在String数组中的索引。
电子计算机是一个数字电路系统,按照数值进行计算是他的强项,对于开发人员来说,每次写类似的代码,都要锱铢必较的
计算每一个属性对应的位置,生怕写错了索引带来错误。
然而需求总是随着时间、政策法令、政治、宗教等客观因素,以及人的主管意愿在变化着。
----以下需求属于为了讲解需求变化场景,可能与事实需求有出入
需求情形1:公司要求,在模板上添加申请人、审核人
需求情形2:客户(公安等)要求每个设备的IP要记录备案
需求情形3:xxx设备需要符合国标(GBxxxx),需要添加属性 AAA
很显然,我的刚刚描述的类,他的业务属性和维保属性肯定会有需要变化的。
公司实际业务场景也可能遇到类似的需求变化,万一哪一天添加到字段对业务需求比较重要呢,需要提到前面几列,
或者列的顺序没有维护好,错乱了怎么办,偶然间闪过一个概念,能不能把Excel单元行绑定到JavaBean,偶然间就构思简单的
搜索关键字:how to binding excel row to javabean,Google一番找到了本文将要使用的框架Poiji
https://github.com/ozlerhakan/poiji
A tiny library converting excel rows to a list of Java objects based on Apache POI
一个基于Apache POI轻量级工具库,将Excel行转换成Java对象列表
按照官方文档,我们把代码重构了一下
public class Device { @ExcelCellName("设备编号") private String device_id; //设备编号 @ExcelCellName("设备名称") private String name; //设备名称 @ExcelCellName("设备型号") private String model; //设备型号 @ExcelCellName("存放位置") private String location; //存放位置 @ExcelCellName("序列号") private String serial; //序列号 @ExcelCellName("IP地址") private String ipv4; //IP地址 @ExcelCellName("项目编号") private String project_number; //项目编号 @ExcelCellName("项目名称") private String project_name; //项目名称 @ExcelCellName("建设人") private String constructor_name;//建设人 @ExcelCellName("客户单位名称") private String customer_name; //客户单位名称 @ExcelCellName("负责人") private String project_manager_name; //负责人 @ExcelCellName("最后一次检查/巡查日期") private Date lastExamine; //最后一次检查/巡查日期 }
在每个属性上使用注释,添加注解,把列名加上去,例如 设备名称列,写成
@ExcelCellName("设备名称") private String name;
然后一行代码,我将获得批量的java对象
List<Device> devs = Poiji.fromExcel(new File("g:\\device-import-template.xlsx"), Device.class); System.out.println(devs);
笔者在实际开发测试过程中遇到一种情况,excel表头有空格的情况下无法绑定数值,日常工作过程中难免有人为
操作误差/失误,误输入空格,这种情况将有可能导致导入的业务数据就是错误的,数据在特定业务场景下运转起来,
因蝴蝶效应,有可能造成更深远的影响。
笔者已经修改源代码,默认去除行首行尾的空格。trimTagName方法
截至写本文章的时候,官方更新到1.18.1版本,由于1.18.1版本需要POI 4.0版本,现实中生产环境中使用的是3.1.6
笔者修改了一下源代码,目前兼容3.1.6,可以放到生产环境使用。
package com.poiji.deserialize; import com.poiji.bind.Poiji; import com.poiji.deserialize.model.byid.Employee; import com.poiji.deserialize.model.byname.EmployeeByName; import com.poiji.option.PoijiOptions; import com.poiji.option.PoijiOptions.PoijiOptionsBuilder; import org.junit.Test; import org.junit.runner.RunWith; import org.junit.runners.Parameterized; import java.io.File; import java.time.format.DateTimeFormatter; import java.util.Arrays; import java.util.List; import static com.poiji.util.Data.unmarshallingDeserialize; import static org.hamcrest.CoreMatchers.instanceOf; import static org.hamcrest.CoreMatchers.is; import static org.hamcrest.CoreMatchers.not; import static org.hamcrest.CoreMatchers.notNullValue; import static org.junit.Assert.assertThat; import static org.junit.Assert.fail; /** * Created by passedbylove@gmail.com on 2018-11-15. */ @RunWith(Parameterized.class) public class DeserializersByNameTagWithWhiteSpaceTest { private String path; private List<Employee> expectedEmployess; private Class<?> expectedException; public DeserializersByNameTagWithWhiteSpaceTest(String path, List<Employee> expectedEmployess, Class<?> expectedException) { this.path = path; this.expectedEmployess = expectedEmployess; this.expectedException = expectedException; } @Parameterized.Parameters(name = "{index}: ({0})={1}") public static Iterable<Object[]> queries() { return Arrays.asList(new Object[][]{ {"src/test/resources/employees-tagwithwhitespace.xlsx", unmarshallingDeserialize(), null}, }); } @Test public void shouldMapExcelToJava() { PoijiOptions options = PoijiOptionsBuilder.settings().datePattern("dd/MM/yyyy").dateTimeFormatter(DateTimeFormatter.ofPattern("yyyy-MM-dd")).trimCellValue(true).trimTagName(true).build(); try { List<EmployeeByName> actualEmployees = Poiji.fromExcel(new File(path), EmployeeByName.class,options); assertThat(actualEmployees, notNullValue()); assertThat(actualEmployees.size(), not(0)); assertThat(actualEmployees.size(), is(expectedEmployess.size())); EmployeeByName actualEmployee1 = actualEmployees.get(0); EmployeeByName actualEmployee2 = actualEmployees.get(1); EmployeeByName actualEmployee3 = actualEmployees.get(2); Employee expectedEmployee1 = expectedEmployess.get(0); Employee expectedEmployee2 = expectedEmployess.get(1); Employee expectedEmployee3 = expectedEmployess.get(2); assertThat(actualEmployee1.toString(), is(expectedEmployee1.toString())); assertThat(actualEmployee2.toString(), is(expectedEmployee2.toString())); assertThat(actualEmployee3.toString(), is(expectedEmployee3.toString())); } catch (Exception e) { if (expectedException == null) { fail(e.getMessage()); } else { assertThat(e, instanceOf(expectedException)); } } } }
以下奉送上修改后的poiji 1.18.0源代码
平时空闲时间不多,如有疑问欢迎留言交流。