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 ="\n第"+(i+1)+"行：表物理名不能为空。\n";
errorStr+=error;
continue;
}
String mapValue=dataName+moduleName1+moduleName2+moduleName3+tableEnName;
if(checkRepeatMap.containsValue(mapValue)){
error ="\n第"+(i+1)+"行：该行数据为重复数据，请删除该行后重新提交。\n";
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+"条数据存在问题，请修正后再次导入：\n"+errorStr);
} else {
/*保存操作*/
}
} catch (Exception e) {
//logger.error("批量导入出错："+e.getMessage().toString());
OutPutUtil.ajaxOut(response, "批量导入出错："+e.getMessage().toString());
e.printStackTrace();
}
}

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

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

<div class="easyui-dialog" id="role-jurisdiction-dlg2" data-options="title:'批量导入',closed:true" style="width: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="width:300px" onchange="checkFile();">
<input type="button" name="btnUpload" value="上传" id="btnUpload" style="width:60px" onclick="ajaxFileUpload();" />
</div>