jxl 导出固定格式的excel

格式的excel"/>

jxl 导出固定格式的excel

说明：该实例主要侧重与参数的设置

js：

/*** 导出*/
function btnExport_onClicked(){//拼导出的结果集gen_exp_result();//alert(gs_exportResult.length);alert(gs_exportResult);if(null == gs_exportResult || "" == gs_exportResult){alert("导出的结果为空！请重新查询！");return;}var exp = {};exp.fileName      = '原系统查询：机动车-200410后统计报表-报表一';//exp.caption       = '原系统查询：机动车-200410后统计报表-报表一';exp.subCaption =  getSearchConditionStr();//共28列exp.t_width         = [15,10,10,5,	12,		12,10,10,10,10,10,10,	10,		10,10,10,10,10,		10,		10,10,10,10,10,10,10,10,	10];exp.t_align         = ['center','center','center','center','center','center','center','center','center','center','center','center','center','center','center','center','center','center','center','center','center','center','center','center','center','center','center','center'];exp.t_header        = [	['核对方法：行:1=2+15+18+20+23+27 2=3+8+14 15=16+18 18=19 20=21+22 23=24+25+26 3=4+…+7 8=9+…+13 列:1=2+9+10+15  2=3+…+8 10=11+12+13+14 单位：辆','cspan','cspan','cspan','机 动 车 保 有 量','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan','cspan_23','报废'],['rspan','rspan','rspan','rspan','总计','营运','cspan','cspan','cspan','cspan','cspan','cspan_7','非营运','特种','cspan','cspan','cspan','cspan_5','其他','其中','cspan','cspan','cspan','cspan','cspan','cspan','cspan_8','rspan'],['rspan','rspan','rspan','rspan','rspan','合计','公路客运','公交客运','出租客运','旅游客运','货运','租赁','rspan','合计','警用','消防','救护','工程抢险','rspan','进口','个人','新注册','转入','转移','cspan_2','变更','审验','rspan'],['rspan','rspan','rspan','rspan','rspan_3','rspan_2','rspan_2','rspan_2','rspan_2','rspan_2','rspan_2','rspan_2','rspan_3','rspan_2','rspan_2','rspan_2','rspan_2','rspan_2','rspan_3','rspan_2','rspan_2','rspan_2','rspan_2','过户','转出','rspan_2','rspan_2','rspan_4'],['rspan','rspan','rspan','rspan_5_4','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24']];//注rspan_5_4表示跨5行4列 ， rspan_5表示跨5行exp.t_data	 = gs_exportResult;//alert(exp.t_data.length);alert(exp.t_data[0]);var action = getSystemWebURL() + "Export.YXTXLS";progressbar = new LoadingBar(document);progressbar.setMessage("正在导出...");progressbar.show();$("btnExport").disabled = true;openPostWindow(action, exp.toJSONString(), 'hidden_frame');
//	openPostWindow(action, jQuery.toJSON(exp), 'hidden_frame');}

function gen_exp_result(){result_data = gs_Result;//gs_Result是查询出来的结果集，var temp_exportResult = [	['总计','cspan','cspan_3','1'],['汽车','合计','cspan_2','2'] ,['rspan','载客','小计','3'] ,['rspan','rspan','大型','4'],['rspan','rspan','中型','5'],['rspan','rspan','小型','6'],['rspan','rspan_5','微型','7'],['rspan','载货','小计','8'],['rspan','rspan','重型','9'],['rspan','rspan','中型','10'],['rspan','rspan','轻型','11'],['rspan','rspan','微型','12'],['rspan','rspan_6','低速','13'],['rspan_13','其他汽车','cspan_2','14'],['摩托车','合计','cspan_2','15'],['rspan','普通','cspan_2','16'],['rspan_3','轻便','cspan_2','17'],['农用运输车','合计','cspan_2','18'],['rspan_2','三轮','cspan_2','19'],['拖拉机','合计','cspan_2','20'],['rspan','大型','cspan_2','21'],['rspan_3','小型','cspan_2','22'],['挂车','合计','cspan_2','23'],['rspan','重型','cspan_2','24'],['rspan','中型','cspan_2','25'],['rspan_4','轻型','cspan_2','26'],['其它类型(含手扶拖拉机)','cspan','cspan_3','27']];//存放导出结果//alert(temp_exportResult);for(var i=0;i<temp_exportResult.length;i++){var exp_row_data = temp_exportResult[i];for(var j=0;j<result_data.length;j++){var data = result_data[j];if(data.N_XH == exp_row_data[3]){exp_row_data.splice(4,0,(data.ITEM1 == 0 ? " ": data.ITEM1),(data.ITEM2 == 0 ? " ": data.ITEM2),(data.ITEM3 == 0 ? " ": data.ITEM3),(data.ITEM4 == 0 ? " ": data.ITEM4),(data.ITEM5 == 0 ? " ": data.ITEM5),(data.ITEM6 == 0 ? " ": data.ITEM6),(data.ITEM7 == 0 ? " ": data.ITEM7),(data.ITEM8 == 0 ? " ": data.ITEM8),(data.ITEM9 == 0 ? " ": data.ITEM9),(data.ITEM10 == 0 ? " ": data.ITEM10),(data.ITEM11 == 0 ? " ": data.ITEM11),(data.ITEM12 == 0 ? " ": data.ITEM12),(data.ITEM13 == 0 ? " ": data.ITEM13),(data.ITEM14 == 0 ? " ": data.ITEM14),(data.ITEM15 == 0 ? " ": data.ITEM15),(data.ITEM16 == 0 ? " ": data.ITEM16),(data.ITEM17 == 0 ? " ": data.ITEM17),(data.ITEM18 == 0 ? " ": data.ITEM18),(data.ITEM19 == 0 ? " ": data.ITEM19),(data.ITEM20 == 0 ? " ": data.ITEM20),(data.ITEM21 == 0 ? " ": data.ITEM21),(data.ITEM22 == 0 ? " ": data.ITEM22),(data.ITEM23 == 0 ? " ": data.ITEM23),(data.ITEM24 == 0 ? " ": data.ITEM24));gs_exportResult.push(exp_row_data);}}}}

/*** 设置复杂表头* @param sheet* @param tHeader* 规则：1、只跨行：被跨的单元格都用"rspan"填充（程序中会调过不处理，只是为了使用者设置时方便看懂），跨行的最后一个单元格写法如 rspan_5 表示跨5行* 2、只跨列：被跨的单元格都用"cspan"填充（程序中会调过不处理，只是为了使用者设置时方便看懂），跨行的最后一个单元格写法如 cspan_5 表示跨5列* 2、既跨行又跨列：被跨的单元格都用"rspan"或者"cspan"填充（程序中会调过不处理，只是为了使用者设置时方便看懂），跨行的最后一个单元格写法如 rspan_5_4表示跨5行4列*/public void setDataComplexColumnHeader(WritableSheet sheet, JSONArray tHeader){int row = currentRow;try{for(int i=0; i<tHeader.length(); i++){row = currentRow++;JSONArray header = (JSONArray)tHeader.get(i);for(int j=0; j<header.length(); j++){String h = header.getString(j);if(!h.equals("")){if(h.toLowerCase().startsWith("cspan")){if(h.toLowerCase().equals("cspan")){//不处理
//								//此处不处理
//								sheet.mergeCells(j-1, row, j, row);
//								h=sheet.getCell(j-1, row).getContents();
//								ExcelUtils.insertOneCellData(sheet, j-1, row, h, ExcelUtils.getHeaderCellFormat());}else{String cspan = h.toLowerCase().replace("cspan_", "");//eg：cspan_5表示跨5列int cspan_cnt = Integer.parseInt(cspan);sheet.mergeCells(j - cspan_cnt + 1, row, j, row);h=sheet.getCell(j- cspan_cnt + 1, row).getContents();ExcelUtils.insertOneCellData(sheet, j- cspan_cnt + 1, row, h, ExcelUtils.getHeaderCellFormat());}}else if(h.toLowerCase().startsWith("rspan")){if(h.toLowerCase().equals("rspan")){//不处理
//								sheet.mergeCells(j, row-1, j, row);
//								h=sheet.getCell(j, row-1).getContents();
//								ExcelUtils.insertOneCellData(sheet, j, row-1, h, ExcelUtils.getHeaderCellFormat());}else{//固定写法传入，不考虑是否有空指针String rspan = h.toLowerCase().replace("rspan_", "");//eg：rspan_5表示跨5行，rspan_5_4表示跨5行4列String[] rspan_arr = rspan.split("_");int rspan_cnt = Integer.parseInt(rspan_arr[0]);int cspan_cnt = 1;if(rspan_arr.length > 1){cspan_cnt = Integer.parseInt(rspan_arr[1]);}sheet.mergeCells(j - cspan_cnt + 1, row - rspan_cnt + 1, j, row);h=sheet.getCell(j - cspan_cnt + 1, row - rspan_cnt + 1).getContents();ExcelUtils.insertOneCellData(sheet, j - cspan_cnt + 1, row - rspan_cnt + 1, h, ExcelUtils.getHeaderCellFormat());}}else{ExcelUtils.insertOneCellData(sheet, j, row, h, ExcelUtils.getHeaderCellFormat());}}}}catch(Exception e){e.printStackTrace();}}