jxl合并单元格导出excel

来源：互联网发布：卸载阿里云监控编辑：程序博客网时间：2024/04/29 03:42

//后台方法

public ActionForward exportOrder(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {

response.setCharacterEncoding("UTF-8");
OutputStream os = response.getOutputStream();// 取得输出流
String fileName = "工单信息.xls";
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=\"" + new String(fileName.getBytes("GBK"),"ISO-8859-1")+ "\";");
WritableWorkbook wwb = Workbook.createWorkbook(os); // 建立excel文件

Map<String,String> map = toMap(request); //获得前台传递的参数转化成map。也可以用request.getParameter("")
List<Map<String, String>> list = workOrderService.getOrderValueByKey(map); //获得excel的数据
try{
WritableSheet ws = wwb.createSheet("工单信息",0); //创建第一个sheet名称
WritableFont wf_table = new WritableFont(WritableFont.ARIAL, 11,
WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK); // 定义格式字体下划线斜体粗体颜色

WritableCellFormat wcf_table1 = new WritableCellFormat(wf_table);
wcf_table1.setAlignment(jxl.format.Alignment.CENTRE);
wcf_table1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);

//这里面要导出的表格一共三列，下标从0开始
ws.setColumnView(0, 25); // 设置第一列的宽度
ws.setColumnView(1, 25); // 设置第二列的宽度
ws.setColumnView(2, 100); // 设置第三列的宽度

Label title=new Label(0,0,"广西联通集团客户业务订单",wcf_table1); //设置第一列第一行的数据为“广西联通***订单”

//将定义好的单元格添加到工作表中

ws.addCell(title);

//开始合并单元格。第一第三个参数表示列，第二第四个参数表示行

ws.mergeCells(0, 0, 2, 0); //合并第一列的第一行到第三列的第一行
ws.mergeCells(0, 1, 0, 8); //合并第一列的第二行到第一列的第九行
ws.mergeCells(0, 9, 0, 12);
ws.mergeCells(0, 13, 1, 13);
ws.mergeCells(0, 14, 0, 22);
ws.mergeCells(0, 23, 0, 29);

//设置每一列的内容
ws.addCell(new Label(0,1,"工单信息",wcf_table1));
ws.addCell(new Label(0,2,"工单信息",wcf_table1));
ws.addCell(new Label(0,3,"工单信息",wcf_table1));
ws.addCell(new Label(0,4,"工单信息",wcf_table1));
ws.addCell(new Label(0,5,"工单信息",wcf_table1));
ws.addCell(new Label(0,6,"工单信息",wcf_table1));
ws.addCell(new Label(0,7,"工单信息",wcf_table1));
ws.addCell(new Label(0,8,"工单信息",wcf_table1));
ws.addCell(new Label(0,9,"合同信息",wcf_table1));
ws.addCell(new Label(0,10,"合同信息",wcf_table1));
ws.addCell(new Label(0,11,"合同信息",wcf_table1));
ws.addCell(new Label(0,12,"合同信息",wcf_table1));
ws.addCell(new Label(0,13,"客户提出的特殊要求",wcf_table1));
ws.addCell(new Label(0,14,"全程电路信息",wcf_table1));
ws.addCell(new Label(0,15,"全程电路信息",wcf_table1));
ws.addCell(new Label(0,16,"全程电路信息",wcf_table1));
ws.addCell(new Label(0,17,"全程电路信息",wcf_table1));
ws.addCell(new Label(0,18,"全程电路信息",wcf_table1));
ws.addCell(new Label(0,19,"全程电路信息",wcf_table1));
ws.addCell(new Label(0,20,"全程电路信息",wcf_table1));
ws.addCell(new Label(0,21,"全程电路信息",wcf_table1));
ws.addCell(new Label(0,22,"全程电路信息",wcf_table1));
ws.addCell(new Label(0,23,"电路信息",wcf_table1));
ws.addCell(new Label(0,24,"电路信息",wcf_table1));
ws.addCell(new Label(0,25,"电路信息",wcf_table1));
ws.addCell(new Label(0,26,"电路信息",wcf_table1));
ws.addCell(new Label(0,27,"电路信息",wcf_table1));
ws.addCell(new Label(0,28,"电路信息",wcf_table1));
ws.addCell(new Label(0,29,"电路信息",wcf_table1));
ws.addCell(new Label(1,1,"服务订单号",wcf_table1));
ws.addCell(new Label(1,2,"工单类型",wcf_table1));
ws.addCell(new Label(1,3,"工单生成日期",wcf_table1));
ws.addCell(new Label(1,4,"立项审批DocID",wcf_table1));
ws.addCell(new Label(1,5,"项目名称",wcf_table1));
ws.addCell(new Label(1,6,"工程类别",wcf_table1));
ws.addCell(new Label(1,7,"要求竣工交付日期",wcf_table1));
ws.addCell(new Label(1,8,"故障修复时限(小时)",wcf_table1));
ws.addCell(new Label(1,9,"合同编号",wcf_table1));
ws.addCell(new Label(1,10,"合同客户单位名称(全称)",wcf_table1));
ws.addCell(new Label(1,11,"客户经理姓名",wcf_table1));
ws.addCell(new Label(1,12,"客户经理联系电话",wcf_table1));
ws.addCell(new Label(1,14,"BSS设备号",wcf_table1));
ws.addCell(new Label(1,15,"电路编号",wcf_table1));
ws.addCell(new Label(1,16,"旧电路编号",wcf_table1));
ws.addCell(new Label(1,17,"业务要求",wcf_table1));
ws.addCell(new Label(1,18,"新带宽速率",wcf_table1));
ws.addCell(new Label(1,19,"当前带宽速率",wcf_table1));
ws.addCell(new Label(1,20,"跨域类别",wcf_table1));
ws.addCell(new Label(1,21,"电路类型",wcf_table1));
ws.addCell(new Label(1,22,"城市",wcf_table1));
ws.addCell(new Label(1,23,"客户单位名称",wcf_table1));
ws.addCell(new Label(1,24,"装机地址",wcf_table1));
ws.addCell(new Label(1,25,"发端旧地址",wcf_table1));
ws.addCell(new Label(1,26,"联系人",wcf_table1));
ws.addCell(new Label(1,27,"联系电话",wcf_table1));
ws.addCell(new Label(1,28,"接口类型",wcf_table1));

ws.addCell(new Label(1,29,"传输模式",wcf_table1));

//表格模版完成，开始将数据加到表中

for(int i =0;i<list.size();i++){
Map<String,String> valueMap = list.get(i);
ws.addCell(new Label(2,1,valueMap.get("service_order_id"),wcf_table1));
ws.addCell(new Label(2,2,valueMap.get("order_type"),wcf_table1));
ws.addCell(new Label(2,3,valueMap.get("order_create_date"),wcf_table1));
ws.addCell(new Label(2,4,valueMap.get("doc_id"),wcf_table1));
ws.addCell(new Label(2,5,valueMap.get("project_name"),wcf_table1));
ws.addCell(new Label(2,6,valueMap.get("project_type"),wcf_table1));
ws.addCell(new Label(2,7,valueMap.get("required_complete_date"),wcf_table1));
ws.addCell(new Label(2,8,valueMap.get("fault_repair_time"),wcf_table1));
ws.addCell(new Label(2,9,valueMap.get("contract_no"),wcf_table1));
ws.addCell(new Label(2,10,valueMap.get("contract_com_name"),wcf_table1));
ws.addCell(new Label(2,11,valueMap.get("customer_manager_name"),wcf_table1));
ws.addCell(new Label(2,12,valueMap.get("customer_manager_tel"),wcf_table1));
ws.addCell(new Label(2,13,valueMap.get("customer_special_require"),wcf_table1));
ws.addCell(new Label(2,14,valueMap.get("bss_equipment_id"),wcf_table1));
ws.addCell(new Label(2,15,valueMap.get("circuit_code"),wcf_table1));
ws.addCell(new Label(2,16,valueMap.get("circuit_code_old"),wcf_table1));
ws.addCell(new Label(2,17,valueMap.get("biz_require"),wcf_table1));
ws.addCell(new Label(2,18,valueMap.get("rate_new"),wcf_table1));
ws.addCell(new Label(2,19,valueMap.get("rate_now"),wcf_table1));
ws.addCell(new Label(2,20,valueMap.get("cross_domain_type"),wcf_table1));
ws.addCell(new Label(2,21,valueMap.get("circuit_type"),wcf_table1));
ws.addCell(new Label(2,22,valueMap.get("city_a"),wcf_table1));
ws.addCell(new Label(2,23,valueMap.get("customer_com_name_a"),wcf_table1));
ws.addCell(new Label(2,24,valueMap.get("installed_address_a"),wcf_table1));
ws.addCell(new Label(2,25,valueMap.get("old_address_a"),wcf_table1));
ws.addCell(new Label(2,26,valueMap.get("contact_a"),wcf_table1));
ws.addCell(new Label(2,27,valueMap.get("contact_tel_a"),wcf_table1));
ws.addCell(new Label(2,28,valueMap.get("interface_type_a"),wcf_table1));
ws.addCell(new Label(2,29,valueMap.get("transmission_mode_a"),wcf_table1));
}
wwb.write();
wwb.close();
os.flush();
os.close();
System.out.println("导出完成");
}catch(Exception e){
System.out.println(e);
}
return null;

}

//前台调用：

function printTableToExcel(){
Ext.Ajax.request({
timeout:1000*600,
url:'cssp/bssorder/workOrderAction.do?method=exportOrder&ufid=' + ufid,
form:Ext.fly('form'),
isUpload:true
});
}

0 0