前後端分離下EasyExcel的使用 項目環境:SpringBoot+Vue 依賴導入 <!--easyexcel--> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3 ...
前後端分離下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>
