poi-导入导出excl表格信息

62 阅读 0 评论 41 点赞

我是靠谱客的博主健康萝莉，最近开发中收集的这篇文章主要介绍poi-导入导出excl表格信息，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1.引入相关依赖包（注意jar冲突）:


```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>

2.通过固定模板导出数据：

@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);
    }

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;
}

3.2 PoiBaseView.render导出


```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);
    }

4.excl导入数据，通过文件流

 ImportParams params = new ImportParams();
        params.setTitleRows(0);
        params.setHeadRows(1);
        List<HaExcelApplicationDTO> oldList = ExcelImportUtil.importExcel(
                    file.getInputStream(),
                    HaExcelApplicationDTO.class, params);