EasyPoi + layerUI 实现基本的表格导入
1、在pom文件中引入EasyPoi的坐标依赖:
注意:如果各项目间存在父子项目依赖关系,该POI坐标依赖应配置在父项目的pom.xml文件中
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
2、创建项目数据表
CREATE TABLE `led_generation_records` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`create_user` bigint(20) DEFAULT NULL COMMENT '创建人',
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
`update_user` bigint(20) DEFAULT NULL COMMENT '修改人',
`del` varchar(255) DEFAULT NULL COMMENT '软删除',
`version` bigint(20) DEFAULT NULL COMMENT '版本',
`type` varchar(255) DEFAULT NULL COMMENT '类型',
`sort` bigint(20) DEFAULT NULL COMMENT '排序',
`description` text COMMENT '描述',
`generation` varchar(255) DEFAULT NULL COMMENT '发电量',
`statistics` varchar(255) DEFAULT NULL COMMENT '统计',
`year` int(6) DEFAULT NULL COMMENT '年',
`month` int(6) DEFAULT NULL COMMENT '月',
`day` int(6) DEFAULT NULL COMMENT '日',
`dept_name` varchar(255) DEFAULT NULL COMMENT '组织机构',
`dept_id` bigint(20) DEFAULT NULL COMMENT '组织机构id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2332 DEFAULT CHARSET=utf8 COMMENT='历年发电记录';
3、根据表创建对应的实体类,此处采用lombok注解@Data来替代类中各属性的get/set方法,使用@ExcelTarget(" ")来指定该表格的唯一标识,通过@Excel(name = “发电量”)指定需要导入、导出表格的列头名称。
注意:表格的导入导出涉及到数据流传输,因此一定要让该实体类实现序列化接口***Serializable***
@Data
@TableName("led_generation_records")
@ExcelTarget("history")
public class LedGenerationRecords implements Serializable {
private static final long serialVersionUID=1L;
@TableId(value = "id", type = IdType.AUTO)
private Long id;
@TableField(value = "create_time", fill = FieldFill.INSERT)
private Date createTime;
@TableField(value = "create_user", fill = FieldFill.INSERT)
private Long createUser;
@TableField(value = "update_time", fill = FieldFill.UPDATE)
private Date updateTime;
@TableField(value = "update_user", fill = FieldFill.UPDATE)
private Long updateUser;
@TableField("del")
private String del;
@TableField("version")
private Long version;
@TableField("type")
private String type;
@TableField("sort")
private Long sort;
@TableField("description")
private String description;
@Excel(name = "发电量")
@TableField("generation")
private String generation;
@TableField("statistics")
private String statistics;
@Excel(name = "年")
@TableField("year")
private Integer year;
@Excel(name = "月")
@TableField("month")
private Integer month;
@Excel(name = "日")
@TableField("day")
private Integer day;
@Excel(name = "组织机构")
@TableField("dept_name")
private String deptName;
@Excel(name = "组织id")
@TableField("dept_id")
private Long deptId;
4、可通过测试方法导出数据表格作为表格导入模板
@Test
public void ExportExcel() throws IOException {
List<LedGenerationRecords> ledGenerationRecordsList = new ArrayList<>();
for(int i=0;i<5;i++){
LedGenerationRecords ledGenerationRecords = new LedGenerationRecords();
ledGenerationRecords.setGeneration("100");
ledGenerationRecords.setYear(2020);
ledGenerationRecords.setMonth(11);
ledGenerationRecords.setDay(12);
ledGenerationRecords.setDeptId(25l);
ledGenerationRecords.setDeptName("测试电厂");
ledGenerationRecordsList.add(ledGenerationRecords);
}
FileOutputStream fileOutputStream = new FileOutputStream("D:\\tmp/数据表格.xls");
Workbook exportExcel = ExcelExportUtil.exportExcel(new ExportParams("历年数据导入", "数据表"), LedGenerationRecords.class, ledGenerationRecordsList);
exportExcel.write(fileOutputStream);
fileOutputStream.close();
exportExcel.close();
}
5、代码生成的模板表如下:
6、前台代码与JS
<button id="btnImp" class="layui-btn icon-btn"><i class="layui-icon"></i>导入</button>
var upload = layui.upload;
upload.render({
elem: '#btnImp'
,url: Feng.ctxPath +'/ledGenerationRecords/importExcel'
,accept: 'file'
,exts: 'xls|xlsx'
,done: function(res){
table.reload(LedGenerationRecords.tableId, {url: Feng.ctxPath + "/ledGenerationRecords/insertData"});
window.setTimeout(function () {
window.location.reload();
},800)
}
});
7、后台对应接口
@RequestMapping("/importExcel")
@ResponseBody
public ResponseData uploadExcel(@RequestPart("file") MultipartFile file, HttpServletRequest request) throws IOException {
JSONObject result = new JSONObject();
String fileName = UUID.randomUUID().toString().replaceAll("-", "");
String ext = FilenameUtils.getExtension(file.getOriginalFilename());
String fileNames = fileName + "." + ext;
request.getSession().setAttribute("importFile", fileNames);
String fileParentPath = ConstantsContext.getBpmnFileUploadPath();
String pathName = fileParentPath + fileNames;
File importFile = new File(pathName);
file.transferTo(importFile);
result.put("result",pathName);
return ResponseData.success(0, "上传成功", result);
}
@RequestMapping("/insertData")
@ResponseBody
public String getUploadData(HttpServletRequest request) {
String name = (String) request.getSession().getAttribute("importFile");
String fileSavePath = ConstantsContext.getBpmnFileUploadPath();
if (name != null) {
File file = new File(fileSavePath + name);
try {
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(1);
List result = ExcelImportUtil.importExcel(file, LedGenerationRecords.class, params);
result.forEach(System.out::println);
ledGenerationRecordsService.saveExcel(result);
} catch (Exception e) {
e.printStackTrace();
}
}
return "redirect:/ledGenerationRecords";
}
8、注意:因建表时设置主键策略为自动增长,所以在对应的xxxMapper.xml文件中的insert语句要设置对应的自动增长
<insert id="saveExcel" parameterType="cn.stylefeng.guns.modular.led.entity.LedGenerationRecords" useGeneratedKeys="true" keyProperty="id">
insert into led_generation_records (id, generation ,year ,month ,day ,dept_name,dept_id) values (#{id},#{generation},#{year},#{month},#{day},#{deptName},#{deptId})
</insert>
注:EasyPOi还有很多强大的表格格式设置功能并且很好上手,具体可以参考EasyPOI的官网。