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

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

  1 package com.shjh.core.util;
  2 import java.io.IOException;
  3 import java.io.InputStream;
  4 
  5 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  6 import org.apache.poi.ss.usermodel.Cell;
  7 import org.apache.poi.ss.usermodel.Row;
  8 import org.apache.poi.ss.usermodel.Sheet;
  9 import org.apache.poi.ss.usermodel.Workbook;
 10 import org.apache.poi.ss.util.CellRangeAddress;
 11 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 12 
 13 public class ExcelReaderUtil {
 14         private static final String EXTENSION_XLS = "xls";
 15         private static final String EXTENSION_XLSX = "xlsx";
 16         private Workbook workbook = null;
 17         private Sheet sheet = null;//表
 18         private int sheetNum = 0; // 第sheetnum个工作表
 19         
 20         public void setWorkbook(Workbook workbook) {
 21             this.workbook = workbook;
 22         }
 23 
 24         public void setSheet(Sheet sheet) {
 25             this.sheet = sheet;
 26         }
 27 
 28         public void setSheetNum(int sheetNum) {
 29             this.sheetNum = sheetNum;
 30         }
 31 
 32         public ExcelReaderUtil() {
 33         }
 34 
 35         public ExcelReaderUtil(InputStream inputStream,String fileName) throws IOException {
 36             if (fileName.endsWith(EXTENSION_XLS)) {
 37                 workbook = new HSSFWorkbook(inputStream);
 38             } else if (fileName.endsWith(EXTENSION_XLSX)) {
 39                 workbook = new XSSFWorkbook(inputStream);
 40             }
 41         }
 42 
 43         /**
 44          * 指定工作表、行、列下的内容
 45          * 
 46          * @param sheetNum
 47          * @param rowNum
 48          * @param cellNum
 49          * @return String
 50          */
 51         public String getCellValue(int rowNum, int colNum) {
 52             if (sheetNum < 0 || rowNum < 0){
 53                 return "";
 54             }
 55             String strExcelCell = "";
 56             try {
 57                 sheet = this.workbook.getSheetAt(sheetNum);
 58                 //判断是否是合并单元格，如果是，就将行、列索引改为合并单元格的索引
 59                 for(int numMR = 0; numMR < this.sheet.getNumMergedRegions(); numMR++){
 60                       //获取合并单元格
 61                       CellRangeAddress cellRangeAddress = this.sheet.getMergedRegion(numMR);
 62                       int firstColumnInd = cellRangeAddress.getFirstColumn();    
 63                       int lastColumnInd = cellRangeAddress.getLastColumn();    
 64                       int firstRowInd = cellRangeAddress.getFirstRow();    
 65                       int lastRowInd = cellRangeAddress.getLastRow();    
 66                       //如果当前单元格在这个合并单元格里
 67                       if(rowNum >= firstRowInd && rowNum <= lastRowInd && colNum >= firstColumnInd && colNum <= lastColumnInd){    
 68                           rowNum = firstRowInd;
 69                           colNum = firstColumnInd;
 70                           break;
 71                       }  
 72                 }
 73                 Row row = sheet.getRow(rowNum);
 74                 if (row.getCell((short) colNum) != null) { 
 75                     switch (row.getCell((short) colNum).getCellType()) {
 76                     case Cell.CELL_TYPE_FORMULA:
 77                         strExcelCell = "FORMULA ";
 78                         break;
 79                     case Cell.CELL_TYPE_NUMERIC: {
 80                         strExcelCell = String.valueOf(row.getCell((short) colNum).getNumericCellValue());
 81                     }
 82                         break;
 83                     case Cell.CELL_TYPE_STRING:
 84                         strExcelCell = row.getCell((short) colNum).getStringCellValue();
 85                         break;
 86                     case Cell.CELL_TYPE_BLANK:
 87                         strExcelCell = "";
 88                         break;
 89                     default:
 90                         strExcelCell = "";
 91                         break;
 92                     }
 93                 }
 94             } catch (Exception e) {
 95                 e.printStackTrace();
 96             }
 97             return strExcelCell;
 98         }
 99         
100         /**
101          * sheetNum下的记录行数
102          * 
103          * @return int
104          */
105         public int getRowCount() {
106             Sheet sheet = workbook.getSheetAt(this.sheetNum);
107             int rowCount = -1;
108             rowCount = sheet.getLastRowNum();
109             return rowCount;
110         }
111 }

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

 1     @RequestMapping(value="/excelUpload")
 2     public void excelUpload(@RequestParam MultipartFile fileToUpload,@RequestParam String tableGroupId, HttpServletRequest request, HttpServletResponse response){
 3         int nullLine=0;
 4         String errorStr = "";
 5         try {
 6             ExcelReaderUtil readExcel = new ExcelReaderUtil(fileToUpload.getInputStream(),fileToUpload.getOriginalFilename());
 7             readExcel.setSheetNum(0); // 设置读取索引为0的工作表
 8             Map<String,String> checkRepeatMap=new HashMap<String,String>();//检验数据是否正常的集合
 9             List<String> tabIdList=new ArrayList<String>();//最终得到的tableid集合
10             /*这里为循环校验数据*/
11             for (int i = 2; i < readExcel.getRowCount()+1; i++) {
12                 String error="";
13                 //库名,先判断是否在合并单元格内，若有则取合并单元格的值
14                 String dataName=readExcel.getCellValue(i, 0).trim();//库
15                 String moduleName1=readExcel.getCellValue(i, 1).trim();//一级模块
16                 String moduleName2=readExcel.getCellValue(i, 2).trim();//二级模块
17                 String moduleName3=readExcel.getCellValue(i, 3).trim();//三级模块
18                 //String tableChiName=rows[4]==null ? "" :rows[4].trim();//表中文名  *暂时用不上
19                 String tableEnName=readExcel.getCellValue(i, 5).trim().toUpperCase();//表物理名
20                 //空行校验
21                 if(StringUtils.isBlank(dataName) && StringUtils.isBlank(moduleName1) && StringUtils.isBlank(moduleName2) && StringUtils.isBlank(moduleName3) && StringUtils.isBlank(tableEnName))
22                 {
23                     nullLine++;
24                     continue;
25                 }
26                 
27                 //空校验
28                 if(StringUtils.isBlank(tableEnName) )
29                 {
30                     error ="
第"+(i+1)+"行：表物理名不能为空。
";
31                     errorStr+=error;
32                     continue;
33                 }
34                 String mapValue=dataName+moduleName1+moduleName2+moduleName3+tableEnName;
35                 if(checkRepeatMap.containsValue(mapValue)){
36                     error ="
第"+(i+1)+"行：该行数据为重复数据，请删除该行后重新提交。
";
37                     errorStr+=error;
38                     continue;
39                 }else{
40                     checkRepeatMap.put((i+1)+"",mapValue);
41                 }
42                 /*各种校验*/
43             }
44             
45             if (StringUtils.isNotBlank(errorStr)) {
46                 int errorCount=readExcel.getRowCount()-1-tabIdList.size()-nullLine;
47                 OutPutUtil.ajaxOut(response, "导入失败！有"+errorCount+"条数据存在问题，请修正后再次导入：
"+errorStr);
48             } else {
49                 /*保存操作*/
50             }
51         } catch (Exception e) {
52             //logger.error("批量导入出错："+e.getMessage().toString());
53             OutPutUtil.ajaxOut(response, "批量导入出错："+e.getMessage().toString());
54             e.printStackTrace();
55         }
56     }

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

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

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

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