excel导入导出通用工具类

背景

本人在上家公司有段时间负责报表的事情，频繁的需要使用excel的导入导出，于是写了一套公用代码，

写作时间是2015年6月，现在有空分享出来，供大家参考

特性

导入模板具有以下特性：
1、列格式化和列值校验，是否允许空判断
2、可指定列提取
3、提供回调函数，进行额外字段填充和业务逻辑

4、报错机制，报错提示批量、准确具体行列

导出模板具有以下特性：

1、可生成序列号

2、可指定列长度

此套导入和导出开发模板简单易用，可读性强，维护方便，让程序员避开复杂的代码操作，专注业务。

基础类

AbstractCellValueVerify：抽象列值校验器

AbstractCellVerify：抽象列校验器，格式化和校验列值，可根据构造函数拓展，比较校验是否允许为null。AbstractCellValueVerify和AbstractCellVerify关系是AbstractCellVerify使用到了AbstractCellValueVerify。
AbstractVerifyBuidler：抽象校验构建器，用来构建实体对应所有列的校验器


下面都是具体格式的列校验器，对列格式化为对应格式，并对列值进行校验
BigDecimalVerify、DateTimeVerify、DoubleVerify、IntegerVerify、StringToIntegerVerify、StringVerify。
特殊说明一下的是StringToIntegerVerify：针对字符串转int类型的情况，比如借款类型


ParseSheetCallback：解析Sheet回调函数

导入示例：

/** * 导入用户校验类 * @author Administrator * */public class MemberVerifyBuilder extends AbstractVerifyBuidler {private static MemberVerifyBuilder builder = new MemberVerifyBuilder();public static MemberVerifyBuilder getInstance() {return builder;}/** * 定义列校验实体：提取的字段、提取列、校验规则 */private MemberVerifyBuilder() {cellEntitys.add(new CellVerifyEntity("name", "A", new StringVerify("姓名", true)));cellEntitys.add(new CellVerifyEntity("age", "B", new IntegerVerify("年龄", true)));cellEntitys.add(new CellVerifyEntity("country", "D", new StringToIntegerVerify("国家",new AbstractCellValueVerify() {@Overridepublic Object verify(Object fileValue) {// TODO 转换：从excel中得到string转成需要的integerreturn 1;}}, true)));cellEntitys.add(new CellVerifyEntity("date", "F", new DateTimeVerify("创建日期", "yyyy/MM/dd",true)));// 必须调用super.init();}}

public class MainClass {public static void main(String[] args) throws Exception {parseSheet();parseSheetWithCallback();}/** * 解析excel * @throws Exception */public static void parseSheet() throws Exception {Workbook wb = WorkbookFactory.create(new FileInputStream("src/test/java/example/imp/import.xlsx"));Sheet sheet = wb.getSheetAt(0);// parseSheetList<Member> list = ExcelImportUtils.parseSheet(Member.class, MemberVerifyBuilder.getInstance(), sheet, 1);System.out.println(JSON.toJSONString(list));}/** * 解析excel带回调函数：做一些额外字段填充 * @throws Exception */public static void parseSheetWithCallback() throws Exception {Workbook wb = WorkbookFactory.create(new FileInputStream("src/test/java/example/imp/import.xlsx"));Sheet sheet = wb.getSheetAt(0);final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// parseSheetList<Member> list = ExcelImportUtils.parseSheet(Member.class, MemberVerifyBuilder.getInstance(), sheet, 1, new ParseSheetCallback<Member>() {@Overridepublic void callback(Member t, int rowNum) throws Exception {t.setDateDesc(sdf.format(t.getDate()));}});System.out.println(JSON.toJSONString(list));}}

导入示例：

public class MainClass {/** * 定义：excel标题，提取数据的字段，占用列长度 */private static final Object[][] fields = new Object[][]{{"姓名","name",POIConstant.TIME},{"年龄","age",POIConstant.NUMBER},{"国家","country",POIConstant.NAME},{"创建日期","dateDesc",POIConstant.TIME},};private static final Object[][] fieldsWithNum = new Object[][]{{"序号","",POIConstant.NUMBER},{"姓名","name",POIConstant.TIME},{"年龄","age",POIConstant.NUMBER},{"国家","country",POIConstant.NAME},{"创建日期","dateDesc",POIConstant.TIME},};public static void main(String[] args) throws Exception {exportBean();exportBeanWithNum();exportMap();}/** * 1、导出对象 * @throws Exception */public static void exportBean() throws Exception{List<Member> list = new ArrayList<>();list.add(new Member("张三", 28, 1, "2016-10-19"));list.add(new Member("李四", 25, 2, "2016-10-19"));Workbook bean = ExcelExportUtils.createWorkbook(list, fields);bean.write(new FileOutputStream("src/test/java/example/exp/exportBean.xlsx"));}/** * 2、导出对象带序列号 * @throws Exception */public static void exportBeanWithNum() throws Exception{List<Member> list = new ArrayList<>();list.add(new Member("张三", 28, 1, "2016-10-19"));list.add(new Member("李四", 25, 2, "2016-10-19"));Workbook bean = ExcelExportUtils.createWorkbook(list, fieldsWithNum, true);bean.write(new FileOutputStream("src/test/java/example/exp/exportBeanWithNum.xlsx"));}/** * 导出map * @throws Exception */public static void exportMap() throws Exception{List<Map<String, Object>> list = new ArrayList<>();Map<String, Object> map = new HashMap<>();map.put("name", "张三");map.put("age", 28);map.put("country", 1);map.put("dateDesc", "2016-10-19");list.add(map);map = new HashMap<>();map.put("name", "张三");map.put("age", 28);map.put("country", 1);map.put("dateDesc", "2016-10-19");list.add(map);Workbook bean = ExcelExportUtils.createWorkbook(list, fields);bean.write(new FileOutputStream("src/test/java/example/exp/exportMap.xlsx"));}}

源码下载

https://github.com/kuyuyingzi/excel-io.git

后续

能力有限，后续有空也会不断优化，有什么建议还希望各位大神指点！！！