Springboot基于easyexcel实现一个excel文件包含多个sheet表格的数据导出_easyexcel导出多个sheet

前言

EasyExcel 是一款基于Java的开源Excel操作工具，它提供了简单且强大的 API，使开发人员可以轻松地读写、操作和生成Excel文件。

EasyExcel 支持 Excel 文件的导入和导出，可以处理大量数据，具有高性能和低内存占用。它可以读取 Excel 文件中的数据，并将数据转换为 Java 对象，也可以将Java对象写入Excel文件。

EasyExcel 还提供了丰富的格式化选项和功能，如设置单元格样式、合并单元格、设置公式等。同时，EasyExcel 还支持多线程操作，可以在处理大量数据时提高处理效率。由于其简单易用的特点，EasyExcel 被广泛应用于数据导入导出、报表生成、数据分析等领域。

一、引入easyexcel依赖

             <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>3.2.1</version>
                <exclusions>
                    <exclusion>
                        <groupId>poi-ooxml-schemas</groupId>
                        <artifactId>org.apache.poi</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>

二、创建实体类

package com.ruoyi.exportData.vo;
 
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
 
@Data
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@ColumnWidth(25)
@ContentRowHeight(30)
@HeadRowHeight(50)
@Builder
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
@HeadFontStyle(fontHeightInPoints = 12)
@ContentFontStyle(fontHeightInPoints = 11)
public class ExportListVo {
    @ExcelProperty(value = "名称")
    private String name;
    @ExcelProperty(value = "数据")
    private String value;
}

easyexcel常用注解

@ExcelProperty 用于标识excel中的字段，可以指定字段在Excel中的列索引或列名

@ColumnWith:：设置列宽

@ColumnWidth： 全局列宽

@ContentFontStyle： 用于设置单元格内容字体格式的注解

@ContentLoopMerge：用于设置合并单元格

@ContentRowHeight：用于设置行高

@ContentStyle：设置内容格式

@HeadFontStyle：用于定制标题字体格式

@HeadRowHeight：设置标题行行高

@HeadStyle：设置标题样式

@ExcelIgnore：不将该字段转换成Excel

@ExcelIgnoreUnannotated：没有注解的字段都不转换

三、controller层接口

package com.ruoyi.web.controller.zx.export;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.ruoyi.baseconsulttopics.service.IBaseconsultTopicsService;
import com.ruoyi.committee.service.ICommitteeService;
import com.ruoyi.common.domain.ResultVo;
import com.ruoyi.document.service.IDocumentService;
import com.ruoyi.exportData.vo.ExportListVo;
import com.ruoyi.meet.service.IMeetService;
import com.ruoyi.network.service.INetworkService;
import com.ruoyi.news.service.INewsService;
import com.ruoyi.proposal.service.IProposalService;
import com.ruoyi.publicopinion.service.IPublicopinionService;
import com.ruoyi.scholarly.service.IScholarlyLearningService;
import com.ruoyi.scholarly.service.IScholarlyReadService;
import com.ruoyi.transformdocument.service.TransformDocumentService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
 
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
 
@RestController
@RequestMapping("/export")
@Slf4j
public class ExportDataIndexController {
    @Autowired
    private IProposalService proposalService;
    @Autowired
    private IPublicopinionService publicopinionService;
    @Autowired
    private INetworkService networkService;
    @Autowired
    private ICommitteeService committeeService;
    @Autowired
    private IMeetService meetService;
    @Autowired
    private IDocumentService documentService;
    @Autowired
    private INewsService newsService;
    @Autowired
    private IScholarlyLearningService scholarlyLearningService;
    @Autowired
    private TransformDocumentService transformDocumentService;
    @Autowired
    private IBaseconsultTopicsService baseconsultTopicsService;
 
 
 
    @GetMapping("/exportDataIndex")
    public void exportExcel(HttpServletResponse response) {
        try (OutputStream out = response.getOutputStream()) {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("云南省政协数据可视化中心", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            handleExcel(out);
            out.flush();
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }
 
    private void handleExcel(OutputStream out) {
        try (ExcelWriter excelWriter = EasyExcelFactory.write(out).build()) {
            //设置内容样式
            WriteCellStyle contentStyle = new WriteCellStyle();
            contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//居中
            contentStyle.setWrapped(true);//自动换行
            //设置头部样式
            WriteCellStyle headerStyle = new WriteCellStyle();
            headerStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //设置策略
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headerStyle,contentStyle);
            WriteSheet proposalSheet = EasyExcelFactory.writerSheet(0, "委员提案").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
            WriteSheet publicopinionSheet = EasyExcelFactory.writerSheet(1, "社情民意").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
            WriteSheet consultationSheet = EasyExcelFactory.writerSheet(2, "协商议政").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
            WriteSheet committeeSheet = EasyExcelFactory.writerSheet(3, "委员信息").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
            WriteSheet meetSheet = EasyExcelFactory.writerSheet(4, "会议活动").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
            WriteSheet documentSheet = EasyExcelFactory.writerSheet(5, "公文流转").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
            WriteSheet provinceSheet = EasyExcelFactory.writerSheet(6, "云南全省政协").head(ExportListVo.class).registerWriteHandler(horizontalCellStyleStrategy).build();
            excelWriter.write(getProposal(), proposalSheet);
            excelWriter.write(getPublicopinion(), publicopinionSheet);
            excelWriter.write(getConsultation(),consultationSheet);
            excelWriter.write(getCommittee(),committeeSheet);
            excelWriter.write(getMeet(),meetSheet);
            excelWriter.write(getDocument(),documentSheet);
            excelWriter.write(getProvinceWide(),provinceSheet);
        }
    }
 
    //首页-委员提案
    private List<ExportListVo> getProposal() {
        List<ExportListVo> list = new ArrayList<>();
        int proposalCount = proposalService.proposalCount(null, area);
        List<ResultVo> proposalKind = proposalService.proposalKind(null, area);
        ExportListVo vo = new ExportListVo("委员提案总数",proposalCount+" 条");
        list.add(vo);
        for (ResultVo result : proposalKind) {
            ExportListVo kind = new ExportListVo();
            kind.setName(result.getName());
            kind.setValue(result.getValue()+" 条");
            list.add(kind);
        }
        return list;
    }
 
    //首页-社情民意
    private List<ExportListVo> getPublicopinion() {
        List<ExportListVo> list = new ArrayList<>();
        int opinionCount = publicopinionService.opinionCount(null, area, null);
        List<ResultVo> opinionType = publicopinionService.opinionType(null, area);
        ExportListVo vo = new ExportListVo("社情民意总数",opinionCount+" 条");
        list.add(vo);
        for (ResultVo result : opinionType) {
            ExportListVo type = new ExportListVo(result.getName(),result.getValue()+" 条");
            list.add(type);
        }
        return list;
    }
 
    //首页-网络议政/远程协商
    public List<ExportListVo> getConsultation(){
        List<ExportListVo> list = new ArrayList<>();
        List<ResultVo> netWorkCount = networkService.netWorkCount(null, area);
        List<ResultVo> remoteCount = networkService.remoteCount(null, area);
        List<ResultVo> networkConduct = networkService.networkConduct(area);
        List<ResultVo> networkClosed = networkService.networkClosed(area);
        List<ResultVo> remoteConduct = networkService.remoteConduct(area);
        List<ResultVo> remoteClosed = networkService.remoteClosed(area);
        ExportListVo vo = new ExportListVo("网络议政总数",netWorkCount.get(0).getValue()+" 条");
        ExportListVo vo1 = new ExportListVo("远程协商总数",remoteCount.get(0).getValue()+" 条");
        ExportListVo vo2 = new ExportListVo("网络议政-进行中",networkConduct.get(0).getValue()+" 条");
        ExportListVo vo3 = new ExportListVo("网络议政-已结束",networkClosed.get(0).getValue()+" 条");
        ExportListVo vo4 = new ExportListVo("远程协商-进行中",remoteConduct.get(0).getValue()+" 条");
        ExportListVo vo5 = new ExportListVo("远程协商-已结束",remoteClosed.get(0).getValue()+" 条");
        list.add(vo);
        list.add(vo1);
        list.add(vo2);
        list.add(vo3);
        list.add(vo4);
        list.add(vo5);
        return list;
    }
 
    //首页-委员信息
    public List<ExportListVo> getCommittee(){
        List<ExportListVo> list = new ArrayList<>();
        int committeeCount = committeeService.committeeCount(area,null);//委员人数
        int standingCommitteeCount = committeeService.standingCommmitteeCount(area,null);//常委人员
        int officeCount = committeeService.officeCount(area);//机关人
        ExportListVo vo = new ExportListVo("委员人数",committeeCount+" 人");
        ExportListVo vo1 = new ExportListVo("常委人数",standingCommitteeCount+" 人");
        ExportListVo vo2 = new ExportListVo("机关人数",officeCount+" 人");
        list.add(vo);
        list.add(vo1);
        list.add(vo2);
        List<ResultVo> partiesCount = committeeService.partiesCount(area,null);//委员构成
        List<ResultVo> ageCount = committeeService.ageCount(area,null);//年龄
        List<ResultVo> genderCount = committeeService.genderCount(area,null);//性别
        List<ResultVo> nationCount = committeeService.nationCount(area,null);//民族
        list.add(new ExportListVo("党派",null));
        for (ResultVo result : partiesCount) {
            ExportListVo parties = new ExportListVo(result.getName(), result.getValue() + " 人");
            list.add(parties);
        }
        list.add(new ExportListVo("年龄",null));
        for (ResultVo result : ageCount) {
            ExportListVo age = new ExportListVo(result.getName(), result.getValue() + " 人");
            list.add(age);
        }
        list.add(new ExportListVo("性别",null));
        for (ResultVo result : genderCount) {
            ExportListVo gender = new ExportListVo(result.getName(), result.getValue() + " 人");
            list.add(gender);
        }
        list.add(new ExportListVo("民族",null));
        for (ResultVo result : nationCount) {
            ExportListVo nation = new ExportListVo(result.getName(), result.getValue() + "人");
            list.add(nation);
        }
        return list;
    }
 
    //首页-会议活动
    public List<ExportListVo> getMeet(){
        List<ExportListVo> list = new ArrayList<>();
        List<ResultVo> meetCount = meetService.meetCount(null,area);
        list.add(new ExportListVo("会议活动总数",meetCount.get(0).getValue()+" 次"));
        List<ResultVo> yearCount = meetService.yearCount(area);
        for (ResultVo result : yearCount) {
            list.add(new ExportListVo(result.getName(),result.getValue()+" 次"));
        }
        return list;
    }
 
    //首页-公文流转
    public List<ExportListVo> getDocument(){
        List<ExportListVo> list = new ArrayList<>();
        int documentCount = documentService.documentCount(null, area);
        list.add(new ExportListVo("公文流转总数",documentCount+" 条"));
        List<ResultVo> yearCount = documentService.yearCount(null, area);
        for (ResultVo result : yearCount) {
            list.add(new ExportListVo(result.getName(),result.getValue()+" 条"));
        }
        return list;
    }
 
    //首页-云南全省政协
    public List<ExportListVo> getProvinceWide(){
        List<ExportListVo> list = new ArrayList<>();
        int committeeCount = committeeService.committeeCount(area,null);//委员人数
        int standingCommitteeCount = committeeService.standingCommmitteeCount(area,null);//常委人员
        int officeCount = committeeService.officeCount(area);//机关人
        int opinionCount = publicopinionService.opinionCount(null, area, null);//社情民意
        int newsCount = newsService.newsCount(null, area);//政协新闻
        int proposalCount = proposalService.proposalCount(null, area);//委员提案
        List<ResultVo> netWorkCount = networkService.netWorkCount(null, area);//网络议政
        List<ResultVo> remoteCount = networkService.remoteCount(null, area);//远程协商
        Integer readCount = scholarlyLearningService.getCount("", area);
        List<ResultVo> meetCount = meetService.meetCount(null,area);//会议活动
        int documentCount = documentService.documentCount(null, area);//公文流转
        int exchangeDocumentCount = transformDocumentService.exchangeDocumentCount();//公文交换
        int baseTopicCount = baseconsultTopicsService.baseTopicCount(area);//协商在基层
        list.add(new ExportListVo("委员人数",committeeCount+" 人"));
        list.add(new ExportListVo("常委人数",standingCommitteeCount+" 人"));
        list.add(new ExportListVo("机关人数",officeCount+" 人"));
        list.add(new ExportListVo("政协新闻",newsCount+" 条"));
        list.add(new ExportListVo("社情民意",opinionCount+"条"));
        list.add(new ExportListVo("委员提案",proposalCount+" 条"));
        list.add(new ExportListVo("网络议政",netWorkCount.get(0).getValue()+" 次"));
        list.add(new ExportListVo("远程协商",remoteCount.get(0).getValue()+ " 次"));
        list.add(new ExportListVo("书香政协",readCount+" 条"));
        list.add(new ExportListVo("会议活动",meetCount.get(0).getValue()+" 次"));
        list.add(new ExportListVo("公文流转",documentCount+" 条"));
        list.add(new ExportListVo("公文交换",exchangeDocumentCount+" 条"));
        list.add(new ExportListVo("协商在基层",baseTopicCount+" 条"));
        return list;
    }
 
}

四、在浏览器地址栏输入地址：http://localhost:8997/export/exportDataIndex   

进行数据导出

最后导出的文件如下所示：

至此，一个工作簿多个工作表导出的功能就完成了。