1 导入依赖
<!--EasyExcel相关依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>org.apiguardian</groupId>
<artifactId>apiguardian-api</artifactId>
<version>1.1.2</version>
</dependency>
2 使用说明
EasyExcel 和 EasyPoi 的使用非常类似,都是通过注解来控制导入导出。接下来我们以用户信息导入导出为例,分别实现下简单的单表导出。
3 简单导出
- 首先创建一个会员对象EasyExcelUser,封装用户信息,这里使用了EasyExcel的注解;
package com.pa.pojo.easyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.pa.utils.easyExcelUser.GenderConverter;
import com.pa.utils.easyExcelUser.StatusConverter;
import lombok.Data;
import lombok.EqualsAndHashCode;
import java.util.Date;
/**
* @author:
* @Date: 2022-06-09 21:44
* @Description:
* @Modified By:
* @Version:
*/
@Data
@EqualsAndHashCode(callSuper = false)
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)//表头样式 居中
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)//内容样式 居中
public class EasyExcelUser {
@ExcelProperty("序号")
@ColumnWidth(10)
private Long id;
@ExcelProperty("用户名")
@ColumnWidth(20)
private String userName;
@ExcelProperty("职务")
@ColumnWidth(20)
private String position;
@ExcelProperty(value = "性别", converter = GenderConverter.class)
//@ExcelProperty(value = "性别")
@ColumnWidth(10)
private Integer gender;
@ExcelProperty("政治面貌")
@ColumnWidth(20)
private String politicsStatus;
@ExcelProperty("手机号")
@ColumnWidth(20)
private String phone;
@ExcelProperty("入职日期")
@ColumnWidth(20)
@DateTimeFormat("yyyy-MM-dd")
private Date workAge;
@ExcelProperty(value ="账号状态", converter = StatusConverter.class)
@ColumnWidth(20)
private int status;
@ExcelProperty("创建时间")
@ColumnWidth(20)
@DateTimeFormat("yyyy-MM-dd HH:MM:SS")
private Date createTime;
@ExcelProperty("部门名")
@ColumnWidth(20)
private String departmentName;
@ExcelProperty("角色名")
@ColumnWidth(20)
private String roleName;
}
上面代码使用到了EasyExcel的核心注解,我们分别来了解下:
@ExcelProperty:核心注解,value属性可用来设置表头名称,converter属性可以用来设置类型转换器;
@ColumnWidth:用于设置表格列的宽度;
@DateTimeFormat:用于设置日期转换格式。
在EasyExcel中,如果你想实现枚举类型到字符串的转换(比如gender属性中,0->男,1->女),需要自定义转换器,下面为自定义的GenderConverter代码实现;
package com.pa.utils.easyExcelUser;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.metadata.data.WriteCellData;
/**
* excel性别转换器
*
*/
public class GenderConverter implements Converter<Integer> {
@Override
public Class<?> supportJavaTypeKey() {
//对象属性类型
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
//CellData属性类型
return CellDataTypeEnum.STRING;
}
@Override
public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
//CellData转对象属性
String cellStr = context.getReadCellData().getStringValue();
if (StrUtil.isEmpty(cellStr)) return null;
if ("男".equals(cellStr)) {
return 0;
} else if ("女".equals(cellStr)) {
return 1;
} else {
return null;
}
}
@Override
public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
//对象属性转CellData
Integer cellValue = context.getValue();
if (cellValue == null) {
return new WriteCellData<>("");
}
if (cellValue == 0) {
return new WriteCellData<>("男");
} else if (cellValue == 1) {
return new WriteCellData<>("女");
} else {
return new WriteCellData<>("");
}
}
}
StatusConverter转换器实现
package com.pa.utils.easyExcelUser;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
/**
* @Description : 自定义转换器 状态
* @Author :
* @Date :
* <p>
* 在EasyExcel中,如果你想实现枚举类型到字符串的转换(Status属性中,1->正常,0->停用),需要自定义转换器
**/
public class StatusConverter implements Converter<Integer> {
@Override
public Class<?> supportJavaTypeKey() {
//对象属性类型 0 1
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
//CellData属性类型 "停用" "启用"
return CellDataTypeEnum.STRING;
}
@Override
public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
//CellData转对象属性
String cellStr = context.getReadCellData().getStringValue();
if (StrUtil.isEmpty(cellStr)) return null;
if ("停用".equals(cellStr)) {
return 0;
} else if ("启用".equals(cellStr)) {
return 1;
} else {
return null;
}
}
@Override
public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
//对象属性转CellData
Integer cellValue = context.getValue();
if (cellValue == null) {
return new WriteCellData<>("");
}
if (cellValue == 0) {
return new WriteCellData<>("停用");
} else if (cellValue == 1) {
return new WriteCellData<>("启用");
} else {
return new WriteCellData<>("");
}
}
}
接下来我们在Controller中添加一个接口,用于导出以及导入用户列表到Excel,还需给响应头设置下载excel的属性,具体代码如下;
package com.pa.controller.easyExcelUser;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.pa.pojo.Department;
import com.pa.pojo.Role;
import com.pa.pojo.User;
import com.pa.pojo.easyExcel.EasyExcelUser;
import com.pa.service.department.DepartmentService;
import com.pa.service.role.RoleService;
import com.pa.service.user.UserService;
import com.pa.utils.MD5Utils;
import com.pa.utils.R;
import com.pa.utils.easyExcelUser.CommonResult;
import lombok.SneakyThrows;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
/**
* @author: Xiao Guanglin
* @Date: 2022-06-09 21:57
* @Description:
* @Modified By:
* @Version:
*/
@Controller
//@Api(tags = "EasyExcelController", description = "EasyExcel导入导出测试")
@RequestMapping("/easyExcel")
public class EasyExcelController {
Logger logger = LoggerFactory.getLogger(getClass());
@Autowired
UserService userService;
@Autowired
RoleService roleService;
@Autowired
DepartmentService departmentService;
/**
* @描述: 导出用户表到本地(Excel)
* @author XiaoGuangLin
* @date 2022/6/15 19:47
* @param response
*/
@RequestMapping(value = "/exportUserList", method = RequestMethod.GET)
public void exportMemberList(HttpServletResponse response) {
List<EasyExcelUser> easyExcelUsers = userService.getEasyExcelUsers();
int id = 1;
for (EasyExcelUser easyExcelUser : easyExcelUsers) {
easyExcelUser.setId((long) id++);
}
for (EasyExcelUser easyExcelUser : easyExcelUsers) {
}
try {
setExcelRespProp(response, "用户信息表");
EasyExcel.write(response.getOutputStream())
.head(EasyExcelUser.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("用户列表")
.doWrite(easyExcelUsers);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* @描述: 导入用户表到系统(Excel)
* @author XiaoGuangLin
* @date 2022/6/15 19:47
* @param
*/
@SneakyThrows
@RequestMapping(value = "/importUserList", method = RequestMethod.POST)
@ResponseBody
public CommonResult importUserList(@RequestPart("txt_file") MultipartFile file){
List<EasyExcelUser> EasyExcelUserList = EasyExcel.read(file.getInputStream())
.head(EasyExcelUser.class)
.sheet()
.doReadSync();
List<Role> roleList = roleService.getRoles();
List<Department> departmentList = departmentService.getDepartments();
String password = "123456";
String passwordEncryption= MD5Utils.mdtEncode(password);
for (EasyExcelUser easyExcelUser : EasyExcelUserList) {
if(easyExcelUser.getUserName()==null)break;
if(userService.getUserByUserName(easyExcelUser.getUserName())==null){
User user = new User();
user.setUserName(easyExcelUser.getUserName());
user.setPassword(password);
user.setPosition(easyExcelUser.getPosition());
user.setGender(easyExcelUser.getGender());
user.setPhone(easyExcelUser.getPhone());
Integer roleId = null;
for (Role role : roleList) {
if(role.getRoleName().equals(easyExcelUser.getRoleName())){
roleId = role.getRoleId();
break;
}
}
System.out.println("roleId=="+roleId);
user.setRoleId(roleId);
Integer departId = null;
for (Department department : departmentList) {
}
for (Department department : departmentList) {
if(department.getDepartmentName().equals(easyExcelUser.getDepartmentName())){
departId = department.getDepartmentId();
break;
}
}
user.setDepartId(departId);
user.setWorkAge(easyExcelUser.getWorkAge());
user.setStatus(easyExcelUser.getStatus());
user.setCreateTime(new Date());
user.setPoliticsStatus(easyExcelUser.getPoliticsStatus());
userService.addUser(user);
}
}
System.out.println("EasyExcelUserList="+EasyExcelUserList);
return CommonResult.success(EasyExcelUserList);
//return "redirect:/user/getUsers";
}
/**
* 设置excel下载响应头属性
*/
private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
}
}
4 导入
首先,导入需要一个模态框上传excel表格,这里使用的是bootstrap的模态框组件:
<!--文件上传 模态框-->
<form>
<div class="modal fade" id="fileInput-modal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
<div class="modal-dialog modal-lg" role="document">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
<h4 class="modal-title" id="myModalLabel">请选择Excel文件</h4>
</div>
<div class="modal-body">
<a th:href="@{/ExcelTemplate/用户信息表导入模板.xlsx}" class="form-control" style="border:none;">下载导入模板</a>
<input type="file" name="txt_file" id="txt_file" multiple class="file-loading" />
</div>
</div>
</div>
</div>
</form>
如图:
当然,需要配合前端代码:
<!--导入按钮-->
<a class="btn btn-success m-r-5" type="button" data-toggle="modal" data-target="#fileInput-modal">
<i class="mdi mdi-block-helper"></i> 导入</a>
/* js代码 */
/**
* 用户列表 文件上传
*/
$(function () {
//0.初始化fileinput
var oFileInput = new FileInput();
oFileInput.Init("txt_file", "/easyExcel/importUserList");
});
//初始化fileinput
var FileInput = function () {
var oFile = new Object();
//初始化fileinput控件(第一次初始化)
oFile.Init = function(ctrlName, uploadUrl) {
var control = $('#' + ctrlName);
//初始化上传控件的样式
control.fileinput({
language: 'zh', //设置语言
uploadUrl: uploadUrl, //上传的地址
allowedFileExtensions: ['xlsx','xls'],//接收的文件后缀
showUpload: true, //是否显示上传按钮
showCaption: false,//是否显示标题
browseClass: "btn btn-primary", //按钮样式
//dropZoneEnabled: false,//是否显示拖拽区域
//minImageWidth: 50, //图片的最小宽度
//minImageHeight: 50,//图片的最小高度
//maxImageWidth: 1000,//图片的最大宽度
//maxImageHeight: 1000,//图片的最大高度
//maxFileSize: 0,//单位为kb,如果为0表示不限制文件大小
//minFileCount: 0,
maxFileCount: 10, //表示允许同时上传的最大文件个数
enctype: 'multipart/form-data',
validateInitialCount:true,
previewFileIcon: "<i class='glyphicon glyphicon-king'></i>",
msgFilesTooMany: "选择上传的文件数量({n}) 超过允许的最大数值{m}!",
});
//导入文件上传完成之后的事件
$("#txt_file").on("fileuploaded", function (event, data, previewId, index) {
$("#fileInput-modal").modal("hide");
var data = data.response.lstOrderImport;
if (data == undefined) {
toastr.error('文件格式类型不正确');
return;
}
//1.初始化表格
var oTable = new TableInit();
oTable.Init(data);
$("#div_startimport").show();
});
}
return oFile;
};