java导出excel文件2

0. 使用的jar包:

        <dependency>
            <groupId>com.github.andyczy</groupId>
            <artifactId>java-excel-utils</artifactId>
            <version>4.0</version>
        </dependency>

最终结果: 根据response直接流下载,不会重复生成文件上传到oss到下载!

 

1.service层下载方法:

@Override
    public void downloadBillExcel(HttpServletResponse response, PageMapperDto mapperDto) {
        try {
            List<OrderBillingPageResponseDto> responseDtoList = crudMapper.findOrderBillingPage(mapperDto);
            log.info("条件查询账单:{}", responseDtoList.size());

            String fileName = String.format(OrderBillingConstants.STRING_FORMAT_2, "账单信息汇总",
                    BankUtils.getCurrentDateTime());
            log.info("准备生成的账单excel文件名称:{}", fileName);

            List<String[]> sheetDataList = new ArrayList<>();
            String[] title = Stream
                    .of("订单编号", "账单期数", "账单日期", "承租人姓名", "申请时间", "分公司", "分期金额", "应还本金", "应还利息", "逾期违约金", "逾期违约罚息",
                            "代扣结果", "资方业务状态", "账单标记", "账单状态", "是否已逾期", "实际还款日期", "已还月租", "已还本金", "已还利息", "已还违约金",
                            "已还罚息", "待还月租", "待还本金", "待还利息", "待还违约金", "待还罚息")
                    .toArray(String[]::new);
            sheetDataList.add(title);

            int size = responseDtoList.size();
            if (CollectionUtils.isNotEmpty(responseDtoList)) {
                for (int i = 0; i < size; i++) {
                    OrderBillingPageResponseDto responseDto = responseDtoList.get(i);
                    sheetDataList.add(orderBillingPageResponseDtoToStringArray(responseDto));
                }
            }
            List<List<String[]>> dataList = new ArrayList<>();
            dataList.add(sheetDataList);

            HashMap<Integer, HashMap<Integer, Integer>> mapColumnWidth = new HashMap<>();
            HashMap<Integer, Integer> mapColumn = new HashMap<>();
            for (int i = 0; i < title.length; i++) {
                if (i == 0 || i == 4 || i == 5) {
                    mapColumn.put(i, 20);
                } else {
                    mapColumn.put(i, 10);
                }
            }
            // 第一个sheet页单元格列宽
            mapColumnWidth.put(1, mapColumn);

            ExcelUtil.downloadExcel(response, fileName, null, new String[] { "账单信息" }, dataList, null, mapColumnWidth);
        } catch (Exception e) {
            log.error("Exception:{}", e);
            ExcelUtil.downloadExceptionExcel(response, e);
        }
    }

2. 根据响应对象生成数组的方法orderBillingPageResponseDtoToStringArray:

private String[] orderBillingPageResponseDtoToStringArray(OrderBillingPageResponseDto responseDto) {
        String applyTimeString = StringUtils.EMPTY;
        if (null != responseDto.getApplyTime()) {
            applyTimeString = DateUtils.formatDate(responseDto.getApplyTime(), VehicleConstants.TIANYI_TIME_FORMAT);
        }

        BillStateEnum billStateEnum = BillStateEnum.getEnumByCode(responseDto.getState());
        String billStateString = StringUtils.EMPTY;
        if (billStateEnum != null) {
            billStateString = billStateEnum.getMsg();
        }
        String billFlagString = StringUtils.EMPTY;
        BillFlagEnum billFlagEnum = BillFlagEnum.getEnumByCode(responseDto.getFlag());
        if (billFlagEnum != null) {
            billFlagString = billFlagEnum.getMsg();
        }

        String billStatusString = StringUtils.EMPTY;
        BillStatusEnum billStatusEnum = BillStatusEnum.getEnumByCode(responseDto.getStatus());
        if (billStatusEnum != null) {
            billStatusString = billStatusEnum.getMsg();
        }

        String isOverdueString = StringUtils.EMPTY;
        YesNoEnum yesNoEnum = YesNoEnum.getEnumByCode(responseDto.getIsOverdue());
        if (yesNoEnum != null) {
            isOverdueString = yesNoEnum.getMsg();
        }

        String billRepayDtString = StringUtils.EMPTY;
        if (null != responseDto.getBillRepayDt()) {
            billRepayDtString = DateUtils.formatDate(responseDto.getBillRepayDt(), OrderConstant.TIME_FORMAT);
        }

        String[] result = new String[27];
        result[0] = responseDto.getOrderCode();
        result[1] = String.valueOf(responseDto.getBillPeriod());
        result[2] = DateUtils.formatDate(responseDto.getBillDt(), OrderConstant.TIME_FORMAT);
        result[3] = String.valueOf(responseDto.getUserName());
        result[4] = applyTimeString;
        result[5] = responseDto.getOrganName();
        result[6] = String.valueOf(responseDto.getBillFee());
        result[7] = String.valueOf(responseDto.getBillCapital());
        result[8] = String.valueOf(responseDto.getBillInterest());
        result[9] = String.valueOf(responseDto.getOverdueContractAmt());
        result[10] = String.valueOf(responseDto.getOverduePenalty());
        result[11] = responseDto.getWithholdResult();
        result[12] = billStateString;
        result[13] = billFlagString;
        result[14] = billStatusString;
        result[15] = isOverdueString;
        result[16] = billRepayDtString;
        result[17] = String.valueOf(responseDto.getPaidFee());
        result[18] = String.valueOf(responseDto.getBillCapital().subtract(responseDto.getUnpaidCapital()));
        result[19] = String.valueOf(responseDto.getBillInterest().subtract(responseDto.getUnpaidIntrest()));
        result[20] = String.valueOf(responseDto.getOverdueContractAmt().subtract(responseDto.getUnpaidContractAmt()));
        result[21] = String.valueOf(responseDto.getOverduePenalty().subtract(responseDto.getUnpaidPenalty()));
        result[22] = String.valueOf(responseDto.getUnpaidFee());
        result[23] = String.valueOf(responseDto.getUnpaidCapital());
        result[24] = String.valueOf(responseDto.getUnpaidIntrest());
        result[25] = String.valueOf(responseDto.getUnpaidContractAmt());
        result[26] = String.valueOf(responseDto.getUnpaidPenalty());
        return result;
    }

3.excel的方法:

/**
     * 下载excel - web
     *
     * @param response 请求返回
     * @param fileName 文件名
     * @param labelNameList 标签名列表
     * @param sheetNameList sheet列表
     * @param dataList 数据
     * @param regionMap 单元格合并
     * @param mapColumnWidth 自定义列宽
     */
    public static void downloadExcel(HttpServletResponse response, String fileName, String[] labelNameList,
                                     String[] sheetNameList, List<List<String[]>> dataList, HashMap regionMap,
                                     HashMap mapColumnWidth) {
        ExcelUtils excelUtils = ExcelUtils.initialization();
        excelUtils.setLabelName(labelNameList);
        excelUtils.setDataLists(dataList);
        excelUtils.setFileName(fileName);
        excelUtils.setResponse(response);
        excelUtils.setSheetName(sheetNameList);
        excelUtils.setRegionMap(regionMap);
        excelUtils.setMapColumnWidth(mapColumnWidth);
        excelUtils.exportForExcelsOptimize();
    }