导出excel笔记

工作中导出excel的笔记，jxl实现。

/**
	 * 导出操作
	 * @return
	 * @throws Exception
	 */
	public String exportExcel() throws Exception {
		HttpServletRequest request = ActionContext.getActionContext().getRequest();

		//获取需导出的试题集合list
		List list = planAuditService.getExportData();
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		try {
			//取得response HttpServletResponse
			HttpServletResponse response=ActionContext.getActionContext().getResponse();
			OutputStream os = response.getOutputStream();// 取得输出流   
			response.reset();// 清空输出流  

			String excelName = dateFormat.format(new Date()) + "培训班数据";
			response.setHeader("Content-disposition", "attachment; filename="+new String(excelName.getBytes("GB2312"),"8859_1")+".xls");// 设定输出文件头   
			response.setContentType("application/msexcel");// 定义输出类型 application/vnd.ms-excel

			WritableWorkbook wwb = Workbook.createWorkbook(os); // 建立excel文件   

			WritableSheet ws = wwb.createSheet("Sheet1", 10);// 创建一个工作表

			//    设置单元格的文字格式
			WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false,
					UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
			WritableCellFormat wcf = new WritableCellFormat(wf);
			wcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//设置边框
			wcf.setVerticalAlignment(VerticalAlignment.BOTTOM); 
			wcf.setAlignment(Alignment.LEFT); 

			WritableCellFormat wcf_back = new WritableCellFormat(wf);
			wcf_back.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//设置边框
			wcf_back.setVerticalAlignment(VerticalAlignment.BOTTOM); 
			wcf_back.setAlignment(Alignment.LEFT); 
			wcf_back.setBackground(jxl.format.Colour.GRAY_25);//设置背景色

			WritableCellFormat wcf_nr = new WritableCellFormat(wf);//培训班内容单元格样式
			wcf_back.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//设置边框
			wcf_back.setVerticalAlignment(VerticalAlignment.BOTTOM); 
			wcf_back.setAlignment(Alignment.LEFT); 

			ws.setRowView(0, 500);//高度 根据需要设置
			ws.setColumnView(0, 30);//宽度
			ws.setColumnView(1, 30);//宽度
			ws.setColumnView(2, 30);//宽度
			ws.setColumnView(3, 15);//宽度
			ws.setColumnView(4, 15);//宽度
			ws.setColumnView(5, 15);//宽度
			ws.setColumnView(6, 15);//宽度
			ws.setColumnView(7, 20);//宽度

			//准备excel数据
			String zbdw = "";
			Label col_1 = null;//第1列内容
			Label col_2 = null;
			Label col_3 = null;
			Label col_4 = null;
			Label col_5 = null;
			Label col_6 = null;
			Label col_7 = null;
			Label col_8 = null;
			Label col_9 = null;//第9列内容
			Label col_10 = null;//培训内容列
			Label ltnr = null;
			int rowid = 0;//插入数据所在的行数，0开始
			for (int j = 0; j < list.size(); j++) {
				Object[] obj = (Object[]) list.get(j);//当前循环的数据
				zbdw = obj[1] == null ? "" : obj[1].toString();
				//从第二条数据开始判断是否显示“主办单位”
				if(j>0){
					Object[] obj_1 = (Object[]) list.get(j-1);//上一循环数据
					zbdw = obj[1] == null ? "" : obj[1].toString();
					if(zbdw.equals(obj_1[1] == null ? "" : obj_1[1].toString())){
						zbdw = "";
					}else{
						Label l1 = new  Label( 0,  rowid,  "主办单位" ,wcf_back); 
						Label l2 = new  Label( 1,  rowid,  "培训班名称" ,wcf_back); 
						Label l3 = new  Label( 2,  rowid,  "培训机构" ,wcf_back); 
						Label l4 = new  Label( 3,  rowid,  "培训班类型" ,wcf_back); 
						Label l5 = new  Label( 4,  rowid,  "培训班类型" ,wcf_back); 
						Label l6 = new  Label( 5,  rowid,  "开课时间" ,wcf_back); //若需自动判断选项数量，则可放开Label lt = null的注释
						Label l7 = new  Label( 6,  rowid,  "结束时间" ,wcf_back); 
						Label l8 = new  Label( 7,  rowid,  "离岗状态" ,wcf_back); 
						Label l9 = new  Label( 8,  rowid,  "学时" ,wcf_back);  

						ws.addCell(l1);
						ws.addCell(l2);
						ws.addCell(l3);
						ws.addCell(l4);
						ws.addCell(l5);
						ws.addCell(l6);
						ws.addCell(l7);
						ws.addCell(l8);
						ws.addCell(l9);
						rowid++;
					}
				}else{
					Label l1 = new  Label( 0,  rowid,  "主办单位" ,wcf_back); 
					Label l2 = new  Label( 1,  rowid,  "培训班名称" ,wcf_back); 
					Label l3 = new  Label( 2,  rowid,  "培训机构" ,wcf_back); 
					Label l4 = new  Label( 3,  rowid,  "培训班类型" ,wcf_back); 
					Label l5 = new  Label( 4,  rowid,  "培训班类型" ,wcf_back); 
					Label l6 = new  Label( 5,  rowid,  "开课时间" ,wcf_back); //若需自动判断选项数量，则可放开Label lt = null的注释
					Label l7 = new  Label( 6,  rowid,  "结束时间" ,wcf_back); 
					Label l8 = new  Label( 7,  rowid,  "离岗状态" ,wcf_back); 
					Label l9 = new  Label( 8,  rowid,  "学时" ,wcf_back);  

					ws.addCell(l1);
					ws.addCell(l2);
					ws.addCell(l3);
					ws.addCell(l4);
					ws.addCell(l5);
					ws.addCell(l6);
					ws.addCell(l7);
					ws.addCell(l8);
					ws.addCell(l9);
					rowid++;
				}

				col_1 = new Label(0, rowid, zbdw, wcf);
				col_2 = new Label(1, rowid, obj[2]==null?"":obj[2].toString(), wcf);
				col_3 = new Label(2, rowid, obj[3]==null?"":obj[3].toString(), wcf);
				col_4 = new Label(3, rowid, obj[4]==null?"":obj[4].toString(), wcf);
				col_5 = new Label(4, rowid, obj[5]==null?"":obj[5].toString(), wcf);
				col_6 = new Label(5, rowid, obj[6]==null?"":obj[6].toString(), wcf);
				col_7 = new Label(6, rowid, obj[7]==null?"":obj[7].toString(), wcf);
				col_8 = new Label(7, rowid, obj[8]==null?"":obj[8].toString(), wcf);
				col_9 = new Label(8, rowid, obj[9]==null?"":obj[9].toString(), wcf);

				ws.addCell(col_1);
				ws.addCell(col_2);
				ws.addCell(col_3);
				ws.addCell(col_4);
				ws.addCell(col_5);
				ws.addCell(col_6);
				ws.addCell(col_7);
				ws.addCell(col_8);
				ws.addCell(col_9);
				rowid++;

				ltnr = new Label(1, rowid, "培训内容", wcf_back);//培训内容
				ws.addCell(ltnr);

				//合并第3列第rowid行到第9列第rowid行的所有单元格 sheet.mergeCells(0,0,5,0); 
				ws.mergeCells(2, rowid, 8, rowid);
				col_10 = new Label(2, rowid, obj[10]==null?"":obj[10].toString(), wcf_nr);
				ws.addCell(col_10);
				rowid++;
			}

			wwb.write();
			wwb.close();

		} catch (IOException e){
			e.printStackTrace();
		} catch (RowsExceededException e1){
			e1.printStackTrace();
		} catch (WriteException e2){
			e2.printStackTrace();
		}
		return null;
	}

更多offic格式文件可用poi，目前为止poi也不能实现2007的导出