<rt id="bn8ez"></rt>
<label id="bn8ez"></label>
  • 

    • <span id="bn8ez"></span>
    

    <label id="bn8ez"><meter id="bn8ez"></meter></label>
    

    
    

    




    







												


        

    
	
    
	
    
	
    
			
					
	
    
		
    
			采用jxl實現數據庫結果集導出到excel文件
		
    
		
		
    
				采用jxl實現數據庫結果集導出到excel文件
		
    
		
    
		
    
				關鍵字：
		
    jxl, excel, servlet

    代碼：
    servlet：
    import?jxl.WorkbookSettings;
    import?jxl.Workbook;
    import?jxl.write.WritableWorkbook;
    import?jxl.write.WritableSheet;
    import?jxl.write.Label;
    import?jxl.write.WriteException;
    import?org.springframework.web.context.WebApplicationContext;
    import?org.springframework.web.context.support.WebApplicationContextUtils;
    import?org.springframework.jdbc.core.JdbcTemplate;
    import?org.springframework.jdbc.core.ResultSetExtractor;
    import?org.springframework.jdbc.support.JdbcUtils;
    import?org.springframework.dao.DataAccessException;
    import?org.apache.commons.logging.Log;
    import?org.apache.commons.logging.LogFactory;
    import?org.apache.commons.lang.StringUtils;
    import?org.apache.commons.lang.ArrayUtils;

    import?javax.servlet.http.HttpServlet;
    import?javax.servlet.http.HttpServletRequest;
    import?javax.servlet.http.HttpServletResponse;
    import?javax.servlet.ServletException;
    import?javax.servlet.ServletConfig;
    import?java.util.Locale;
    import?java.util.HashMap;
    import?java.util.Map;
    import?java.io.IOException;
    import?java.sql.ResultSet;
    import?java.sql.SQLException;
    import?java.sql.ResultSetMetaData;

    /**
    ?*?
    Title:ExcelGenerator?servlet
    ?*?
    Description:?采用jxl實現數據庫結果集導出到excel文件。
    ?*?
    Copyright:?Copyright.com?(c)?2003
    ?*?
    Company:
    ?*?History:
    ?*?create
    ?*
    ?*?    @author?youlq
    ?*?    @version?1.0
    ?    */
    public?class?ExcelGenerator?extends?HttpServlet{
    ??    //設定每個Sheet的行數
    ??private?int?pagesize=5000;
    ??    private?WorkbookSettings?workbookSettings=new?WorkbookSettings();
    ??    //springframework?的?WebApplicationContext
    ??public?static?WebApplicationContext?wac=null;
    ??    //springframework?的?jdbc?操作模版類
    ??public?static?JdbcTemplate?jdbcTemplate=null;
    ??    protected?final?Log?logger=LogFactory.getLog(getClass());

    ??    /**
    ???*?初始化
    ???*
    ???*?    @param?config
    ???*?    @throws?ServletException
    ???    */
    ??    public?void?init(ServletConfig?config)?throws?ServletException{
    ????    super.init(config);
    ????    try{
    ??????    if(null!=getInitParameter("pagesize")){
    ????????pagesize    =Integer.parseInt(getInitParameter("pagesize"));
    ??????}
    ??????workbookSettings.setLocale(Locale.getDefault());
    ??????wac    =WebApplicationContextUtils.getRequiredWebApplicationContext(getServletContext());
    ??????jdbcTemplate    =(JdbcTemplate)wac.getBean("jdbcTemplate");
    ????}    ?catch(Exception?e){
    ??????logger.error(    "ExcelGenerator?init()?error?!"+e,?e.getCause());
    ??????e.printStackTrace();
    ????}
    ??}

    ??    public?String?getServletInfo(){
    ????    return?"Servlet?used?to?generate?excel?output";
    ??}

    ??    public?void?doGet(HttpServletRequest?request,?HttpServletResponse?response)?throws?ServletException,?IOException{
    ????generateExcel(request,?response);
    ??}

    ??    public?void?doPost(HttpServletRequest?request,?HttpServletResponse?response)?throws?ServletException,?IOException{
    ????generateExcel(request,?response);
    ??}

    ??    /**
    ???*?in:
    ???*?field1#Title&field2#Title&field3#Title
    ???*?out:
    ???*?{
    ???*?field1:Title,
    ???*?field2:Title
    ???*?field3:Title
    ???*?}
    ???*
    ???*?    @param?columnTitle
    ???    */
    ??    public?static?HashMap?generateColumnTitleMap(String?columnTitle){
    ????HashMap?map    =new?HashMap();
    ????String[]?level1    =StringUtils.split(columnTitle,?"&");
    ????    if(ArrayUtils.isEmpty(level1))?return?null;
    ????    for(int?i=0;i<level1.length;i++){
    ??????String[]?level2    =StringUtils.split(level1[i],?"#");
    ??????    if(ArrayUtils.isEmpty(level2)||level2.length!=2)?return?null;
    ??????map.put(level2[    0].toLowerCase(),?level2[1]);
    ????}
    ????    return?map;
    ??}

    ??    public?void?generateExcel(HttpServletRequest?request,?HttpServletResponse?response)
    ????    throws?ServletException,?IOException{
    ????    //todo?只允許本機調用。
    ????request.getRemoteHost();
    ????request.getServerName();
    ????response.setHeader(    "Content-Disposition",?"attachment;");
    ????response.setContentType(    "application/x-msdownload");
    ????String?sql    =(String)request.getSession().getAttribute("ExcelGenerator_sql");
    ????String?columnTitle    =(String)request.getSession().getAttribute("ExcelGenerator_columntitle");
    ????Map?columnTitleMap    =null;

    ????    if(StringUtils.isBlank(sql))?throw?new?ServletException("sql?字符串為空！");
    ????    if(!StringUtils.isBlank(columnTitle)){
    ??????columnTitleMap    =generateColumnTitleMap(columnTitle);
    ??????    if(null==columnTitleMap){
    ????????logger.error(    "generateColumnTitleMap?error?!columnTitle="+columnTitle);
    ??????}
    ????}

    ????    final?WritableWorkbook?writableWorkbook=Workbook.createWorkbook(response.getOutputStream(),?workbookSettings);
    ????    if(jdbcTemplate==null)?throw?new?ServletException("ExcelGenerator?沒有初始化成功！jdbcTemplate==null。");
    ????    final?Map?columnTitleMap1=columnTitleMap;
    ????jdbcTemplate.query(sql,?    new?ResultSetExtractor(){
    ??????    public?Object?extractData(ResultSet?rs)?throws?SQLException,?DataAccessException{
    ????????    try{
    ??????????    int?counter=0;
    ??????????    int?page=1;
    ??????????WritableSheet?writableSheet    =writableWorkbook.createSheet("第"+page+"頁",?0);
    ??????????ResultSetMetaData?rsmd    =rs.getMetaData();
    ??????????    int?columnCount=rsmd.getColumnCount();
    ??????????String[]?columnNames    =new?String[columnCount];
    ??????????    for(int?i=1;i<=columnCount;i++){
    ????????????columnNames[i    -1]=rsmd.getColumnName(i).toLowerCase();
    ????????????    if(columnTitleMap1==null){
    ??????????????writableSheet.addCell(    new?Label(i-1,?counter,?columnNames[i-1]));
    ????????????}    ?else{
    ??????????????writableSheet.addCell(    new?Label(i-1,?counter,?(String)columnTitleMap1.get(columnNames[i-1])));
    ????????????}
    ??????????}
    ??????????counter    =1;
    ??????????Object?oValue    =null;
    ??????????String?value    =null;
    ??????????    while(rs.next()){
    ????????????    //row
    ????????????for(int?i=1;i<=columnCount;i++){
    ??????????????oValue    =JdbcUtils.getResultSetValue(rs,?i);
    ??????????????    if(oValue==null){
    ????????????????value    ="";
    ??????????????}    ?else{
    ????????????????value    =oValue.toString();
    ??????????????}
    ??????????????writableSheet.addCell(    new?Label(i-1,?counter,?value));
    ????????????}
    ????????????    if(counter++>pagesize){
    ??????????????counter    =0;
    ??????????????writableSheet    =writableWorkbook.createSheet("第"+(++page)+"頁",?0);
    ????????????}
    ??????????}
    ????????}    ?catch(WriteException?e){
    ??????????e.printStackTrace();
    ????????}
    ????????    return?null;
    ??????}
    ????}
    ????);
    ????writableWorkbook.write();
    ????    try{
    ??????writableWorkbook.close();
    ????}    ?catch(WriteException?e){
    ??????logger.error(    "writableWorkbook.close()?error?!"+e,?e.getCause());
    ??????e.printStackTrace();
    ????}
    ??}
    }

    web.xml
    ??<servlet>
    ????    <servlet-name>ExcelGeneratorSPAN style="COLOR: #800000">servlet-name>
    ????    <servlet-class>com.fsti.xmnms.web.servlet.ExcelGeneratorSPAN style="COLOR: #800000">servlet-class>
    ????    <init-param>
    ?????    <param-name>pagesizeSPAN style="COLOR: #800000">param-name>
    ?????    <param-value>5000SPAN style="COLOR: #800000">param-value>
    ????    SPAN style="COLOR: #800000">init-param>
    ????    <load-on-startup>3SPAN style="COLOR: #800000">load-on-startup>
    ??    SPAN style="COLOR: #800000">servlet>
    ??    <servlet-mapping>
    ????    <servlet-name>ExcelGeneratorSPAN style="COLOR: #800000">servlet-name>
    ????    <url-pattern>*.xlsSPAN style="COLOR: #800000">url-pattern>
    ??    SPAN style="COLOR: #800000">servlet-mapping>


    測試頁面：

    @ page contentType="text/html;charset=GB2312" language="java" %>]]>
    <html>
    <head>
          <title>ExcelGenerator testSPAN style="COLOR: #000000">title>
    SPAN style="COLOR: #000000">head>
    <body>

          String sql="select id,source_id,user_label from alarm_state";
          String columntitle="id#ID&source_id#源設備&user_label#用戶標簽";
      session    .setAttribute("ExcelGenerator_sql",sql);
      session    .setAttribute("ExcelGenerator_columntitle",columntitle);
      response    .sendRedirect("asd.xls");
    %>]]>
    SPAN style="COLOR: #000000">body>
    SPAN style="COLOR: #000000">html>

    ]]>
    
		
    
			posted on 2005-11-06 16:04 一餐三碗 閱讀(6712) 評論(4)  編輯  收藏  所屬分類: 教程 
		
    
		
    
	
    
	
	


	


    
	    
    


    

    評論
    

    
	
	
		
    
			
    
				# re: 采用jxl實現數據庫結果集導出到excel文件
					
						2005-11-12 00:00
					
				aoenu  回復  更多評論
				  
			
    			
				數據量太大的時候不會內存益出吧				
		
    
		
    
		
		
    
			
    
				# re: 采用jxl實現數據庫結果集導出到excel文件
					
						2005-11-12 22:06
					
				一餐三碗  回復  更多評論
				  
			
    			
				代碼里面沒有考慮這個問題。不過幾萬條數據還是沒問題的				
		
    
		
    
		
		
    
			
    
				# 郁悶
					
						2007-05-30 10:01
					
				郁悶  回復  更多評論
				  
			
    			
				我的excel是這樣的,第一個單元格是8，第二個單元格是5，第三個單元格是第二個單元格/第一個單元格,值應該是0.625,然后我又設置第三個單元格的格式，以百分數顯示，小數位數為０，這時第三個單元格的內容為63%,

    我用

    Cell c３ = rs.getCell(２, ２);

    String strc３ = c３.getContents();

    我這樣想得到第三個單元格中的63%,可是我怎么得到的是62%，請問我在變動excel的情況下，怎么通過java的jxl包得到63%呀

    謝謝了

    有知道的請往我的郵箱發信息,或ＱＱ聯系

    郵箱：jlzhjx@163.com

    QQ:524240736

    不勝感激呀

    				
		
    
		
    
		
		
    
			
    
				# re: 采用jxl實現數據庫結果集導出到excel文件
					
						2013-05-06 17:17
					
				撒地方  回復  更多評論
				  
			
    			
				撒旦法				
		
    
		
    
		

    





    








    

				
	
    
	
		
	
    
	
    
	

	
    


    







    
        
	



主站蜘蛛池模板：
亚洲国产另类久久久精品小说|
一区二区三区精品高清视频免费在线播放|
99re6在线精品视频免费播放
|
亚洲午夜电影在线观看高清|
午夜免费福利在线观看|
日韩av无码免费播放|
亚洲av无一区二区三区|
久久水蜜桃亚洲av无码精品麻豆
|
亚洲男人的天堂久久精品|
亚洲伊人色欲综合网|
日韩亚洲精品福利|
AA免费观看的1000部电影|
国产成人免费AV在线播放
|
亚洲AV无码码潮喷在线观看|
免费无遮挡无码视频网站|
www视频在线观看免费|
久久久久久精品免费看SSS|
久久er国产精品免费观看2|
你好老叔电影观看免费|
人妻仑乱A级毛片免费看|
一级做α爱过程免费视频|
国产成人综合久久精品亚洲|
亚洲精品无码久久久久A片苍井空|
91亚洲一区二区在线观看不卡|
亚洲av日韩av无码黑人|
亚洲天堂中文字幕|
亚洲一区二区三区在线观看网站|
亚洲免费福利在线视频|
老司机午夜性生免费福利|
国产精品无码永久免费888|
国产精品视频白浆免费视频|
a毛看片免费观看视频|
手机看黄av免费网址|
国产美女无遮挡免费网站|
亚洲国产精品SSS在线观看AV|
亚洲最大的成网4438|
亚洲国产精品日韩av不卡在线|
青青草97国产精品免费观看|
免费久久人人爽人人爽av|
国产精彩免费视频|
在线亚洲精品自拍|