1. 上传文件
1.1. 后端
1.1.1. 添加依赖
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.2.0</version> </dependency>
1.1.2. 实体类
package com.wedu.modules.contracts.entry;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.wedu.modules.contracts.utils.DeptUtil;
import com.wedu.modules.contracts.utils.StateUtil;
import com.wedu.modules.contracts.utils.TypeUtil;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.format.annotation.DateTimeFormat;
import java.util.Date;
/**
* @author master
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@ColumnWidth(20)
@TableName("f_agreement")
public class ContractsEntity {
/**
* 主键id
*/
@ExcelIgnore
@ExcelProperty("编号")
private Integer id;
/**
* 合同名称
*/
@ExcelProperty("合同名称")
private String title;
/**
* 合同类型(试用期,正式员工,外包人员)
*/
@ExcelProperty(value = "合同类型",converter = TypeUtil.class)
private Integer type;
/**
* 合同编号
*/
@ExcelProperty("合同编号")
private Integer num;
/**
* 薪资
*/
@ExcelProperty("薪资")
private Double salary;
/**
* 岗位
*/
@ExcelProperty("岗位")
private String post;
/**
* 合同状态(0生效1作废)
*/
@ExcelProperty(value = "合同状态",converter = StateUtil.class)
private Integer state;
/**
* 单位名称
*/
@ExcelProperty("单位名称")
private String companyName;
/**
* 员工id
*/
@ExcelProperty("员工ID")
private Integer userId;
/**
* 员工姓名
*/
@ExcelProperty("员工姓名")
private String userName;
/**
* 合同生效时间
*/
@ExcelProperty("合同生效时间")
@JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date effectTime;
/**
* 合同失效时间
*/
@ExcelProperty("合同失效时间")
@JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date lostEffectTime;
/**
* 合同结束时间
*/
@ExcelProperty("合同结束时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date endTime;
/**
* 合同人所属部门id
*/
@ExcelProperty(value = "合同人所属部门id",converter = DeptUtil.class)
private Integer deptId;
/**
* 家庭住址
*/
@ExcelProperty("家庭住址")
private String address;
/**
* 电话号码
*/
@ExcelProperty("联系电话")
private String mobile;
/**
* 身份证号码
*/
@ExcelProperty("身份证号码")
private String idNum;
/**
* 头像
*/
@ExcelIgnore
private String avatar;
/**
* 逻辑删除
*/
@ExcelIgnore
@TableLogic(value="0",delval="1")
@TableField(value = "deleted")
private Integer del;
}
这里的实体类中因为实体类中的属性一些是int类型的转出的时候要转为对应的文字比如合同的状态(0为未确认,1为生效,2为作废)那么可以写一个工具类来进行转换
StateUtils
package com.wedu.modules.contracts.utils;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
/**
* @author master
*/
public class StateUtil implements Converter<Integer> {
@Override
public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
String value = cellData.getStringValue();
switch (value){
case "未确认":
return 0;
case "生效":
return 1;
case "作废":
return 2;
default:
return -1;
}
}
@Override
public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
switch (value){
case 0:
return new WriteCellData<>("未确认");
case 1:
return new WriteCellData<>("生效中");
case 2:
return new WriteCellData<>("已作废");
default:
return new WriteCellData<>("null");
}
}
}
然后在@ExcelProperty中通过converter=工具类名.class来使用
如果不想导出某些属性的数据则可以在属性上加一个@ExcelIgnore注解
1.1.3. Controller
/**
* 文件上传
* <p>
* 1. 创建excel对应的实体对象 参照{@link ContractsEntity}
* <p>
* 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link ContractsListener}
* <p>
* 3. 直接读即可
*/
@PostMapping("/upload")
@ResponseBody
public R upload(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), ContractsEntity.class, new ContractsListener(contractsService)).sheet().doRead();
return R.ok();
}
1.1.4. 回调监听器
package com.wedu.modules.contracts.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import com.wedu.modules.contracts.entry.ContractsEntity;
import com.wedu.modules.contracts.service.ContractsService;
import com.wedu.modules.contracts.service.impl.ContractsServiceImpl;
import lombok.extern.slf4j.Slf4j;
import java.util.List;
/**
* @author master
*/
@Slf4j
public class ContractsListener implements ReadListener<ContractsEntity> {
/**
* 每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
*/
private List<ContractsEntity> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private ContractsService contractsService;
public ContractsListener() {
// 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
contractsService = new ContractsServiceImpl();
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param contractsService
*/
public ContractsListener(ContractsService contractsService) {
this.contractsService = contractsService;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(ContractsEntity data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
cachedDataList.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
contractsService.saveBatch(cachedDataList);
log.info("存储数据库成功!");
}
}
1.2. 前端
1.2.1 手动上传
<el-upload
style="display: inline-block"
ref="upload"
action
:auto-upload="false"
:before-upload="beforeUpload"
:on-remove="handleRemove"
:on-change="fileChange"
:on-exceed="handleExceed"
:disabled="importBtnDisabled"
:limit="1"
:http-request="uploadFile"
:file-list="fileList"
accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
>
<el-button
style="margin-left: 10px"
slot="trigger"
size="small"
@click="selectFile"
>选择文件</el-button
>
<el-button
:loading="fullscreenLoading"
size="small"
style="margin-left: 10px"
type="primary"
plain
class="el-icon-upload2 el-icon--right"
element-loading-text="拼命上传中..."
v-loading.fullscreen.lock="fullscreenLoading"
@click="submitUpload"
>{{ fileUploadBtnText }}</el-button
>
<span slot="tip" class="el-upload__tip">只能上传xls/xlsx文件</span>
</el-upload>
js
data: {
fileUploadBtnText: '点击上传', //上传文件提示文字
importBtnDisabled: false, // 按钮是否禁用,
fullscreenLoading: false, // 上传文件加载
fileList:[] // 上传文件列表
},
methos:{
//导入
beforeUpload(file) {
console.log("上传前");
const isText = file.type === "application/vnd.ms-excel";
const isTextComputer =
file.type ===
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
if (!isText && !isTextComputer) {
this.$message.warning("请上传excel格式文件!");
this.fileUploadBtnText = "导入失败";
this.fullscreenLoading = false;
}
return isText || isTextComputer;
},
// 覆盖默认的上传行为,自定义上传的实现
uploadFile(file) {
if (this.fileList.length === 0) {
this.$message.warning("请选择文件");
} else {
const data = new FormData();
const fileUps = file.file;
this.fullscreenLoading = true;
data.append("file", fileUps);
this.$http({
url: this.$http.adornUrl("/contracts/upload"),
method: "POST",
headers: {
"Content-Type": "multipart/form-data",
},
data: data,
}).then(
(res) => {
console.log(res);
if (res.data.code === 0) {
this.fileUploadBtnText = "导入成功";
this.fullscreenLoading = false;
this.$message({
type: "success",
message: "导入成功",
});
}
},
(err) => {
this.fileUploadBtnText = "导入失败";
this.fullscreenLoading = false;
this.$message.error("导入失败");
}
);
this.$refs.upload.clearFiles(); //移除文件,防止重复上传
}
this.getDataList();
},
// 上传excel
submitUpload() {
this.$refs.upload.submit();
},
//限制只能选中一个文件
fileChange(file, fileList) {
this.fileList = fileList.slice(-1);
},
// 文件超出个数限.getDataList
handleExceed(files, fileList) {
this.$message.warning("只能选择 1个文件");
},
//移除文件
handleRemove() {
this.fileList = [];
},
selectFile() {
this.fileUploadBtnText = "点击上传";
},
}
2. 下载文件
2.1. 后端
2.1.1. 文件下载
2.1.1.1. 实体类
同上
2.1.2.2. Controller
@PostMapping("/download")
public void download(@RequestBody Long[] ids, HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
List<ContractsEntity> contractsEntities = contractsDao.selectBatchIds(Arrays.asList(ids));
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), ContractsEntity.class).sheet("模板").doWrite(contractsEntities);
}
2.2. 前端
<el-button
type="danger"
@click="downloadExcelSelect()"
:disabled="dataListSelections.length <= 0"
>导出选中</el-button
>
js
methods:{
//导出选中
downloadExcelSelect() {
var ids = this.dataListSelections.map((item) => item.id);
this.$confirm(`确定对[id=${ids.join(",")}]进行导出操作?`, "提示", {
confirmButtonText: "确定",
cancelButtonText: "取消",
type: "warning",
}).then(() => {
// 发送 HTTP 请求以下载 Excel 文件
this.$http({
url: this.$http.adornUrl("/contracts/download"),
method: "post",
data: this.$http.adornData(ids, false),
responseType: "blob", // 设置响应类型为二进制流
})
.then((response) => {
// 创建一个 URL 对象,指向返回的二进制数据
const url = window.URL.createObjectURL(new Blob([response.data])); // 创建一个 <a> 元素,设置其属性,模拟点击下载
const link = document.createElement("a");
link.href = url;
link.setAttribute("download", "data.xlsx"); // 设置下载文件的默认名称
document.body.appendChild(link);
link.click(); // 清理创建的 URL 对象
window.URL.revokeObjectURL(url);
})
.catch((error) => {
console.error("下载失败", error);
});
});
},
}