<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?操作模版類(lèi)
    ??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#用戶(hù)標(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 一餐三碗 閱讀(6712) 評(píng)論(4)  編輯  收藏  所屬分類(lèi): 教程 
		
    
		
    
	
    
	
	


	


    
	    
    


    

    評(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)論
				  
			
    			
				撒旦法				
		
    
		
    
		

    





    








    

				
	
    
	
		
	
    
	
    
	

	
    


    







    
        
	



主站蜘蛛池模板：
中文字幕成人免费高清在线|
国产福利免费观看|
91av免费在线视频|
精精国产www视频在线观看免费|
中文字幕乱理片免费完整的|
午夜精品射精入后重之免费观看|
国产一卡2卡3卡4卡2021免费观看|
啦啦啦在线免费视频|
0588影视手机免费看片|
免费无码A片一区二三区
|
今天免费中文字幕视频|
最近免费中文字幕大全免费|
成年女人毛片免费视频|
亚洲日韩国产成网在线观看|
亚洲天堂中文字幕|
亚洲国产成人久久综合|
中文字幕免费在线看|
国产黄色免费网站|
内射无码专区久久亚洲|
亚洲成年轻人电影网站www
|
精品特级一级毛片免费观看|
亚洲中文无码永久免|
国产精品无码永久免费888|
国产成人精品免费视频大|
四虎永久在线精品免费影视|
久久精品国产亚洲|
亚洲AV永久无码精品水牛影视|
亚洲一级毛片视频|
国产午夜亚洲精品|
香蕉免费一级视频在线观看|
免费看韩国黄a片在线观看|
亚洲精品国产成人片|
无码欧精品亚洲日韩一区|
亚洲色中文字幕在线播放|
成全视频高清免费观看电视剧|
国产在线观看片a免费观看|
亚洲成a人无码av波多野按摩|
免费看美女午夜大片|
久久久久免费看成人影片|
国产精品成人免费综合|
久久久久亚洲AV无码永不|