当前位置：首页 > news >正文

前端vue后端java使用easyexcel框架下载表格xls数据工具类

news 2025/7/11 1:05:37

一使用alibaba开源的 easyexcel框架，后台只需一个工具类即可实现下载

后端下载实现

依赖 pom.xml

 <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.1.2</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.0</version></dependency>

后台JAVA代码

/*** 下载xls数据* @param params* @param response* @throws IOException*/@PostMapping("/exportData")public void exportData(@RequestBody String params, HttpServletResponse response) throws IOException {JSONObject query = JSONObject.parseObject(params);String beginTime = query.getString("beginDate");String endTime = query.getString("endDate");List<AliIotLog> resultList = new ArrayList<>();//查询业务数据列表  resultList WebDownloadUtil.downloadXlsByList(response, resultList,LogExport.class,"xls");}

一个 java bean代码，用于设置导出时的列映射显示名称关系

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import java.io.Serializable;
import java.time.LocalDateTime;@ContentRowHeight(20)//注解用于指定某元素的内容行高度为20。
@HeadRowHeight(20)//注解用于指定某元素的表头行高度为20。
@ColumnWidth(30) //注解用于指定某元素的列宽度为30。
public class LogExport implements Serializable {private static final long serialVersionUID = 1L;/*** 设备协议内容*/@ExcelProperty("接收报文")private String inHexStr;/*** 回复内容*/@ExcelProperty("发送报文")private String outHexStr;/*** 地址或通道*/@ExcelProperty("地址")private String addr;/*** 时间*/@ExcelProperty("时间")private LocalDateTime ctime;public String getInHexStr() {return inHexStr;}public void setInHexStr(String inHexStr) {this.inHexStr = inHexStr;}public String getOutHexStr() {return outHexStr;}public void setOutHexStr(String outHexStr) {this.outHexStr = outHexStr;}public String getAddr() {return addr;}public void setAddr(String addr) {this.addr = addr;}public LocalDateTime getCtime() {return ctime;}public void setCtime(LocalDateTime ctime) {this.ctime = ctime;}}

完整下载工具类代码

/*** web 下载文件封装* @author hua* @date 2024-07-06 14:30*/
public class WebDownloadUtil {/*** 下载文件* @param response* @param resultList 列表数据* @param clazz 类形* @param format 表格格式 xlx xlsx csv* @throws IOException*/public static void downloadXlsByList(HttpServletResponse response, List resultList,Class clazz,String format) throws IOException {response.setContentType("application/vnd.ms-excel");response.setHeader("content-disposition","attachment;filename=data_export.xls");//下载文件名称在前端vue页面处理ExcelTypeEnum excelTypeEnum = ExcelTypeEnum.CSV;if("xls".equals(format)){excelTypeEnum = ExcelTypeEnum.XLS;}else if("xlsx".equals(format)){excelTypeEnum = ExcelTypeEnum.XLSX;}ExcelWriterBuilder writeWork = EasyExcel.write(response.getOutputStream(),clazz  ).excelType(excelTypeEnum).registerConverter(new Converter<LocalDateTime>(){public String format = "yyyy-MM-dd HH:mm:ss";@Overridepublic Class<LocalDateTime> supportJavaTypeKey() {return LocalDateTime.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic LocalDateTime convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {return LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern(format)).atStartOfDay();}@Overridepublic WriteCellData<?> convertToExcelData(LocalDateTime localDateTime, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {if(localDateTime==null){return new WriteCellData<>("");}DateTimeFormatter formatter = DateTimeFormatter.ofPattern(format);String format = formatter.format(localDateTime);return new WriteCellData(format);}});ExcelWriterSheetBuilder sheet = writeWork.sheet();sheet.doWrite(resultList);}
}

前端vue下载实现

vue前端页下载调用按钮

   import webUtil from "@api/webUtil";export default {name: 'sys_log',data () {return {queryBody:{},list:[],
}methods: {exportExcel () {//单击下载console.log('query data',this.queryBody)let url='/xxx/xxx/xxxx';webUtil.downloadXls(url,this.queryBody,"导出数据")}}}

vue 完整下载工具类

import request from '@/plugins/request';const webUtil= {downloadXls:function(url,data, fileNamePrefix){request({url: url,method: 'post',responseType: "blob",data}).then(data => {let blob = new Blob([data], {type: 'application/x-msdownload;charset=UTF-8'});let fileName = fileNamePrefix + Date.parse(new Date()) + '.xls';if (window.navigator.msSaveOrOpenBlob) {navigator.msSaveBlob(blob, fileName);} else {let link = document.createElement('a');link.href = window.URL.createObjectURL(blob);link.download = fileName;link.click();window.URL.revokeObjectURL(link.href);}}).catch(error => {console.error('Error exporting and downloading data:', error);}));}};
export default webUtil;