jxl 打印excel,样式设置

￡神魔★判官ぃ 2022-03-11 03:43 268阅读 0赞

运用jxl导出excel，并设置一些打印的样式：

public void outputExcel(HttpServletRequest request, HttpServletResponse response,String title)throws  IOException
    	{
    		//获取输出流 
    		OutputStream os = response.getOutputStream(); 
    		HttpSession session=request.getSession();
    		String oprator=(String)session.getAttribute("yhxm");
    		//设置编码  
    		response.setHeader("Content-disposition", "attachment; filename="+"cprdpxx"+".xls");// 设定输出文件头 
    		request.setCharacterEncoding("utf-8");  
    		//设置文件格式
    		response.setContentType("application/vnd.ms-excel;charset=utf-8");
     		//加标题    
    		//标题字体   
    		//设置单元格字体，位置
    		jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.COURIER, 18, WritableFont.BOLD, true);  
    		jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc); 
    		try {
    			wcfFC.setAlignment(jxl.format.Alignment.CENTRE);
    			wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);    
    			//字段字体   
    			jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(WritableFont.COURIER, 12, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK);  
    			jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(wfc1);
    			wcfFC1.setBorder(Border.NONE,BorderLineStyle.THIN,Colour.GRAY_50);
    			//设置字体位置
    			wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);  
    			wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    			//
    			//2013/7/17
    			//标题字体
    			jxl.write.WritableFont wfc3 = new jxl.write.WritableFont(WritableFont.COURIER, 14, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK);  
    			jxl.write.WritableCellFormat wcfFC3 = new jxl.write.WritableCellFormat(wfc3);
    			wcfFC3.setBorder(Border.NONE,BorderLineStyle.THIN,Colour.GRAY_50);
    			//设置字体位置
    			wcfFC3.setAlignment(jxl.format.Alignment.CENTRE);  
    			wcfFC3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
    			
    			//查询结果字体  
    			jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat();  
    			wcfFC2.setAlignment(jxl.format.Alignment.CENTRE); 
    			wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);   
    			WritableWorkbook wbook = Workbook.createWorkbook(os);  
    			//设置默认字体
    			WritableFont font=new WritableFont(WritableFont.COURIER, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
    			WritableCellFormat cFormat = new WritableCellFormat(font);
    			cFormat.setAlignment(jxl.format.Alignment.CENTRE);
    			cFormat.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.GRAY_50);
    			cFormat.setBackground(Colour.WHITE);
    			//2013/7/17
    			//设置信息头栏字体
    			WritableFont font11=new WritableFont(WritableFont.COURIER, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
    			WritableCellFormat cFormat11 = new WritableCellFormat(font11);
    			cFormat11.setAlignment(jxl.format.Alignment.CENTRE);
    			cFormat11.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.GRAY_50);
    			cFormat11.setBackground(Colour.WHITE);
    			
    			
    			//写sheet名称 
    			WritableSheet wsheet = wbook.createSheet("长江大学职称评审投票信息统计表", 0);
    			//设置单元格默认列宽
    			wsheet.getSettings().setDefaultColumnWidth(10);
    			wsheet.getSettings().setDefaultRowHeight(350);
                            //设置sheet页面水平打印
                             wsheet.getSettings().setHorizontalCentre(true);
    			//默认为横向打印//2013/7/17
    			wsheet.setPageSetup(PageOrientation.LANDSCAPE.LANDSCAPE,PaperSize.A4,0.5d,0.5d);
    			/**
    			 * lrr 2014-05-27 BEGIN
    			 * 功能描述：设置页码格式：第 X 页（共 X 页）
    			 */
    //			wsheet.setFooter("", "&P", "");//过时的方法
    			··HeaderFooter footer = new HeaderFooter();
    			··Contents contentsFooter = footer.getCentre();  
    		    contentsFooter.setFontSize(10);  
    		    contentsFooter.append("第 ");
    		    contentsFooter.appendPageNumber();
    		    contentsFooter.append(" 页 （ 共 ");
    		    contentsFooter.appendTotalPages();  
    		    contentsFooter.append(" 页 ）");
    			//设置打印标题行
    //			wsheet.getSettings().setVerticalFreeze(3);
    			SheetSettings ss=wsheet.getSettings();
    			ss.setFooter(footer);// 设置页脚
    			/**
    			 * lrr 2014-05-27 END
    			 * 功能描述：设置页码格式：第 X 页（共 X 页）
    			 */
    //			ss.setPrintTitles(0,2,0,10);
    			ss.setPrintTitlesRow(0,2);//设置固定打印标题
    			ss.setOrientation(PageOrientation.LANDSCAPE);
    			wsheet.setPageSetup(PageOrientation.LANDSCAPE);
    			
    			//标题   
    			wsheet.mergeCells(0, 0, 10, 0);
    			/**
    			 * lrr 2014-05-24 改 BEGIN
    			 * 功能描述：修改投票信息统计表格式
    			 */
    			wsheet.addCell(new jxl.write.Label(0, 0, "长江大学职称评审投票信息统计表",wcfFC3));//设置统计表标题
    			WritableFont font1=new WritableFont(WritableFont.COURIER, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
    			WritableCellFormat cFormat1 = new WritableCellFormat(font1);
    			cFormat1.setAlignment(jxl.format.Alignment.RIGHT);
    			cFormat1.setBorder(Border.NONE,BorderLineStyle.THIN,Colour.GRAY_50);
    			cFormat1.setBackground(Colour.WHITE);
    			wsheet.addCell(new jxl.write.Label(0,1,"评审日期："+(new SimpleDateFormat("yyyy-MM-dd").format(new java.util.Date())) ,cFormat1));//合计列
    			/**
    			 * lrr 2014-05-24 改 END
    			 * 功能描述：修改投票信息统计表格式
    			 */			
    			/**
    			 * 以下为从数据库中查询数据添加到wsheet中
    			 */	
    			PersistenceManagerOfZcps pmoz=new PersistenceManagerOfZcps();
    			String zsql="select count(*) from view_cprdp where 1=1 "+title;//总人数
    			//String xb_n_sql="select count(*) from view_cprxx where xb='1'";//性别男 人数
    			String hj="";//合计
    			String sql="select * from view_cprdp where 1=1 "+title;
    //			String year=request.getParameter("year");
    			sql+="order by year_dm desc,zcbh asc,ls,ty_num desc,cpbh";
    //            //System.out.println("sql="+sql);
    			ResultSet rs=pmoz.executeQuery(sql);
    			List<zc_cprdpxx> cprdp_list=new ArrayList<zc_cprdpxx>();
    			int zs = 0;//参评人总人数
    			int ns=0;//男参评人数
    			try {
    				while(rs.next())
    				{
    					zc_cprdpxx zp=new zc_cprdpxx();
    					PersistenceManagerOfZcps pm=new PersistenceManagerOfZcps();
    						zp.setYear(rs.getString("year_mc")==null?"":rs.getString("year_mc").trim());
    						zp.setCpbh(rs.getString("cpbh")==null?"":rs.getString("cpbh").trim());
    						zp.setCpxm(rs.getString("cpxm")==null?"":rs.getString("cpxm").trim());
    						zp.setLxdh(rs.getString("lxdh")==null?"":rs.getString("lxdh").trim());
    						zp.setTy_num(rs.getString("ty_num")==null?"":rs.getString("ty_num").trim());
    						zp.setBty_num(rs.getString("bty_num")==null?"":rs.getString("bty_num").trim());
    						zp.setQq_num(rs.getString("qq_num")==null?"":rs.getString("qq_num").trim());
    						zp.setSftg(rs.getString("sftg")==null?"":rs.getString("sftg").trim());
    						zp.setLs(rs.getString("ls")==null?"":rs.getString("ls").trim());
    						zp.setZwm(rs.getString("zwm_mc")==null?"":rs.getString("zwm_mc").trim());//2013/7/21
    						zp.setZcbh(pm.getPureCode_zcm(rs.getString("zcbh")==null?"":rs.getString("zcbh").trim()));	
    						zp.setCprzt(pm.getPureCode("code_zczt", rs.getString("zcbh").substring(1, 2)));
    						pm.close();
    						//zp.setNl(rs.getString("nl")==null?"":rs.getString("nl").trim());
    						
    						cprdp_list.add(zp);				
    				}
    				rs=pmoz.executeQuery(zsql);
    				while(rs.next())
    				{
    					 zs=rs.getInt(1);
    				}
    				rs.close();
    			   }catch (SQLException e) {
    				 os.close();
    				// TODO Auto-generated catch block
    				this.cwts("导出失败，请稍后重试！", response);
    				e.printStackTrace();
    			}
    			finally
    			{
    				pmoz.close();//关闭数据连接
    			}
    			
     			
    			
    			//hj="评委总人数为："+String.valueOf(zs)+"     性别        男："+String.valueOf(ns)+"   女："+String.valueOf(zs-ns);
    			wsheet.mergeCells(0, 1, 10, 1);//合并0到10单元格
    			//wsheet.addCell(new jxl.write.Label(0,1,hj ,cFormat));//合计列
    			// 设置固定的打印标题//2013/7/17
    		wsheet.getSettings().setPrintArea(0, 2, 0,7);
    //		SheetSettings setting = wsheet.getSettings();  
    //		setting.setPrintTitlesRow(0, 3); 
    		
    			int i = 0;
    			int j=0;
    			//String[] colum={"年度","参评人编号","单位名称","参评人姓名","性别","申报职称级别","申报职称名称"};
    			//2013/7/17修改
    			String[] colum={"年度", "参评人编号","工作单位","参评人姓名", "申报职称","申报状态","轮数","同意数","不同意数","弃权数","是否通过"};
    			for (i = 0; i <11; i++) {   
    				// 加入行字段名    
    				wsheet.addCell(new jxl.write.Label(i, 2, colum[i],      cFormat11));
    				} 
    			for(i=3;i<cprdp_list.size()+3;i++)
    			{
    				wsheet.addCell(new jxl.write.Label(0,i, cprdp_list.get(i-3).getYear(),      cFormat));
    				wsheet.addCell(new jxl.write.Label(1,i, cprdp_list.get(i-3).getCpbh(),      cFormat));
    				wsheet.addCell(new jxl.write.Label(2,i, cprdp_list.get(i-3).getLxdh(),      cFormat));
    				wsheet.addCell(new jxl.write.Label(3,i, cprdp_list.get(i-3).getCpxm(),      cFormat));
    				wsheet.addCell(new jxl.write.Label(4,i, cprdp_list.get(i-3).getZwm(),      cFormat));
    				wsheet.addCell(new jxl.write.Label(5,i, cprdp_list.get(i-3).getCprzt(),      cFormat));
    				wsheet.addCell(new jxl.write.Label(6,i, cprdp_list.get(i-3).getLs(),      cFormat));
    				wsheet.addCell(new jxl.write.Label(7,i, cprdp_list.get(i-3).getTy_num(),      cFormat));
    				wsheet.addCell(new jxl.write.Label(8,i, cprdp_list.get(i-3).getBty_num(),      cFormat));
    				wsheet.addCell(new jxl.write.Label(9,i, cprdp_list.get(i-3).getQq_num(),      cFormat));
    				wsheet.addCell(new jxl.write.Label(10,i, cprdp_list.get(i-3).getSftg(),      cFormat));
     				
    			} 
    			
    			wsheet.mergeCells(0, i, 10, i);//合并0到10单元格
    			/**
    			 * lrr 2014-05-24 增 BEGIN
    			 * 功能描述：增加四行：1.监票人 2.评审委员会主任
    			 */
    			wsheet.addCell(new jxl.write.Label(0,i,"制表人："+oprator ,cFormat1));//合计列
    			i = i+1;
    			wsheet.mergeCells(0, i, 10, i+1);//合并两行并同时合并单元格
    			wsheet.addCell(new jxl.write.Label(0,i,"监票人：       " ,cFormat1));//合计列
    			i = i+2;
    			wsheet.mergeCells(0, i, 10, i+1);//合并两行并同时合并单元格
    			wsheet.addCell(new jxl.write.Label(0,i,"评审委员会主任：       " ,cFormat1));//合计列
    			/**
    			 * lrr 2014-05-24 增 END
    			 * 功能描述：增加两行：1.监票人 2.评审委员会主任
    			 */
    			wbook.write();   
    			wbook.close();  
    			
    		} catch (WriteException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    			//this.cwts("导出失败，请稍后重试！", response);
    		} catch (IOException e1) {
    			// TODO Auto-generated catch block
    			e1.printStackTrace();
    			//this.cwts("导出失败，请稍后重试！", response);
    		 }finally{  
    			 try{
    			     os.close();
    			    } catch (IOException e1) {
    					// TODO Auto-generated catch block
    					e1.printStackTrace();
    					//this.cwts("导出失败，请稍后重试！", response);
    				 }
    		}
    	}

相应的jxl.jar在附件中可下载。

WritableSheet.mergeCells(0, 0, 0, 1);//合并单元格，第一个参数：要合并的单元格最左上角的列号，第二个参数：要合并的单元格最左上角的行号，第三个参数：要合并的单元格最右角的列号，第四个参数：要合并的单元格最右下角的行号