使用autopoi导出和导入Excel数据

开发项目中经常使用到Excel文件的导入和导出，今天就来总结一下使用autopoi做导出和导入Excel数据

添加依赖

<dependency>
    <groupId>org.jeecgframework</groupId>
    <artifactId>autopoi-web</artifactId>
    <version>1.0.5</version>
    <exclusions>
        <exclusion>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
        </exclusion>
    </exclusions>
</dependency>

exclusions 是将 commons-codec 从 autopoi 中排除，避免冲突

一、导出实体类List数据
1.创建实体类，添加注解

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.springframework.format.annotation.DateTimeFormat;

import javax.validation.constraints.NotEmpty;
import javax.validation.constraints.NotNull;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

@Setter
@Getter
@ToString
@ApiModel(value = "CandidateParamAdd", description = "投标情况-投标人添加参数")
public class CandidateParamAdd implements Serializable {

    @ApiModelProperty(value = "投标单位名称", required = true)
    @NotEmpty(message = "投标单位名称不能为空")
    @Excel(name="投标单位名称",width=15)
    private String bidUnitName;

    @ApiModelProperty(value = "机构代码")
    @NotNull(message = "社会统一信用代码不能为空")
    @Excel(name="机构代码",width=15)
    private String mainUidCode;

    @ApiModelProperty(value = "保证金缴纳情况", required = true)
    @NotNull(message = "保证金缴纳情况不能为空")
    @Excel(name="保证金缴纳情况",width=15)
    private String bondPayStatus;

    //格式化时间
    @ApiModelProperty(value = "招标文件获取时间", required = true)
    @NotNull(message = "招标文件获取时间不能为空")
    @Excel(name="招标文件获取时间",width=15,format = "yyyy-MM-dd")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd")
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date dataSubmitEndTime;

    @Excel(name="招标文件费(元)",width=15)
    @ApiModelProperty(value = "招标文件费(元)")
    private BigDecimal fileFee;
}

2.导出工具类

public class ExcleUtil {
	public static void createExecl(Workbook workbook, HttpServletRequest request, HttpServletResponse response,String name){
        if (workbook instanceof HSSFWorkbook) {
            name = name + ".xls";
        } else {
            name = name + ".xlsx";
        }
        ServletOutputStream out = null;
        ByteArrayOutputStream baos = null;
        try {
            out = response.getOutputStream();
            response.reset();
            String userAgent = request.getHeader("user-agent");
            userAgent = userAgent ==null?"":userAgent.toLowerCase();
            //针对IE或者以IE为内核的浏览器：
            if(userAgent.contains("msie") ||userAgent.contains("trident")){
                name = URLEncoder.encode(name, "UTF-8");
            }else{
                name = new String(name.getBytes(), "iso-8859-1");
            }
            response.setContentType("application/force-download");
            response.addHeader("Content-Disposition", "attachment;fileName=" + name);
            baos = new ByteArrayOutputStream();
            workbook.write(baos);
            response.setContentLengthLong(baos.size());
            out.write(baos.toByteArray());
        } catch (IOException e) {
            throw new BootException(e.getMessage(),e);
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
                if (baos != null) {
                    baos.close();
                }
            } catch (IOException e) {
                throw new BootException(e.getMessage(),e);
            }
        }
    }
protected static void exportXls( HttpServletResponse response, Workbook wb) {
        String codedFileName = "临时文件";
        if (wb instanceof HSSFWorkbook) {
            codedFileName = codedFileName + ".xls";
        } else {
            codedFileName = codedFileName + ".xlsx";
        }
        ServletOutputStream out;
        try {
            out = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename=" + oConvertUtils.toUtf8String(codedFileName));
            response.setContentType("application/vnd.ms-excel");
            wb.write(out);
            out.close();
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }
}

3.导出（填充数据）

@GetMapping(value = "/exportCandidateTempt")
@ApiOperation(value = "导出投标单位模板", notes = "导出投标单位模板")
    public void exportCandidateTempt(HttpServletRequest request, HttpServletResponse response) {
        CandidateParamAdd candidateParamAdd = new CandidateParamAdd();
        candidateParamAdd.setBidUnitName("A公司");
        candidateParamAdd.setMainUidCode("20210101");
        candidateParamAdd.setBondPayStatus("6573.00");
        candidateParamAdd.setDataSubmitEndTime(new Date());
        candidateParamAdd.setFileFee(new BigDecimal(123.00));
        //此处的list可以是自己从数据库中查询的数据
        List<CandidateParamAdd>  candidateParamAdds = Lists.newArrayList(candidateParamAdd);
        //此实体类用来设置一些属性
        //entity.setTitle("设置标题");
        //entity.setSecondTitle("设置子标题");
        ExportParams entity = new ExportParams();
        entity.setSheetName("投标情况模板");
        Workbook workbook = ExcelExportUtil.exportExcel(entity, CandidateParamAdd.class,candidateParamAdds);
        ExcleUtil.createExecl(workbook,request,response,"导出投标单位模板");
    }

模板样例

二、导出表头变化数据

解决这类数据并且专家数量是变化的情况就得用到另外一个方法，查看源码可以看到这样一个方法，那么只需要组装一下数据即可

1.导出方法

@ApiOperation(value = "代理端评审汇总-导出", notes = "代理端评审汇总-导出")
@GetMapping(value = "/unitexport")
    public void unitExportXls(BidUnitParamList bidUnit, HttpServletResponse response) {
    	//此处Pair非必须，只要能得到List<Map<String, Object>和List<ExcelExportEntity>就行
        Pair<List<Map<String, Object>>, List<ExcelExportEntity>> pair = this.bidUnitService.agencyExpert(bidUnit);
        Workbook wb = ExcelExportUtil.exportExcel(new ExportParams("标题", "二级标题", "左下角sheet名字"), pair.getValue(), pair.getKey());
        Sheet sheet = wb.getSheet(bidUnit.getTitle());
        sheet.setAutobreaks(true);
        ExcleUtil.exportXls(response, wb);
    }

ExportParams是用来设置一些属性的，例如标题等，List<ExcelExportEntity 里面的数据主要是表头和设置分组之类的，List<Map<String, Object>为数据

ExcelExportEntity 数据
key为下标，name为表头显示内容

Map<String, Object>数据
其中map中key值为ExcelExportEntity 下标，这样才能是数据一一对应，map中的value就是数据

要实现分组效果
只需要在ExcelExportEntity 中添加groupName和多加一个ExcelExportEntity 实体在subColumnList中添加子数据的下标key即可（此处的key在map数据中无需有对应的key值）

模板样例

三、合并行列单元格

代码

@ApiOperation(value = "所有投标单位表", notes = "所有投标单位表")
@GetMapping(value = "/exportBidOpenSignIn")
    public void exportXls(@RequestParam(name = "bidSectionId",required = true) Long bidSectionId,HttpServletRequest request, HttpServletResponse response) {
        List<BidOpenSignInExportDTO> bidOpenSignInExportDTOS = bidOpenCloseService.queryDetailBySectionId(bidSectionId);
        BidSectionDTO dto = bidSectionService.getDTO(bidSectionId);
        if (Objects.nonNull(dto)) {
            ExportParams entity = new ExportParams();
            entity.setTitle("所有投标单位表");
            entity.setSecondTitle("标段编号:" + dto.getBidSectionNo() + "     标段名称:" + dto.getBidSectionName());
            entity.setSheetName("所有投标单位");
            Workbook workbook = ExcelExportUtil.exportExcel(entity,BidOpenSignInExportDTO.class,bidOpenSignInExportDTOS);
            //合并单元格操作开始
            Sheet sheet = workbook.getSheet("所有投标单位");
            CellRangeAddress region = new CellRangeAddress(3, 4, 0, 0);
            sheet.addMergedRegion(region);
            CellRangeAddress region1 = new CellRangeAddress(4, 5, 1, 2);
            sheet.addMergedRegion(region1);
            //合并单元格操作结束，复杂合并可能需要计算
            ExcleUtil.createExecl(workbook,request,response,"所有投标单位表");
        }
    }

四、创建多sheet表格

1.数据实体类(List<Map<String,Object>和List<ExcelExportEntity中数据同上)

@Data
@NoArgsConstructor
@Accessors(chain = true)
@ApiModel(value = "BidSectionVueTableInfoDTO", description = "活动表格返回数据")
public class BidSectionVueTableInfoDTO implements Serializable {
    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "数据")
    private List<Map<String,Object>> dataList;

    @ApiModelProperty(value = "表头")
    private List<ExcelExportEntity> titleList;

    @ApiModelProperty(value = "专家名称")
    private String expertName;

}

2.导出数据

@ApiOperation(value = "代理端评审详情（专家评分表）-导出", notes = "代理端评审汇总和评审详情（专家评分表）-导出")
@GetMapping(value = "/expertEvalDetailInfo")
    public void expertEvalInfo1(BidUnitParamList bidUnit, HttpServletResponse response) {
        List<BidSectionVueTableInfoDTO> pair = bidUnitService.expertEvalDetailInfo(bidUnit);
        Workbook wb = new HSSFWorkbook();
        pair.forEach(e -> {
            new ExcelExportServer().createSheetForMap(wb, new ExportParams("标题", "子标题", "sheet名字"), pair.getTitlList, pair.getDataList());
        });
        ExcleUtil.exportXls(response, wb);
    }

四、从Excel导入数据
1.创建实体类
记住：@Excel(name = “标题”) name中的文字对应表格中表头的文字，一模一样

import java.io.Serializable;

import com.anhuibidding.common.aspect.annotation.Dict;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import com.fasterxml.jackson.annotation.JsonFormat;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.springframework.format.annotation.DateTimeFormat;

/**
 * @Description: 系统通告表
 * @Author: jeecg-boot
 * @Date:  2019-01-02
 * @Version: V1.0
 */
@Data
@TableName("sys_announcement")
public class SysAnnouncement implements Serializable {
    private static final long serialVersionUID = 1L;

    /**
     * id
     */
    @TableId(type = IdType.ID_WORKER_STR)
    private java.lang.String id;
    /**
     * 标题
     */
    @Excel(name = "标题", width = 15)
    private java.lang.String title;
    /**
     * 内容
     */
    @Excel(name = "内容", width = 30)
    private java.lang.String msgContent;
    /**
     * 开始时间
     */
    @Excel(name = "开始时间", width = 15, format = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private java.util.Date startTime;
    /**
     * 结束时间
     */
    @Excel(name = "结束时间", width = 15, format = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private java.util.Date endTime;
    /**
     * 发布人
     */
    @Excel(name = "发布人", width = 15)
    private java.lang.String sender;
    /**
     * 优先级（L低，M中，H高）
     */
    @Excel(name = "优先级", width = 15, dicCode = "priority")
    @Dict(dicCode = "priority")
    private java.lang.String priority;
    
    /**
     * 消息类型1:通知公告2:系统消息
     */
    @Excel(name = "消息类型", width = 15, dicCode = "msg_category")
    @Dict(dicCode = "msg_category")
    private java.lang.String msgCategory;
    /**
     * 通告对象类型（USER:指定用户，ALL:全体用户）
     */
    @Excel(name = "通告对象类型", width = 15, dicCode = "msg_type")
    @Dict(dicCode = "msg_type")
    private java.lang.String msgType;
    /**
     * 发布状态（0未发布，1已发布，2已撤销）
     */
    @Excel(name = "发布状态", width = 15, dicCode = "send_status")
    @Dict(dicCode = "send_status")
    private java.lang.String sendStatus;
    /**
     * 发布时间
     */
    @Excel(name = "发布时间", width = 15, format = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private java.util.Date sendTime;
    /**
     * 撤销时间
     */
    @Excel(name = "撤销时间", width = 15, format = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private java.util.Date cancelTime;
    /**
     * 删除状态（0，正常，1已删除）
     */
    private java.lang.String delFlag;
    /**
     * 创建人
     */
    private java.lang.String createBy;
    /**
     * 创建时间
     */
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private java.util.Date createTime;
    /**
     * 更新人
     */
    private java.lang.String updateBy;
    /**
     * 更新时间
     */
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private java.util.Date updateTime;
    /**
     * 指定用户
     **/
    private java.lang.String userIds;
    /**
     * 业务类型(email:邮件 bpm:流程)
     */
    private java.lang.String busType;
    /**
     * 业务id
     */
    private java.lang.String busId;
    /**
     * 打开方式 组件：component 路由：url
     */
    private java.lang.String openType;
    /**
     * 组件/路由 地址
     */
    private java.lang.String openPage;
}

源码中的导入方法

2.导入数据

@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
public Result<?> importExcel(HttpServletRequest request, HttpServletResponse response) {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            MultipartFile file = entity.getValue();// 获取上传文件对象
            ImportParams params = new ImportParams();
            params.setTitleRows(2);
            params.setHeadRows(1);
            params.setNeedSave(true);
            try {
            	//使用api获取到的List数据
                List<SysAnnouncement> listSysAnnouncements = ExcelImportUtil.importExcel(file.getInputStream(), SysAnnouncement.class, params);
                //执行自己的保存方法开始
                for (SysAnnouncement sysAnnouncementExcel : listSysAnnouncements) {
                	if(sysAnnouncementExcel.getDelFlag()==null){
                		sysAnnouncementExcel.setDelFlag(CommonConstant.DEL_FLAG_0.toString());
					}
                    sysAnnouncementService.save(sysAnnouncementExcel);
                }
                //执行自己的保存方法结束
                return Result.ok("文件导入成功！数据行数：" + listSysAnnouncements.size());
            } catch (Exception e) {
                log.error(e.getMessage(),e);
                return Result.error("文件导入失败！");
            } finally {
                try {
                    file.getInputStream().close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return Result.error("文件导入失败！");
    }

本文地址：https://blog.csdn.net/weixin_49051190/article/details/112213522