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

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

目录

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

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

三、写入Excel简单示例 

一、读取、写入Excel工具类

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

/**
 * 工具类-使用poi读取Excel
 */
@Slf4j
public class ExcelUtilByPOI {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    /**
     * 读取excel文件中的全部表格
     * 描述：适用于没有单元格合并的excel，并且 (titleOfRow,titleOfColumn)位置为读取内容的起始位置的情况
     * 每一行构成一个map，key值是列标题，value是列值。没有值的单元格其value值为null
     * 返回结果最外层list对应excel文件，第二层Iterable对应sheet页，第三层map对应sheet页中一行
     *
     * @param filePath 文件路径
     * @param sheetCnt 读取的文件中前sheetCnt个sheet数据。如果值为-1，则读取所有的sheet，否则，读取前sheetCnt个sheet的数据。
     * @return
     * @throws Exception
     */
public static List<List<Map<String, Object>>> readExcelWithFiexPos(String filePath, int titleInRow, int titleInColumn, int sheetCnt) throws IOException {
        Workbook wb = null;
        try {
            List<List<Map<String, Object>>> 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<Map<String, Object>> 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();
            }
        }
    }

    /**
     * 读取excel文件中的全部表格
     * 描述：适用于没有单元格合并的excel，并且 以fiexedValue在sheet中的位置为读取内容起始位置的情况
     * 每一行构成一个map，key值是列标题，value是列值。没有值的单元格其value值为null
     * 返回结果最外层list对应excel文件，第二层Iterable对应sheet页，第三层map对应sheet页中一行
     *
     * @param fixedValue 固定值（第一个列标题）
     * @param filePath   文件路径
     * @param sheetCnt   读取的文件中前sheetCnt个sheet数据。如果值为-1，则读取所有的sheet，否则，读取前sheetCnt个sheet的数据。
     * @return
     * @throws Exception
     */
    public static List<List<Map<String, Object>>> readExcelWithFiexedTitle(String filePath, String fixedValue, int sheetCnt) throws IOException {
        Workbook wb = null;
        try {
            List<List<Map<String, Object>>> 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<Integer> posForSheet = readPosForValue(sheet, fixedValue);
                    List<Map<String, Object>> 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();
            }
        }
    }

    /**
     * 读取excel文件个sheet第rowNum行的内容，从firstColNum列开始往后读取到第lastColNum列
     *
     * @param filePath
     * @param rowNum
     * @param firstColNum
     * @param lastColNum
     * @return
     */
    public static List<List<Object>> readRowData(String filePath, int rowNum, int firstColNum, int lastColNum) throws IOException {
        List<List<Object>> 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<Object> sheetContentList = new ArrayList<>();
            Cell cell = null;
            String value = null;
            for (int i = firstColNum; i < lastColNum; i++) { //读取指定行的内容
                cell = row.getCell(i);
                value = readCellByType(cell);
                sheetContentList.add(value);
            }
            dataList.add(sheetContentList);

        }

        return dataList;
    }

    /**
     * 读取给定sheet的内容
     * 描述：
     * 读取excel文件中的指定名称的表格 用于没有单元格合并的表格，且 (titleOfRow,titleOfColumn)位置为读取内容的起始位置的情况
     * 每一行构成一个map(key值是列标题，value是列值)。没有值的单元格其value值为null。
     * 返回结果最外层的list对应一个sheet页，第二层的map对应sheet页中的一行。
     *
     * @param sheet
     * @return
     */
    private static List<Map<String, Object>> readSheet(Sheet sheet, int titleInRow, int titleInColumn) {
        List<Map<String, Object>> sheetList = null;
        sheetList = new ArrayList<Map<String, Object>>();
        List<String> titles = new ArrayList<>();
        int rowSize = sheet.getLastRowNum() + 1;
        for (int i = titleInRow; i < rowSize; i++) {
            Row row = sheet.getRow(i);
            if (row == null)
                continue;
            ;//略过空行
            int cellSize = row.getLastCellNum();
            if (i == titleInRow) //标题行
            {
                for (int j = titleInColumn; j < cellSize; j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        titles.add(cell.toString());
                    }
                }
            } else { //对应每一行的数据
                Map<String, Object> rowDataMap = new LinkedHashMap<>();
                for (int j = titleInColumn; j < titleInColumn + titles.size(); j++) {
                    Cell cell = row.getCell(j);
                    String value = null;
                    CellType cellType = null;
                    if (cell == null) {
                        continue;
                    }
                    cellType = cell.getCellTypeEnum();
                    switch (cellType) {
                        case STRING:
//                                 value = cell.getRichStringCellValue().getString();
                            value = cell.getStringCellValue();
                            break;
                        case NUMERIC: //包含日期和普通数字
                            if (DateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();
                                DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                                value = df.format(date);
                            } else {
                                double cellValue = cell.getNumericCellValue();
                                value = String.valueOf(cellValue);
                                // 下面的代码，会自动舍弃读取单元格中显示的值（可能是做了round（）之后的结果），不是单元格中最原本的值
                                /*NumberFormat nf = NumberFormat.getInstance();
                                String tmpValue = nf.format(cell.getNumericCellValue());
                                if (tmpValue.indexOf(",") >= 0) {
                                    tmpValue = tmpValue.replaceAll(",", "");
                                }
                                value = tmpValue;*/

                            }
                            break;
                        case FORMULA:
//                                 cell.getCellFormula();
                            cell.setCellType(CellType.STRING);
                            value = cell.getStringCellValue();
                            break;
                        case BOOLEAN:
                            value = String.valueOf(cell.getBooleanCellValue());
                            break;
                        default:
                            if (cell != null) {
                                value = cell.toString();
                            }
                    }
                    String key = titles.get(j - titleInColumn);
                    rowDataMap.put(key, value);
                }
                sheetList.add(rowDataMap);
            }
        }
        return sheetList;
    }

    /**
     * 读取sheet中指定值的位置
     *
     * @param sheet
     * @return
     */
    private static List<Integer> readPosForValue(Sheet sheet, String fixedValue) {
        List<Integer> posList = new ArrayList();
        Object value = null;
        for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) { // 获取每行
            XSSFRow row = (XSSFRow) sheet.getRow(j);
            if (row != null) {
                for (int k = 0; k < sheet.getRow(0).getPhysicalNumberOfCells(); k++) { // 获取每个单元格
                    Cell cell = row.getCell(k);
                    if (cell == null) {
                        continue;
                    }
                    CellType cellTypeEnum = cell.getCellTypeEnum();
                    switch (cellTypeEnum) {
                        case STRING:
                            value = cell.getRichStringCellValue().getString();
                            break;
//                        case Cell.CELL_TYPE_NUMERIC:
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                value = cell.getDateCellValue();
                            } else {
                                double cellValue = cell.getNumericCellValue();
                                value = String.valueOf(cellValue);
                               /* // 下面的代码，会自动舍弃读取单元格中显示的值（可能是做了round（）之后的结果），不是单元格中最原本的值
                                NumberFormat nf = NumberFormat.getInstance();
                                String tmpValue = nf.format(cell.getNumericCellValue());
                                if (tmpValue.indexOf(",") >= 0) {
                                    tmpValue = tmpValue.replaceAll(",", "");
                                }
                                value = tmpValue;*/
                            }
                            break;
                        case BOOLEAN:
                            value = cell.getBooleanCellValue();
                            break;
                        case FORMULA:
                            value = cell.getCellFormula();
                            break;
                        default:
                            value = "";
                            break;
                    }
                    if (fixedValue.equals(value.toString())) {
                        posList.add(cell.getRowIndex());
                        posList.add(cell.getColumnIndex());
                        break;
                    }
                }
            }
            if (!BeanUtil.isEmpty(posList)) {
                break;
            }
        }
        return posList;
    }

    /**
     * 根据excel的版本，获取相应的Workbook
     *
     * @param file
     * @return
     */
    public static Workbook getWorkbook(File file) throws IOException {
        Workbook wb = null;
        InputStream fis = new FileInputStream(file);
        if (file.getName().endsWith(EXCEL_XLS)) //2003
        {
            wb = new HSSFWorkbook(fis);
        } else if (file.getName().endsWith(EXCEL_XLSX)) {
            wb = new XSSFWorkbook(fis);//2007 2010
        }
        if (fis != null) {
            fis.close();
        }
        return wb;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }
 /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedCellValue(Sheet sheet, int row, int column) {
        String value = null;
        int mergedCellCnt = sheet.getNumMergedRegions();
        for (int i = 0; i < mergedCellCnt; i++) {
            CellRangeAddress mergedCell = sheet.getMergedRegion(i);
            int firstColumn_pos = mergedCell.getFirstColumn();
            int lastColumn_pos = mergedCell.getLastColumn();
            int firstRow_pos = mergedCell.getFirstRow();
            int lastRow_pos = mergedCell.getLastRow();
            if (row >= firstRow_pos && row <= lastRow_pos) {
                if (column >= firstColumn_pos && column <= lastColumn_pos) {
                    Row firstRow = sheet.getRow(firstRow_pos);
                    Cell cell = firstRow.getCell(firstColumn_pos);
                    if (cell == null) {
                        continue;
                    }
                    CellType cellType = cell.getCellTypeEnum();
                    switch (cellType) {
                        case STRING:
//                                 value = cell.getRichStringCellValue().getString();
                            value = cell.getStringCellValue();
                            break;
                        case NUMERIC: //包含日期和普通数字
                            if (DateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();
                                DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                                value = df.format(date);
                            } else {
                                double cellValue = cell.getNumericCellValue();
                                value = String.valueOf(cellValue);

                         /*       下面的代码，会自动舍弃读取单元格中显示的值（可能是做了round（）之后的结果），不是单元格中最原本的值
                                NumberFormat nf = NumberFormat.getInstance();
                                String tmpValue = nf.format(cell.getNumericCellValue());
                                if (tmpValue.indexOf(",") >= 0) {
                                    tmpValue = tmpValue.replaceAll(",", "");
                                }
                                value = tmpValue;*/
                            }
                            break;
                        case FORMULA:
//                                 cell.getCellFormula();
                            cell.setCellType(CellType.STRING);
                            value = cell.getStringCellValue();
                            break;
                        case BOOLEAN:
                            value = String.valueOf(cell.getBooleanCellValue());
                            break;
                        default:
                            if (cell != null) {
                                value = cell.toString();
                            }
                    }
                    return cell == null || value == null ? "" : value;
                }
            }

        }

        return null;
    }

    /**
     * 读取单元格的类型
     *
     * @param cell
     * @return
     */
    public static String readCellByType(Cell cell) {
        if (cell == null) {
            return null;
        }
        CellType cellType = cell.getCellTypeEnum();
        String value = null;
        switch (cellType) {
            case STRING:
//                                 value = cell.getRichStringCellValue().getString();
                value = cell.getStringCellValue();
                break;
            case NUMERIC: //包含日期和普通数字
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                    value = df.format(date);
                } else {
                    double cellValue = cell.getNumericCellValue();
                    value = String.valueOf(cellValue);

                 /* 下面的代码，会自动舍弃读取单元格中显示的值（可能是做了round（）之后的结果），不是单元格中最原本的值
                    NumberFormat nf = NumberFormat.getInstance();
                    String tmpValue = nf.format(cell.getNumericCellValue());
                    if (tmpValue.indexOf(",") >= 0) {
                        tmpValue = tmpValue.replaceAll(",", "");
                    }
                    value = tmpValue;*/
                }
                break;
            case FORMULA:
//                                 cell.getCellFormula();
                cell.setCellType(CellType.STRING);
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            default:
                if (cell != null) {
                    value = cell.toString();
                }
        }
        return cell == null || value == null ? "" : value;
    }

    /**
     * 读取一个excel文件中所有的sheet名字列表
     *
     * @param filePath 文件路径
     * @return
     * @throws Exception
     */
    public static List<String> readSheetNames(String filePath) throws Exception {
        Workbook wb = null;
        List<String> sheetNames = new ArrayList<String>();
        try {
            if (filePath.endsWith(EXCEL_XLS) || filePath.endsWith(EXCEL_XLSX)) {
                // 读取Excel文档
                File file = new File(filePath);
                wb = getWorkbook(file);
                int sheetSize = wb.getNumberOfSheets();
                // 遍历sheet页
                for (int i = 0; i < sheetSize; i++) {
                    Sheet sheet = wb.getSheetAt(i);
                    sheetNames.add(sheet.getSheetName());
                }
            } else {
                throw new BaseException(EPowerResultCode.JUDGE_EXCEL_FILE);
            }
            return sheetNames;

        } catch (FileNotFoundException e) {
            log.error(e.getMessage());
            throw e;
        } finally {
            if (wb != null) {
                wb.close();
            }
        }
    }

    /**
     * 读取excel中所有sheet的标题
     *
     * @return
     */
    public static List<String> readSheetTitles(String filePath) throws Exception {
        Workbook wb = null;
        List<String> titleList = new ArrayList<String>();
        try {
            if (filePath.endsWith(EXCEL_XLS) || filePath.endsWith(EXCEL_XLSX)) {
                // 读取Excel文档
                File file = new File(filePath);
                wb = getWorkbook(file);
                int sheetSize = wb.getNumberOfSheets();
                for (int i = 0; i < sheetSize; i++) {
                    Sheet sheet = wb.getSheetAt(i);
                    Row row = sheet.getRow(0);//默认第一行为表头
                    short lastCellNum = row.getLastCellNum();//共有多少列
                    for (int j = 0; j < lastCellNum; j++) {
                        Cell cell = row.getCell(j);
                        titleList.add(cell.getStringCellValue().trim());
                    }
                }
            }
            return titleList;

        } catch (Exception e) {
            log.error(e.getMessage());
            throw e;
        } finally {
            if (wb != null) {
                wb.close();
            }
        }
    }

    /**
     * 导出文件
     * @param request
     * @param response
     * @param wb
     * @param fileName
     */
    public static void exportFile(HttpServletRequest request, HttpServletResponse response, Workbook wb, String fileName)  throws Exception{
        String userAgent = request.getHeader("USER-AGENT");
        if (userAgent.contains("MSIE")) {// IE浏览器
            fileName = URLEncoder.encode(fileName, "UTF8");
        } else if (userAgent.contains("Mozilla")) {// google,火狐浏览器
            fileName = new String(fileName.getBytes(), "ISO8859-1");
        } else {
            fileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器
        }
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
        OutputStream outputStream = response.getOutputStream();
        wb.write(outputStream);
    }

    }

二、Excel样式设置工具类

/**
 * 导出的Excel单元格样式设置
 */
public class ExcelStyleUtil {
    /**
     * 表头字体
     *
     * @return
     */
    public static Font headerFoot(Workbook wb) {
        Font headerFont = wb.createFont();
        headerFont.setFontName("微软雅黑");
        headerFont.setFontHeightInPoints((short) 13);
        headerFont.setBold(true);
//        headerFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        headerFont.setColor(IndexedColors.BLACK.getIndex());
        return headerFont;
    }

    /**
     * 正文字体
     *
     * @param wb
     * @return
     */
    public static Font contextFont(Workbook wb) {
        Font contextFont = wb.createFont();
        contextFont.setFontName("微软雅黑");
        contextFont.setFontHeightInPoints((short) 13);
        contextFont.setBold(false);
        contextFont.setColor(IndexedColors.BLACK.getIndex());
//        contextFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        return contextFont;
    }


    /**
     * 表头样式-左右上下居中
     *
     * @param wb
     * @return
     */
    public static CellStyle headerStyle(Workbook wb) {
        CellStyle headerStyle = wb.createCellStyle();
        Font font = headerFoot(wb);
        headerStyle.setFont(font);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        headerStyle.setLocked(true);//锁定
        headerStyle.setWrapText(false);// 自动换行
        headerStyle.setBorderBottom(BorderStyle.THIN);//下边框
        headerStyle.setBorderTop(BorderStyle.THIN);//上边框
        headerStyle.setBorderLeft(BorderStyle.THIN);//左
        headerStyle.setBorderRight(BorderStyle.THIN);//右
        return headerStyle;
    }


    /**
     * 单元格样式 - 水平、垂直居中
     *
     * @param wb
     * @return
     */
    public static CellStyle contextAlignCenterStyle(Workbook wb) {

        CellStyle style = wb.createCellStyle();
        Font font = contextFont(wb);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setLocked(true);
        style.setWrapText(true);// 自动换行
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderLeft(BorderStyle.THIN);//左
        style.setBorderRight(BorderStyle.THIN);//右
        return style;
    }

    /**
     * 单元格样式-无左边框
     * @param wb
     * @return
     */
    public static CellStyle contextNoLeftBorder(Workbook wb){
        CellStyle style = wb.createCellStyle();
        Font font = contextFont(wb);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setLocked(true);
        style.setWrapText(true);// 自动换行
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右
        return style;
    }
    /**
     * 单元格样式-无左右边框
     * @param wb
     * @return
     */
    public static CellStyle contextNoLeftRightBorder(Workbook wb){
        CellStyle style = wb.createCellStyle();
        Font font = contextFont(wb);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setLocked(true);
        style.setWrapText(true);// 自动换行
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        return style;
    }
}

三、写入Excel简单示例 

 public void testWriteExcel(HttpServletRequest request, HttpServletResponse response){
        //1.创建book
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("sheet名称，可为空");
        //2.创建sheet
        sheet.createFreezePane(1, 2, 0, 0);//冻结首列和前两行


        CellStyle headerStyle = ExcelStyleUtil.headerStyle(wb);
        //3.创建行
        Row row0 = sheet.createRow(0);
        //4.创建单元格
        Cell cell00 = row0.createCell(0);
        cell00.setCellStyle(headerStyle);//设置单元格样式
        cell00.setCellValue("单元格值");
        CellRangeAddress mergeReion = new CellRangeAddress(0, 0, 0, 2);
        sheet.addMergedRegion(mergeReion);//合并首行的前两个单元格

        //（1）第二行设置表头
        Row row1 = sheet.createRow(1);
        for (int i = 0; i < titles.size(); i++) {
            Object title = titles.get(i);
            Cell cell1i = row1.createCell(i);
            cell1i.setCellValue(String.valueOf(title));
            cell1i.setCellStyle(headerStyle);
        }
        
        //（2）下面设置表体数据：创建10行3列的表体
        CellStyle contextStyle = ExcelStyleUtil.contextAlignCenterStyle(wb);//内容样式
        for (int i = 0; i < 10; i++) {
            Row rowi = sheet.createRow(i + 2);
            for(int j=0;j<3;j++){
                Cell cellij = rowi.createCell(j);
                sheet.setColumnWidth(j,5000);//设置列宽
                cellij.setCellStyle(contextStyle);
            }
        }
        //（3）导出到浏览器
        try{
            ExcelUtilByPOI.exportFile(request,response,wb,"文件名");//调用工具方法
        }catch (Exception e){
            throw new BaseException(EPowerResultCode.DOWNLOAD_FILE_FAIL);
        }
    }