Java Controller下兼容xls和xlsx且可识别合并单元格的excel导入功能

1.工具类，读取单元格数据的时候，如果当前单元格是合并单元格，会自动读取合并单元格的值

package com.shjh.core.util;
import java.io.IOException;
import java.io.InputStream;

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;

public class ExcelReaderUtil {
        private static final String EXTENSION_XLS = "xls";
        private static final String EXTENSION_XLSX = "xlsx";
        private Workbook workbook = null;
        private Sheet sheet = null;//表
        private int sheetNum = 0; // 第sheetnum个工作表
        
        public void setWorkbook(Workbook workbook) {
            this.workbook = workbook;
        }

        public void setSheet(Sheet sheet) {
            this.sheet = sheet;
        }

        public void setSheetNum(int sheetNum) {
            this.sheetNum = sheetNum;
        }

        public ExcelReaderUtil() {
        }

        public ExcelReaderUtil(InputStream inputStream,String fileName) throws IOException {
            if (fileName.endsWith(EXTENSION_XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(EXTENSION_XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            }
        }

        /**
         * 指定工作表、行、列下的内容
         * 
         * @param sheetNum
         * @param rowNum
         * @param cellNum
         * @return String
         */
        public String getCellValue(int rowNum, int colNum) {
            if (sheetNum < 0 || rowNum < 0){
                return "";
            }
            String strExcelCell = "";
            try {
                sheet = this.workbook.getSheetAt(sheetNum);
                //判断是否是合并单元格，如果是，就将行、列索引改为合并单元格的索引
                for(int numMR = 0; numMR < this.sheet.getNumMergedRegions(); numMR++){
                      //获取合并单元格
                      CellRangeAddress cellRangeAddress = this.sheet.getMergedRegion(numMR);
                      int firstColumnInd = cellRangeAddress.getFirstColumn();    
                      int lastColumnInd = cellRangeAddress.getLastColumn();    
                      int firstRowInd = cellRangeAddress.getFirstRow();    
                      int lastRowInd = cellRangeAddress.getLastRow();    
                      //如果当前单元格在这个合并单元格里
                      if(rowNum >= firstRowInd && rowNum <= lastRowInd && colNum >= firstColumnInd && colNum <= lastColumnInd){    
                          rowNum = firstRowInd;
                          colNum = firstColumnInd;
                          break;
                      }  
                }
                Row row = sheet.getRow(rowNum);
                if (row.getCell((short) colNum) != null) { 
                    switch (row.getCell((short) colNum).getCellType()) {
                    case Cell.CELL_TYPE_FORMULA:
                        strExcelCell = "FORMULA ";
                        break;
                    case Cell.CELL_TYPE_NUMERIC: {
                        strExcelCell = String.valueOf(row.getCell((short) colNum).getNumericCellValue());
                    }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        strExcelCell = row.getCell((short) colNum).getStringCellValue();
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        strExcelCell = "";
                        break;
                    default:
                        strExcelCell = "";
                        break;
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return strExcelCell;
        }
        
        /**
         * sheetNum下的记录行数
         * 
         * @return int
         */
        public int getRowCount() {
            Sheet sheet = workbook.getSheetAt(this.sheetNum);
            int rowCount = -1;
            rowCount = sheet.getLastRowNum();
            return rowCount;
        }
}

2.Controller内调用，尤其要注意传参部分的写法，需要加上注解，且变量名必须和jsp页面里对应的参数名要完全一致。

    @RequestMapping(value="/excelUpload")
    public void excelUpload(@RequestParam MultipartFile fileToUpload,@RequestParam String tableGroupId, HttpServletRequest request, HttpServletResponse response){
        int nullLine=0;
        String errorStr = "";
        try {
            ExcelReaderUtil readExcel = new ExcelReaderUtil(fileToUpload.getInputStream(),fileToUpload.getOriginalFilename());
            readExcel.setSheetNum(0); // 设置读取索引为0的工作表
            Map<String,String> checkRepeatMap=new HashMap<String,String>();//检验数据是否正常的集合
            List<String> tabIdList=new ArrayList<String>();//最终得到的tableid集合
            /*这里为循环校验数据*/
            for (int i = 2; i < readExcel.getRowCount()+1; i++) {
                String error="";
                //库名,先判断是否在合并单元格内，若有则取合并单元格的值
                String dataName=readExcel.getCellValue(i, 0).trim();//库
                String moduleName1=readExcel.getCellValue(i, 1).trim();//一级模块
                String moduleName2=readExcel.getCellValue(i, 2).trim();//二级模块
                String moduleName3=readExcel.getCellValue(i, 3).trim();//三级模块
                //String tableChiName=rows[4]==null ? "" :rows[4].trim();//表中文名  *暂时用不上
                String tableEnName=readExcel.getCellValue(i, 5).trim().toUpperCase();//表物理名
                //空行校验
                if(StringUtils.isBlank(dataName) && StringUtils.isBlank(moduleName1) && StringUtils.isBlank(moduleName2) && StringUtils.isBlank(moduleName3) && StringUtils.isBlank(tableEnName))
                {
                    nullLine++;
                    continue;
                }
                
                //空校验
                if(StringUtils.isBlank(tableEnName) )
                {
                    error ="
第"+(i+1)+"行：表物理名不能为空。
";
                    errorStr+=error;
                    continue;
                }
                String mapValue=dataName+moduleName1+moduleName2+moduleName3+tableEnName;
                if(checkRepeatMap.containsValue(mapValue)){
                    error ="
第"+(i+1)+"行：该行数据为重复数据，请删除该行后重新提交。
";
                    errorStr+=error;
                    continue;
                }else{
                    checkRepeatMap.put((i+1)+"",mapValue);
                }
                /*各种校验*/
            }
            
            if (StringUtils.isNotBlank(errorStr)) {
                int errorCount=readExcel.getRowCount()-1-tabIdList.size()-nullLine;
                OutPutUtil.ajaxOut(response, "导入失败！有"+errorCount+"条数据存在问题，请修正后再次导入：
"+errorStr);
            } else {
                /*保存操作*/
            }
        } catch (Exception e) {
            //logger.error("批量导入出错："+e.getMessage().toString());
            OutPutUtil.ajaxOut(response, "批量导入出错："+e.getMessage().toString());
            e.printStackTrace();
        }
    }

3.js代码，这里需要引用  ajaxfileupload.js 插件。参数名与controller里传入的参数名要一致。

        function ajaxFileUpload(){
                     if (checkFile()) { //此函数就不贴了，就是判断一下文件后缀名是否正确
                        //弹出遮盖层
                        $("#loadingDiv").show();
                        var tableGroupId = $("#tableGroupParam").val();
                        $.ajaxFileUpload({
                             url:'<%=basePath %>userController/excelUpload.do?tableGroupId='+tableGroupId,
                             secureuri:false,
                             fileElementId:'fileToUpload', //文件选择框的id属性
                             dataType: 'text',//服务器返回的格式，可以是json
                             //相当于java中try语句块的用法
                             success:function (data, status) {
                                 //关闭遮盖层
                                 $("#loadingDiv").hide();
                                  alert(data);
                             },
                             error:function (data, status, e) {
                                 alert("导入出错："+data);
                                }
                            
                            });
                        
                     } 
         }

4.html代码，就是用到了file控件，没什么特别的

        <div class="easyui-dialog" id="role-jurisdiction-dlg2" data-options="title:'批量导入',closed:true" style="400px;height:100px;">
            <input type="hidden" id="tableGroupParam" value="0" class="input-xs" disabled="disabled">
            <div id="loadingDiv">
                <img src="<%=basePath %>/img/loading.gif" style="margin-top:5px;margin-left:120px;" /> 
            </div>
            <input type="file" name="fileToUpload" id="fileToUpload" size="300" style="300px" onchange="checkFile();">
            <input type="button" name="btnUpload" value="上 传" id="btnUpload" style="60px" onclick="ajaxFileUpload();" />
        </div>