动态导出excel

现在有个业务场景，需要动态导出单据费用。由于不同的业务单位配置的费用名称不同，所有表头需要动态拼接。

表头还会一级表头合并，如下图所示：

 

表头动态加载，同时对应的行数据也要根据表头加载。

使用阿里的easyExcel。

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;

public BaseResultVo exportFeeDetail(BillFeeDTO dto, HttpServletResponse response) throws Exception {
        Long companyId = 1L;
        /**表单**/
        Sheet sheet = new Sheet(1,0);
        sheet.setSheetName("第一个Sheet");
        /**创建一个表格**/
        Table table = new Table(1);
        List<List<String>> headList = Lists.newArrayList();
        List<String> headTitle0 = Lists.newArrayList(),
                headTitle11 = Lists.newArrayList();
        headTitle0.add("托运单号");
        headList.add(headTitle0);
        headTitle1.add("托运单状态");
        headList.add(headTitle1);
        
        /**动态加载费用表头**/
        List<FeeItemVO>  feeItemVOList = feeItemApi.listFeeItemByType(companyId, "运输配送类");
       
        feeItemVOList.stream().forEach(feeItemVO -> {
            List<String> headTitleN = Lists.newArrayList();
            headTitleN.add("营收");
            headTitleN.add(feeItemVO.getFeeName());
            headList.add(headTitleN);
        });
    
        headTitle9.add("运输/配送成本");
        headTitle9.add("承运商");
        headList.add(headTitle9);
        feeItemVOList.stream().forEach(feeItemVO -> {
            List<String> headTitleN = Lists.newArrayList();
            headTitleN.add("运输/配送成本");
            headTitleN.add(feeItemVO.getFeeName());
            headList.add(headTitleN);
        });
        headTitle10.add("毛利");
        headList.add(headTitle10);
        headTitle11.add("毛利率");
        headList.add(headTitle11);
        table.setHead(headList);

        /** 所有数据行集合 **/
        List<List<Object>> list = Lists.newArrayList();
        BaseResultVo resultVo = list(dto, null);
		PageInfo<Map<String, Object>> mapPageInfo = (PageInfo<Map<String, Object>>)resultVo.getData();
		if (null != mapPageInfo || !CollectionUtils.isEmpty(mapPageInfo.getList())) {
			List<Map<String, Object>> mapList = mapPageInfo.getList();
			for (int i = 0; i < mapList.size(); i++) {
				/**第 n 行的数据**/
				List<Object> row = Lists.newArrayList();
				row.add(mapList.get(i).get("billNo"));
				row.add(mapList.get(i).get("status"));
				/**根据费用名称 找费用*/
				for (FeeItemVO feeItemVO : feeItemVOList) {
					row.add(mapList.get(i).get(BillFeeEnums.FeeTypeEnum.revenue.getId()+ feeItemVO.getFeeName()));
				}
				list.add(row);
			}
		}
		/**汇总合计行*/
		List<Object> row = Lists.newArrayList();
		dto.setTitleType(BillFeeEnums.TitleTypeEnum.detail.getId());
		BaseResultVo totalFeeResultVo = listTotalFee(dto);

		Map<String, Object> totalFeeMap = (Map<String, Object>)totalFeeResultVo.getData();
		row.add("合计");
		row.add("");
		/**单据营收： 提货费:80.00,干线费:50.00,配送费:100.00**/
		for (FeeItemVO feeItemVO : feeItemVOList) {
			row.add(totalFeeMap.get(BillFeeEnums.FeeTypeEnum.revenue.getId()+ feeItemVO.getFeeName()));
		}
        //todo 该行的其他数据
		list.add(row);
        String fileName = ExcelUtils.generatorFileName("fee_detail");
        ExcelWriter excelWriter =new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        excelWriter.write1(list,sheet,table);
        /**表头合并、毛利、毛利率合并**/
        excelWriter.merge(0,1,0,0);
        excelWriter.merge(0,1,1,1);
		int length = 7 + 3 * feeItemVOList.size() + 2 + 1;
		excelWriter.merge(0,1,length,length);

		/**毛利报表 汇总行合并**/
		int lastRow = 1 + list.size();
		excelWriter.merge(lastRow,lastRow,0,7);
        /** 释放资源**/
        excelWriter.finish();
        System.out.println("ok");
        return null;
    }

 

生成excel文件名：

  public static String generatorFileName(String title) throws UnsupportedEncodingException {
        return URLEncoder.encode(String.format("%s%s", title,
                DateTimeFormatter.ofPattern("yyyyMMdd").format(LocalDateTime.now())), "UTF-8");
    }

HttpServletResponse设置：

private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
    try {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "no-store");
        response.addHeader("Cache-Control", "max-age=0");
        return response.getOutputStream();
    } catch (IOException e) {
        throw new Exception("导出excel表格失败!", e);
    }
}

 

Controller层：

@RequestMapping(value = "/exportFeeDetail",method = RequestMethod.GET)
	public BaseResultVo exportFeeDetail(@RequestParam(name = "no", required = false) String no,
										 @RequestParam(name = "customerId", required = false) String customerId,
										 @RequestParam(name = "statusList", required = false) String statusList,
										 HttpServletResponse response) throws Exception {
		BillFeeDTO dto = new BillFeeDTO();
		dto.setNo(no);
		if (Strings.isNotBlank(customerId)) {
			dto.setCustomerId(Long.parseLong(customerId));
		}

		if (StringUtil.isNotBlank(statusList)) {
			String[] statusArray = statusList.split("-");
			if (null != statusArray && statusArray.length > 0) {
				dto.setStatusList(Arrays.stream(statusArray).map(o->Integer.parseInt(o)).collect(Collectors.toList()));
			}
		}
		return billFeeService.exportFeeDetail(dto, response);
	}

注意：

1.Controller层，get请求。

2.请求参数，如果不是必填，需要加入@RequestParam(name = "no", required = false) String no，required = false限制。

3.状态列表，不能 @RequestParam(name = "statusList", required = false) List<Integer> statusList,前端转义错误。

要使用：@RequestParam(name = "statusList", required = false) String statusList；可以与前端约定值使用"-"分隔。

4.用postman测试导出excel，选择“send and download”。

5.合并单元格，需要数清楚第几列，然后使用

merge(int firstRow, int lastRow, int firstCol, int lastCol) 。

excelWriter.merge(0,1,col,col)，把第col列的，第一行和第二行合并。

6.二级表头：

List<List<String>> headList = Lists.newArrayList();

List<String> headTitle8 = Lists.newArrayList();

headTitle8.add("提货成本");
headTitle8.add("承运商");
headList.add(headTitle8);