天天看點
傳回

jxl 列印excel,樣式設定

Jxl導出excel代碼

  1. public void outputExcel(HttpServletRequest request, HttpServletResponse response,String title)throws  IOException  
  2.     {  
  3.         //擷取輸出流   
  4.         OutputStream os = response.getOutputStream();   
  5.         HttpSession session=request.getSession();  
  6.         String oprator=(String)session.getAttribute("yhxm");  
  7.         //設定編碼    
  8.         response.setHeader("Content-disposition", "attachment; filename="+"cprdpxx"+".xls");// 設定輸出檔案頭   
  9.         request.setCharacterEncoding("utf-8");    
  10.         //設定檔案格式  
  11.         response.setContentType("application/vnd.ms-excel;charset=utf-8");  
  12.         //加标題      
  13.         //标題字型     
  14.         //設定單元格字型，位置  
  15.         jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.COURIER, 18, WritableFont.BOLD, true);    
  16.         jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);   
  17.         try {  
  18.             wcfFC.setAlignment(jxl.format.Alignment.CENTRE);  
  19.             wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);      
  20.             //字段字型     
  21.             jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(WritableFont.COURIER, 12, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK);    
  22.             jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(wfc1);  
  23.             wcfFC1.setBorder(Border.NONE,BorderLineStyle.THIN,Colour.GRAY_50);  
  24.             //設定字型位置  
  25.             wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);    
  26.             wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
  27.             //  
  28.             //2013/7/17  
  29.             //标題字型  
  30.             jxl.write.WritableFont wfc3 = new jxl.write.WritableFont(WritableFont.COURIER, 14, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK);    
  31.             jxl.write.WritableCellFormat wcfFC3 = new jxl.write.WritableCellFormat(wfc3);  
  32.             wcfFC3.setBorder(Border.NONE,BorderLineStyle.THIN,Colour.GRAY_50);  
  33.             //設定字型位置  
  34.             wcfFC3.setAlignment(jxl.format.Alignment.CENTRE);    
  35.             wcfFC3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);    
  36.             //查詢結果字型    
  37.             jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat();    
  38.             wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);   
  39.             wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);     
  40.             WritableWorkbook wbook = Workbook.createWorkbook(os);    
  41.             //設定預設字型  
  42.             WritableFont font=new WritableFont(WritableFont.COURIER, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);  
  43.             WritableCellFormat cFormat = new WritableCellFormat(font);  
  44.             cFormat.setAlignment(jxl.format.Alignment.CENTRE);  
  45.             cFormat.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.GRAY_50);  
  46.             cFormat.setBackground(Colour.WHITE);  
  47.             //2013/7/17  
  48.             //設定資訊頭欄字型  
  49.             WritableFont font11=new WritableFont(WritableFont.COURIER, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);  
  50.             WritableCellFormat cFormat11 = new WritableCellFormat(font11);  
  51.             cFormat11.setAlignment(jxl.format.Alignment.CENTRE);  
  52.             cFormat11.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.GRAY_50);  
  53.             cFormat11.setBackground(Colour.WHITE);  
  54.             //寫sheet名稱   
  55.             WritableSheet wsheet = wbook.createSheet("長江大學職稱評審投票資訊統計表", 0);  
  56.             //設定單元格預設列寬  
  57.             wsheet.getSettings().setDefaultColumnWidth(10);  
  58.             wsheet.getSettings().setDefaultRowHeight(350);  
  59.             //預設為橫向列印//2013/7/17  
  60.             wsheet.setPageSetup(PageOrientation.LANDSCAPE.LANDSCAPE,PaperSize.A4,0.5d,0.5d);  
  61. //          wsheet.setFooter("", "&P", "");//過時的方法  
  62.             ··HeaderFooter footer = new HeaderFooter();  
  63.             ··Contents contentsFooter = footer.getCentre();    
  64.             contentsFooter.setFontSize(10);    
  65.             contentsFooter.append("第 ");  
  66.             contentsFooter.appendPageNumber();  
  67.             contentsFooter.append(" 頁 （ 共 ");  
  68.             contentsFooter.appendTotalPages();    
  69.             contentsFooter.append(" 頁 ）");  
  70.             //設定列印标題行  
  71. //          wsheet.getSettings().setVerticalFreeze(3);  
  72.             SheetSettings ss=wsheet.getSettings();  
  73.             ss.setFooter(footer);// 設定頁腳  
  74. //          ss.setPrintTitles(0,2,0,10);  
  75.             ss.setPrintTitlesRow(0,2);//設定固定列印标題  
  76.             ss.setOrientation(PageOrientation.LANDSCAPE);  
  77.             wsheet.setPageSetup(PageOrientation.LANDSCAPE);  
  78.             //标題     
  79.             wsheet.mergeCells(0, 0, 10, 0);  
  80.             wsheet.addCell(new jxl.write.Label(0, 0, "長江大學職稱評審投票資訊統計表",wcfFC3));//設定統計表标題  
  81.             WritableFont font1=new WritableFont(WritableFont.COURIER, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);  
  82.             WritableCellFormat cFormat1 = new WritableCellFormat(font1);  
  83.             cFormat1.setAlignment(jxl.format.Alignment.RIGHT);  
  84.             cFormat1.setBorder(Border.NONE,BorderLineStyle.THIN,Colour.GRAY_50);  
  85.             cFormat1.setBackground(Colour.WHITE);  
  86.             wsheet.addCell(new jxl.write.Label(0,1,"評審日期："+(new SimpleDateFormat("yyyy-MM-dd").format(new java.util.Date())) ,cFormat1));//合計列  
  87.             PersistenceManagerOfZcps pmoz=new PersistenceManagerOfZcps();  
  88.             String zsql="select count(*) from view_cprdp where 1=1 "+title;//總人數  
  89.             //String xb_n_sql="select count(*) from view_cprxx where xb='1'";//性别男 人數  
  90.             String hj="";//合計  
  91.             String sql="select * from view_cprdp where 1=1 "+title;  
  92. //          String year=request.getParameter("year");  
  93.             sql+="order by year_dm desc,zcbh asc,ls,ty_num desc,cpbh";  
  94. //            //System.out.println("sql="+sql);  
  95.             ResultSet rs=pmoz.executeQuery(sql);  
  96.             List<zc_cprdpxx> cprdp_list=new ArrayList<zc_cprdpxx>();  
  97.             int zs = 0;//參評人總人數  
  98.             int ns=0;//男參評人數  
  99.             try {  
  100.                 while(rs.next())  
  101.                 {  
  102.                     zc_cprdpxx zp=new zc_cprdpxx();  
  103.                     PersistenceManagerOfZcps pm=new PersistenceManagerOfZcps();  
  104.                         zp.setYear(rs.getString("year_mc")==null?"":rs.getString("year_mc").trim());  
  105.                         zp.setCpbh(rs.getString("cpbh")==null?"":rs.getString("cpbh").trim());  
  106.                         zp.setCpxm(rs.getString("cpxm")==null?"":rs.getString("cpxm").trim());  
  107.                         zp.setLxdh(rs.getString("lxdh")==null?"":rs.getString("lxdh").trim());  
  108.                         zp.setTy_num(rs.getString("ty_num")==null?"":rs.getString("ty_num").trim());  
  109.                         zp.setBty_num(rs.getString("bty_num")==null?"":rs.getString("bty_num").trim());  
  110.                         zp.setQq_num(rs.getString("qq_num")==null?"":rs.getString("qq_num").trim());  
  111.                         zp.setSftg(rs.getString("sftg")==null?"":rs.getString("sftg").trim());  
  112.                         zp.setLs(rs.getString("ls")==null?"":rs.getString("ls").trim());  
  113.                         zp.setZwm(rs.getString("zwm_mc")==null?"":rs.getString("zwm_mc").trim());//2013/7/21  
  114.                         zp.setZcbh(pm.getPureCode_zcm(rs.getString("zcbh")==null?"":rs.getString("zcbh").trim()));    
  115.                         zp.setCprzt(pm.getPureCode("code_zczt", rs.getString("zcbh").substring(1, 2)));  
  116.                         pm.close();  
  117.                         //zp.setNl(rs.getString("nl")==null?"":rs.getString("nl").trim());  
  118.                         cprdp_list.add(zp);               
  119.                 }  
  120.                 rs=pmoz.executeQuery(zsql);  
  121.                 while(rs.next())  
  122.                 {  
  123.                      zs=rs.getInt(1);  
  124.                 }  
  125.                 rs.close();  
  126.                }catch (SQLException e) {  
  127.                  os.close();  
  128.                 // TODO Auto-generated catch block  
  129.                 this.cwts("導出失敗，請稍後重試！", response);  
  130.                 e.printStackTrace();  
  131.             }  
  132.             finally  
  133.             {  
  134.                 pmoz.close();//關閉資料連接配接  
  135.             }  
  136.             //hj="評委總人數為："+String.valueOf(zs)+"     性别        男："+String.valueOf(ns)+"   女："+String.valueOf(zs-ns);  
  137.             wsheet.mergeCells(0, 1, 10, 1);//合并0到10單元格  
  138.             //wsheet.addCell(new jxl.write.Label(0,1,hj ,cFormat));//合計列  
  139.             // 設定固定的列印标題//2013/7/17  
  140.         wsheet.getSettings().setPrintArea(0, 2, 0,7);  
  141. //      SheetSettings setting = wsheet.getSettings();    
  142. //      setting.setPrintTitlesRow(0, 3);   
  143.             int i = 0;  
  144.             int j=0;  
  145.             //String[] colum={"年度","參評人編号","機關名稱","參評人姓名","性别","申報職稱級别","申報職稱名稱"};  
  146.             //2013/7/17修改  
  147.             String[] colum={"年度", "參評人編号","工作機關","參評人姓名", "申報職稱","申報狀态","輪數","同意數","不同意數","棄權數","是否通過"};  
  148.             for (i = 0; i <11; i++) {     
  149.                 // 加入行字段名      
  150.                 wsheet.addCell(new jxl.write.Label(i, 2, colum[i],      cFormat11));  
  151.                 }   
  152.             for(i=3;i<cprdp_list.size()+3;i++)  
  153.             {  
  154.                 wsheet.addCell(new jxl.write.Label(0,i, cprdp_list.get(i-3).getYear(),      cFormat));  
  155.                 wsheet.addCell(new jxl.write.Label(1,i, cprdp_list.get(i-3).getCpbh(),      cFormat));  
  156.                 wsheet.addCell(new jxl.write.Label(2,i, cprdp_list.get(i-3).getLxdh(),      cFormat));  
  157.                 wsheet.addCell(new jxl.write.Label(3,i, cprdp_list.get(i-3).getCpxm(),      cFormat));  
  158.                 wsheet.addCell(new jxl.write.Label(4,i, cprdp_list.get(i-3).getZwm(),      cFormat));  
  159.                 wsheet.addCell(new jxl.write.Label(5,i, cprdp_list.get(i-3).getCprzt(),      cFormat));  
  160.                 wsheet.addCell(new jxl.write.Label(6,i, cprdp_list.get(i-3).getLs(),      cFormat));  
  161.                 wsheet.addCell(new jxl.write.Label(7,i, cprdp_list.get(i-3).getTy_num(),      cFormat));  
  162.                 wsheet.addCell(new jxl.write.Label(8,i, cprdp_list.get(i-3).getBty_num(),      cFormat));  
  163.                 wsheet.addCell(new jxl.write.Label(9,i, cprdp_list.get(i-3).getQq_num(),      cFormat));  
  164.                 wsheet.addCell(new jxl.write.Label(10,i, cprdp_list.get(i-3).getSftg(),      cFormat));  
  165.             }   
  166.             wsheet.mergeCells(0, i, 10, i);//合并0到10單元格  
  167.             wsheet.addCell(new jxl.write.Label(0,i,"制表人："+oprator ,cFormat1));//合計列  
  168.             i = i+1;  
  169.             wsheet.mergeCells(0, i, 10, i+1);//合并兩行并同時合并單元格  
  170.             wsheet.addCell(new jxl.write.Label(0,i,"監票人：       " ,cFormat1));//合計列  
  171.             i = i+2;  
  172.             wsheet.mergeCells(0, i, 10, i+1);//合并兩行并同時合并單元格  
  173.             wsheet.addCell(new jxl.write.Label(0,i,"評審委員會主任：       " ,cFormat1));//合計列  
  174.             wbook.write();     
  175.             wbook.close();    
  176.         } catch (WriteException e) {  
  177.             // TODO Auto-generated catch block  
  178.             e.printStackTrace();  
  179.             //this.cwts("導出失敗，請稍後重試！", response);  
  180.         } catch (IOException e1) {  
  181.             // TODO Auto-generated catch block  
  182.             e1.printStackTrace();  
  183.             //this.cwts("導出失敗，請稍後重試！", response);  
  184.          }finally{    
  185.              try{  
  186.                  os.close();  
  187.                 } catch (IOException e1) {  
  188.                     // TODO Auto-generated catch block  
  189.                     e1.printStackTrace();  
  190.                     //this.cwts("導出失敗，請稍後重試！", response);  
  191.                  }  
  192.         }  
  193.     }   
jxl
上一篇: JXL基礎應用
下一篇: java jxl api_JXl常見API說明

繼續閱讀