# AutoPoi **Repository Path**: lifuping_net/auto-poi ## Basic Information - **Project Name**: AutoPoi - **Description**: AutoPoi 学习 - **Primary Language**: Java - **License**: Apache-2.0 - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2021-04-22 - **Last Updated**: 2022-06-17 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # AutoPoi #### 介绍 AutoPoi 学习参考文档：https://www.kancloud.cn/zhangdaiscott/autopoi/1624233 #### AutoPOI的主要特点 ```js 1.设计精巧,使用简单 2.接口丰富,扩展简单 3.默认值多,write less do more 4.AbstractView 支持,web导出可以简单明了 ``` #### AutoPOI的几个入口工具类 ```js 1.ExcelExportUtil Excel导出(普通导出,模板导出) 2.ExcelImportUtil Excel导入 3.WordExportUtil Word导出(只支持docx ,doc版本poi存在图片的bug,暂不支持) ``` #### 关于Excel导出XLS和XLSX区别 ```js 1.导出时间XLS比XLSX快2-3倍 2.导出大小XLS是XLSX的2-3倍或者更多 3.导出需要综合网速和本地速度做考虑^~^ ``` #### maven ```xml org.jeecgframework autopoi-web 1.3 ``` | **1.3**.x | [1.3](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.3) | [Central](https://mvnrepository.com/repos/central) | [3](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.3/usages) | Mar, 2021 | | ------------------------------------------------------------ | ------------------------------------------------------------ | ------------------------------------------------------------ | ------------------------------------------------------------ | --------- | | **1.2**.x | [1.2.5](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.2.5) | [Central](https://mvnrepository.com/repos/central) | [2](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.2.5/usages) | Jan, 2021 | | [1.2.4](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.2.4) | [Central](https://mvnrepository.com/repos/central) | 0 | Jan, 2021 | | | [1.2.3](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.2.3) | [Central](https://mvnrepository.com/repos/central) | [1](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.2.3/usages) | Dec, 2020 | | | [1.2.2](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.2.2) | [Central](https://mvnrepository.com/repos/central) | [1](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.2.2/usages) | Nov, 2020 | | | [1.2.1](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.2.1) | [Central](https://mvnrepository.com/repos/central) | 0 | Aug, 2020 | | | [1.2](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.2) | [Central](https://mvnrepository.com/repos/central) | [1](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.2/usages) | Jul, 2020 | | | **1.1**.x | [1.1.2](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.1.2) | [Central](https://mvnrepository.com/repos/central) | 0 | Jul, 2020 | | **1.0**.x | [1.0.3](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.0.3) | [Central](https://mvnrepository.com/repos/central) | [1](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.0.3/usages) | May, 2019 | | [1.0.2](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.0.2) | [Central](https://mvnrepository.com/repos/central) | 0 | Apr, 2019 | | | [1.0.1](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.0.1) | [Central](https://mvnrepository.com/repos/central) | 0 | Apr, 2019 | | | [1.0.0](https://mvnrepository.com/artifact/org.jeecgframework/autopoi-web/1.0.0) | [Central](https://mvnrepository.com/repos/central) | | | | #### AutoPoi导出实例 1.注解,导入导出都是基于注解的,实体上做上注解,标示导出对象,同时可以做一些操作 ```java @ExcelTarget("courseEntity") public class CourseEntity implements java.io.Serializable { /** 主键 */ private String id; /** 课程名称 */ @Excel(name = "课程名称", orderNum = "1", needMerge = true) private String name; /** 老师主键 */ @ExcelEntity(id = "yuwen") @ExcelVerify() private TeacherEntity teacher; /** 老师主键 */ @ExcelEntity(id = "shuxue") private TeacherEntity shuxueteacher; @ExcelCollection(name = "选课学生", orderNum = "4") private List students; ``` 2.基础导出传入导出参数,导出对象,以及对象列表即可完成导出 ```java HSSFWorkbook workbook = ExcelExportUtil.exportExcel(new ExportParams( "2412312", "测试", "测试"), CourseEntity.class, list); ``` ```java HSSFWorkbook workbook = ExcelExportUtil.exportExcel(new ExportParams( "2412312", "测试", "测试"), CourseEntity.class, list,exportFields); ``` 3.基础导出,带有索引在到处参数设置一个值,就可以在导出列增加索引 ```java ExportParams params = new ExportParams("2412312", "测试", "测试"); params.setAddIndex(true);//设置索引 HSSFWorkbook workbook = ExcelExportUtil.exportExcel(params, TeacherEntity.class, telist); ``` 4.导出Map 创建类似注解的集合,即可完成Map的导出**`,略有麻烦`** ```java List entity = new ArrayList(); entity.add(new ExcelExportEntity("姓名", "name")); entity.add(new ExcelExportEntity("性别", "sex")); List> list = new ArrayList>(); Map map; for (int i = 0; i < 10; i++) { map = new HashMap(); map.put("name", "1" + i); map.put("sex", "2" + i); list.add(map); } HSSFWorkbook workbook = ExcelExportUtil.exportExcel(new ExportParams( "测试", "测试"), entity, list); ``` 5.模板导出根据模板配置,完成对应导出 ```java TemplateExportParams params = new TemplateExportParams(); params.setHeadingRows(2); params.setHeadingStartRow(2); Map map = new HashMap(); map.put("year", "2013"); map.put("sunCourses", list.size()); Map obj = new HashMap(); map.put("obj", obj); obj.put("name", list.size()); params.setTemplateUrl("org/jeecgframework/poi/excel/doc/exportTemp.xls"); Workbook book = ExcelExportUtil.exportExcel(params, CourseEntity.class, list, map); ``` 6.导入设置导入参数,传入文件或者流,即可获得相应的list ```java ImportParams params = new ImportParams(); params.setTitleRows(2); params.setHeadRows(2); //params.setSheetNum(9); params.setNeedSave(true); long start = new Date().getTime(); List list = ExcelImportUtil.importExcel(new File( "d:/tt.xls"), CourseEntity.class, params); ``` 7.Excel导入校验,过滤不符合规则的数据,追加错误信息到Excel,提供常用的校验规则,已经通用的校验接口 ```java /** * Email校验 */ @Excel(name = "Email", width = 25) @ExcelVerify(isEmail = true, notNull = true) private String email; /** * 手机号校验 */ @Excel(name = "Mobile", width = 20) @ExcelVerify(isMobile = true, notNull = true) private String mobile; ExcelImportResult result = ExcelImportUtil.importExcelVerify(new File( "d:/tt.xls"), ExcelVerifyEntity.class, params); for (int i = 0; i < result.getList().size(); i++) { System.out.println(ReflectionToStringBuilder.toString(result.getList().get(i))); } ``` 8.Replace用法若数据库中存储的是0/1 ，则导出/导入的excel单元格中显示的是女/男 ```java @Excel(name="测试替换",width=15,replace={"男_1","女_0"}) private java.lang.String fdReplace; ``` #### Excel 注解说明 @Excel | 属性 | 类型 | 默认值 | 功能 | | :------------- | :------- | :--------------- | :----------------------------------------------------------- | | name | String | null | 列名,支持name_id | | needMerge | boolean | fasle | 是否需要纵向合并单元格(用于含有list中,单个的单元格,合并list创建的多个row) | | orderNum | String | "0" | 列的排序,支持name_id | | replace | String[] | {} | 值得替换导出是{a_id,b_id} 导入反过来 | | savePath | String | "upload" | 导入文件保存路径,如果是图片可以填写,默认是upload/className/ IconEntity这个类对应的就是upload/Icon/ | | type | int | 1 | 导出类型 1 是文本 2 是图片,3 是函数,10 是数字默认是文本 | | width | double | 10 | 列宽 | | height | double | 10 | 列高,后期打算统一使用@ExcelTarget的height,这个会被废弃,注意 | | isStatistics | boolean | fasle | 自动统计数据,在追加一行统计,把所有数据都和输出这个处理会吞没异常,请注意这一点 | | isHyperlink | boolean | FALSE | 超链接,如果是需要实现接口返回对象 | | isImportField | boolean | TRUE | (作废参数)校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id | | exportFormat | String | "" | 导出的时间格式,以这个是否为空来判断是否需要格式化日期 | | importFormat | String | "" | 导入的时间格式,以这个是否为空来判断是否需要格式化日期 | | format | String | "" | 时间格式,相当于同时设置了exportFormat 和 importFormat | | databaseFormat | String | "yyyyMMddHHmmss" | 导出时间设置,如果字段是Date类型则不需要设置数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出 | | numFormat | String | "" | 数字格式化,参数是Pattern,使用的对象是DecimalFormat ,请在数值类型的字段上使用 [用法参考](http://doc.jeecg.com/[https://www.jianshu.com/p/b3699d73142e](https://www.jianshu.com/p/b3699d73142e)) | | imageType | int | 1 | 导出类型 1 从file读取 2 是从数据库中读取默认是文件同样导入也是一样的 | | suffix | String | "" | 文字后缀,如% 90 变成90% | | isWrap | boolean | TRUE | 是否换行即支持\n | | mergeRely | int[] | {} | 合并单元格依赖关系,比如第二列合并是基于第一列则{0}就可以了 | | mergeVertical | boolean | fasle | 纵向合并内容相同的单元格 | | fixedIndex | int | -1 | 对应excel的列,忽略名字 | | isColumnHidden | boolean | FALSE | 导出隐藏列 | @ExcelCollection | 属性 | 类型 | 默认值 | 功能 | | :------- | :------- | :-------------- | :----------------------- | | id | String | null | 定义ID | | name | String | null | 定义集合列名,支持nanm_id | | orderNum | int | 0 | 排序,支持name_id | | type | Class | ArrayList.class | 导入时创建对象使用 | 单表导出实体注解源码 ```java public class SysUser implements Serializable { /**id*/ private String id; /**登录账号 */ @Excel(name = "登录账号", width = 15) private String username; /**真实姓名*/ @Excel(name = "真实姓名", width = 15) private String realname; /**头像*/ @Excel(name = "头像", width = 15) private String avatar; /**生日*/ @Excel(name = "生日", width = 15, format = "yyyy-MM-dd") private Date birthday; /**性别（1：男 2：女）*/ @Excel(name = "性别", width = 15,dicCode="sex") private Integer sex; /**电子邮件*/ @Excel(name = "电子邮件", width = 15) private String email; /**电话*/ @Excel(name = "电话", width = 15) private String phone; /**状态(1：正常 2：冻结）*/ @Excel(name = "状态", width = 15,replace={"正常_1","冻结_0"}) private Integer status; ``` 一对多导出实体注解源码 ```java @Data public class JeecgOrderMainPage { /**主键*/ private java.lang.String id; /**订单号*/ @Excel(name="订单号",width=15) private java.lang.String orderCode; /**订单类型*/ private java.lang.String ctype; /**订单日期*/ @Excel(name="订单日期",width=15,format = "yyyy-MM-dd") private java.util.Date orderDate; /**订单金额*/ @Excel(name="订单金额",width=15) private java.lang.Double orderMoney; /**订单备注*/ private java.lang.String content; /**创建人*/ private java.lang.String createBy; /**创建时间*/ private java.util.Date createTime; /**修改人*/ private java.lang.String updateBy; /**修改时间*/ private java.util.Date updateTime; @ExcelCollection(name="客户") private List jeecgOrderCustomerList; @ExcelCollection(name="机票") private List jeecgOrderTicketList; } ```