easyExcel、easyExcel解决多sheet导出生成文件在项目根目录问题

1. easyExcel简单使用

/**
 * id
 */
@ExcelIgnore
@ApiModelProperty(value = "id")
private Integer id;
 
/**
 * 系统名称
 */
@ColumnWidth(15)
@ExcelProperty(value = "系统名称" ,index = 0)
@ApiModelProperty(value = "系统名称")
private String sysName;

/**
     * 分页数据导出
     * @return
     */
    @GetMapping("/exportSystemPage")
    @ApiOperation(value = "系统分页数据导出", response = SystemPageDto.class)
    @ApiImplicitParams({
            @ApiImplicitParam(dataType = "String", name = "sysName", value = "系统名称"),
            @ApiImplicitParam(dataType = "Integer", name = "sysType", value = "系统类型：0-新建/1-已建"),
            @ApiImplicitParam(dataType = "Integer", name = "state", value = "状态：0-建设中、1-运行中、2-停用、3-其它"),
            @ApiImplicitParam(dataType = "String", name = "belongDept", value = "所属部门"),
            @ApiImplicitParam(dataType = "Integer", name = "category", value = "类别(0-存量系统、1-关联系统)")
    })
    public void exportSystemPage(HttpServletResponse response, String sysName, Integer sysType, Integer state,
                                     String belongDept, Integer category) throws UnsupportedEncodingException {
        // 用户信息
        // 获取这个人的身份，判断是不是管理中心帐号
        String userDeptCode = SessionContext.getUserDeptCode();
        String deptCode = null;
        if (!topDeptCode.startsWith(userDeptCode)) {
            deptCode = userDeptCode;
        }
        if (StringUtil.isNotBlank(sysName)) {
            sysName = URLDecoder.decode(sysName, StandardCharsets.UTF_8.name());
        }
        if (StringUtil.isNotBlank(belongDept)) {
            belongDept = URLDecoder.decode(belongDept, StandardCharsets.UTF_8.name());
        }

        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
            String date = sdf.format(new Date());
            List<SystemPageDto> systemList = systemService.getSystemList(sysName, sysType, state, belongDept, category, deptCode);
            String fileName = "系统列表-" + date + ".xlsx";
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8").replace("+", " "));
            EasyExcel.write(response.getOutputStream(), SystemPageDto.class).sheet("系统数据导出").doWrite(systemList);
        } catch (Exception e) {
            log.error("系统数据导出失败" + e.getMessage(), e);
        }
    }

<dependency>
   <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>

2. easyExcel解决多sheet导出的问题

package com.sdy.resdir.biz.dto;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.sdy.common.model.BaseModel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

/**
 * <p>
 * 部门下资源DTO
 * </p>
 *
 * @author cgj
 * @since 2021-09-01
 */
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@ApiModel(description="下载部门下资源DTO")
public class TestOneDTO extends BaseModel {

    @ExcelProperty(value = "部门名称" ,index = 0)
    @ApiModelProperty(value = "资源id")
    private Long resId;

    @ExcelProperty(value = "部门名称" ,index = 1)
    @ColumnWidth(20)
    @ApiModelProperty(value = "部门名称")
    private String deptName;


    @ExcelProperty(value = "资源名称" ,index = 2)
    @ColumnWidth(40)
    @ApiModelProperty(value = "资源名称")
    private String resName;

    @ExcelProperty(value = "资源类型" ,index = 3)
    @ColumnWidth(20)
    @ApiModelProperty(value = "资源类型")
    private String resTypeStr;

}

package com.sdy.resdir.biz.dto;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.sdy.common.model.BaseModel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

/**
 * <p>
 * 部门下资源DTO
 * </p>
 *
 * @author cgj
 * @since 2021-09-01
 */
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@ApiModel(description="下载部门下资源DTO")
public class TestTwoDTO extends BaseModel {

    @ExcelProperty(value = "部门名称" ,index = 0)
    @ApiModelProperty(value = "资源id")
    private Long resId;

    @ExcelProperty(value = "资源名称" ,index = 1)
    @ColumnWidth(40)
    @ApiModelProperty(value = "资源名称")
    private String resName;

    @ExcelProperty(value = "资源权限" ,index = 2)
    @ColumnWidth(10)
    @ApiModelProperty(value = "资源权限")
    private String resPowerStr;

    @ExcelProperty(value = "资源层级" ,index = 3)
    @ColumnWidth(10)
    @ApiModelProperty(value = "资源层级")
    private String resLevelStr;

}

@ApiOperation(value = "数源单位下资源下载")
    @GetMapping("/downLoadCollectDeptRes")
    public void downLoadCollectDeptRes(HttpServletResponse response) throws BizException {
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
            String date = sdf.format(new Date());
            String fileName = "资源列表-" + date + ".xlsx";
            // 这里 指定文件
            // ExcelWriter excelWriter = EasyExcel.write(fileName).build();
            List<RdResourceDir> rdResourceDirList = rdResourceDirService.list(Wrappers.<RdResourceDir>lambdaQuery()
                    .in(RdResourceDir::getId, 2, 3, 6, 8, 10));
            ArrayList<TestOneDTO> testOneDTOS = new ArrayList<>();
            ArrayList<TestTwoDTO> testTwoDTOS = new ArrayList<>();
            for (RdResourceDir rdResourceDir : rdResourceDirList) {
                TestOneDTO testOneDTO = new TestOneDTO();
                testOneDTO.setResId((long)rdResourceDir.getId());
                testOneDTO.setDeptName(rdResourceDir.getResSourceDept());
                testOneDTO.setResName(rdResourceDir.getResName());
                testOneDTO.setResTypeStr(String.valueOf(rdResourceDir.getResType()));
                testOneDTOS.add(testOneDTO);
                TestTwoDTO testTwoDTO = new TestTwoDTO();
                testTwoDTO.setResId((long)rdResourceDir.getId());
                testTwoDTO.setResName(rdResourceDir.getResName());
                testTwoDTO.setResPowerStr(String.valueOf(rdResourceDir.getResPower()));
                testTwoDTO.setResLevelStr(String.valueOf(rdResourceDir.getResLevel()));
                testTwoDTOS.add(testTwoDTO);
            }
            // ---------------------1-----------------------------
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8").replace("+", " "));
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

            WriteSheet writeSheet = EasyExcel.writerSheet(0, "one").head(TestOneDTO.class).build();
            excelWriter.write(testOneDTOS, writeSheet);
            // EasyExcel.write(response.getOutputStream(), TestOneDTO.class).sheet(0, "one").doWrite(testOneDTOS);
            WriteSheet writeSheetTwo = EasyExcel.writerSheet(1, "two").head(TestTwoDTO.class).build();
            excelWriter.write(testOneDTOS, writeSheetTwo);
            response.flushBuffer();
            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
        } catch (Exception e) {
            log.error("系统数据导出失败" + e.getMessage(), e);
        }
    }