table导出为Excel jxl

平时用js导出table有些不足，导出的Excel内容也不够好看。

所以写了个通用的将Table导出为Excel的方法，可完成表格导出(不支持嵌套table)。

js计算cellStrArray部分参考http://fireinjava.iteye.com/admin/blogs/759190

 

代码如下：

   构造字符串：每个对象以row,col,rowSpan,colSpan,value形式组成串，以便Jxl来addCell、mergeCells。

   说明：jxl创建Cell用的是 WritableSheet.addCell(new Label(col, row,value,style))

                      跨行跨列用的是WritableSheet.mergeCells(col, row, col2,row2)

/**
 浏览器判断
 */
var Sys = {};
var ua = navigator.userAgent.toLowerCase();
if (window.ActiveXObject)
	Sys.ie = ua.match(/msie ([\d.]+)/)[1];
else if (document.getBoxObjectFor)
	Sys.firefox = ua.match(/firefox\/([\d.]+)/)[1];
	
function containsArray(array, obj) {
	for (var i = 0; i < array.length; i++) {
		if (array[i] == obj) {
			return i;
			break;
		}
	}
	return -1;
}

Array.prototype.contains = function(obj) {
	return containsArray(this, obj);
}

function exportTableToJxlExcel(sheetName,tableId) {
	
	var offsetLeftArray = new Array();
	var cell;// 单元格Dom
	var col;// 单元格实际所在列
	var cellStr;// 每个cell以row,col,rowSpan,colSpan,value形式
	var cellStrArray = [];
	var objTab = document.getElementById(tableId);

	// 遍历第一次取出offsetLeft集合
	for (var i = 0; i < objTab.rows.length; i++) {
		for (var j = 0; j < objTab.rows[i].cells.length; j++) {
			cell = objTab.rows[i].cells[j];
			if (offsetLeftArray.contains(cell.offsetLeft) == -1)
				offsetLeftArray.push(cell.offsetLeft);
		}
	}

	offsetLeftArray.sort(function(x, y) { return parseInt(x) - parseInt(y); });
	//alert("offsetLeft集合:" + offsetLeftArray.join(','));

	// 遍历第二次生成cellStrArray
	for (var i = 0; i < objTab.rows.length; i++) {
		for (var j = 0; j < objTab.rows[i].cells.length; j++) {
			cell = objTab.rows[i].cells[j];
			col = offsetLeftArray.contains(cell.offsetLeft);
			cellStr = i + ',' + col + ',' + cell.rowSpan + ',' + cell.colSpan + "," + (Sys.firefox?cell.textContent:cell.innerText);
			cellStrArray.push(cellStr);
		}
	}

	// 显示
	//var str = "行，列，rowSpan,colSpan,值\n";
	//str += cellStrArray.join('\n');
	//alert(str);
	
	//把cellStrArray传到后台	
	buffalo.remoteCall("ReportExcelExport.export", ['sheet名称',cellStrArray], function(reply){
		var result = reply.getResult();
		if(result!=null && result.length>0){
	    	document.location.href = "<%=request.getContextPath()%>/prj/common/download.jsp?fileName="+result;
		}else{
			alert("无相关记录");
		}
 	});
		
}

  

 

 java后台部分：

  Jxl生成EXCEL(其中ExcelUtil参考 http://fireinjava.iteye.com/blog/702905 )

 

	public String exportToJxlExcel(String sheetName, String[] cellStrArray) {
		try {
			String fileName = "E:/table.xls";// TODO 
			FileOutputStream os = new FileOutputStream(fileName);
			if (cellStrArray != null && cellStrArray.length > 0) {
				WritableWorkbook wwb = Workbook.createWorkbook(os);
				WritableCellFormat wcf_title = ExcelUtil.createWcfTitle();
				WritableCellFormat wcf_text = ExcelUtil.createWcfText();
				WritableSheet ws = wwb.createSheet(sheetName, 0);
				for (int i = 0; i < 20; i++)
					ws.setColumnView(i, 20);

				Label lbl = null;
				String[] objProps = null;
				int col;
				int row;
				int rowSpan;
				int colSpan;
				for (String objStr : cellStrArray) {// 每个对象以row,col,rowSpan,colSpan,value形式
					objProps = objStr.split(",");
					col = Integer.parseInt(objProps[1]);
					row = Integer.parseInt(objProps[0]);
					rowSpan = Integer.parseInt(objProps[2]);
					colSpan = Integer.parseInt(objProps[3]);
					lbl = new Label(col, row, objProps[4], row == 0 ? wcf_title : wcf_text);
					ws.addCell(lbl);
					if (rowSpan > 1 || colSpan > 1)
						ws.mergeCells(col, row, col + colSpan - 1, row + rowSpan - 1);
				}

				wwb.write();
				wwb.close();
				os.close();
				os.flush();
				return fileName;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

 

  前台提交到后台的值，如：

[0,0,1,1,品牌, 0,1,1,1,型号, 0,2,1,1,数量(单位:万), 0,3,1,1,百分比, 1,0,3,1,LG, 1,1,1,1,KF510, 1,2,1,1,0, 1,3,1,1,0.02%, 2,1,1,1,KG129, 2,2,1,1,0.001, 2,3,1,1,0.1%, 3,1,1,1,合计, 3,2,1,1,0.001, 3,3,1,1,0.12, 4,0,1,2,合计, 4,2,1,1,0.001, 4,3,1,1, ]

 

来个简单的效果图：

 

 

评论

1 楼 huzhiyong56 2012-04-27  

能给一个 完整的列子吗？