具有合并单元格的Excel的读取

可以看到下图的Excel的例子中value1以及value2对应的单元格是为合并的单元格

若是按照一般的读取方法，读取出来的value值都是重复的，就不好和之前的key形成对应关系，
在下图中，每一个横向的key对应一个value，每个key都是不一样的
须知：
通过第一个key key start来知道我们需要开始的位置
通过最后一个key key end来知道我们循环结束的位置

须知：
我这边默认需要提取的value都是在合并单元格中，可以实现判断一下需要取值的单元格是不是合并单元格，我这边就没写了，但是判断的方法也包含在代码里面

下面贴出代码
皆是maven环境下

pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.yang</groupId>
  <artifactId>ImportExcel</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>ImportExcel</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>
  </dependencies>
</project>

ImportExcel

package com.yang;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

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

public class ImportExcel {
    public static void main(String[] args) throws Exception {
        importExcel("e:\\1.xlsx");
    }

    public static void importExcel(String file) throws Exception {
        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        String cellData = "";
        String tString = "";
        List<String> headArr = new ArrayList<String>();
        try {
            wb = ExcelUtil.create(file);
            if (wb != null) {
                //获取第一个sheet
                sheet = wb.getSheetAt(0);
                //获取最大行数
                int rownum = sheet.getPhysicalNumberOfRows();
                //获取第一行
                row = sheet.getRow(0);
                //获取最大列数
                int colnum = row.getPhysicalNumberOfCells();
                //全局的rwo显示
                int allRow = 1;
                for (int i = 0; i < rownum; i++) {
                    row = sheet.getRow(i);
                    if (row != null) {
                        tString=sheet.getRow(i).getCell(0)+"";
                        if (tString.trim().startsWith(
                                "key1 start")) {
                            //将当前行赋予全局row，以后从此遍历
                            allRow = i;
                            break;
                        }
                    }
                }

                //获取8到18行数据
                for (int i = allRow; i < rownum; i++) {
                    row = sheet.getRow(i);
                    if (row != null) {
                        for (int j = 0; j < colnum; j++) {
                            tString = ExcelUtil.getMergedRegionValue(sheet, i, j) + "";
                            j = ExcelUtil.returnLast(sheet, i, j) + 1;
                            cellData = ExcelUtil.getMergedRegionValue(sheet, i, j) + "";
                            j = ExcelUtil.returnLast(sheet, i, j) + 1;
                            headArr.add(cellData);
                            if (tString.startsWith("end")) {
                                allRow = i + 2;
                                break;
                            }
                        }
                        if (tString.startsWith("end")) {
                            break;
                        }
                    } else {
                        break;
                    }
                }
            }
            for(String string:headArr){
                System.out.println(string);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

ExcelUtil

package com.yang;


import com.sun.media.sound.InvalidFormatException;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

public class ExcelUtil {

    /**
     * 判断Excel 的版本。城建对应对象
     * @param file
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static Workbook create(String file) throws IOException,
            InvalidFormatException {
        boolean isExcel2003 = file.toLowerCase().endsWith("xls") ? true : false;
        Workbook workbook = null;
        if (isExcel2003) {
            workbook = new HSSFWorkbook(new FileInputStream(new File(file)));
        } else {
            workbook = new XSSFWorkbook(new FileInputStream(new File(file)));
        }
        return workbook;
    }


    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    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 ;
    }


    /**
     * 判断指定的单元格是否是合并单元格
     * @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  int returnLast(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 lastColumn;
                }
            }
        }
        return 1;
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){

        if(cell == null) return "";

        if(cell.getCellType() == Cell.CELL_TYPE_STRING){

            return cell.getStringCellValue();

        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){

            return String.valueOf(cell.getBooleanCellValue());

        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){

            return cell.getCellFormula() ;

        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){

            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                return sdf.format(
                        HSSFDateUtil.getJavaDate(cell.getNumericCellValue()))
                        .toString();
            } else {
                return new DecimalFormat("#.##")
                        .format(cell.getNumericCellValue());
            }
        }
        return "";
    }
}