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

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

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

    
    

    




    







												


        

    
	
    
	
    
	
    
			
					
	
    
		
    
			采用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?沒有初始化成功！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>


    測(cè)試頁面：

    @ 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 一餐三碗 閱讀(6713) 評(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)論
				  
			
    			
				代碼里面沒有考慮這個(gè)問題。不過幾萬條數(shù)據(jù)還是沒問題的				
		
    
		
    
		
		
    
			
    
				# 郁悶
					
						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)問我在變動(dòng)excel的情況下，怎么通過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)論
				  
			
    			
				撒旦法				
		
    
		
    
		

    





    








    

				
	
    
	
		
	
    
	
    
	

	
    


    







    
        
	



主站蜘蛛池模板：
免费又黄又爽又猛大片午夜|
中文文字幕文字幕亚洲色|
亚洲国产精品久久久久久|
91大神亚洲影视在线|
亚洲人成网站日本片|
亚洲国产精品ⅴa在线观看|
免费无遮挡无码视频在线观看|
丝瓜app免费下载网址进入ios|
无码国产精品一区二区免费模式
|
亚洲中文字幕乱码AV波多JI|
色欲色欲天天天www亚洲伊|
高清免费久久午夜精品|
色欲A∨无码蜜臀AV免费播|
日韩免费精品视频|
国产又粗又长又硬免费视频|
中文字幕亚洲一区二区三区|
久久精品国产亚洲av影院|
亚洲日本中文字幕天天更新|
eeuss影院免费直达入口|
最近2019免费中文字幕视频三
|
久久国产乱子伦精品免费一|
成人毛片18女人毛片免费视频未|
亚洲成a人无码av波多野按摩
|
亚洲精品自在在线观看|
亚洲国产精品久久网午夜
|
一级片在线免费看|
精品熟女少妇a∨免费久久|
精品国产免费观看|
国产偷v国产偷v亚洲高清|
在线观看日本亚洲一区|
国产精品黄页免费高清在线观看|
999久久久免费精品国产|
亚洲中文字幕无码爆乳av中文|
亚洲黄色在线观看|
鲁啊鲁在线视频免费播放|
最近2019免费中文字幕视频三|
亚洲国产免费综合|
亚洲高清中文字幕|
一级人做人爰a全过程免费视频|
99国产精品永久免费视频|
亚洲精品午夜无码专区|