欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

使用autopoi导出和导入Excel数据

程序员文章站 2022-06-17 08:18:10
开发项目中经常使用到Excel文件的导入和导出,今天就来总结一下使用autopoi做导出和导入Excel数据添加依赖 org.jeecgframework autopoi-web 1.0.5 ...

开发项目中经常使用到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,"导出投标单位模板");
    }

模板样例
使用autopoi导出和导入Excel数据
二、导出表头变化数据
使用autopoi导出和导入Excel数据
解决这类数据并且专家数量是变化的情况就得用到另外一个方法,查看源码可以看到这样一个方法,那么只需要组装一下数据即可
使用autopoi导出和导入Excel数据
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为表头显示内容
使用autopoi导出和导入Excel数据
Map<String, Object>数据
其中map中key值为ExcelExportEntity 下标,这样才能是数据一一对应,map中的value就是数据
使用autopoi导出和导入Excel数据
要实现分组效果使用autopoi导出和导入Excel数据
只需要在ExcelExportEntity 中添加groupName和多加一个ExcelExportEntity 实体在subColumnList中添加子数据的下标key即可(此处的key在map数据中无需有对应的key值)

使用autopoi导出和导入Excel数据
使用autopoi导出和导入Excel数据
模板样例
使用autopoi导出和导入Excel数据
三、合并行列单元格
使用autopoi导出和导入Excel数据
代码

@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表格
使用autopoi导出和导入Excel数据
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;
}

源码中的导入方法
使用autopoi导出和导入Excel数据
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

相关标签: Excel操作 java