前言

EasyExcel 是一款基于Java的開源Excel操作工具，它提供了簡單且強大的 API，使開發(fā)人員可以輕松地讀寫、操作和生成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>

二、創(chuàng)建實體類

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

進行數據導出

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

文章來源地址http://www.zghlxwxcb.cn/news/detail-772029.html

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

到了這里，關于Springboot基于easyexcel實現一個excel文件包含多個sheet表格的數據導出的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！