导出excel笔记

旧城等待， 2022-03-27 13:07 88阅读 0赞

工作中导出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的导出