java 导出word中循环表格 java快速导出excel

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。快速、简洁、解决大文件内存溢出的java处理Excel工具

• 快速：快速的读取excel中的数据。
• 简洁：映射excel和实体类，让代码变的更加简洁。
• 大文件：在读写大文件的时候使用磁盘做缓存，更加的节约内存。

当项目中同时使用easypoi和easyExcel时，easypoi依赖放在easyExcel的前面

导出常用注解

注解	类型	描述
ExcelProperty	导出	index 指定写到第几列，默认根据成员变量排序。value指定写入的名称，默认成员变量的名字，多个value可以参照快速开始中的复杂头
ExcelIgnore	导出	默认所有字段都会写入excel，这个注解会忽略这个字段
DateTimeFormat	导出	日期转换，将Date写到excel会调用这个注解。里面的value参照java.text.SimpleDateFormat
NumberFormat	导出	数字转换，用Number写excel会调用这个注解。里面的value参照java.text.DecimalFormat
ExcelIgnoreUnannotated	导出	默认不加ExcelProperty 的注解的都会参与读写，加了不会参与

导出方法参数：WriteWorkbook,WriteSheet ,WriteTable都会有的参数，如果为空，默认使用上级。

• converter 转换器，默认加载了很多转换器。也可以自定义。
• writeHandler 写的处理器。可以实现WorkbookWriteHandler,SheetWriteHandler,RowWriteHandler,CellWriteHandler，在写入excel的不同阶段会调用
• relativeHeadRowIndex 距离多少行后开始。也就是开头空几行
• needHead 是否导出头
• head 与clazz二选一。写入文件的头列表，建议使用class。
• clazz 与head二选一。写入文件的头对应的class，也可以使用注解。
• autoTrim 字符串、表头等数据自动trim

WriteWorkbook（理解成excel对象）参数

• excelType 当前excel的类型 默认xlsx
• outputStream 与file二选一。写入文件的流
• file 与outputStream二选一。写入的文件
• templateInputStream 模板的文件流
• templateFile 模板文件
• autoCloseStream 自动关闭流。
• password 写的时候是否需要使用密码
• useDefaultStyle 写的时候是否是使用默认头

WriteSheet（就是excel的一个Sheet）参数

• sheetNo 需要写入的编码。默认0
• sheetName 需要些的Sheet名称，默认同sheetNo

WriteTable（就把excel的一个Sheet,一块区域看一个table）参数

• tableNo 需要写入的编码。默认0

样式注解

• @ExcelProperty
• @ColumnWith 列宽
• @ContentFontStyle 文本字体样式
• @ContentLoopMerge 文本合并
• @ContentRowHeight 文本行高度
• @ContentStyle 文本样式
• @HeadFontStyle 标题字体样式
• @HeadRowHeight 标题高度
• @HeadStyle 标题样式
• @ExcelIgnore 忽略项
• @ExcelIgnoreUnannotated 忽略未注解

@ExcelProperty
必要的一个注解，注解中有三个参数value,index分别代表列明，列序号value和index只能二选一，通常不用设置converter

• value 通过标题文本对应
• index 通过文本行号对应

文件后缀

文件后缀	MIME TYPE
.doc	application/msword
.dot	application/msword
.docx	application/vnd.openxmlformats-officedocument.wordprocessingml.document
.dotx	application/vnd.openxmlformats-officedocument.wordprocessingml.template
.docm	application/vnd.ms-word.document.macroEnabled.12
.dotm	application/vnd.ms-word.template.macroEnabled.12
.xls	application/vnd.ms-excel
.xlt	application/vnd.ms-excel
.xla	application/vnd.ms-excel
.xlsx	application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
.xltx	application/vnd.openxmlformats-officedocument.spreadsheetml.template
.xlsm	application/vnd.ms-excel.sheet.macroEnabled.12
.xltm	application/vnd.ms-excel.template.macroEnabled.12
.xlam	application/vnd.ms-excel.addin.macroEnabled.12
.xlsb	application/vnd.ms-excel.sheet.binary.macroEnabled.12
.pdf	application/pdf
.ppt	application/vnd.ms-powerpoint
.pot	application/vnd.ms-powerpoint
.pps	application/vnd.ms-powerpoint
.ppa	application/vnd.ms-powerpoint
.pptx	application/vnd.openxmlformats-officedocument.presentationml.presentation
.potx	application/vnd.openxmlformats-officedocument.presentationml.template
.ppsx	application/vnd.openxmlformats-officedocument.presentationml.slideshow
.ppam	application/vnd.ms-powerpoint.addin.macroEnabled.12
.pptm	application/vnd.ms-powerpoint.presentation.macroEnabled.12
.potm	application/vnd.ms-powerpoint.presentation.macroEnabled.12
.ppsm	application/vnd.ms-powerpoint.slideshow.macroEnabled.12
.zip	application/zip
.tar	application/x-tar
.gif	image/gif
.png	image/png
.html	text/html
.txt	text/plain

添加pom依赖

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

<!--工具类-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.3.1</version>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.21</version>
</dependency>

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-collections4</artifactId>
    <version>4.4</version>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

第一种：普通导出

实体类

package com.example.mybatismysql8demo.excel;

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

import java.math.BigDecimal;

@Data
public class GoodsExportExcel {

    @ExcelProperty(value = {"商品信息","商品名称"},index = 0)
    public String goodsName;

    @ExcelProperty(value = {"商品信息","商品价格"},index = 1)
    public BigDecimal price;

    @ExcelProperty(value = {"商品信息","商品数量"},index = 2)
    public Integer num;

    public GoodsExportExcel(String goodsName, BigDecimal price, Integer num) {
        this.goodsName = goodsName;
        this.price = price;
        this.num = num;
    }
}

执行方法

package com.example.mybatismysql8demo.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.example.mybatismysql8demo.excel.GoodsExportExcel;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;

@RestController
public class EasyExcelController {

    @RequestMapping(value = "easyExcelExport", method = RequestMethod.GET)
    public void easyExcelExport(HttpServletResponse response) {
        //数据
        List<GoodsExportExcel> data = new ArrayList<>();
        data.add(new GoodsExportExcel("苹果",new BigDecimal(10),100));
        data.add(new GoodsExportExcel("香蕉",new BigDecimal(8),200));
        try {
            //设置返回类型及编码类型
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //可以防止中文乱码,和easyExcel没有关系
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(System.currentTimeMillis() + ".xls" , StandardCharsets.UTF_8));
            //浏览器下载
            EasyExcel.write(response.getOutputStream(), GoodsExportExcel.class)
                    //文件类型
                    .excelType(ExcelTypeEnum.XLS)
                    //是否自动关闭流
                    .autoCloseStream(Boolean.TRUE)
                    //自动列宽(不太精确)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .sheet("商品信息")
                    .doWrite(data);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 本地下载
     * @param args
     */
    public static void main(String[] args) {
        // 存放路径
        String path = "D:/";
        // 文件名称
        String fileName = path + System.currentTimeMillis() + ".xls";
        //数据
        List<GoodsExportExcel> data = new ArrayList<>();
        data.add(new GoodsExportExcel("苹果",new BigDecimal(10),100));
        data.add(new GoodsExportExcel("香蕉",new BigDecimal(8),200));
        EasyExcel.write(fileName, GoodsExportExcel.class).sheet("商品信息").doWrite(data);
    }
}

第二种：复杂导出（合并、批注、自定义拦截、一级下拉框）

实体类

package com.example.mybatismysql8demo.excel;

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

import java.math.BigDecimal;

@Data
public class GoodsExportExcel {

    @ExcelProperty(value = {"商品信息","商品名称"},index = 0)
    public String goodsName;

    @ExcelProperty(value = {"商品信息","商品价格"},index = 1)
    public BigDecimal price;

    @ExcelProperty(value = {"商品信息","商品数量"},index = 2)
    public Integer num;

    @ExcelProperty(value = {"商品信息","是否进口"},index = 3)
    public String inward;

    public GoodsExportExcel(String goodsName, BigDecimal price, Integer num) {
        this.goodsName = goodsName;
        this.price = price;
        this.num = num;
    }
}

合并规则

package com.example.mybatismysql8demo.handler;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.List;
import java.util.Map;

/**
 * excel导出数据内容单元格合并规则
 * @author Administrator
 */
@Slf4j
public class ExcelFileCellMergeStrategy implements CellWriteHandler, RowWriteHandler, SheetWriteHandler {
    /**
     * 合并列的范围索引
     */
    private int[] mergeColumnIndex;

    /**
     * 合并起始行索引
     */
    private int mergeRowIndex;

    /**
     * 合并校验列值
     */
    private int colIndex;

    /**下拉框数据*/
    private Map<Integer, String[]> mapDropDown;


    public ExcelFileCellMergeStrategy(int[] mergeColumnIndex, int mergeRowIndex, int colIndex, Map<Integer, String[]> mapDropDown) {
        this.mergeColumnIndex = mergeColumnIndex;
        this.mergeRowIndex = mergeRowIndex;
        this.colIndex = colIndex;
        this.mapDropDown = mapDropDown;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }


    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        System.out.println("----------合并开始-----------");
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();
        //某行开始合并
        if (curRowIndex > mergeRowIndex) {
            //判断每行的第一列是否相同,相同时进行合并
            if (isSame(cell, curRowIndex)) {
                for (int columnIndex : mergeColumnIndex) {
                    //对应的列进行合并
                    if (curColIndex == columnIndex) {
                        mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                        break;
                    }
                }
            }
        }
        System.out.println("----------合并结束-----------");

        System.out.println("----------自定义拦截开始-----------");
        // 这里可以对cell进行任何操作
        log.info("第{}行，第{}列写入完成", cell.getRowIndex(), cell.getColumnIndex());
        if (aBoolean && cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
            CreationHelper createHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
            Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
            hyperlink.setAddress("https://github.com/alibaba/easyexcel");
            cell.setHyperlink(hyperlink);
        }
        System.out.println("----------自定义拦截结束-----------");
    }

    private Boolean isSame(Cell cell, int curRowIndex){
        //获取当前行的某列的数据
        Cell curCell = cell.getSheet().getRow(curRowIndex).getCell(colIndex);
        //当前行某列值
        Object curData = curCell.getCellTypeEnum() == CellType.STRING ? curCell.getStringCellValue() : curCell.getNumericCellValue();
        //获取当前行的上一行数据
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(colIndex);
        //上一行某列值
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 比较当前行的某列的单元格与上一行是否相同，相同合并当前单元格与上一行
        return curData.equals(preData);
    }


    /**
     * 当前单元格向上合并
     * @param writeSheetHolder
     * @param cell
     * @param curRowIndex
     * @param curColIndex
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        //获取当前行的当前列的数据和上一行的当前列数据，通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1 ).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        //比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
        if (curData.equals(preData)){
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergedRegions.size() && !isMerged; i++) {
                CellRangeAddress cellAddresses = mergedRegions.get(i);
                //若上 一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellAddresses.isInRange(curRowIndex - 1 , curColIndex)){
                    sheet.removeMergedRegion(i);
                    cellAddresses.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellAddresses);
                    isMerged = true;
                }
            }
            //若上一个单元格未被合并，则新增合并单元
            if (!isMerged){
                CellRangeAddress cellAddresses = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellAddresses);
            }
        }
    }

    @Override
    public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {
        //integer表示行数，给第二行添加批注
        if (BooleanUtils.isTrue(aBoolean) && integer == 1) {
            Sheet sheet = writeSheetHolder.getSheet();
            Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
            ClientAnchor anchor = new HSSFClientAnchor();
            anchor.setCol1(1);
            anchor.setCol2(2);
            anchor.setRow1(0);
            anchor.setRow2(2);
            anchor.setDx1(0);
            anchor.setDy1(0);
            anchor.setDx2(0);
            anchor.setDy2(0);
            // 在第二行的第二列创建一个批注
            Comment comment = drawingPatriarch.createCellComment(anchor);
            // 输入批注信息
            comment.setString(new HSSFRichTextString("创建批注!"));
            // 将批注添加到第一行第一列单元格对象中
            sheet.getRow(0).getCell(0).setCellComment(comment);
        }
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        System.out.println("----下拉框---");
        Sheet sheet = writeSheetHolder.getSheet();
        ///开始设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();
        for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
            /*起始行、终止行、起始列、终止列**/
            CellRangeAddressList addressList = new CellRangeAddressList(2, 1000, entry.getKey(), entry.getKey());
            /*设置下拉框数据**/
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            sheet.addValidationData(dataValidation);
        }
        System.out.println("----下拉框结束---");
    }
}

执行方法

package com.example.mybatismysql8demo.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.example.mybatismysql8demo.excel.GoodsExportExcel;
import com.example.mybatismysql8demo.handler.ExcelFileCellMergeStrategy;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;

@RestController
public class EasyExcelController {

    @RequestMapping(value = "easyExcelExport", method = RequestMethod.GET)
    public void easyExcelExport(HttpServletResponse response) {
        //数据
        List<GoodsExportExcel> data = new ArrayList<>();
        data.add(new GoodsExportExcel("苹果",new BigDecimal(10),100));
        data.add(new GoodsExportExcel("苹果",new BigDecimal(9),50));
        data.add(new GoodsExportExcel("香蕉",new BigDecimal(8),200));
        try {
            //设置返回类型及编码类型
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //可以防止中文乱码,和easyExcel没有关系
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(System.currentTimeMillis() + ".xls" , StandardCharsets.UTF_8));
            //需要合并的索引列(列从0开始)
            int[] mergeColIndex = {0};
            //需要从第二行开始,列头第一行
            int mergeRowIndex = 2;
            //设置当某列值相同才合并（列从0开始）
            int colIndex = 0;
            //下拉框值设置（实际运用在模版下载）
            String[] yesOrNo = new String[]{"是", "否"};
            Map<Integer, String[]> mapDropDown = new HashMap<>(1);
            //3表示实体类中的index值对应
            mapDropDown.put(3, yesOrNo);
            //浏览器下载
            EasyExcel.write(response.getOutputStream(), GoodsExportExcel.class)
                        //文件类型
                        .excelType(ExcelTypeEnum.XLS)
                        //是否自动关闭流
                        .autoCloseStream(Boolean.TRUE)
                        //设置批注
                        .inMemory(Boolean.TRUE)
                        //合并策略
                        .registerWriteHandler(new ExcelFileCellMergeStrategy(mergeColIndex, mergeRowIndex,colIndex,mapDropDown))
                        //自动列宽(不太精确)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        .sheet("商品信息")
                        .doWrite(data);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

第三种：样式导出

• 忽视无注解的字段：@ExcelIgnoreUnannotated
• 表头行高：@HeadRowHeight(35)
• 数据列宽：@ColumnWidth(20)
• 数据行高：@ContentRowHeight(30)
• 头背景设置成红色 IndexedColors.RED.getIndex()：@HeadStyle(fillPatternType =
  FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10)
• 头字体设置成20：@HeadFontStyle(fontHeightInPoints = 20)
• 内容的背景设置成绿色 IndexedColors.GREEN.getIndex()：@ContentStyle(fillPatternType =
  FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 17)
• 内容字体设置成20：@ContentFontStyle(fontHeightInPoints = 20)
• 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex()：@HeadStyle(fillPatternType =
  FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 14)
• 字符串的头字体设置成20：@HeadFontStyle(fontHeightInPoints = 30)
• 字符串的内容的背景设置成天蓝 IndexedColors.SKY_BLUE.getIndex()：@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
• 字符串的内容字体设置成20：@ContentFontStyle(fontHeightInPoints = 30)
  
  实体类

package com.example.mybatismysql8demo.excel;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import lombok.Data;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import java.math.BigDecimal;

@Data
//忽视无注解的字段
@ExcelIgnoreUnannotated
/*表头行高*/
@HeadRowHeight(35)
/*数据列宽*/
@ColumnWidth(20)
/*数据行高*/
@ContentRowHeight(30)
// 头字体设置成20
@HeadFontStyle(fontHeightInPoints = 20)
// 头背景设置成黄色 IndexedColors.YELLOW.getIndex()
@HeadStyle(fillForegroundColor = 27, horizontalAlignment= HorizontalAlignment.CENTER)
// 内容的背景设置成粉色 IndexedColors.PINK.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND,fillForegroundColor = 14, horizontalAlignment= HorizontalAlignment.CENTER,borderBottom = BorderStyle.THIN,borderLeft = BorderStyle.THIN)
// 内容字体设置成16
@ContentFontStyle(fontHeightInPoints = 16)
public class GoodsExportExcel {

    @ExcelProperty(value = {"商品信息","商品名称"},index = 0)
    public String goodsName;

    @ExcelProperty(value = {"商品信息","商品价格"},index = 1)
    public BigDecimal price;

    @ExcelProperty(value = {"商品信息","商品数量"},index = 2)
    public Integer num;

    @ExcelProperty(value = {"商品信息","是否进口"},index = 3)
    public String inward;

    /**列标题样式*/
    @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10)
    /**字体样式*/
    @ContentFontStyle(fontHeightInPoints = 11,color = 10)
    @ColumnWidth(30)
    @ExcelProperty(value = {"商品信息","错误信息"},index = 4)
    private String errorMsg;

    private String ignore;

    public GoodsExportExcel(String goodsName, BigDecimal price, Integer num) {
        this.goodsName = goodsName;
        this.price = price;
        this.num = num;
    }
}

执行方法

package com.example.mybatismysql8demo.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.example.mybatismysql8demo.excel.GoodsExportExcel;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;

@RestController
public class EasyExcelController {

    @RequestMapping(value = "easyExcelExport", method = RequestMethod.GET)
    public void easyExcelExport(HttpServletResponse response) {
        //数据
        List<GoodsExportExcel> data = new ArrayList<>();
        data.add(new GoodsExportExcel("苹果",new BigDecimal(10),100));
        data.add(new GoodsExportExcel("苹果",new BigDecimal(9),50));
        data.add(new GoodsExportExcel("香蕉",new BigDecimal(8),200));
        try {
            //设置返回类型及编码类型
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //可以防止中文乱码,和easyExcel没有关系
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(System.currentTimeMillis() + ".xls" , StandardCharsets.UTF_8));
            //表头样式
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            //设置表头居中对齐
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //内容样式
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置内容靠左对齐
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
            //浏览器下载
            EasyExcel.write(response.getOutputStream(), GoodsExportExcel.class)
                        //文件类型
                        .excelType(ExcelTypeEnum.XLS)
                        //是否自动关闭流
                        .autoCloseStream(Boolean.TRUE)
                        //自动列宽(不太精确)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        //设置样式
                        .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                        .sheet("商品信息")
                        .doWrite(data);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

第四种：多级下拉框导出

实体类

package com.example.mybatismysql8demo.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;

@Data
public class CityInfo {

    @ExcelProperty(value = "名称",index = 0)
    private String name;

    @ExcelProperty(value = "省",index = 1)
    private String province;

    @ExcelProperty(value = "市",index = 2)
    private String city;

    @ExcelProperty(value = "区",index = 3)
    private String area;

}

多级下拉框配置

package com.example.mybatismysql8demo.config;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;

import java.util.List;
import java.util.Map;

public class SpinnerWriteHandler implements SheetWriteHandler {

    /**
     * 多级分类值
     */
    private Map<Integer, Object> dropDown;

    /**
     * 一级分类值
     */
    private Map<Integer, String[]> oneDropDown;

    /**
     * 一级分类名称
     */
    private List<String> firstLevel;

    /**
     * 父级分类名称：一级与二级分类名称
     */
    private List<String> secondLevel;

    /**
     * 父级对应的子类
     */
    private Map<String, List<String>> thirdLevel;

    /**
     * 开始行填充下拉框值
     */
    private Integer rowIndex;


    public SpinnerWriteHandler(Map<Integer, Object> dropDown, Map<Integer, String[]> oneDropDown,Integer rowIndex) {
        this.dropDown = dropDown;
        this.oneDropDown = oneDropDown;
        this.rowIndex = rowIndex;
    }

    public SpinnerWriteHandler(Map<Integer, Object> dropDown,Integer rowIndex) {
        this.dropDown = dropDown;
        this.rowIndex = rowIndex;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        //多级下拉框
        if (dropDown != null) {
            Sheet sheet = writeSheetHolder.getSheet();
            ///开始设置下拉框
            DataValidationHelper helper = sheet.getDataValidationHelper();
            //设置数据有效性和添加名称管理器
            Workbook workbook = writeWorkbookHolder.getWorkbook();
            for (Map.Entry<Integer, Object> entry : dropDown.entrySet()) {
                //多级分类
                Map<String, Object> values = (Map<String, Object>) entry.getValue();
                firstLevel = (List<String>) values.get("firstLevel");
                secondLevel = (List<String>) values.get("secondLevel");
                thirdLevel = (Map<String, List<String>>) values.get("thirdLevel");
                twoDownList(workbook, helper, sheet, entry.getKey());
            }
        }
        //一级分类
        if (oneDropDown != null) {
            oneDownList(writeSheetHolder);
        }
    }


    public void twoDownList(Workbook workbook, DataValidationHelper helper, Sheet sheet, Integer index) {
        Sheet hideSheet = workbook.createSheet("category");
        int rowId = 0;
        //一级数据填充
        Row provinceRow = hideSheet.createRow(rowId++);
        provinceRow.createCell(0).setCellValue("省列表");
        for (int i = 0; i < firstLevel.size(); i++) {
            //设置列
            Cell provinceCell = provinceRow.createCell(i + 1);
            //列对应的值
            provinceCell.setCellValue(firstLevel.get(i));
        }
        // 二级数据填充，将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。二级与三级分类
        for (String key : secondLevel) {
            List<String> son = thirdLevel.get(key);
            //判空
            if (CollectionUtil.isNotEmpty(son)){
                Row row = hideSheet.createRow(rowId++);
                row.createCell(0).setCellValue(key);
                for (int j = 0; j < son.size(); j++) {
                    Cell cell = row.createCell(j + 1);
                    cell.setCellValue(son.get(j));
                }
                // 添加名称管理器
                String range = getRange(1, rowId, son.size());
                Name name = workbook.createName();
                //key不可重复
                //判断key是否包含特殊符号
                name.setNameName(key);
                String formula = "category!" + range;
                name.setRefersToFormula(formula);
            }
        }
        //起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(rowIndex, 1000, index, index);
        //设置下拉框数据
        DataValidationConstraint constraint = helper.createExplicitListConstraint(firstLevel.toArray(new String[]{}));
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        //处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
        //对1000行设置有效性:i表示从第几行开始下标为1开始计算
        for (int i = rowIndex; i < Integer.sum(1000,rowIndex); i++) {
            //offset表示一级关联对应的坐标值，colNum表示当前级的子级列值，下标从1开始算
            //setDataValidation("C" ,sheetPro,i,4);
            setDataValidation(indexToColumn(index + 1), (SXSSFSheet) sheet, i, index + 2);
            //offset表示二级关联对应的坐标值，colNum表示当前级的子级列值，下标从1开始算
            //setDataValidation("D" ,sheetPro,i,5);
            setDataValidation(indexToColumn(index + 2), (SXSSFSheet) sheet, i, index + 3);
        }
    }


    /**
     * 将excel表格中列索引转成列号字母，从A对应1开始
     */
    private static String indexToColumn(int index) {
        if (index <= 0) {
            try {
                throw new Exception("Invalid parameter");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        index--;
        StringBuilder column = new StringBuilder();
        do {
            if (column.length() > 0) {
                index--;
            }
            column.insert(0, ((char) (index % 26 + (int) 'A')));
            index = (int) ((index - index % 26) / 26);
        } while (index > 0);

        return column.toString();
    }


    //一级下拉框
    public void oneDownList(WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        ///开始设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();
        for (Map.Entry<Integer, String[]> entry : oneDropDown.entrySet()) {
            /*起始行、终止行、起始列、终止列**/
            CellRangeAddressList addressList = new CellRangeAddressList(rowIndex, 1000, entry.getKey(), entry.getKey());
            /*设置下拉框数据**/
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            /*处理Excel兼容性问题**/
            if (dataValidation instanceof XSSFDataValidation) {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            } else {
                dataValidation.setSuppressDropDownArrow(false);
            }
            sheet.addValidationData(dataValidation);
        }

    }


    /**
     * 设置有效性
     *
     * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
     * @param sheet
     * @param rowNum 行数
     * @param colNum 列数
     */
    public static void setDataValidation(String offset, SXSSFSheet sheet, int rowNum, int colNum) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidation dataValidationList;
        dataValidationList = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum, (XSSFDataValidationHelper) helper);
        sheet.addValidationData(dataValidationList);
    }


    /**
     * 加载下拉列表内容
     *
     * @param formulaString
     * @param naturalRowIndex
     * @param naturalColumnIndex
     * @param dvHelper
     * @return
     */
    private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex, XSSFDataValidationHelper dvHelper) {
        /*   加载下拉列表内容
         *   举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
         *   如果A2是江苏省，那么此处就是江苏省下的市信息。
         */
        DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        int firstRow = naturalRowIndex - 1;
        int lastRow = naturalRowIndex - 1;
        int firstCol = naturalColumnIndex - 1;
        int lastCol = naturalColumnIndex - 1;
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 数据有效性对象
        XSSFDataValidation dataValidationList = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
        dataValidationList.setEmptyCellAllowed(false);
        // 设置输入信息提示信息
        dataValidationList.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
        // 设置输入错误提示信息
        dataValidationList.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
        return dataValidationList;
    }


    /**
     * 计算formula
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    private static String getRange(int offset, int rowId, int colCount) {
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            // 26-51之间，包括边界（仅两次字母表计算）
            if ((colCount - 25) / 26 == 0 || colCount == 51) {
                // 边界值
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }

}

执行方法

package com.example.mybatismysql8demo.controller;

import com.alibaba.excel.EasyExcel;
import com.example.mybatismysql8demo.config.SpinnerWriteHandler;
import com.example.mybatismysql8demo.excel.CityInfo;
import com.example.mybatismysql8demo.excel.GoodsExportExcel;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;

@RestController
public class EasyExcelController {

    @RequestMapping(value = "easyExcelExport", method = RequestMethod.GET)
    public void easyExcelExport(HttpServletResponse response) {
        try {
            //一级下拉框
            Map<Integer, String[]> mapDropDown = new HashMap<>(1);
            mapDropDown.put(0, new String[]{"小明","小红"});
            //多级下拉框
            Map<Integer,Object> mapAll = new LinkedHashMap<>();
            //一级数据
            List<String> firstLevel = Arrays.asList("江西省", "河南省");
            //一、二级数据名称
            List<String> secondLevel = Arrays.asList("江西省", "南昌市", "抚州市", "河南省", "周口市");
            //父级对应的子级数据
            Map<String,List<String>> thirdLevel = new LinkedHashMap<>();
            //依次列出各省的市、各市的县（对应级下的子级数据）
            thirdLevel.put("江西省", Arrays.asList("南昌市", "抚州市"));
            thirdLevel.put("河南省",Collections.singletonList("周口市"));
            thirdLevel.put("南昌市",Arrays.asList("东湖区","西湖区"));
            thirdLevel.put("抚州市",Arrays.asList("临川区","崇仁区"));
            thirdLevel.put("周口市",Arrays.asList("川汇区","扶沟县"));
            Map<String,Object> categoryMap = new HashMap<String,Object>(4);
            categoryMap.put("firstLevel",firstLevel);
            categoryMap.put("secondLevel",secondLevel);
            categoryMap.put("thirdLevel",thirdLevel);
            //1表示一级分类字段下标(1-3都是联动的)
            mapAll.put(1,categoryMap);
            //告诉浏览器数据格式，将头和数据传到前台
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" +  URLEncoder.encode("地区信息表.xls", "UTF-8"));
            EasyExcel.write(response.getOutputStream(), CityInfo.class)
                    .autoCloseStream(Boolean.FALSE)
                    .registerWriteHandler(new SpinnerWriteHandler(mapAll,mapDropDown,1))
                    .sheet("地区信息")
                    .doWrite(Collections.EMPTY_LIST);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}