java excel合并单元格读取

转自  https://blog.csdn.net/qq_21454973/article/details/80581459 并稍微改动60行 读取cell内容。

文件： 

输出： 

厂家1_1000000_自然人11_196493_
厂家1_1000000_自然人12_164194_
厂家1_1000000_自然人13_17269_
厂家1_1000000_自然人14_56635_
厂家1_1000000_自然人15_565406_
厂家2_2000000_自然人21_483501_
厂家2_2000000_自然人22_621527_
厂家2_2000000_自然人23_415647_
厂家2_2000000_自然人24_54321_
厂家2_2000000_自然人25_425002_

pom.xml

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.15</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.15</version>
</dependency>

代码：

package com.navitek.utils;



import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;

/**
 * @Author: syl
 * @Date: 2019/7/3 0003 16:39
 * @Description:
 */

public class ExcelUtils {

    public static void main(String[] args) {
        getAllByExcel("E:\\all_temp\\temp.xls");

    }
    public static void getAllByExcel(String filepath) {
        try {
            // 同时支持Excel 2003、2007
            File excelFile = new File(filepath); // 创建文件对象
            FileInputStream is = new FileInputStream(excelFile); // 文件流
            Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel

            String[] res = readExcel(workbook, 0, 1, 0);
            for (int i = 0; i < res.length; i++) {
                System.out.println(res[i]);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private static String[] readExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine) {
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        String[] res = new String[sheet.getLastRowNum() - tailLine + 1];

        for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {

            row = sheet.getRow(i);
            res[i] = "";
            for (Cell c : row) {
                boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
                // 判断是否具有合并单元格
                if (isMerge) {
                    String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
                    //System.out.print(rs + "_"+ row.getRowNum()+"_"+c.getColumnIndex() +"_");
                    res[i] += rs+ "_";
                } else {
                    //System.out.print(c.getRichStringCellValue() + "");
                    res[i] += getCellValue(c)+ "_";
                }
            }
            //System.out.println();
        }

        if(startReadLine > 0){
            String[] result = new String[res.length - startReadLine];
            for (int i = 0; i < startReadLine; i++) {
                for (int j = 0; j < res.length; j++) {
                    if(j == res.length - 1)
                        continue;
                    res[j] = res[j+1];
                }
            }
            for (int i = 0; i < result.length; i++) {
                result[i] = res[i];
            }
            return result;
        }else{
            return res;
        }
    }

    private 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;
    }

    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {

                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }

        return null;
    }

    private static String getCellValue(Cell cell) {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        String cellValue = "";
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_STRING: // 文本
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC: // 数字、日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = fmt.format(cell.getDateCellValue()); // 日期型
                } else {
                    cellValue = String.valueOf((int) cell.getNumericCellValue()); // 数字
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN: // 布尔型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK: // 空白
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_ERROR: // 错误
                cellValue = "错误";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = "错误";
                break;
            default:
                cellValue = "错误";
        }
        return cellValue;
    }
}