在工作中经常会遇到操作excel的需求，对于格式简单、统一的excel可以选择EasyExcel来实现功能，很简单方便；而对于复杂的excel文件，如有各式各样的合并单元格、表头不固定、行列跟随业务数据动态变化……格式变幻多端的文件，easyExcel就显得无能为力了，选择poi就可以解决此问题了。

这里贴上在工作中解决问题时，自己封装的工具类，方便大家查阅：

目录

一、读取Excel、写入Excel文件工具类

二、导出Excel文件的样式设置工具类

三、写入Excel简单示例 

一、读取、写入Excel工具类

注：poi版本为org.apache.poi:4.1.0

@Slf4j
public class ExcelUtilByPOI {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    
public static List>> readExcelWithFiexPos(String filePath, int titleInRow, int titleInColumn, int sheetCnt) throws IOException {
        Workbook wb = null;
        try {
            List>> excelData = new ArrayList<>();
            if (filePath.endsWith(EXCEL_XLS) || filePath.endsWith(EXCEL_XLSX)) {
                File file = new File(filePath);
                wb = getWorkbook(file);
                int sheetSize = sheetCnt == -1 ? wb.getNumberOfSheets() : sheetCnt;
                //遍历sheet
                for (int i = 0; i < sheetSize; i++) {
                    Sheet sheet = wb.getSheetAt(i);
                    List> theSheetData = readSheet(sheet, titleInRow, titleInColumn);
                    excelData.add(theSheetData);
                }
            } else {
                log.error("读取的不是Excel文件");
            }
            return excelData;
        } catch (FileNotFoundException e) {
            throw e;
        } finally {
            if (wb != null) {
                wb.close();
            }
        }
    }

    
    public static List>> readExcelWithFiexedTitle(String filePath, String fixedValue, int sheetCnt) throws IOException {
        Workbook wb = null;
        try {
            List>> excelData = new ArrayList<>();
            if (filePath.endsWith(EXCEL_XLS) || filePath.endsWith(EXCEL_XLSX)) {
                File file = new File(filePath);
                wb = getWorkbook(file);
                int sheetSize = sheetCnt == -1 ? wb.getNumberOfSheets() : sheetCnt;
                //遍历sheet
                for (int i = 0; i < sheetSize; i++) {
                    Sheet sheet = wb.getSheetAt(i);
                    List posForSheet = readPosForValue(sheet, fixedValue);
                    List> theSheetData = readSheet(sheet, posForSheet.get(0), posForSheet.get(1));
                    excelData.add(theSheetData);
                }
            } else {
                log.error("读取的不是Excel文件");
            }
            return excelData;
        } catch (FileNotFoundException e) {
            throw e;
        } finally {
            if (wb != null) {
                wb.close();
            }
        }
    }

    
    public static List> readRowData(String filePath, int rowNum, int firstColNum, int lastColNum) throws IOException {
        List> dataList = new ArrayList<>();

        File file = new File(filePath);
        Workbook wb = getWorkbook(file);
        int sheetCnt = wb.getNumberOfSheets();

        for (int cnt = 0; cnt < sheetCnt; cnt++) { //遍历每一个sheet
            Sheet sheet = wb.getSheetAt(cnt);
            Row row = sheet.getRow(rowNum);
            lastColNum = lastColNum == -1 ? row.getLastCellNum() : lastColNum; //如果没有lastColNum则为其值为最后一列

            List