java读取Excel(包含合并单元格)

1、导入依赖

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

2、ServiceImpl

@Override
public int ExcelBunk(MultipartFile file) throws Exception {
    try {
        /**
         *
         * 判断文件版本
         */
        String fileName = file.getOriginalFilename();
        String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);

        InputStream ins = file.getInputStream();

        Workbook wb = null;

        if (suffix.equals("xlsx")) {

            wb = new XSSFWorkbook(ins);

        } else {
            wb = new HSSFWorkbook(ins);
        }
        /**
         * 获取excel表单
         */
        Sheet sheet = wb.getSheetAt(0);
        List<CellRangeAddress> combineCell = getCombineCell(sheet);

        /**
         * line = 6 :从表的第七行开始获取记录
         */
        String bunkLibraryId = null;
        if (null != sheet) {
            for (int line = 6; line <= sheet.getLastRowNum(); line++) {

                Row row = sheet.getRow(line);
				//如果表格是空的就直接跳出这次循环
                if (null == row) {
                    continue;
                }
                /**
                 * 获取第一个单元格的内容
                 */
                 
                 //接收值取到的值
                String stg;  //获取到的值
                String stg2; //获取到的值
                String stg3; //获取到的值

                String cellValue = getCellValue(row.getCell(0));

                if (cellValue != null) {
                    //获取列
                    Cell cell1 = row.getCell(0);
                    stg = getCellValue(cell1);
                    Cell cell2 = row.getCell(2);
                    stg2 = getCellValue(cell2); 
                    //判断是不是合并的单元格
                    //第一个参数是表单，第二个参数的行下标，第三个参数是列下标
                    if (isMergedRegion(sheet, line, 3) == true) {
                        //是就取合并的单元格里的值
                        stg3 = isCombineCell(combineCell, row.getCell(3), sheet);
                    } else {
                        //不是就直接取值
                        Cell cell3 = row.getCell(3);
                        stg3 = getCellValue(cell3);
                    }
                    //把取到的数据拼接成自己想要的格式（可以根据自己的需求自己拼接或者存储在实体类中）
                    if (stg3 != null && !stg3.equals("") && stg != null && !stg.equals("")) {
                      if (bunkLibraryId == null) {
                             bunkLibraryId = stg3 + "," + stg + "," + stg2;
                         } else {
                             bunkLibraryId += "、" + stg3 + "," + stg + "," + stg2;
                         }
                     }
                }
            }
            //            System.out.println("-----------" + bunkLibraryId);
        }
        //这里把 Excel表中取到的数据存到数据库中
        String s = RoomArrangement(bunkLibraryId);
        if (s == "ok") {
            return 1;
        }
    } catch (Exception e) {
        logger.error("service方法：ExcelBunk()", e);
        e.printStackTrace();
    }

    return 0;
}
/**
 * 执行SQL
 * @param bunkLibraryId 
 * @return
 */
public String RoomArrangement(String bunkLibraryId) {
    try {
       // 这里就根据自己的需求写DAO存储数据
    } catch (Exception e) {
        logger.error("service方法：RoomArrangement", e);
        e.printStackTrace();
    }
    return "ok";
}
  
/**
 * 判断指定的单元格是否是合并单元格
 *
 * @param sheet
 * @param row    行下标
 * @param column 列下标
 * @return
 */
private 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 listCombineCell 存放合并单元格的list
 * @param cell            需要判断的单元格
 * @param sheet           sheet
 * @return
 */
public 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
 * @return List<CellRangeAddress>
 */
public List<CellRangeAddress> getCombineCell(Sheet sheet) {
    List<CellRangeAddress> list = new ArrayList<>();
    //获得一个 sheet 中合并单元格的数量
    int sheetmergerCount = sheet.getNumMergedRegions();
    //遍历所有的合并单元格
    for (int i = 0; i < sheetmergerCount; i++) {
        //获得合并单元格保存进list中
        CellRangeAddress ca = sheet.getMergedRegion(i);
        list.add(ca);
    }
    return list;
}

/**
 * 获取单元格的值（获取不同类型，都返回String）
 *
 * @param cell
 * @return
 */
private String getCellValue(Cell cell) {
    String st = null;
    if (cell == null || cell.equals("")) {
        st = null;
    } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        //公式
        st = cell.getStringCellValue();
    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        //数值型
        st = cell.getNumericCellValue() + "";
        st = st.substring(0, st.lastIndexOf("."));
    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        //字符串型
        st = cell.getStringCellValue();
    } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        //空值
        st = cell.getStringCellValue();
    }
    return st;
}

3、Controller

@RequestMapping(value = "/import")
	//这个返回值是自己定义JSON（自己写个实体类去定义一个JSON，返回给前端）
    public CommonResponse excelImport(MultipartFile file) {

        CommonResponse responseResult = new CommonResponse();

        try {
            int aa = excelService.ExcelBunk(file);
            if (aa == 1) {
                responseResult.setCode(200);
                responseResult.setMsg("提交成功！");
            } else {
                responseResult.setCode(400);
                responseResult.setMsg("Excel导入出错！请联系管理员。");
            }
        } catch (Exception e) {
            logger.error("controller方法：excelImport()",e);
            e.printStackTrace();
        }

        return responseResult;
    }

4、前端（用的是Layui框架）

<button type="button" class="layui-btn" id="bedExcel"><i class="layui-icon"></i>导入Excel</button>
	<script>
		$(function () {
		    layui.use(['table', "form", "layer", 'upload'], function () {
			var upload = layui.upload;
			upload.render({
		            elem: '#bedExcel'
		            , url: '自己的URL路径'
		            , method: 'get' //默认：get请求
		            , accept: 'file' //普通文件
		            ,before: function () {
		                //加载图层（程序未执行完成之前会有一个转圈圈的图层）
		                upload_coverage = layer.load(2);
		            }
		            , done: function (res) {
		                if (res.code == 200) {
		                    //关闭加载图层
		                    layer.close(upload_coverage);
		                    //回调弹框
		                    layer.alert("绑定成功！", {closeBtn: 0}, function () {
		                        layer.closeAll();
		                        window.location.href = "/roomnumber/pagebunklibrary";
		                    });
		                } else {
		                    //关闭加载图层
		                    layer.close(upload_coverage);
		                    //回调弹框
		                    layer.alert("Excel导入出错，请联系管理人员!", {closeBtn: 0}, function () {
		                        layer.closeAll();
		                        window.location.href = "/roomnumber/pagebunklibrary";
		                    });
		                }
		            }
		            ,error: function(){
		                //关闭加载图层
		                layer.close(upload_coverage);
		                layer.alert("未找到服务器！！", {closeBtn: 0}, function () {
		                    layer.closeAll();
		                });
		            }
		        });
	        }
        <script/>