easyExcel导出多个sheet的excel

最新推荐文章于 2026-04-15 10:56:10 发布

原创最新推荐文章于 2026-04-15 10:56:10 发布 · 7.2k 阅读

39 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

java

这篇博客介绍了如何在Java中使用easyExcel库来导出包含多个sheet的Excel文件。首先，文章提及了导出单个sheet Excel的场景和实现步骤，包括定义导出模型和编写controller层代码。接着，详细阐述了导出多sheet Excel的过程，包括更新的导出模型、controller层的代码变更，以及使用到的辅助工具类。通过示例接口访问，成功展示了生成的包含两个sheet的Excel文件。

easyExcel导出

导出背景

导出背景

需求是导出业务数据生成单一sheet的excel或者是导出业务数据生成多sheet的excel。文章记录一下实现过程。

正文开始：

依赖

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>2.1.1</version>
		</dependency>

单个sheet的excel导出

定义导出模型

定义导出excel的字段模型，对应excel的表头。

package com.jeesite.modules.exportexcel.excelbo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

/**
 * 业务字典表数据导出
 * @author  fyn
 * @date    2021/7/30
 */
@Data
public class ExcelBo extends BaseRowModel {
    @ExcelProperty(value = {"编码"}, index = 0)
    private String id;

    @ExcelProperty(value = {"名称"}, index = 1)
    private String dictionariesName;
}

controller层代码

package com.jeesite.modules.exportexcel.controller;

import com.alibaba.excel.EasyExcel;
import com.jeesite.modules.exportexcel.excelbo.ExcelBo;
import com.rewin.modules.entity.TbusinessDictionariesEnergy;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;

@RestController
@RequestMapping("${adminPath}/exportExcelDemo")
@Slf4j
public class ExportController {

    @GetMapping(value = "/export")
    public void exportExcel(HttpServletResponse response) {

        String sheetName = "设备类型";
        //此处数据集为手动创建数据  -- 可替换为具体业务逻辑数据
        ArrayList<TbusinessDictionariesEnergy> tbusinessDictionariesEnergyList = new ArrayList<>();
        for(int i =1;i<4;i++){
            TbusinessDictionariesEnergy tbusinessDictionariesEnergy = new TbusinessDictionariesEnergy();
            tbusinessDictionariesEnergy.setId(String.valueOf(i));
            tbusinessDictionariesEnergy.setDictionariesName("数据" + i);
            tbusinessDictionariesEnergyList.add(tbusinessDictionariesEnergy);
        }
        ArrayList<ExcelBo> excelBoArrayList = new ArrayList<>();
        tbusinessDictionariesEnergyList.forEach(tbusinessDictionariesEnergy1 -> {
            ExcelBo tableHeaderExcelProperty = new ExcelBo();
            tableHeaderExcelProperty.setId(tbusinessDictionariesEnergy1.getId());
            tableHeaderExcelProperty.setDictionariesName(tbusinessDictionariesEnergy1.getDictionariesName());
            excelBoArrayList.add(tableHeaderExcelProperty);
        });
        String fileName = new String("设备类型.xlsx".getBytes(), StandardCharsets.ISO_8859_1);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName );
        try {
            EasyExcel.write(response.getOutputStream(), ExcelBo.class).sheet(sheetName).sheetNo(1).doWrite(excelBoArrayList);
        }catch (Exception e){
            log.error(e.getMessage());
        }
    }

}

启动项目之后，直接在浏览器地址栏访问 ：
在这里插入图片描述
浏览器左下角弹框：

打开看一下内容：

OK，到此单个sheet的excel导出完成。

多个sheet的excel导出

所用的导出模型

package com.jeesite.modules.exportexcel.excelbo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

/**
 * 企业数据导出字段
 * @author  fyn
 * @date    2021/7/30
 */
@Data
public class CompanyExcelBo extends BaseRowModel {
    @ExcelProperty(value = {"序号"}, index = 0)
    private Integer number;

    @ExcelProperty(value = {"企业社会信用代码"}, index = 1)
    private String companySocialCreditCode;

    @ExcelProperty(value = {"企业名称"}, index = 2)
    private String companyName;

}

controller层代码

package com.jeesite.modules.exportexcel.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.jeesite.modules.exportexcel.excelbo.CompanyExcelBo;
import com.jeesite.modules.exportexcel.excelbo.ExcelBo;
import com.jeesite.modules.exportexcel.util.MultipleSheelPropety;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;

@RestController
@RequestMapping("${adminPath}/exportExcelDemo")
@Slf4j
public class ExportController {

    /**
     * 多个sheet的excel导出
     * @param response
     */
    @GetMapping(value = "/sheetSExportAllExcel")
    public void sheetSExportAllExcel(HttpServletResponse response) {
        ArrayList<MultipleSheelPropety> excelList = new ArrayList<>();
        for(int j=1;j<3;j++){
            if(1 == j){
                //第一个sheet数据    此处数据集为手动创建数据  -- 实际开发替换为具体业务逻辑数据
                ArrayList<ExcelBo> excelBoArrayList = new ArrayList<>();
                for(int i =1;i<4;i++){
                    //设备数据
                    ExcelBo excelBo = new ExcelBo();
                    excelBo.setId(String.valueOf(i));
                    excelBo.setDictionariesName("数据" + i);
                    excelBoArrayList.add(excelBo);
                }
                Sheet sheet = new Sheet(j, 0);
                sheet.setSheetName("设备");
                MultipleSheelPropety multipleSheelPropety = new MultipleSheelPropety();
                multipleSheelPropety.setData(excelBoArrayList);
                multipleSheelPropety.setSheet(sheet);
                excelList.add(multipleSheelPropety);
            }else if(2 == j){
                //第二个sheet数据    此处数据集为手动创建数据  -- 实际开发替换为具体业务逻辑数据
                ArrayList<CompanyExcelBo> companyExcelBoArrayList = new ArrayList<CompanyExcelBo>();
                for (int i=1;i<4;i++){
                    //企业数据
                    CompanyExcelBo companyExcelBo = new CompanyExcelBo();
                    companyExcelBo.setNumber(i);
                    companyExcelBo.setCompanyName("知名企业" + i);
                    companyExcelBo.setCompanySocialCreditCode("#1￥@" + i);
                    companyExcelBoArrayList.add(companyExcelBo);
                }
                Sheet sheet = new Sheet(j, 0);
                sheet.setSheetName("企业");
                MultipleSheelPropety multipleSheelPropety = new MultipleSheelPropety();
                multipleSheelPropety.setData(companyExcelBoArrayList);
                multipleSheelPropety.setSheet(sheet);
                excelList.add(multipleSheelPropety);
            }
        }
        String fileName = new String("全部数据.xlsx".getBytes(), StandardCharsets.ISO_8859_1);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName );
        ExcelWriter excelWriter = null;
        try{
            excelWriter = EasyExcel.write(response.getOutputStream()).build();
            for(int i = 0;i<excelList.size();i++){
                if(!CollectionUtils.isEmpty(excelList.get(i).getData())){
                    //这里 需要指定写用哪个class去写
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, excelList.get(i).getSheet().getSheetName()).head(excelList.get(i).getData().get(0).getClass()).build();
                    excelWriter.write(excelList.get(i).getData(), writeSheet);
                }
            }
            //千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
        }catch (Exception e){
            log.error(e.getMessage());
        }
    }

}

所用到的工具类

package com.jeesite.modules.exportexcel.util;

import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import lombok.Data;

import java.util.List;

@Data
public class MultipleSheelPropety{

    private List<? extends BaseRowModel> data;

    private Sheet sheet;
}