easyexcel导出excel 简单实现

controller

@ApiOperation(value = "查询导出", notes = "查询导出")
@PostMapping("getOutExcel")
public void getOutExcel(@RequestBody ComplexVo complexVo, HttpServletResponse response) throws Exception {
	complexService.getOutExcel(complexVo, response);
}
1
2
3
4
5

service

public void getOutExcel(ComplexVo complexVo, HttpServletResponse response)throws Exception;;
1

@Override
public void getOutExcel(ComplexVo complexVo, HttpServletResponse response) throws Exception {
        try {
        //查询数据sql
            List<ComplexDTO> complexDTOS = complexMapper.queryComplexByOrgnCode(complexVo);
            List<Sys1Sjzd> dmzs = dataManager.getDataDict(complexVo.getOrgnCode(), "1", null, 1);
            String isOpen = dataManager.getConfigValue(complexVo.getOrgnCode(), "C8");
            if (CommonUtils.listNotBlank(complexDTOS)) {
                for (ComplexDTO item : complexDTOS) {
                    item.setAge(AgeUtil.getAgeDetail(item.getBirthday(), null));
                    if (StringUtils.isNotBlank(item.getInWardDate()) && StringUtils.isNotBlank(item.getOutAreaDate())) {
                        item.setHospDays(String.valueOf(DateApiUtils.getDiffDay(item.getInWardDate(), item.getOutAreaDate(), isOpen)));
                    }
                    if (StringUtils.isNotBlank(item.getOutHospType())) {
                        List<Sys1Sjzd> dmz = dmzs.stream().filter(p -> item.getOutHospType().equals(p.getXmdm())).collect(Collectors.toList());
                        item.setOutHospType(dmz.get(0).getDmz());
                    }
                    if (StringUtils.isBlank(item.getArchiveStatus())) {
                        item.setArchiveStatus("0");
                    }
                }
                EasyExcel.write(getOutputStream("查询人员信息", response), ComplexDTO.class)
                        .autoCloseStream(false)//不要自动关闭，交给 Servlet 自己处理
                        //.autoCloseStream(Boolean.TRUE)//适情况处理
                        .sheet("人员信息")
                        .doWrite(complexDTOS);
            }
            //ExportXlsxUtil.writeXlsx(complexDTOS, ComplexDTO.class, "查询列表", response);
        } catch (Exception e) {
            //throw new RuntimeException(e);
            log.error("导出异常",e);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

DTO元素字段部分注解

@ApiModel
@Data
@ExcelIgnoreUnannotated
@HeadRowHeight(value = 30)
@ContentRowHeight(value = 25)
@ColumnWidth(value = 20)
public class ComplexDTO implements Serializable {

    @ExcelProperty(value = "床位", index = 2)
    private String bedNo;

    private String id;
    private String rylbmc;

    @ExcelProperty(value = "人员编码号", index = 9)
    private String ryNo;

    @ApiModelProperty("姓名")
    @ExcelProperty(value = "姓名", index = 3)
    private String patName;

    @ApiModelProperty("性别")
    @ExcelProperty(value = "性别", converter = SexStatusConverter.class, index = 4)
    private String sex;

    @ApiModelProperty("年龄")
    @ExcelProperty(value = "年龄", index = 5)
    private String age;
    
    @ExcelProperty(value = "状态",converter = CheckStatusConverter.class, index = 17)
    private String patStatus;

    @ExcelProperty(value="归档状态", converter = NullableConverter.class, index = 19)
    private String archiveStatus;
 }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

类型转换类

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import lombok.Data;

@Data
public class CheckStatusConverter implements Converter<String>  {
    @Override
    public Class supportJavaTypeKey() {
        return null;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return null;
    }

    /**
     * 将excel的数据类型转为java数据类型,用到补充
     *
     * @param cellData
     * @param excelContentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }

    /**
     * 转换值
     *
     * @param value 状态 0：入院登记,1：病区分床,2：病区出院 3：病人出院,4：取消结算,5：进入ICU 6：进入产房,7：转科状态,8：数据转出 9：作废记录
     * @param excelContentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public CellData convertToExcelData(String value, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        String str = "入院登记";
        if ("1".equals(value)) {
            str = "病区分床";
        } else if ("2".equals(value)) {
            str = "病区出院";
        } else if ("3".equals(value)) {
            str = "病人出院";
        } else if ("4".equals(value)) {
            str = "取消结算";
        } else if ("5".equals(value)) {
            str = "进入ICU";
        } else if ("6".equals(value)) {
            str = "进入产房";
        } else if ("7".equals(value)) {
            str = "转科状态";
        } else if ("8".equals(value)) {
            str = "数据转出";
        }  else if ("9".equals(value)) {
            str = "作废记录";
        }
        return new CellData<>(str);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68