POI获取合并单元格的值

POI获取合并单元格时，如果是遍历获取合并单元格的所有子单元格的值，office的excel都会有值，wps的excel只会第一个子单元格有值，其他子单元格都没值，即cell=null。故意凡是获取合并单元格的值，都获取第一个子单元格的值即可 

package com.oop.createObj;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
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.util.CellRangeAddress;
import java.io.FileInputStream;
import java.util.Arrays;
import java.util.stream.Stream;

/**
 * @ProjectName: spring-boot-lantian
 * @Package: com.oop.createObj
 * @ClassName: SendFactoryTest
 * @Author: zhangs
 * @Email: 853632587@qq.com
 * @Description: 工厂模式测试
 * @Date: 2020/11/4 15:53
 * @Version: 1.0
 */
public class SendFactoryTest {

    public static void main(String[] args) throws Exception {

        //Sender mail= SendFactory.produceMail();
        //mail.send();

        HSSFWorkbook wk=new HSSFWorkbook(new FileInputStream("E://订单导出.xls"));

        HSSFSheet sheet=wk.getSheetAt(0);

        int rows=sheet.getPhysicalNumberOfRows();

        int cells=sheet.getRow(0).getPhysicalNumberOfCells();

        for(int r=0;r<rows;r++){

            HSSFRow row=sheet.getRow(r);
            StringBuilder str=new StringBuilder();
            for(int c=0;c<cells;c++){
                HSSFCell cell=row.getCell(c);

                if(isMergedRegion(sheet,r,c)){
                    str.append(">>>"+getMergedRegionValue(sheet,r,c)+"||");
                }else{

                    str.append(">>>"+row.getCell(c)+"||");
                }

            }
            System.out.println(r+"str"+str);
            str.setLength(0);

        }

    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    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;
    }

    /**
     * 获取合并单元格的值
     * @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 cell
     * @return
     */
    public  static String getCellValue(Cell cell){
        if(cell == null) return "";
        return cell.toString();
    }

    public static void main1(String[] args){


        Object[][] rowName = new Object[][]{
          {"序号",2000,"NM"},
                {"分组编号",2000,"NM"},
                {"订单编号",6500,"NM"},
                {"第三方订单号",6500,"CM"},
                {"SKU",2500,"NM"},
                {"订单金额",2500,"CM"},
                {"运费",2500,"CM"},
                {"商品名称",12000,"NM"},
                {"规格",7000,"NM"},
                {"单位",2500,"NM"},
                {"数量",2500,"NM"},
          {"单价",2500,"NM"},
                {"金额",2500,"NM"}, {"税率",2500,"NM"},{"税额",2500,"NM"},{"结算主体",7000,"M"},{"发票抬头",12000,"M"},{"发票类型",2500,"M"},{"客户名称",7000,"M"},{"项目名称",5000,"M"},{"采购员",5000,"M"},
          {"收件人",6500,"M"},{"详细地址",7000,"M"},{"店铺",7000,"M"},{"订单状态",2500,"CM"},{"订单标签",4500,"CM"},{"结算状态",4500,"CM"},{"发票编号",5000,"CM"},{"订单开始时间",5000,"CM"},{"审批完成时间",5000,"CM"},{"订单完成时间",5000,"CM"},
          {"下单月",5000,"CM"}, {"订单完成月",5000,"CM"},{"商品一级分类",5000,"NM"}, {"商品二级分类",5000,"NM"}, {"商品三级分类",5000,"NM"}};


        Stream.iterate(0, i -> i + 1).limit(rowName.length).forEach(i -> System.out.println(rowName[i][2]));


    }
}