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;// 单元格Domvar 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(','));// 遍历第二次生成cellStrArrayfor (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, ]
?
来个简单的效果图：
?
?