前后端分离下EasyExcel的使用
项目环境:SpringBoot+Vue
依赖导入
<!--easyexcel-->
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.0.2</version>
</dependency>
tips
3.0.1版本 @ColumnWidth失效问题 用其他版本即可
实体类关联Excel
@ExcelProperty：value属性可用来设置表头名称
@ExcelPropertyvalue属性可用来设置表头名称
点击查看代码
@TableName(value = "five_insurances")
@Data
public class FiveInsurances implements Serializable { /** * */ @ExcelProperty("编号") @ColumnWidth(10) @TableId(value = "id", type = IdType.AUTO) private Integer id;
/** * 工号 */ @ExcelProperty("工号") @ColumnWidth(15) @TableField(value = "number") private String number;
/** * 姓名 */ @ExcelProperty("姓名") @ColumnWidth(20) @TableField(value = "name") private String name;
/** * 部门id */ @ExcelProperty("部门") @ColumnWidth(20) @TableField(value = "dept_id") private Integer deptId;
/** * 电话 */ @ExcelProperty("电话") @ColumnWidth(20) @TableField(value = "phone") private String phone;
/** * 缴纳基数 */ @ExcelProperty({"社保", "缴纳基数"}) @ColumnWidth(20) @TableField(value = "base_payment") private String basePayment;
/** * 个人缴纳 */ @ExcelProperty({"社保", "个人", "缴纳费用"}) @ColumnWidth(20) @TableField(value = "self_payment") private String selfPayment;
/** * 工伤保险缴纳比例 */ @ExcelProperty({"社保", "企业", "工伤保险缴纳比例"}) @ColumnWidth(20) @TableField(value = "ratio") private String ratio;
/** * 企业缴纳 */ @ExcelProperty({"社保", "企业", "缴纳费用"}) @ColumnWidth(20) @TableField(value = "com_payment") private String comPayment;
/** * 备注 */ @ExcelProperty({"社保", "备注"}) @ColumnWidth(30) @TableField(value = "remarks") private String remarks;
@ExcelIgnore @TableField(exist = false) private Dept dept;
@ExcelIgnore @TableField(exist = false) private static final long serialVersionUID = 1L;
}
导出Excel
Controller
@GetMapping("/fihf")
@ApiOperation(value = "导出五险一金列表Excel")
@ApiImplicitParams( @ApiImplicitParam(dataType = "Interger",name = "page",value = "page==-1:查询所有;page==-2,返回空模板",required = false) )
public void exportList(HttpServletResponse response, @RequestParam(value = "page", defaultValue = "1") Integer page) throws Exception { PageBean<List<FiveInsurances>> pageBean = fiveInsurancesService.selectFiveInsurancesList(page); ExcelUtils.exportToWeb(response,"sheet1",FiveInsurances.class,pageBean.getData()); }
前端Axios请求
exportFile(page = this.pageBean.current) { //复选框选中则设置page为-1,表示导出全部 if (this.checked) { page = -1; } //关闭对话框 this.dialogVisible = false; this.axios({ method: 'get', url: baseURL + "fileExport/fihf", params: { page: page, }, responseType: 'blob' //响应类型须设置为二进制文件流 }).then((res) => { if (!res) { return } const link = document.createElement("a");//创建a标签 let blob = new Blob([res.data], { type: "multipart/form-data" }); //设置文件类型 link.style.display = "none"; let url = URL.createObjectURL(blob); link.href = url; //给a标签href属性赋值 link.setAttribute("download", decodeURI(Date.now() + '.xlsx')); document.body.appendChild(link);//挂载a标签 link.click();//a标签click事件 document.body.removeChild(link); //移除a标签 window.URL.revokeObjectURL(url); //销毁下载链接
console.log(res); this.checked = false; return this.$message.success("导出报表数据成功!") })
},
导入Excel
Controller
@PostMapping("/fihf")
@ApiOperation(value = "导入五险一金列表Excel")
public ResultVO<FiveInsurances> FiveInsurances(@RequestPart("file") MultipartFile file) throws IOException { if (file.getSize() < 0) { return ResultVO.createFail(404, "导入数据失败"); } List<FiveInsurances> list = ExcelUtils.importFile(file, FiveInsurances.class);
//持久化到数据库 int i = fiveInsurancesService.insertBatch(list); if (i > 0) { return ResultVO.createSuccess("读取excel成功", null); } else { return ResultVO.createFail(); }
}
前端Axios请求
handleBeforUpload(file) { console.log("beforeUpload", file); //创建文件附件 let formData = new FormData(); //添加到formdata formData.append("file", file); this.axios({ method: 'post', url: baseURL + "fileImport/fihf", data: formData, Headers: { "Content-Type": "multipart/form-data", } }).then((res) => { console.log("读取excel", res); if (res.data.data == 10000) { this.$message.success(res.data.msg) } })
},
Excel导入导出工具类
点击查看代码
package com.self.salarymanagement.utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.util.IOUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
/*** Excel工具类*/
@Slf4j
public class ExcelUtils {
/** * 导出Excel到指定路径下 * * @param path 路径 * @param excelName Excel名称 * @param sheetName sheet页名称 * @param clazz Excel要转换的类型 * @param data 要导出的数据 */ public static void exportFileToLocal(String path, String excelName, String sheetName, Class clazz, List data) { String fileName = path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue()); EasyExcel.write(fileName, clazz).sheet(sheetName).doWrite(data); }
/** * 导出Excel到web * * @param response 响应 * @param sheetName sheet页名称 * @param clazz Excel要转换的类型 * @param data 要导出的数据 * @throws Exception */ public static void exportToWeb(HttpServletResponse response, String sheetName, Class clazz, List data) throws Exception {
// response.setContentType("application/vnd.ms-excel"); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder .encode可以防止中文乱码 String excelName = URLEncoder.encode(String.valueOf(System.currentTimeMillis()), "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue()); EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).doWrite(data); }
/** * 导出Excel到web * * @param response 响应 * @param excelName Excel名称 * @param sheetName sheet页名称 * @param clazz Excel要转换的类型 * @param data 要导出的数据 * @throws Exception */ public static void exportToWeb(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {
// response.setContentType("application/vnd.ms-excel"); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 excelName = URLEncoder.encode(excelName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue()); EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data); }
/** * 将指定位置指定名称的Excel导出到web * * @param response 响应 * @param path 文件路径 * @param excelName 文件名称 * @throws UnsupportedEncodingException */ public static String export2Web4File(HttpServletResponse response, String path, String excelName) throws UnsupportedEncodingException { File file = new File(path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue())); if (!file.exists()) { return "文件不存在！"; }
response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 excelName = URLEncoder.encode(excelName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
try (
FileInputStream in = new FileInputStream(file);
ServletOutputStream out = response.getOutputStream(); ) { IOUtils.copy(in, out); return "导出成功！"; } catch (Exception e) { log.error("导出文件异常：", e); }
return "导出失败！"; }
public static <T> List<T> importFile(MultipartFile file, Class<T> clazz) throws IOException {
return EasyExcel.read(file.getInputStream())
.head(clazz)
.registerReadListener(new DefaultExcelListener<T>())
.sheet()
.doReadSync(); }
}
Excel导入监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
/*** @author Liu* @create 2023-03-08-11:44* @description:*/
@Slf4j
public class DefaultExcelListener<T> extends AnalysisEventListener<T> {
private final List<T> rows = new ArrayList<>();
/** * 读取excel操作 * * @param obj 数据 * @param analysisContext 上下文 */ // 每读一样，会调用该invoke方法一次 @Override public void invoke(T obj, AnalysisContext analysisContext) { //添加到list rows.add(obj); log.info("list容量" + rows.size() + obj); /** 数据量不是特别大，可以不需要打开 // 实际数据量比较大时，rows里的数据可以存到一定量之后进行批量处理（比如存到数据库）， // 然后清空列表，以防止内存占用过多造成OOM if(rows.size() >= 500){ log.info("存入数据库ing"); try { Thread.sleep(3000); } catch (InterruptedException e) { e.printStackTrace(); } rows.clear(); } */ }
/** * 读取玩excel后的操作 */ @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { log.info("成功读取【" + rows.size() + "】条数据");
System.out.println("================================"); rows.forEach(System.out::println); System.out.println("================================");
}
/** * 在读取excel异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。 */ @Override public void onException(Exception exception, AnalysisContext context) { log.error("解析失败，但是继续解析下一行:{}", exception.getMessage()); if (exception instanceof ExcelDataConvertException) { ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception; log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData()); } }
/** * @return 返回读取的总数据 */ public List<T> getRows() { return rows; }
}
多级表头设置
嵌套el-table-column即可
<el-table-column label="社保" width="180"> <el-table-column label="缴纳基数" width="120"> <template slot-scope="scope"> <span style="margin-left: 10px">{{ scope.row.basePayment }}</span> </template> </el-table-column> <el-table-column label="个人" width="120"> <el-table-column label="缴纳费用" width="120"> <template slot-scope="scope">
<span style="margin-left: 10px">{{ scope.row.selfPayment }}</span> </template> </el-table-column> </el-table-column> <el-table-column label="企业" width="180"> <el-table-column label="工伤保险缴纳比例" width="180"> <template slot-scope="scope">
<span style="margin-left: 10px">{{ scope.row.ratio }}</span> </template> </el-table-column> <el-table-column label="缴纳费用" width="180"> <template slot-scope="scope">
<span style="margin-left: 10px">{{ scope.row.comPayment }}</span> </template> </el-table-column> </el-table-column> <el-table-column label="备注" width="120"> <template slot-scope="scope"> <span style="margin-left: 10px">{{ scope.row.remarks }}</span> </template> </el-table-column>
</el-table-column>
