如何在后端实现导出前端列表字段到Excel功能

需求

根据表格展示的列表字段、点击导出会弹出一个EXCEL包含下列的信息，但是由于框架自带的是Mybatis-plus需要提供的实体类字段和表格中展示的是一样的，因此是行不通的，因为下列的字段和数据库表不能一一对应上，因此考虑自定义封装一个接口

前端的实现

1. 提供一个导出的点击函数

    <!--引用表格--><BasicTable @register="registerTable" :rowSelection="rowSelection"><!--插槽:table标题--><template #tableTitle><a-button type="primary" @click="handleAdd" preIcon="ant-design:plus-outlined"> 新增</a-button><a-button  type="primary" preIcon="ant-design:export-outlined" @click="exportXlS"> 导出</a-button><j-upload-button type="primary" preIcon="ant-design:import-outlined" @click="onImportXls">导入</j-upload-button><a-dropdown v-if="selectedRowKeys.length > 0"><template #overlay><a-menu><a-menu-item key="1" @click="batchHandleDelete"><Icon icon="ant-design:delete-outlined"></Icon>删除</a-menu-item></a-menu></template><a-button>批量操作<Icon icon="mdi:chevron-down"></Icon></a-button></a-dropdown></template><!--操作栏--><template #action="{ record }"><TableAction :actions="getTableAction(record)" :dropDownActions="getDropDownAction(record)" /></template><!--字段回显插槽--><template #htmlSlot="{ text }"><div v-html="text"></div></template><template #bill_data="{ record }"> <!-- 文字提示 --><a-tooltip><template #title>{{ record.bill}}</template> <a-button type="primary" shape="round" @click = "showBillDetails(record.id)">查看</a-button> </a-tooltip></template><!--省市区字段回显插槽--><template #pcaSlot="{ text }">{{ getAreaTextByCode(text) }}</template><template #fileSlot="{ text }"><span v-if="!text" style="font-size: 12px;font-style: italic;">无文件</span><a-button v-else :ghost="true" type="primary" preIcon="ant-design:download-outlined" size="small"@click="downloadFile(text)">下载</a-button></template></BasicTable>

2.引入组件中的userMethod

  import { useMethods } from '/@/hooks/system/useMethods';

3.tableProps中导出中添加对应的查询参数

const { prefixCls, tableContext, onExportXls, onImportXls } = useListPage({tableProps: {title: '数据报表',api: queryList,columns,canResize: false,useSearchForm: false,showActionColumn: false,summaryData: summaryData,pagination:false,actionColumn: {width: 120,fixed: 'right',},beforeFetch: (params) => {return Object.assign(params, queryParam.value);},afterFetch: (data) =>{}},exportConfig: {name: "数据报表",url: getExportUrl,params:queryParam.value},importConfig: {url: getImportUrl,success: handleSuccess},
});

4. 编写导出函数

  //导入导出方法const { handleExportXls, handleImportXls } = useMethods();/*** 导出事件*/function exportXlS() {return handleExportXls("账号数据表", "/count/opReport/exportExcel", queryParam.value);}

后端逻辑的实现

1.Controller层

使用ModelAndView进行接收，同时Dto是前端查询的那些参数，ReportAccountModal实体类使我们导出Excel对应的那些字段

	/**@param reportAccountDto* @author chenglin* @description 补充导出Excel功能* @date  16:26 2023/5/30**/@RequestMapping(value = "/exportExcel")public ModelAndView exportExcel(HttpServletRequest request, ReportAccountDto reportAccountDto) {return OpReportAccountService.exportExcel(request,reportAccountDto, ReportAccountModal.class,"账号数据表");}

2.创建Modal类

package org.jeecg.modules.count.modal;import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
import lombok.Data;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.springframework.format.annotation.DateTimeFormat;/*** @description: 账号报表EXCEL模型类* @author: chenglin* @date: 2023年05月30日 16:28*/
@Data
public class ReportAccountModal implements Serializable {private static final long serialVersionUID = 1L;/**账号昵称*/@Excel(name = "账号昵称", width = 15)@ApiModelProperty(value = "账号昵称")private String nickName;/**账号*/@Excel(name = "账号", width = 15)@ApiModelProperty(value = "账号")private String account;/**游戏*/@Excel(name = "游戏", width = 15)@ApiModelProperty(value = "游戏")private String subGameIds;/**渠道*/@Excel(name = "渠道", width = 15)@ApiModelProperty(value = "渠道")private String channelName;/**投放消耗*/@Excel(name = "投放消耗", width = 15)@ApiModelProperty(value = "投放消耗")private BigDecimal outCostMoney;/**账号余额*/@Excel(name = "账号余额", width = 15)@ApiModelProperty(value = "账号余额")private BigDecimal surplusAmount;/**曝光*/@Excel(name = "曝光", width = 15)@ApiModelProperty(value = "曝光")private BigDecimal exhibition;/**下载*/@Excel(name = "下载", width = 15)@ApiModelProperty(value = "下载")private BigDecimal download ;/**负责人*/@Excel(name = "负责人", width = 15)@ApiModelProperty(value = "负责人")private String principalUser;/**创建时间*/@Excel(name = "创建时间", width = 15)@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd")@DateTimeFormat(pattern="yyyy-MM-dd")@ApiModelProperty(value = "创建时间")private Date createTime;/**更新时间*/@Excel(name = "更新时间", width = 15)@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd")@DateTimeFormat(pattern="yyyy-MM-dd")@ApiModelProperty(value = "更新时间")private Date updateTime;
}

3.Sevice层

其中我们的这个reusltList是我调用了之前查询数据的接口，然后将对应的数据拷贝到我的Modal类

    ModelAndView exportExcel(HttpServletRequest request, ReportAccountDto reportAccountDto, Class<ReportAccountModal> reportAccountClass, Stringtitle);//这是impl@Overridepublic ModelAndView exportExcel(HttpServletRequest request, ReportAccountDto reportAccountDto,Class<ReportAccountModal> reportAccountVoClass, String title) {LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();String  username = JwtUtil.getUserNameByToken(request);// Step.2 获取导出数据List<ReportAccountVo> resultList = queryAccountList(reportAccountDto,username);List<ReportAccountModal>exportList = new ArrayList<>();for (ReportAccountVo reportAccountVo : resultList) {ReportAccountModal reportAccountModal = new ReportAccountModal();BeanUtils.copyProperties(reportAccountVo,reportAccountModal);exportList.add(reportAccountModal);}// Step.3 AutoPoi 导出ExcelModelAndView mv = new ModelAndView(new JeecgEntityExcelView());//此处设置的filename无效 ,前端会重更新设置一下mv.addObject(NormalExcelConstants.FILE_NAME, title);mv.addObject(NormalExcelConstants.CLASS, reportAccountVoClass);//update-begin--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置--------------------ExportParams exportParams=new ExportParams(title + "报表", "导出人:" + sysUser.getRealname(), title);exportParams.setImageBasePath(upLoadPath);//update-end--Author:liusq  Date:20210126 for：图片导出报错，ImageBasePath未设置----------------------mv.addObject(NormalExcelConstants.PARAMS,exportParams);mv.addObject(NormalExcelConstants.DATA_LIST, exportList);return mv;}