poi获取合并单元格的值

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

以下是用到的操作类

package com.csair.tang.carmanager.utils;

import com.csair.tang.carmanager.excel.ExcelFormat;
import com.csair.tang.carmanager.excel.ExcelInputStreamDto;
import com.csair.tang.carmanager.excel.Region;
import com.csair.tang.carmanager.exception.ServiceException;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Up on 2016/8/26.
 */
public class ExcelUtil {

    private final static Logger LOG = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * @author wcyong
     * @date 2013-6-21
     */
    public static Workbook getWorkbook(InputStream is, ExcelFormat format)
            throws IOException {
        Workbook wb = null;
        if (format == ExcelFormat.xls) {// HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
            wb = new HSSFWorkbook(is);
        } else if (format == ExcelFormat.xlsx) {// XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
            wb = new XSSFWorkbook(is);
        }
        return wb;
    }

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

    /**
     * 如果excel是wps格式，获取合并单元格的cell时，cell会是null，此时不能用该方法，请用getMergedRegionValue(Sheet sheet, int row, int column)
     * @description
     * @author liuzhenpeng
     * @date 2017年2月16日
     * @param sheet
     * @param cell
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet, Cell cell) {
        return getMergedRegionValue(sheet, cell.getRowIndex(),
                cell.getColumnIndex());
    }

    /**
     * 判断合并了行
     * 
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static boolean isMergedRow(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 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;
    }

    /**
     * 如果excel是wps格式，获取合并单元格的cell时，cell会是null，此时不能用该方法，请用isMergedRegion(Sheet sheet, int row, int column)
     * @description
     * @author liuzhenpeng
     * @date 2017年2月16日
     * @param sheet
     * @param cell
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet, Cell cell) {
        int row = cell.getRowIndex();
        int column = cell.getColumnIndex();
        return isMergedRegion(sheet, row, column);
    }

    public static boolean isCellInRegion(int rowIndex, int colIndex,
            Region region) {
        if (rowIndex >= region.getFirstRow() && rowIndex <= region.getLastRow()) {
            if (colIndex >= region.getFirstColumn()
                    && colIndex <= region.getLastColumn()) {
                return true;
            }
        }
        return false;
    }

    public static boolean isCellInRegion(Cell cell, Region region) {
        return isCellInRegion(cell.getRowIndex(), cell.getColumnIndex(), region);
    }

    public static Region getMergedRegion(Sheet sheet, int rowIndex, int colIndex) {
        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 (rowIndex >= firstRow && rowIndex <= lastRow) {
                if (colIndex >= firstColumn && colIndex <= lastColumn) {
                    Region region = new Region();
                    region.setFirstRow(firstRow);
                    region.setLastRow(lastRow);
                    region.setFirstColumn(firstColumn);
                    region.setLastColumn(lastColumn);
                    return region;
                }
            }
        }
        return null;
    }

    public static Region getMergedRegion(Sheet sheet, Cell cell) {
        return getMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());
    }

    /**
     * 判断sheet页中是否含有合并单元格
     * 
     * @param sheet
     * @return
     */
    public static boolean hasMerged(Sheet sheet) {
        return sheet.getNumMergedRegions() > 0 ? true : false;
    }

    /**
     * 合并单元格
     * 
     * @param sheet
     * @param firstRow
     *            开始行
     * @param lastRow
     *            结束行
     * @param firstCol
     *            开始列
     * @param lastCol
     *            结束列
     */
    public static void mergeRegion(Sheet sheet, int firstRow, int lastRow,
            int firstCol, int lastCol) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol,
                lastCol));
    }

    /**
     * 获取单元格的值
     * 
     * @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) {
            return String.valueOf(cell.getNumericCellValue());
        }
        return "";
    }

    public static ExcelInputStreamDto getUploadExcelInputStream(
            HttpServletRequest request, Long maxExcelFileSize)
            throws IOException {
        String[] allowExtensions = { ".et", ".ett", ".xls", ".xlsx" };
        CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
                request.getSession().getServletContext());
        // 判断 request 是否有文件上传,即多部分请求
        if (multipartResolver.isMultipart(request)) {
            // 转换成多部分request
            MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;

            MultipartFile file = multiRequest.getFile("import");

            if (file == null || file.getSize() == 0) {
                throw new ServiceException("文件不存在");
            }

            if (maxExcelFileSize != null && file.getSize() > maxExcelFileSize) {
                throw new ServiceException("文件过大");
            }
            boolean extMatch = false;
            ExcelFormat excelFormat = null;
            for (String ext : allowExtensions) {
                if (file.getOriginalFilename().endsWith(ext)) {
                    extMatch = true;
                    if (".xls".equalsIgnoreCase(ext)) {
                        excelFormat = ExcelFormat.xls;
                    } else if (".xlsx".equalsIgnoreCase(ext)) {
                        excelFormat = ExcelFormat.xlsx;
                    } else if (".et".equalsIgnoreCase(ext)
                            || ".ett".equalsIgnoreCase(ext)) {
                        // WPS的et和ett格式可能内部是xls，也可能是xlsx，只能通过读取文件头判断
                        if (file.getSize() < 2) {
                            // 如果文件小于2字节，无法判断文件头，则直接返回格式不正确
                            throw new ServiceException("不正确的文件格式");
                        }
                        byte[] fileHeaderBytes = new byte[2];
                        InputStream is = file.getInputStream();
                        is.read(fileHeaderBytes, 0, 2);
                        String fileHeaderHex = GetTypeByHead
                                .bytesToHexString(fileHeaderBytes);
                        if ("504B".equalsIgnoreCase(fileHeaderHex)) {
                            excelFormat = ExcelFormat.xlsx;
                        } else if ("D0CF".equalsIgnoreCase(fileHeaderHex)) {
                            excelFormat = ExcelFormat.xls;
                        }
                    } else {
                        throw new ServiceException("不正确的文件格式");
                    }
                    break;
                }
            }
            if (!extMatch) {
                throw new ServiceException("不正确的文件格式");
            }
            ExcelInputStreamDto result = new ExcelInputStreamDto();
            result.setExcelFormat(excelFormat);
            result.setInputStream(file.getInputStream());
            return result;
        }
        throw new ServiceException("不正确的请求");
    }

    /**
     * 判断Row(行)是否为空行(行本身为null或行中的单元格全部为null)
     * @param row
     * @return
     */
    public static boolean isEmptyRow(Row row) {
        if (row != null) {
            short lastCellNum = row.getLastCellNum();
            if (lastCellNum == 0) {// 如果不存在单元格则返回true
                return true;
            } else {
                // 空单元格的个数
                int emptyCellNum = 0;
                for (int i = 0; i < lastCellNum; i++) {
                    Cell cell = row.getCell(i);
                    if (isEmptyCell(cell)) {
                        emptyCellNum++;
                    }
                }
                if (emptyCellNum == lastCellNum) {
                    return true;
                }
            }
        } else {
            return true;
        }
        return false;
    }

    /**
     * 判断Row(行)是否存在空的单元格或者这行是否存在单元格
     * @param row
     * @return
     */
    public static boolean rowContianEmptyCell(Row row) {
        if (row != null) {
            short lastCellNum = row.getLastCellNum();
            if (lastCellNum == 0) {// 如果不存在单元格则返回true
                return true;
            } else {
                for (int i = 0; i < lastCellNum; i++) {
                    Cell cell = row.getCell(i);
                    if (isEmptyCell(cell)) {
                        return true;
                    }
                }
            }
        } else {
            return true;
        }
        return false;
    }

    /**
     * 判断Sheet是否存在空的行或存在空数据的行
     * @param sheet
     * @return
     */
    public static boolean sheetContainEmptyRow(Sheet sheet) {
        if (sheet != null) {
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum == 0) {// 如果不存在sheet则返回true
                return true;
            } else {
                for (int i = 0; i < lastRowNum; i++) {
                    Row row = sheet.getRow(i);
                    if (isEmptyRow(row)) {
                        return true;
                    }
                }
            }
        } else {
            return true;
        }
        return false;
    }

    /**
     * 基于指定列数判断Sheet是否存在空的行或存在空数据的行
     * @param sheet
     * @param columnNum
     * @return
     */
    public static boolean sheetContainEmptyRow(Sheet sheet, int columnNum) {
        if (sheet != null) {
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum == 0) {// 如果不存在sheet则返回true
                return true;
            } else {
                if (lastRowNum >= columnNum) {
                    for (int i = 0; i < columnNum; i++) {
                        Row row = sheet.getRow(i);
                        if (isEmptyRow(row)) {
                            return true;
                        }
                    }
                }else{
                    return true;
                }
            }
        } else {
            return true;
        }
        return false;
    }
    /**
     * 获取表格中空行的行号
     * @param sheet
     * @return
     */
    public static List<Integer> getEmptyRowNos(Sheet sheet){
        List<Integer> list=new ArrayList<>();
        if (sheet != null) {
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum != 0) {// 如果不存在sheet则返回true
                for (int i = 0; i < lastRowNum; i++) {
                    Row row = sheet.getRow(i);
                    if (isEmptyRow(row)) {
                        list.add(i);
                    }
                }
            } 
        }
        return list;
    }
    
    /**
     * 判断Cell(单元格)是否为空
     * 
     * @param cell
     * @return
     */
    public static boolean isEmptyCell(Cell cell) {
        String cellContent = getCellValue(cell);
        if(StringUtils.hasText(cellContent)){
            return false;
        } else{
            return true;
        }
    }

    /**
     * 关闭workbook
     * 
     * @param workbook
     */
    public static void closeWorkbook(Workbook workbook) {
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {
                LOG.error("关闭workbook失败", e);
            }
        }
    }
    
    public static void addDataToRow(Row row,List<String> values){
        if(values!=null && !values.isEmpty()){
            for (int i=0;i<values.size();i++) {
                Cell cell=row.createCell(i);
                cell.setCellValue(values.get(i));
            }
        }
    }
}