jeccg-boot框架实现xls模板导出功能

一、后端部分

//1、在application-dev.yml文件增加模板路径
  path :
    #模板路径
    saxls: /data/opt/saxls/
//2、控制层写法
public class sabassalController extends JeecgController<sabassalVo, IsabassalService> {
    @Autowired
    private IsabassalService isabassalservice;

    @Autowired
    private IMonthNonmeddisbpayService imonthnonmeddisbpayservice;

    private static String saxls;

    @Value(value = "${jeecg.path.saxls}")
    public void setsaxls(String saxls) {
        sabassalController.saxls = saxls;  // 通过setter注入静态变量
    }


    /**
     * 一键按模板导出
     *
     * @param request
     * @return
     */
    @RequestMapping(value = "/exsabassal")

    public void exbsamt(HttpServletRequest request, HttpServletResponse response) throws Exception {

        Map<String, String[]> parameterMap = request.getParameterMap();
//获取前段传入的参数
        String[] months = parameterMap.get("months");
        String smonths=null;
        if (months != null && months.length > 0) {
            smonths = months[0];
        }

        // 第一个模板
        Workbook workbook1 = ExcelExportUtil.exportExcel(getTemplateParams("zbjb"), createDataMapzb(smonths));
       
        // 设置ZIP响应头
        response.setContentType("application/zip");
        String fileName = "sa_reports_" + LocalDate.now().format(DateTimeFormatter.BASIC_ISO_DATE) + ".zip";
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

        try (ZipOutputStream zos = new ZipOutputStream(response.getOutputStream())) {
            // 写入第一个Excel
            addToZip(zos, workbook1, smonthst+"人员表.xlsx");
   
        }
    }

    /**
     * 获取相关数据
     *

     */
    private Map<String, Object> createDataMapzb(String months) {

  

        String estab ="11";
        String poscat=null;
        Map<String, Object> map = new HashMap<>();
		//查询条件参数 months,estab,poscat
        List<sabassalVo> list = isabassalservice.exsabassal(months,estab,poscat);
//1、求和数据列 lvsal


        String sumlvsaltotal = calculateSum(list, sabassalVo::getLvsal);

        // 设置样式（确保 Excel 单元格格式正确）
        Map<String, Object> styles = new HashMap<>();
        styles.put("numberFormat", "0.00");  // 强制两位小数
        map.put("title", "全员表");
        map.put("list", list);
        map.put("sumlvsaltotal", sumlvsaltotal); // 添加合计值

        map.put("years", years);
        map.put("month", month);
        map.put("styles", styles);
        return map;
    }


   
    /**
     * 将xls转为zip
     *

     */
    private void addToZip(ZipOutputStream zos, Workbook workbook, String fileName) throws IOException {
        ZipEntry entry = new ZipEntry(fileName);
        zos.putNextEntry(entry);
        workbook.write(zos);
        workbook.close();
        zos.closeEntry();
    }

    /**
     * 获取基本模板

     * @return
     */
    public static TemplateExportParams getTemplateParams(String name) throws IOException {
        return new TemplateExportParams(saxls+"//jb//"+name+".xlsx");
    }

//公共方法求和统计
     private String calculateSum(List<sabassalVo> list, Function<sabassalVo, BigDecimal> fieldExtractor) {
            return list.stream()
                    .filter(vo -> fieldExtractor.apply(vo) != null)  // 过滤null值
                    .map(fieldExtractor)  // 直接提取 BigDecimal
                    .reduce(BigDecimal.ZERO, BigDecimal::add)
                    .setScale(2, RoundingMode.HALF_UP)
                    .toPlainString();
        }


    private String calculatefwbSum(List<MonthNonmeddisbpay> list, Function<MonthNonmeddisbpay, BigDecimal> fieldExtractor) {
        return list.stream()
                .filter(vo -> fieldExtractor.apply(vo) != null)  // 过滤null值
                .map(fieldExtractor)  // 直接提取 BigDecimal
                .reduce(BigDecimal.ZERO, BigDecimal::add)
                .setScale(2, RoundingMode.HALF_UP)
                .toPlainString();
    }

}

二、前端部分

#1、增加一个按钮
 <a-button type="primary" icon="download" @click="handleExportbassalzip('基本表')">1、基本表导出</a-button>
 #2、增加请求方法
    url: {

          exportbassalXlsUrl: "/sa/saBasic/exsabassal",
     
        },

#3、增加一个方法

handleExportbassalzip(fileName){
      // 增加严格空值判断（包括null/undefined/空字符串）
      if (!this.queryParam.months) {
        this.$message.warning("请选择月份")
        return  // 增加返回语句阻止继续执行
      }
   
      if(!fileName || typeof fileName != "string"){
        fileName = "导出文件"
      }
      let param = this.getQueryParams();
      if(this.selectedRowKeys && this.selectedRowKeys.length>0){
        param['selections'] = this.selectedRowKeys.join(",")
      }
  
      downFile(this.url.exportbassalXlsUrl,param).then((data)=>{
        if (!data) {
          this.$message.warning("文件下载失败")
          return
        }
        if (typeof window.navigator.msSaveBlob !== 'undefined') {
          window.navigator.msSaveBlob(new Blob([data],{type: 'application/vnd.ms-excel'}), fileName+'.zip')
        }else{
          let url = window.URL.createObjectURL(new Blob([data],{type: 'application/vnd.ms-excel'}))
          let link = document.createElement('a')
          link.style.display = 'none'
          link.href = url
          link.setAttribute('download', fileName+'.zip')
          document.body.appendChild(link)
          link.click()
          document.body.removeChild(link); //下载完成移除元素
          window.URL.revokeObjectURL(url); //释放掉blob对象
        }
      })
    
    },

三、模板制作

zbjb.xlsx

文件内容如下：

其中循环列表如下：

{{$fe:list t.id t.names t.depts t.hrtit t.lvl t.lvsal}}