1、首先得先引用easyExcel的版本依賴,我那
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version>
</dependency>
2、然后得弄直接的實體類,(這個實體類是關鍵,主要的功能就是為了導出的表頭還有大標題做準備)
解釋:???@ExcelProperty(value ={"${bigHead}","區劃名稱"},index = 0)?
這個主要 ${bigHead} 這個是 大標題 ,這個標題是動態生成的,
package com.inspur.grp.hrss.labour.project.labourReport;import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.inspur.grp.hrss.labour.framework.base.BaseDomain;
import lombok.EqualsAndHashCode;/*** 每日更新統計記錄(這個是是抽取出來的)對象 labour_day_updatetime**/
@EqualsAndHashCode
public class LabourDayUpdatetime extends BaseDomain {/** 主鍵 */@ExcelIgnoreprivate String id;@ExcelProperty(value ={"${bigHead}","區劃名稱"},index = 0)private String mc;/** 區的編碼 */@ExcelIgnoreprivate String area;/** 鎮的編碼 */@ExcelIgnoreprivate String town;/** 村的編碼 */@ExcelIgnoreprivate String community;/** 就業去向 */@ExcelIgnoreprivate String employDestination;/** 人員類別 */@ExcelIgnoreprivate String category;/** 是否就業 */@ExcelIgnoreprivate String isEmployed;/** 農村勞動力人數 */@ExcelProperty(value ={"${bigHead}","農村勞動力人數"},index = 1)private String ncldlrs;/** 更新人數 */@ExcelProperty(value ={"${bigHead}","農村勞動力更新人數"},index = 2)private String gxrsa;@ExcelProperty(value ={"${bigHead}","更新率"},index = 3)private String gxla;/** 就業人數 */@ExcelProperty(value ={"${bigHead}","就業人數"},index = 4)private String jyrsa;@ExcelProperty(value ={"${bigHead}","國外務工(勞動力)"},index = 5)private String guowaiwug;@ExcelProperty(value ={"${bigHead}","省外務工(勞動力)"},index = 6)private String shengwai;@ExcelProperty(value ={"${bigHead}","省外市內務工(勞動力)"},index = 7)private String shiwaishengnei;@ExcelProperty(value ={"${bigHead}","縣外市內務工(勞動力)"},index = 8)private String xianwaishinwug;@ExcelProperty(value ={"${bigHead}","鄉(鎮)外縣內務工(勞動力)"},index = 9)private String xiangzhenwaixianneiwug;@ExcelProperty(value ={"${bigHead}","鄉(鎮)內務工(勞動力)"},index = 10)private String xiangzhennwug;@ExcelProperty(value ={"${bigHead}","就業率(勞動力)"},index = 11)private String jyla;/** 脫貧勞動力人數 */@ExcelProperty(value ={"${bigHead}","脫貧勞動力人數"},index = 12)private String tpldlrs;@ExcelProperty(value ={"${bigHead}","更新人數(脫貧)"},index = 13)private String gxrsb;@ExcelProperty(value ={"${bigHead}","更新率(脫貧)"},index = 14)private String gxlb;@ExcelProperty(value ={"${bigHead}","就業人數(脫貧)"},index = 15)private String jyrsb;@ExcelProperty(value ={"${bigHead}","國外務工(脫貧)"},index = 16)private String tpguowaiwug;@ExcelProperty(value ={"${bigHead}","省外務工(脫貧)"},index = 17)private String tpshengwai;@ExcelProperty(value ={"${bigHead}","市外省內務工(脫貧)"},index = 18)private String tpshiwaishengnei;@ExcelProperty(value ={"${bigHead}","縣外市內務工(脫貧)"},index = 19)private String tpxianwaishinwug;@ExcelProperty(value ={"${bigHead}","鄉(鎮)外縣內務工(脫貧)"},index = 20)private String tpxiangzhenwaixianneiwug;@ExcelProperty(value ={"${bigHead}","鄉(鎮)內務工(脫貧)"},index = 21)private String tpxiangzhennwug;@ExcelProperty(value ={"${bigHead}","就業率(脫貧)"},index = 22)private String jylb;@ExcelProperty(value ={"${bigHead}","易遷勞動力人數"},index = 23)private String yqldlrs;@ExcelProperty(value ={"${bigHead}","更新人數(易遷)"},index = 24)private String gxrsc;@ExcelProperty(value ={"${bigHead}","更新率(易遷)"},index = 25)private String gxlc;@ExcelProperty(value ={"${bigHead}","就業人數(易遷)"},index = 26)private String jyrsc;@ExcelProperty(value ={"${bigHead}","國外務工(易遷)"},index = 27)private String yqguowaiwug;@ExcelProperty(value ={"${bigHead}","省外務工(易遷)"},index = 28)private String yqshengwai;@ExcelProperty(value ={"${bigHead}","市外省內(易遷)"},index = 29)private String yqshiwaishengnei;@ExcelProperty(value ={"${bigHead}","縣外市內務工(易遷)"},index = 30)private String yqxianwaishinwug;@ExcelProperty(value ={"${bigHead}","鄉(鎮)外縣內務工(易遷)"},index = 31)private String yqxiangzhenwaixianneiwug;@ExcelProperty(value ={"${bigHead}","鄉(鎮)內務工(易遷)"},index = 32)private String yqxiangzhennwug;@ExcelProperty(value ={"${bigHead}","就業率(易遷)"},index = 33)private String jylc;@ExcelProperty(value ={"${bigHead}","未更新的人數"},index = 34)private String xtzwgxdldlrs;public String getMc() {return mc;}public void setMc(String mc) {this.mc = mc;}public String getGxla() {return gxla;}public void setGxla(String gxla) {this.gxla = gxla;}public String getJyla() {return jyla;}public void setJyla(String jyla) {this.jyla = jyla;}public String getGxlb() {return gxlb;}public void setGxlb(String gxlb) {this.gxlb = gxlb;}public String getJylb() {return jylb;}public void setJylb(String jylb) {this.jylb = jylb;}public String getGxlc() {return gxlc;}public void setGxlc(String gxlc) {this.gxlc = gxlc;}public String getJylc() {return jylc;}public void setJylc(String jylc) {this.jylc = jylc;}public String getXtzwgxdldlrs() {return xtzwgxdldlrs;}public void setXtzwgxdldlrs(String xtzwgxdldlrs) {this.xtzwgxdldlrs = xtzwgxdldlrs;}public void setId(String id){this.id = id;}public String getId() {return id;}public void setArea(String area){this.area = area;}public String getArea() {return area;}public void setTown(String town) {this.town = town;}public String getTown() {return town;}public void setCommunity(String community) {this.community = community;}public String getCommunity() {return community;}public void setEmployDestination(String employDestination) {this.employDestination = employDestination;}public String getEmployDestination() {return employDestination;}public void setCategory(String category) {this.category = category;}public String getCategory() {return category;}public void setIsEmployed(String isEmployed) {this.isEmployed = isEmployed;}public String getIsEmployed() {return isEmployed;}public void setNcldlrs(String ncldlrs) {this.ncldlrs = ncldlrs;}public String getNcldlrs() {return ncldlrs;}public void setGxrsa(String gxrsa) {this.gxrsa = gxrsa;}public String getGxrsa() {return gxrsa;}public void setJyrsa(String jyrsa) {this.jyrsa = jyrsa;}public String getJyrsa() {return jyrsa;}public void setTpldlrs(String tpldlrs) {this.tpldlrs = tpldlrs;}public String getTpldlrs() {return tpldlrs;}public void setGxrsb(String gxrsb) {this.gxrsb = gxrsb;}public String getGxrsb() {return gxrsb;}public void setJyrsb(String jyrsb) {this.jyrsb = jyrsb;}public String getJyrsb() {return jyrsb;}public void setYqldlrs(String yqldlrs) {this.yqldlrs = yqldlrs;}public String getYqldlrs() {return yqldlrs;}public void setGxrsc(String gxrsc) {this.gxrsc = gxrsc;}public String getGxrsc() {return gxrsc;}public void setJyrsc(String jyrsc) {this.jyrsc = jyrsc;}public String getJyrsc() {return jyrsc;}public void setShengwai(String shengwai) {this.shengwai = shengwai;}public String getShengwai() {return shengwai;}public void setShiwaishengnei(String shiwaishengnei) {this.shiwaishengnei = shiwaishengnei;}public String getShiwaishengnei() {return shiwaishengnei;}public void setTpshengwai(String tpshengwai) {this.tpshengwai = tpshengwai;}public String getTpshengwai() {return tpshengwai;}public void setTpshiwaishengnei(String tpshiwaishengnei) {this.tpshiwaishengnei = tpshiwaishengnei;}public String getTpshiwaishengnei() {return tpshiwaishengnei;}public void setYqshengwai(String yqshengwai) {this.yqshengwai = yqshengwai;}public String getYqshengwai() {return yqshengwai;}public void setYqshiwaishengnei(String yqshiwaishengnei) {this.yqshiwaishengnei = yqshiwaishengnei;}public String getYqshiwaishengnei() {return yqshiwaishengnei;}public void setGuowaiwug(String guowaiwug) {this.guowaiwug = guowaiwug;}public String getGuowaiwug() {return guowaiwug;}public void setXianwaishinwug(String xianwaishinwug) {this.xianwaishinwug = xianwaishinwug;}public String getXianwaishinwug() {return xianwaishinwug;}public void setXiangzhennwug(String xiangzhennwug) {this.xiangzhennwug = xiangzhennwug;}public String getXiangzhennwug() {return xiangzhennwug;}public void setXiangzhenwaixianneiwug(String xiangzhenwaixianneiwug) {this.xiangzhenwaixianneiwug = xiangzhenwaixianneiwug;}public String getXiangzhenwaixianneiwug() {return xiangzhenwaixianneiwug;}public void setTpguowaiwug(String tpguowaiwug) {this.tpguowaiwug = tpguowaiwug;}public String getTpguowaiwug() {return tpguowaiwug;}public void setTpxianwaishinwug(String tpxianwaishinwug) {this.tpxianwaishinwug = tpxianwaishinwug;}public String getTpxianwaishinwug() {return tpxianwaishinwug;}public void setTpxiangzhennwug(String tpxiangzhennwug) {this.tpxiangzhennwug = tpxiangzhennwug;}public String getTpxiangzhennwug() {return tpxiangzhennwug;}public void setTpxiangzhenwaixianneiwug(String tpxiangzhenwaixianneiwug) {this.tpxiangzhenwaixianneiwug = tpxiangzhenwaixianneiwug;}public String getTpxiangzhenwaixianneiwug() {return tpxiangzhenwaixianneiwug;}public void setYqguowaiwug(String yqguowaiwug) {this.yqguowaiwug = yqguowaiwug;}public String getYqguowaiwug() {return yqguowaiwug;}public void setYqxianwaishinwug(String yqxianwaishinwug) {this.yqxianwaishinwug = yqxianwaishinwug;}public String getYqxianwaishinwug() {return yqxianwaishinwug;}public void setYqxiangzhennwug(String yqxiangzhennwug) {this.yqxiangzhennwug = yqxiangzhennwug;}public String getYqxiangzhennwug() {return yqxiangzhennwug;}public void setYqxiangzhenwaixianneiwug(String yqxiangzhenwaixianneiwug) {this.yqxiangzhenwaixianneiwug = yqxiangzhenwaixianneiwug;}public String getYqxiangzhenwaixianneiwug() {return yqxiangzhenwaixianneiwug;}}
3、然后定義一個接口接收的DTO類
package com.inspur.grp.hrss.labour.project.labourReport.dto;import com.inspur.grp.hrss.labour.project.labourReport.LabourDayUpdatetime;import java.util.List;public class ExportDataRequestDto {private List<LabourDayUpdatetime> labourDayUpdatetimeList;private String addressName; // 這個是前端傳遞的過了的標題名稱// Getters and Setterspublic List<LabourDayUpdatetime> getLabourDayUpdatetimeList() {return labourDayUpdatetimeList;}public void setLabourDayUpdatetimeList(List<LabourDayUpdatetime> labourDayUpdatetimeList) {this.labourDayUpdatetimeList = labourDayUpdatetimeList;}public String getAddressName() {return addressName;}public void setAddressName(String addressName) {this.addressName = addressName;}
}
4、封裝標題工具類
package com.inspur.grp.hrss.labour.util.IDCardUtils;import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.util.PropertyPlaceholderHelper;import java.util.List;
import java.util.Properties;public class ExcelTitleHandler implements CellWriteHandler {/**錯誤信息處理時正則表達式的格式*/private final String EXCEL_ERROR_REG = "^(.*)(\\(錯誤:)(.*)(\\))$";private String bigHead;PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");public ExcelTitleHandler( String bigHead) {this.bigHead = bigHead; //表頭1}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {// 動態設置表頭字段if (!ObjectUtil.isEmpty(head)) {List<String> headNameList = head.getHeadNameList();if (CollectionUtil.isNotEmpty(headNameList)) {Properties properties = new Properties();properties.setProperty("bigHead", bigHead);for (int i = 0 ; i < headNameList.size() ; i++){// 循環遍歷替換headNameList.set(i, placeholderHelper.replacePlaceholders(headNameList.get(i), properties));}}}}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {}
}
5、開始接口請求
//每日更新統計導出@PostMapping("/exportDataUpdate")public void exportDataUpdate(@RequestBody ExportDataRequestDto exportDataRequestDto, HttpServletResponse response) {labourDayUpdatetimeService.exportDataUpdate(exportDataRequestDto, response);}
6、service層
void exportDataUpdate(ExportDataRequestDto exportDataRequestDto, HttpServletResponse response);
7、serviceImpl實現類
private void setIntValueFromDoubleString(LabourDayUpdatetime recode, String value, Consumer<String> setter) {if (value != null && !value.isEmpty()) {try {int intValue = (int) Double.parseDouble(value);setter.accept(String.valueOf(intValue));} catch (NumberFormatException e) {setter.accept("0");}} else {setter.accept("0");}}private void setFormattedPercentage(LabourDayUpdatetime recode, String value, Consumer<String> setter) {if (value != null && !value.isEmpty()) {try {double doubleValue = Double.parseDouble(value);String formattedValue = String.format("%.2f%%", doubleValue * 100);setter.accept(formattedValue);} catch (NumberFormatException e) {setter.accept("0.00%");}} else {setter.accept("0.00%");}}@Overridepublic void exportDataUpdate(ExportDataRequestDto exportDataRequestDto, HttpServletResponse response) {String bigHead=exportDataRequestDto.getAddressName()+"勞動力更新報表";List<LabourDayUpdatetime> labourDayUpdatetimeList = exportDataRequestDto.getLabourDayUpdatetimeList();labourDayUpdatetimeList.stream().forEach(recode->{setIntValueFromDoubleString(recode, recode.getNcldlrs(), recode::setNcldlrs);setIntValueFromDoubleString(recode, recode.getGxrsa(), recode::setGxrsa);setFormattedPercentage(recode, recode.getGxla(), recode::setGxla);setIntValueFromDoubleString(recode, recode.getJyrsa(), recode::setJyrsa);setIntValueFromDoubleString(recode, recode.getGuowaiwug(), recode::setGuowaiwug);setIntValueFromDoubleString(recode, recode.getShengwai(), recode::setShengwai);setIntValueFromDoubleString(recode, recode.getShiwaishengnei(), recode::setShiwaishengnei);setIntValueFromDoubleString(recode, recode.getXianwaishinwug(), recode::setXianwaishinwug);setIntValueFromDoubleString(recode, recode.getXiangzhennwug(), recode::setXiangzhenwaixianneiwug);setIntValueFromDoubleString(recode, recode.getXiangzhennwug(), recode::setXiangzhennwug);setFormattedPercentage(recode, recode.getJyla(), recode::setJyla);setIntValueFromDoubleString(recode, recode.getTpldlrs(), recode::setTpldlrs);setIntValueFromDoubleString(recode, recode.getGxrsb(), recode::setGxrsb);setFormattedPercentage(recode, recode.getGxlb(), recode::setGxlb);setIntValueFromDoubleString(recode, recode.getJyrsb(), recode::setJyrsb);setIntValueFromDoubleString(recode, recode.getTpguowaiwug(), recode::setTpguowaiwug);setIntValueFromDoubleString(recode, recode.getTpshengwai(), recode::setTpshengwai);setIntValueFromDoubleString(recode, recode.getTpshiwaishengnei(), recode::setTpshiwaishengnei);setIntValueFromDoubleString(recode, recode.getTpxianwaishinwug(), recode::setTpxianwaishinwug);setIntValueFromDoubleString(recode, recode.getTpxiangzhenwaixianneiwug(), recode::setTpxiangzhenwaixianneiwug);setIntValueFromDoubleString(recode, recode.getTpxiangzhennwug(), recode::setTpxiangzhennwug);setFormattedPercentage(recode, recode.getJylb(), recode::setJylb);setIntValueFromDoubleString(recode, recode.getYqldlrs(), recode::setYqldlrs);setIntValueFromDoubleString(recode, recode.getGxrsc(), recode::setGxrsc);setFormattedPercentage(recode, recode.getGxlc(), recode::setGxlc);setIntValueFromDoubleString(recode, recode.getJyrsc(), recode::setJyrsc);setIntValueFromDoubleString(recode, recode.getYqguowaiwug(), recode::setYqguowaiwug);setIntValueFromDoubleString(recode, recode.getYqshengwai(), recode::setYqshengwai);setIntValueFromDoubleString(recode, recode.getYqshiwaishengnei(), recode::setYqshiwaishengnei);setIntValueFromDoubleString(recode, recode.getYqxianwaishinwug(), recode::setYqxianwaishinwug);setIntValueFromDoubleString(recode, recode.getYqxiangzhenwaixianneiwug(), recode::setYqxiangzhenwaixianneiwug);setIntValueFromDoubleString(recode, recode.getYqxiangzhennwug(), recode::setYqxiangzhennwug);setFormattedPercentage(recode, recode.getJylc(), recode::setJylc);setIntValueFromDoubleString(recode, recode.getXtzwgxdldlrs(), recode::setXtzwgxdldlrs);});try {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename=" + "數據" + ".xlsx");WriteCellStyle contentWriteCellStyle = new WriteCellStyle();contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);contentWriteCellStyle.setBorderTop(BorderStyle.THIN);contentWriteCellStyle.setBorderRight(BorderStyle.THIN);contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);WriteCellStyle headWriteCellStyle = new WriteCellStyle();headWriteCellStyle.setBorderLeft(BorderStyle.THIN);headWriteCellStyle.setBorderTop(BorderStyle.THIN);headWriteCellStyle.setBorderRight(BorderStyle.THIN);headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//設置頭部標題居中headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 這個策略是 頭是頭的樣式 內容是內容的樣式 其他的策略可以自己實現HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);EasyExcel.write(response.getOutputStream(), LabourDayUpdatetime.class).sheet("數據").registerWriteHandler(new ExcelTitleHandler(bigHead)).registerWriteHandler(new SimpleColumnWidthStyleStrategy(20)).registerWriteHandler(horizontalCellStyleStrategy).doWrite(labourDayUpdatetimeList);}catch (Exception e){throw new RuntimeException(e);}}
7、前端vue的接口
具體代碼就不列出了,這個? ?daochu 就是一個按鈕button
daochu(){if(this.tableData.length==0){this.$message({ message: '請確保有數據進行導出', type: 'warning' });return;}else{const loading = this.$loading({lock: true,text: '導出中',spinner: 'el-icon-loading',background: ' rgba(255, 255, 255, 0.6)'});var data = {labourDayUpdatetimeList: this.tableData, // 這個tableData 這個是你要導出的數據addressName: this.addressName // 這個是你要給你的excel設置的大標題}//發送接口請求labourReportApi.exportDataUpdate(data).then(res => { const url = window.URL.createObjectURL(new Blob([res]));const link = document.createElement('a');link.href = url;link.setAttribute('download', '每日更新情況調度統計.xlsx');document.body.appendChild(link);link.click();document.body.removeChild(link);window.URL.revokeObjectURL(url);this.$message({message: '導出數據成功', type: 'success' });setTimeout(() => {loading.close();}, 1000);})}},
8、具體vue請求
這個是一個js,你可以賦值到你自己的接口js里面去
import request from "@/util/request";
var prefixD = customConfig.VUE_APP_CLIENT_ID + "/labourReport/updatetimeDayTj";
export default {//每日更新導出exportDataUpdate: async function (params) {return request({url: prefixD + "/exportDataUpdate",method: "post",data: params,responseType: "arraybuffer",headers: {'Content-Type': 'application/json'}})},}
9、導出已經完成了,展示效果
因為數據量很多,就展示部分