采用jxl實(shí)現(xiàn)數(shù)據(jù)庫(kù)結(jié)果集導(dǎo)出到excel文件
		
          
		
		
          
				采用jxl實(shí)現(xiàn)數(shù)據(jù)庫(kù)結(jié)果集導(dǎo)出到excel文件
		
          
		
          
		
          
				關(guān)鍵字：
		
          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實(shí)現(xiàn)數(shù)據(jù)庫(kù)結(jié)果集導(dǎo)出到excel文件。
          ?*?
          Copyright:?Copyright.com?(c)?2003
          ?*?
          Company:
          ?*?History:
          ?*?create
          ?*
          ?*?          @author?youlq
          ?*?          @version?1.0
          ?          */
          public?class?ExcelGenerator?extends?HttpServlet{
          ??          //設(shè)定每個(gè)Sheet的行數(shù)
          ??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?只允許本機(jī)調(diào)用。
          ????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?沒(méi)有初始化成功！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+"頁(yè)",?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)+"頁(yè)",?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>


          測(cè)試頁(yè)面：

          @ 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#源設(shè)備&user_label#用戶標(biāo)簽";
            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 一餐三碗 閱讀(6714) 評(píng)論(4)  編輯  收藏  所屬分類: 教程 
		
          
		
          
	
          
	
	


	


          
	    
    


          

          評(píng)論
          

          
	
	
		
          
			
          
				# re: 采用jxl實(shí)現(xiàn)數(shù)據(jù)庫(kù)結(jié)果集導(dǎo)出到excel文件
					
						2005-11-12 00:00
					
				aoenu  回復(fù)  更多評(píng)論
				  
			
          			
				數(shù)據(jù)量太大的時(shí)候不會(huì)內(nèi)存益出吧				
		
          
		
          
		
		
          
			
          
				# re: 采用jxl實(shí)現(xiàn)數(shù)據(jù)庫(kù)結(jié)果集導(dǎo)出到excel文件
					
						2005-11-12 22:06
					
				一餐三碗  回復(fù)  更多評(píng)論
				  
			
          			
				代碼里面沒(méi)有考慮這個(gè)問(wèn)題。不過(guò)幾萬(wàn)條數(shù)據(jù)還是沒(méi)問(wèn)題的				
		
          
		
          
		
		
          
			
          
				# 郁悶
					
						2007-05-30 10:01
					
				郁悶  回復(fù)  更多評(píng)論
				  
			
          			
				我的excel是這樣的,第一個(gè)單元格是8，第二個(gè)單元格是5，第三個(gè)單元格是第二個(gè)單元格/第一個(gè)單元格,值應(yīng)該是0.625,然后我又設(shè)置第三個(gè)單元格的格式，以百分?jǐn)?shù)顯示，小數(shù)位數(shù)為０，這時(shí)第三個(gè)單元格的內(nèi)容為63%,

          我用

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

          String strc３ = c３.getContents();

          我這樣想得到第三個(gè)單元格中的63%,可是我怎么得到的是62%，請(qǐng)問(wèn)我在變動(dòng)excel的情況下，怎么通過(guò)java的jxl包得到63%呀

          謝謝了

          有知道的請(qǐng)往我的郵箱發(fā)信息,或ＱＱ聯(lián)系

          郵箱：jlzhjx@163.com

          QQ:524240736

          不勝感激呀

          				
		
          
		
          
		
		
          
			
          
				# re: 采用jxl實(shí)現(xiàn)數(shù)據(jù)庫(kù)結(jié)果集導(dǎo)出到excel文件
					
						2013-05-06 17:17
					
				撒地方  回復(fù)  更多評(píng)論
				  
			
          			
				撒旦法				
		
          
		
          
		

          





          








          

				
	
          
	
		
	
          
	
          
	

	
          


    







          
        
	




主站蜘蛛池模板：
商水县|
永昌县|
囊谦县|
韶关市|
浠水县|
黎川县|
息烽县|
南通市|
唐河县|
保靖县|
邹城市|
玉林市|
嘉善县|
池州市|
漠河县|
彩票|
蓬溪县|
夏河县|
黔江区|
平果县|
吴桥县|
临沧市|
阜南县|
志丹县|
通江县|
克拉玛依市|
双辽市|
景泰县|
龙岩市|
永安市|
大方县|
保山市|
新和县|
景泰县|
巩留县|
简阳市|
泗水县|
枣强县|
开远市|
文成县|
磐石市|