POI操作Excel实现点击预览

前端代码

table.on('tool(mbdy)', function(obj){
	$.ajax({
		type : "post", //请求方式
		async : true, //是否异步
		url:"ajax.do?ctrl=Mbdy_readRecordsInputPath",
		data : {
			path : obj.data.PATH,
		},		
		dataType : "json",
		success : function(ttt) {
			var aa = "";
			for (i=0;i<ttt.length;i++ ){ 
				if(i==0){
					var strs = ttt[i];
					aa += '<tr>'
					for (j=0;j<strs.length;j++ ){ 
						aa += '<th>'+strs[j]+'</th>';
				    } 
					aa += '</tr>'
					continue; 
				} else{
					var strs = ttt[i];
					aa += '<tr>'
					for (j=0;j<strs.length;j++ ){ 
						aa += '<td>'+strs[j]+'</td>';
				    } 
					aa += '</tr>'
					continue; 
				}
		    } 
			layer.open({
	            shadeClose: true,
	            offset: '20%',
	            area: ['70%', '50%'],
	            content: '<table cellspacing="0" cellpadding="0" border="0" class="layui-table">'+aa+'</table>'
	        });
		}
	});
  });

后端代码

 /** 
 * 通过文件路径获取Excel读取 
 * @param path 文件路径，只接受xls或xlsx结尾 
 * @param isHeader 是否表头 
 * @param headerCount 表头行数 
 * @return count 如果文件路径为空，返回0； 
 */  
public String readRecordsInputPath(Map<String, Object> rmap) { 
	init(rmap);
	String path = getValue(this.getForm().get("path"));
	boolean isHeader = false;
	int headerCount = 0;
    List<String[]> poiList = new ArrayList<String[]>();  
    if(path == null){  
        return null;  
    }else if(!path.endsWith("xls") && !path.endsWith("xlsx")  
          && !path.endsWith("XLS") && !path.endsWith("XLSX")){  
        return null;  
    }  
    File file = new File(path);  
    try {  
        InputStream inputStream = new FileInputStream(file);       
        if(path.endsWith("xls") || path.endsWith("XLS")){  
            poiList = readXLSRecords(inputStream, isHeader, headerCount);   
        }else if(path.endsWith("xlsx") || path.endsWith("XLSX")){  
            poiList = readXLSXRecords(inputStream, isHeader, headerCount);   
        }  
    } catch (Exception e) {  
    	System.err.println(e);
        return null;  
    }  
    return JSON.toJSONString(poiList);  
}  

/** 
 * 解析EXCEL2003文件流 
 * 如果一行记录的行中或行尾出现空格，POI工具类可能会跳过空格不做处理，所以默认第一行是表头，所有待解析的记录都以表头为准 
 * @param inputStream  输入流 
 * @param isHeader  是否要跳过表头 
 * @param headerCount  表头占用行数 
 * @return 返回一个字符串数组List 
 */  
public static List<String[]> readXLSRecords(InputStream inputStream, boolean isHeader, int headerCount) {
	List<String[]> poiList = new ArrayList<String[]>();
	try {
		HSSFWorkbook wbs = new HSSFWorkbook(inputStream);
		HSSFSheet childSheet = wbs.getSheetAt(0);
		// 获取表头
		int begin = childSheet.getFirstRowNum();
		HSSFRow firstRow = childSheet.getRow(begin);
		int cellTotal = firstRow.getPhysicalNumberOfCells();
		// 是否跳过表头解析数据
		if (isHeader) {
			begin += headerCount;
		}
		// 逐行获取单元格数据
		for (int i = begin; i <= childSheet.getLastRowNum(); i++) {
			HSSFRow row = childSheet.getRow(i); // 一行的所有单元格格式都是常规的情况下，返回的row为null
			if (null != row) {
				String[] cells = new String[cellTotal];
				for (int k = 0; k < cellTotal; k++) {
					HSSFCell cell = row.getCell(k);
					cells[k] = getStringXLSCellValue(cell);
				}
				poiList.add(cells);
			}
		}
	} catch (Exception e) {
		return null;
	}
	return poiList;
}

/**
 * 解析EXCEL2007文件流 如果一行记录的行中或行尾出现空格，POI工具类可能会跳过空格不做处理，所以默认第一行是表头，所有待解析的记录都以表头为准
 * 该处理方法中，表头对应都占用一行
 * @param inputStream 输入流
 * @param isHeader    是否要跳过表头
 * @param headerCount 表头占用行数
 * @return 返回一个字符串数组List
 */
public static List<String[]> readXLSXRecords(InputStream inputStream, boolean isHeader, int headerCount) {
	List<String[]> poiList = new ArrayList<String[]>();
	try {
		XSSFWorkbook wbs = new XSSFWorkbook(inputStream);
		XSSFSheet childSheet = wbs.getSheetAt(0);
		// 获取表头
		int begin = childSheet.getFirstRowNum();
		XSSFRow firstRow = childSheet.getRow(begin);
		int cellTotal = firstRow.getPhysicalNumberOfCells();
		// 是否跳过表头解析数据
		if (isHeader) {
			begin += headerCount;
		}
		for (int i = begin; i <= childSheet.getLastRowNum(); i++) {
			XSSFRow row = childSheet.getRow(i); // 一行的所有单元格格式都是常规的情况下，返回的row为null
			if (null != row) {
				String[] cells = new String[cellTotal];
				for (int k = 0; k < cellTotal; k++) {
					XSSFCell cell = row.getCell(k);
					cells[k] = getStringXLSXCellValue(cell);
				}
				poiList.add(cells);
			}
		}
	} catch (Exception e) {
		return null;
	}
	return poiList;
}
 
 /** 
  * 获取单元格数据内容为字符串类型的数据 
  *  
  * @param cell Excel单元格 
  * @return String 单元格数据内容 
  */  
private static String getStringXLSCellValue(HSSFCell cell) {
	String strCell = "";
	if (cell == null) {
		return "";
	}
	// 将数值型参数转成文本格式，该算法不能保证1.00这种类型数值的精确度
	DecimalFormat df = (DecimalFormat) NumberFormat.getPercentInstance();
	StringBuffer sb = new StringBuffer();
	sb.append("0");
	df.applyPattern(sb.toString());
	switch (cell.getCellType()) {
	case HSSFCell.CELL_TYPE_STRING:
		strCell = cell.getStringCellValue();
		break;
	case HSSFCell.CELL_TYPE_NUMERIC:
		double value = cell.getNumericCellValue();
		while (Double.parseDouble(df.format(value)) != value) {
			if ("0".equals(sb.toString())) {
				sb.append(".0");
			} else {
				sb.append("0");
			}
			df.applyPattern(sb.toString());
		}
		strCell = df.format(value);
		break;
	case HSSFCell.CELL_TYPE_BOOLEAN:
		strCell = String.valueOf(cell.getBooleanCellValue());
		break;
	case HSSFCell.CELL_TYPE_BLANK:
		strCell = "";
		break;
	default:
		strCell = "";
		break;
	}
	if (strCell == null || "".equals(strCell)) {
		return "";
	}
	return strCell;
}

/**
 * 获取单元格数据内容为字符串类型的数据
 * 
 * @param cell Excel单元格
 * @return String 单元格数据内容
 */
private static String getStringXLSXCellValue(XSSFCell cell) {
	String strCell = "";
	if (cell == null) {
		return "";
	}
	// 将数值型参数转成文本格式，该算法不能保证1.00这种类型数值的精确度
	DecimalFormat df = (DecimalFormat) NumberFormat.getPercentInstance();
	StringBuffer sb = new StringBuffer();
	sb.append("0");
	df.applyPattern(sb.toString());

	switch (cell.getCellType()) {
	case XSSFCell.CELL_TYPE_STRING:
		strCell = cell.getStringCellValue();
		break;
	case XSSFCell.CELL_TYPE_NUMERIC:
		double value = cell.getNumericCellValue();
		while (Double.parseDouble(df.format(value)) != value) {
			if ("0".equals(sb.toString())) {
				sb.append(".0");
			} else {
				sb.append("0");
			}
			df.applyPattern(sb.toString());
		}
		strCell = df.format(value);
		break;
	case XSSFCell.CELL_TYPE_BOOLEAN:
		strCell = String.valueOf(cell.getBooleanCellValue());
		break;
	case XSSFCell.CELL_TYPE_BLANK:
		strCell = "";
		break;
	default:
		strCell = "";
		break;
	}
	if (strCell == null || "".equals(strCell)) {
		return "";
	}
	return strCell;
}