poi-导入导出excl表格信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

```java
<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-spring-boot-starter</artifactId>
			<version>4.4.0</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>4.1.1</version>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.1.1</version>
		</dependency>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@RequestMapping("exportTemplateXls")
    public void  exportTemplateXls(ModelMap modelMap, HttpServletRequest request,
                                   HttpServletResponse response){
        TemplateExportParams params = new TemplateExportParams("exceltemplates/bulkImport.xlsx");
        Map<String, Object> result = new HashMap<>();
        result.put("mdeList",Lists.newArrayList());
        // 文件名
        modelMap.put(TemplateExcelConstants.FILE_NAME, "批量导入采集任务模板");
        // 参数
        modelMap.put(TemplateExcelConstants.PARAMS, params);
        // 数据
        modelMap.put(TemplateExcelConstants.MAP_DATA, result);
        PoiBaseView.render(modelMap, request, response, TemplateExcelConstants.EASYPOI_TEMPLATE_EXCEL_VIEW);
    }

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
3.通过java配置类导出表格数据
3.1 导出实体：

```java
package com.**;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import lombok.EqualsAndHashCode;

import javax.persistence.Column;
import java.math.BigDecimal;
import java.util.Date;

/**
 *
 *date:2022/7/1
 *description:票据主信息导入实体
 **/
@Data
@EqualsAndHashCode(callSuper = false)
public class HaExcelBillDTO {

    @Excel(name = "第三方报案号")
    private String channelApplicationId;

    @Excel(name = "票据号")
    private String medicalBillingNo;

    @Excel(name = "治疗类型")
    private String cureType;

    @Excel(name = "账单类型")
    private String billType;

    @Excel(name = "社保类型")
    private String  medicalInsuranceType;

    @Excel(name = "材料类型")
    private String documentType;

    @Excel(name = "票据类型")
    private String  einvoiceBillType;

    @Excel(name = "患者姓名")
    private String  insuredName;

    @Excel(name = "医院名称")
    private String hospitalName;

    @Excel(name = "就诊开始日期",format="yyyy-MM-dd")
    private Date medicalStartDate;

    @Excel(name = "就诊结束日期",format="yyyy-MM-dd")
    private Date medicalEndDate;

    @Excel(name = "就诊天数")
    private String medicalCount;

    @Excel(name = "金额")
    private BigDecimal feeAmount;

    @Excel(name = "自费")
    private BigDecimal ownexpenseAmount;

    @Excel(name = "分类自负")
    private BigDecimal selfpayAmount;

    @Excel(name = "医保支付")
    private BigDecimal medicareAmount;

    @Excel(name = "其他第三方支付")
    private BigDecimal otherAmount;

    @Excel(name = "主诊断名称")
    private String primaryDiseaseName;

    @Excel(name = "科室")
    private String hospitalDepartment;

    @Excel(name = "手术")
    private String primaryOperationName;

    @Excel(name = "医生")
    private String doctor;

    @Excel(name = "大项名称")
    private String feeCategory;

    @Excel(name = "大项金额")
    private BigDecimal feeFeeAmount;

    @Excel(name = "大项自费金额")
    private BigDecimal feeOwnexpenseAmount;

    @Excel(name = "大项分类自负金额")
    private BigDecimal feeSelfpayAmount;

    @Excel(name = "大项医保支付金额")
    private BigDecimal feeMedicareAmount;

    @Excel(name = "大项其他第三方支付金额")
    private BigDecimal feeOtherAmount;
}

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

```java
 @RequestMapping(value ="exportBillInfo")
    public void exportBillInfo(ModelMap modelMap, HttpServletRequest request,
                               HttpServletResponse response,ClaimInfoDTO claimQueryDTO){
        List<HaExcelBillDTO> resultList = Lists.newArrayList();
        String title ="此次查询数据不满足导出条件,有未处理完成案件!";
        try {
            List<HaClaimApplicationDO> list = batchImportService.queryImportListNoPage(claimQueryDTO);
            if(CollectionUtils.isEmpty(list)){
                export(modelMap,request,response,title,"票据信息",resultList,HaExcelBillDTO.class,"票据信息导出列表");
                return;
            }
            Optional<HaClaimApplicationDO> isExport = list.stream().filter(l -> !StringUtils.equalsIgnoreCase(l.getStatus(), "4")).findFirst();
            if(!isExport.isPresent()){
                title ="票据列表";
                resultList = medicalBillingRepo.queryBillAndFeeByReportNo(claimQueryDTO);
                if(CollectionUtils.isNotEmpty(resultList)){
                    resultList.forEach(l->{
                        l.setCureType(CureTypeEnum.getTypeName(l.getCureType()));
                        l.setMedicalInsuranceType(SocialSecurityTypeEnum.getTypeName(l.getMedicalInsuranceType()));
                        l.setBillType(BillTypeEnum.getTypeName(l.getBillType()));
                        l.setDocumentType(DocumentTypeEnum.getName(l.getDocumentType()));
                        l.setEinvoiceBillType(EinBillTypeEnum.getName(l.getEinvoiceBillType()));
                    });
                }
            }
        } catch (Exception e) {
            log.info("导出票据信息报错{}",e.getMessage());
            export(modelMap,request,response,e.getMessage(),"票据信息",resultList,HaExcelBillDTO.class,"票据信息导出列表");
            return ;
        }
        export(modelMap,request,response,title,"票据信息",resultList,HaExcelBillDTO.class,"票据信息导出列表");
    }
```public void export(ModelMap modelMap, HttpServletRequest request,
                     HttpServletResponse response ,String title, String sheetName , List resultlLst, Class tClass, String fileName){
        ExportParams params = new ExportParams(title,sheetName, ExcelType.XSSF);

        modelMap.put(NormalExcelConstants.DATA_LIST,resultlLst);

        modelMap.put(NormalExcelConstants.CLASS,tClass);

        modelMap.put(NormalExcelConstants.PARAMS,params);

        modelMap.put(NormalExcelConstants.FILE_NAME,fileName);

        PoiBaseView.render(modelMap,request,response,NormalExcelConstants.EASYPOI_EXCEL_VIEW);
    }

1
2
3
4
5
6
7
 ImportParams params = new ImportParams();
        params.setTitleRows(0);
        params.setHeadRows(1);
        List<HaExcelApplicationDTO> oldList = ExcelImportUtil.importExcel(
                    file.getInputStream(),
                    HaExcelApplicationDTO.class, params);