提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

官网文档：https://easyexcel.opensource.alibaba.com/
优缺点本文不做概述，官网很清楚，本文直接上手代码，操作java代码实现表格的导出（样式调整）、导入等操作

一、依赖

添加maven依赖, 依赖的poi最低版本3.17

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

二、导出Excel

2.1、基本导出内容

代码如下（示例）：

package com.easyexcel.easyexcel.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.easyexcel.easyexcel.util.CustomRowHeightStyleStrategy;
import com.easyexcel.easyexcel.util.EasyExcelUtils;
import com.easyexcel.easyexcel.util.LongestCellWidthHandlerProject;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class importTemplate {
    public static void main(String[] args) {

        //定义需要保存电脑的位置
        String path = "E:\\exercise\\Excel\\test.xlsx";
        
        //构建表头内容=======================================================================================

        //定义简单数据，此处数据，仅使用在表头，
        String[][] headCol = new String[][]{
                {"表头一"},
                {"表头二"},
                {"表头三"},
        };
        //定义表头所需数据格式
        List<List<String>> headList = new ArrayList<>();
        //遍历二位数组，修改格式进行添加
        Arrays.stream(headCol).forEach(item -> headList.add(Arrays.asList(item)));
        
        //构建表下文内容=======================================================================================

        //类似表头，定义简单数据
        String[][] contextCol = new String[][]{
                {"A-甲", "B-甲", "C-甲"},
                {"A-乙", "B-乙", "C-乙"},
                {"A-丙", "B-丙", "C-丙"},
                {"A-丁", "B-丁", "C-丁"},
        };
        List<List<Object>> dataList = new ArrayList<>();
        Arrays.stream(contextCol).forEach(item -> dataList.add(Arrays.asList(item)));

        //构建表主样式=======================================================================================
        ExcelWriter writer = EasyExcelFactory.write(path)
                .inMemory(Boolean.TRUE)
                .build();

		//定义表格下方sheet
        WriteSheet sheet1 = new WriteSheet();
        sheet1.setSheetName("sheet名字1");
        sheet1.setSheetNo(0);

        WriteTable writeTable = EasyExcel.writerTable(0).head(headList)
                .needHead(true)
                .build();

        writer.write(dataList, sheet1, writeTable);
        //关闭
        writer.finish();
        writer.close();


        System.out.println("导出成功····");
    }
}

代码运行后，代码定义保存位置，可以看到

表格样式，内容展示出来，样式接下来慢慢调整

2.2、简单样式

在以上的代码中，添加一个registerWriteHandler处理，并新建工具类

        WriteTable writeTable = EasyExcel.writerTable(0).head(headList)
                .registerWriteHandler(EasyExcelUtils.getStyleStrategy()) //引用表格样式
                .needHead(true)
                .build();

所需样式工具类

package com.easyexcel.easyexcel.util;

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;

/**
 * excel样式
 */
public class EasyExcelUtils {
    public static HorizontalCellStyleStrategy getStyleStrategy() {
        // 头的策略  样式调整
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 头背景
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        // 自动换行
        headWriteCellStyle.setWrapped(true);
        // 设置细边框
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        // 水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//------------------------------------------------------------------------------
        // 内容的策略 宋体
        WriteCellStyle contentStyle = new WriteCellStyle();
        // 设置垂直居中
        contentStyle.setWrapped(true);
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置 水平居中
        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        WriteFont contentWriteFont = new WriteFont();
        // 内容字号
        contentWriteFont.setFontHeightInPoints((short) 12);
        // 边框
        contentStyle.setBorderBottom(BorderStyle.THIN);
        contentStyle.setBorderLeft(BorderStyle.THIN);
        contentStyle.setBorderRight(BorderStyle.THIN);
        contentStyle.setBorderTop(BorderStyle.THIN);
        contentStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentStyle);
    }
}

2.3、行高、列宽

        WriteTable writeTable = EasyExcel.writerTable(0).head(headList)
                .registerWriteHandler(new CustomRowHeightStyleStrategy(1))//引用行高
                .registerWriteHandler(EasyExcelUtils.getStyleStrategy()) //引用表格样式
                .registerWriteHandler(new LongestCellWidthHandlerProject())//设置列宽
                .needHead(true)
                .build();

行高：

package com.easyexcel.easyexcel.util;

import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Row;

/**
 * 行高
 */
public class CustomRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy {

    /**
     * 设置特殊处理的行号
     */
    Integer rowNum;

    public CustomRowHeightStyleStrategy(Integer rowNum) {
        this.rowNum = rowNum;
    }

    /**
     * 设置表头的行高
     */
    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
        if (relativeRowIndex + 1 == rowNum) {
            //指定行的高度
            row.setHeightInPoints((120));
        }else {
            //默认表头高度
            row.setHeightInPoints((20));
        }

    }

    /**
     * 设置内容的行高
     */
    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        //默认主体的高度
        row.setHeightInPoints(20);

    }
}

列宽：

package com.easyexcel.easyexcel.util;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.style.column.AbstractHeadColumnWidthStyleStrategy;

/**
 * 列宽
 */
public class LongestCellWidthHandlerProject extends AbstractHeadColumnWidthStyleStrategy {

	/**
	* 设置每一列的列宽
	*/
    @Override
    protected Integer columnWidth(Head head, Integer columnIndex) {
        switch (columnIndex) {
            case 0:
                return 6;
            case 1:
                return 20;
            case 2:
                return 20;
            default:
                return 13;
        }
    }


}

2.4、列合并

        ExcelWriter writer = EasyExcelFactory.write(path).inMemory(Boolean.TRUE)
               .registerWriteHandler(new ExcelFillCellRowMergeStrategy(0, new int[]{0, 1, 2, 3}))//1、2、3列向下合并，↓
                .build();

package com.easyexcel.easyexcel.util;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

/**
 * 项目认证模板-列合并，↓
 */
public class ExcelFillCellRowMergeStrategy implements CellWriteHandler {

    //数组存放这一行需要合并那几列  [0,1,2] 在这mergeRowIndex行中合并 0、1、2列
    private int[] mergeColumnIndex;

    // 存放需要向上合并的行
    private int mergeRowIndex;

    // 不要合并的行
    private Integer noMergeRowIndex;

    public ExcelFillCellRowMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    public ExcelFillCellRowMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex, Integer noMergeRowIndex) {
        this.mergeColumnIndex = mergeColumnIndex;
        this.mergeRowIndex = mergeRowIndex;
        this.noMergeRowIndex = noMergeRowIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

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

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        int curRowIndex = cell.getRowIndex();
        int curColIndex = cell.getColumnIndex();
        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并
     * .
     *
     * @param writeSheetHolder writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Row preRow = cell.getSheet().getRow(curRowIndex - 1);
        if (preRow == null) {
            // 当获取不到上一行数据时，使用缓存sheet中数据
            preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
        }
        Cell preCell = preRow.getCell(curColIndex);
        Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        //不需要合并的列直接跳出
        if ((noMergeRowIndex != null) && noMergeRowIndex == (curRowIndex - 1)) {
            return;
        }
        // 将当前单元格数据与上一个单元格数据比较
        boolean dataBool = preData.equals(curData);

        //此处需要注意：所以获取每一行第一列数据和上一行第一列数据进行比较，如果相等合并
        boolean equals = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
        if (dataBool && equals) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
}

2.5、冻结窗口

        ExcelWriter writer = EasyExcelFactory.write(path).inMemory(Boolean.TRUE)
                .registerWriteHandler(new FreezeAndFilter())//冻结窗口
                .build();

package com.easyexcel.easyexcel.util;

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.Sheet;


/**
 * 项目认证模板-冻结窗口
 */
public class FreezeAndFilter implements SheetWriteHandler {

    /**
     * 水平拆分位置
     */
    private int colSplit = 0;

    /**
     * 拆分的垂直位置
     */
    private int rowSplit = 6;

    /**
     * 右窗格中可见的左列
     */
    private int leftmostColumn = 0;

    /**
     * 底部窗格中可见的顶行
     */
    private int topRow = 6;

    /**
     * 设置筛选范围
     * （参数A2：D2代表从第二行第A列到第二行第J列要作为筛选框的位置）
     */
    public String autoFilterRange = "A2:D2";

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

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        //设置冻结
        sheet.createFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
    }
}

2.6、指定区域公式

        //定义公式
        String formula = "IF(I7>=90,\"A\",IF(I7>=75,\"B\",IF(I7>=60,\"C\",\"D\")))";
        String formula2 = "round(20*(sumproduct(J$4:K$4,J7:K7)+sumproduct(L$4:S$4,L7:S7)/(sum(L$4:S$4)-sumif(L7:S7,\"该项目不涉及\",L$4:S$4))*0.7),0)";

直接使用字符串可以无法进行生效，必须对表格填充内容为 公式 的强制执行

	//抽取方法
    private static WriteCellData getWriteCellData(String formula) {
        FormulaData formulaData = new FormulaData();
        formulaData.setFormulaValue(formula);
        WriteCellData userAge = new WriteCellData<>();
        userAge.setFormulaData(formulaData);
        return userAge;
    }

	//填充到指定位置使用
	....   getWriteCellData(formula)  .....

三、导入Excel表格

3.1、导入信息

controller

package com.easyexcel.easyexcel.controller;

import com.easyexcel.easyexcel.service.ImportDataService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

@RestController
public class ImportDataController {

    @Autowired
    private ImportDataService importDataService;

    @PostMapping("/importAbilityPoint")
    public String importData(MultipartFile file) throws Exception {
        String s = importDataService.importData(file);
        return "success~~~";
    }

}

service

package com.easyexcel.easyexcel.service;

import org.springframework.web.multipart.MultipartFile;

public interface ImportDataService {

    String importData(MultipartFile file) throws Exception;

}

service-impl

package com.easyexcel.easyexcel.service.impl;

import com.easyexcel.easyexcel.service.ImportDataService;
import com.easyexcel.easyexcel.util.EasyExcelUtil;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

@Service
public class ImportDataServiceImpl implements ImportDataService {

    @Override
    public String importData(MultipartFile file) throws Exception {

        //获得表格内数据
        List<Object> data = EasyExcelUtil.readExcel(file.getInputStream());
        System.out.println("获取到数据为"  + data);
        
        return null;
    }
}

util

package com.easyexcel.easyexcel.util;

import com.alibaba.excel.EasyExcel;
import org.springframework.stereotype.Service;

import java.io.InputStream;
import java.util.List;

@Service
public class EasyExcelUtil {
    
    public static List<Object> readExcel(InputStream fileInputStream) {

        ExcelListener excelListener = new ExcelListener();
        EasyExcel.read(fileInputStream, null, excelListener).sheet().doRead();
        return excelListener.getDataList();
    }

}

package com.easyexcel.easyexcel.util;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;

public class ExcelListener extends AnalysisEventListener<Object> {

    private List<Object> dataList = new ArrayList<>();

    @Override
    public void invoke(Object data, AnalysisContext context) {
        dataList.add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }

    public List<Object> getDataList() {
        return dataList;
    }
}

注释就不解使了，层次分的很清楚，照着做就OK