目录
<!-- commons-io 文件流依赖-->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.7</version>
</dependency>
<!-- commons-fileupload -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.4</version>
</dependency>
<!-- easyexcel依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
上面是导出模板，如果导入得话工资下面得要换成{.salary}，否则报错，也可以设置实体类为字符串然后截取工资部分。
package com.ahead.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.Date;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.*;
/**
* <p>
*
* 创建employee类,用于构建向Excel表格中写数据的类型;
* @ExcelProperty:这个注解是EasyExcel提供,用于生成Excel表格头
* @author 荣
* @since 2022-12-18
*/
@ContentRowHeight(30) //设置内容高度
@HeadRowHeight(50) //设置标题高度
@ColumnWidth(25) //设置列宽
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("employee")
@ApiModel(value = "Employee对象", description = "")
public class Employee implements Serializable {
private static final long serialVersionUID = 1L;
// 字符串的内容的背景设置成天蓝(可以改后面数字)
@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
@ExcelProperty("用户编号")
@ApiModelProperty("用户编号")
@TableId(value = "userId", type = IdType.AUTO)
private Integer userId;
@ExcelProperty("姓名")
@ApiModelProperty("姓名")
@TableField("userName")
private String userName;
@ExcelProperty("性别")
@ApiModelProperty("性别")
@TableField("gender")
private String gender;
// 设置浮点数导出格式
@NumberFormat("#.##")
@ExcelProperty("工资")
@ApiModelProperty("工资")
@TableField("salary")
private Double salary;
// 设置日期导出格式
@DateTimeFormat("yyyy年MM月dd日 HH时mm分ss秒")
@ExcelProperty("入职日期")
@ApiModelProperty("入职日期")
@TableField("hireDate")
private Date hireDate;
}
1.文件上传(此处用得是layui得上传页面)
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
<meta charset="UTF-8">
<title>zyupload上传实例</title>
<link rel="stylesheet" th:href="@{/layuimini-v2/lib/layui-v2.6.3/css/layui.css}" media="all">
<link rel="stylesheet" th:href="@{/layuimini-v2/lib/jq-module/zyupload/zyupload-1.0.0.min.css}" media="all">
<link rel="stylesheet" th:href="@{/layuimini-v2/css/public.css}" media="all">
</head>
<body>
<!--<div class="layuimini-container">-->
<div class="layuimini-main">
<div class="layui-upload-drag" id="test10">
<i class="layui-icon"></i>
<p>点击上传，或将文件拖拽到此处</p>
<div class="layui-hide" id="uploadDemoView">
<hr>
<img src="" alt="上传成功后渲染" style="max-width: 196px">
</div>
</div>
</div>
<div>
<form th:action="@{/ahead/employee/fileDownload}" method="get">
<input type="submit" value="文件下载">
</form>
</div>
<script th:src="@{/layuimini-v2/lib/jquery-3.4.1/jquery-3.4.1.min.js}" charset="utf-8"></script>
<script th:src="@{/layuimini-v2/lib/layui-v2.6.3/layui.js}" charset="utf-8"></script>
<script th:src="@{/layuimini-v2/lib/jq-module/zyupload/zyupload-1.0.0.min.js}" charset="utf-8"></script>
<script type="text/javascript">
layui.use(['upload', 'element', 'layer'], function(){
var $ = layui.jquery
,upload = layui.upload
,element = layui.element
,layer = layui.layer;
//拖拽上传
upload.render({
elem: '#test10'
,url: '../ahead/employee/fileUpload' //此处用的是第三方的 http 请求演示，实际使用时改成您自己的上传接口即可。
,accept: 'file' //普通文件
,done: function(res){
layer.msg('上传成功');
console.log(res);
}
});
</script>
</body>
</html> @RequestMapping("/fileUpload")
@ResponseBody
public DataFormat fileUpload(MultipartFile file) throws FileUploadException, IOException {
InputStream inputStream = file.getInputStream();
ArrayList<Employee> list = new ArrayList<>();
DataFormat dataFormat = new DataFormat();
EasyExcel.read(inputStream, Employee.class, new ReadListener<Employee>() {
@Override
public void invoke(Employee employee, AnalysisContext analysisContext) {
Employee employee1 = new Employee();
//可以根据字段唯一性先查询下数据库字段是否重复
employee1.setUserName(employee.getUserName());
employee1.setGender(employee.getGender());
employee1.setSalary(employee.getSalary());
employee1.setHireDate(employee.getHireDate());
list.add(employee1);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
boolean b = employeeService.saveBatch(list);
if (b == true){
dataFormat.setMsg("上传成功！");
}else {
dataFormat.setMsg("上传失败！");
}
}
}).sheet().headRowNumber(3).doRead();
return dataFormat;
} @RequestMapping("/fileDownload")
@ResponseBody
public void fileDownload(HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
try {
// 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
//获取模板文件的文件流
InputStream inputStream = getClass()
.getClassLoader().getResourceAsStream("Template/userTemplate2.xlsx");
//查询数据
List<Employee> list = employeeService.list(null);
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), Employee.class)
.withTemplate(inputStream)
.autoCloseStream(Boolean.FALSE)
.sheet(0)
.doFill(list);
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = MapUtils.newHashMap();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
