前后端分離下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 = "https://www.cnblogs.com/zdrcgubjo/p/five_insurances")
@Data
public class FiveInsurances implements Serializable {
/**
*
*/
@ExcelProperty("編號")
@ColumnWidth(10)
@TableId(value = "https://www.cnblogs.com/zdrcgubjo/p/id", type = IdType.AUTO)
private Integer id;
/**
* 工號
*/
@ExcelProperty("工號")
@ColumnWidth(15)
@TableField(value = "https://www.cnblogs.com/zdrcgubjo/p/number")
private String number;
/**
* 姓名
*/
@ExcelProperty("姓名")
@ColumnWidth(20)
@TableField(value = "https://www.cnblogs.com/zdrcgubjo/p/name")
private String name;
/**
* 部門id
*/
@ExcelProperty("部門")
@ColumnWidth(20)
@TableField(value = "https://www.cnblogs.com/zdrcgubjo/p/dept_id")
private Integer deptId;
/**
* 電話
*/
@ExcelProperty("電話")
@ColumnWidth(20)
@TableField(value = "https://www.cnblogs.com/zdrcgubjo/p/phone")
private String phone;
/**
* 繳納基數
*/
@ExcelProperty({"社保", "繳納基數"})
@ColumnWidth(20)
@TableField(value = "https://www.cnblogs.com/zdrcgubjo/p/base_payment")
private String basePayment;
/**
* 個人繳納
*/
@ExcelProperty({"社保", "個人", "繳納費用"})
@ColumnWidth(20)
@TableField(value = "https://www.cnblogs.com/zdrcgubjo/p/self_payment")
private String selfPayment;
/**
* 工傷保險繳納比例
*/
@ExcelProperty({"社保", "企業", "工傷保險繳納比例"})
@ColumnWidth(20)
@TableField(value = "https://www.cnblogs.com/zdrcgubjo/p/ratio")
private String ratio;
/**
* 企業繳納
*/
@ExcelProperty({"社保", "企業", "繳納費用"})
@ColumnWidth(20)
@TableField(value = "https://www.cnblogs.com/zdrcgubjo/p/com_payment")
private String comPayment;
/**
* 備注
*/
@ExcelProperty({"社保", "備注"})
@ColumnWidth(30)
@TableField(value = "https://www.cnblogs.com/zdrcgubjo/p/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 = "https://www.cnblogs.com/zdrcgubjo/p/匯出五險一金串列Excel")
@ApiImplicitParams(
@ApiImplicitParam(dataType = "Interger",name = "page",value = "https://www.cnblogs.com/zdrcgubjo/p/page==-1:查詢所有;page==-2,回傳空模板",required = false)
)
public void exportList(HttpServletResponse response, @RequestParam(value = "https://www.cnblogs.com/zdrcgubjo/p/page", defaultValue = "https://www.cnblogs.com/zdrcgubjo/p/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 = https://www.cnblogs.com/zdrcgubjo/p/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 = "https://www.cnblogs.com/zdrcgubjo/p/匯入五險一金串列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 = https://www.cnblogs.com/zdrcgubjo/p/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 =https://www.cnblogs.com/zdrcgubjo/p/= 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="社保" >
<el-table-column label="繳納基數" >
<template slot-scope="scope">
<span style="margin-left: 10px">{{ scope.row.basePayment }}</span>
</template>
</el-table-column>
<el-table-column label="個人" >
<el-table-column label="繳納費用" >
<template slot-scope="scope">
<span style="margin-left: 10px">{{ scope.row.selfPayment }}</span>
</template>
</el-table-column>
</el-table-column>
<el-table-column label="企業" >
<el-table-column label="工傷保險繳納比例" >
<template slot-scope="scope">
<span style="margin-left: 10px">{{ scope.row.ratio }}</span>
</template>
</el-table-column>
<el-table-column label="繳納費用" >
<template slot-scope="scope">
<span style="margin-left: 10px">{{ scope.row.comPayment }}</span>
</template>
</el-table-column>
</el-table-column>
<el-table-column label="備注" >
<template slot-scope="scope">
<span style="margin-left: 10px">{{ scope.row.remarks }}</span>
</template>
</el-table-column>
</el-table-column>
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/547230.html
標籤：Java
下一篇：類加載器（Java）