Java-poi导入excel合并单元格

要导入的excel示例

一、Action

@Action("/import-excel")
    public void toimport() {

        String root = ServletActionContext.getServletContext().getRealPath("/") + fileName;

        if (!StringUtils.isBlank(fileName)) {

            try {
                final InputStream inputStream = new FileInputStream(new File(root));
                 Workbook workbook = WorkbookFactory.create(inputStream);
                String msg = importExcelService.importArtical(workbook);
                sendMsg(true, "", msg);
            } catch (final Throwable e) {
                e.printStackTrace();
                sendFailMsg("", "导入失败！");
            } 
        } else {
            sendFailMsg("没有发现上传文件！", "");
        }
    }

二、ServiceImpl
service层做业务逻辑处理

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import com.bronna.util.PoiExcelUtil;

@Service
public class ImportExcelServiceImpl implements ImportExcelService {
    @Override
    public String importArtical(Workbook workbook) {

        final Sheet xssSheet0 = workbook.getSheetAt(0);
        Row row;

        List<CellRangeAddress> cras = PoiExcelUtil.getCombineCell(xssSheet0);

        for (int i = 1; i < xssSheet0.getPhysicalNumberOfRows(); i++) {
            Row BigRow = xssSheet0.getRow(i);
            if (BigRow == null) {
                break;
            }
            if (PoiExcelUtil.isMergedRegion(xssSheet0, i, 0)) {
                int lastRow = PoiExcelUtil.getRowNum(cras, xssSheet0.getRow(i).getCell(0), xssSheet0);
                for (; i <= lastRow; i++) {
                    row = xssSheet0.getRow(i);

                    // 判断该行第5列是合并单元格
                    if (PoiExcelUtil.isMergedRegion(xssSheet0, i, 4)) {
                        int lastRow2 = PoiExcelUtil.getRowNum(cras, xssSheet0.getRow(i).getCell(4), xssSheet0);
                        for (; i <= lastRow2; i++) {
                            Row nextRow = xssSheet0.getRow(i);

                            AdmissionPlanPojo admissionPlan = new AdmissionPlanPojo();
                            buildAdmission(row, BigRow, nextRow, admissionPlan, 2);
                            dao.save(admissionPlan);
                        }
                    } else {
                        AdmissionPlanPojo admissionPlan = new AdmissionPlanPojo();
                        buildAdmission(row, BigRow, row, admissionPlan,1);
                        dao.save(admissionPlan);
                    }
                }
                i--;
            } else {
                row = xssSheet0.getRow(i);

                AdmissionPlanPojo admissionPlan = new AdmissionPlanPojo();
                buildAdmission(row, BigRow, row, admissionPlan, 1);
                dao.save(admissionPlan);
            }

        }
        return "导入成功";
    }

    private void buildAdmission(Row row, Row BigRow, Row nextRow, AdmissionPlanPojo admissionPlan, Integer type) {
        final String yxsm = PoiExcelUtil.getCellValue(BigRow.getCell(0)); // 学院代码
        admissionPlan.setYxsm(yxsm);
        String yxsmc = PoiExcelUtil.getCellValue(BigRow.getCell(1)); // 学院名
        admissionPlan.setYxsmc(yxsmc);
        Integer yxSum = null;
        if (PoiExcelUtil.getCellValue(BigRow.getCell(2)) != null) {
            yxSum = Integer.valueOf(PoiExcelUtil.getCellValue(BigRow.getCell(2))); // 招生计划总规模(含推免生)
            admissionPlan.setYxSum(yxSum);
        }
        Integer yxFree = null;
        if (PoiExcelUtil.getCellValue(BigRow.getCell(3)) != null) {
            yxFree = Integer.valueOf(PoiExcelUtil.getCellValue(BigRow.getCell(3))); // 推免生
            admissionPlan.setYxFree(yxFree);
        }

        String zydm = PoiExcelUtil.getCellValue(row.getCell(4));
        admissionPlan.setZydm(zydm); // 专业代码
        admissionPlan.setZymc(PoiExcelUtil.getCellValue(row.getCell(5))); // 专业

        Integer zySum = Integer.valueOf(PoiExcelUtil.getCellValue(row.getCell(6))); // 招生计划总规模(含推免生)

        admissionPlan.setZySum(zySum);
        Integer zyFree = Integer.valueOf(PoiExcelUtil.getCellValue(row.getCell(7)));// 推免生
        admissionPlan.setZyFree(zyFree);
        String xxfs = PoiExcelUtil.getCellValue(nextRow.getCell(8)); // 学习方式
        admissionPlan.setXxfs(xxfs);
        Integer xxfsSum = Integer.valueOf(PoiExcelUtil.getCellValue(nextRow.getCell(9))); // 招生计划总规模(不含推免生)

        admissionPlan.setXxfsSum(xxfsSum);
        Integer xxfsFree = Integer.valueOf(PoiExcelUtil.getCellValue(nextRow.getCell(10))); // 推免生

        admissionPlan.setXxfsFree(xxfsFree);
        admissionPlan.setCreateTime(new Date());
        admissionPlan.setLastUpdateTime(new Date());
    }


}

三、工具类

package com.bronna.util;

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

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;

/**
 * 功能: poi导出excel工具类 date: 2018年9月5日 上午10:15:51 <br/>
 *
 * @author sxxu@wisdombudcom
 * @version
 * @since JDK 1.7
 */
public class PoiExcelUtil {

    /**
     * 获取单元格的值
     * 
     * @param fCell
     * @return
     */
    public static String getCellValue(Cell fCell) {
        if (fCell == null)
            return null;
        if (fCell.getCellType() == Cell.CELL_TYPE_STRING) {
            return fCell.getStringCellValue();
        } else if (fCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(fCell.getBooleanCellValue());
        } else if (fCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return fCell.getCellFormula();
        } else if (fCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return String.valueOf(fCell.getNumericCellValue());
        }
        return null;
    }

    /**
     * 合并单元格处理,获取合并行
     * 
     * @param sheet
     * @return List<CellRangeAddress>
     */
    public static List<CellRangeAddress> getCombineCell(Sheet sheet) {
        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
        // 获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        // 遍历所有的合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            // 获得合并单元格保存进list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }

    public static int getRowNum(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet) {
        int xr = 0;
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        for (CellRangeAddress ca : listCombineCell) {
            // 获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
                if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
                    xr = lastR;
                }
            }

        }
        return xr;

    }

    /**
     * 判断单元格是否为合并单元格，是的话则将单元格的值返回
     * 
     * @param listCombineCell
     *            存放合并单元格的list
     * @param cell
     *            需要判断的单元格
     * @param sheet
     *            sheet
     * @return
     */
    public static String isCombineCell(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet) throws Exception {
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        String cellValue = null;
        for (CellRangeAddress ca : listCombineCell) {
            // 获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
                if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
                    Row fRow = sheet.getRow(firstR);
                    Cell fCell = fRow.getCell(firstC);
                    cellValue = getCellValue(fCell);
                    break;
                }
            } else {
                cellValue = "";
            }
        }
        return cellValue;
    }

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


}