java从数据库导入excel



java把数据从数据库导入到excel

 

 

package com.madhouse.sys.util;

 

import java.io.File;

import java.io.IOException;

 

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

 

import jxl.Workbook;

import jxl.write.Label;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

 

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

 

import com.mysql.jdbc.Connection;

import com.mysql.jdbc.Statement;

 

/**

  * <li>把数据导入到Excel公用类</li>

  * </br> This is about <code>ExcelUtil</code>

  *

  * @author hjy273

  * @version 1.0

  * @date Sep 6, 2008 9:52:52 PM

  */

public class ExcelUtil {

 

     private static Log log = LogFactory.getLog(ExcelUtil. class );

 

     public ExcelUtil() {

 

     }

 

     public boolean DB2Excel(ResultSet rs) {

         boolean flag = false ;

         WritableWorkbook workbook = null ;

         WritableSheet sheet = null ;

         Label label = null ;

 

         // 创建Excel表

         try {

              workbook = Workbook.createWorkbook( new File( "e:/_report/output.csv" ));

             //workbook = Workbook.createWorkbook(os);

             

              // 创建Excel表中的sheet

             sheet = workbook.createSheet( "First Sheet" , 0 );

 

             // 向Excel中添加数据

             ResultSetMetaData rsmd = rs.getMetaData();

             int columnCount = rsmd.getColumnCount();

             String colName = null ;

             int row = 0 ;

             // 添加标题

             for ( int i = 0 ; i < columnCount; i++) {

                 colName = rsmd.getColumnName(i + 1 );

                 label = new Label(i, row, colName);

                 // log.debug("标题:"+i+"---"+row +"---"+ colName);

                 sheet.addCell(label);

             }

             row++;

             log.debug( "写入标题成功" );

             while (rs.next()) {

                 for ( int i = 0 ; i < columnCount; i++) {

                     label = new Label(i, row, rs.getString(i + 1 ));

                     log.debug( "行:" +i+ "---" +row + "---" + rs.getString(i+ 1 ));

                     sheet.addCell(label);

                 }

                 row++;

             }

             log.debug( "写入内容成功" );

 

             // 关闭文件

             workbook.write();

             workbook.close();

             log.info( "数据成功写入Excel" );

             flag = true ;

         } catch (SQLException e) {

             log.debug(e.getMessage());

         } catch (RowsExceededException e) {

             log.debug(e.getMessage());

         } catch (WriteException e) {

             log.debug(e.getMessage());

         } catch (IOException e) {

             log.debug(e.getMessage());

         } finally {

             try {

                 workbook.close();

             } catch (Exception e) {

             }

         }

         return flag;

     }

 

     /**

      * 测试方法

      *

      * @param args

      */

     public static void main(String[] args) {

         // write your code

         try {

 

             Class.forName( "org.gjt.mm.mysql.Driver" ).newInstance();

             Connection conn = (Connection) DriverManager

                     .getConnection(

                             "jdbc:mysql://localhost:3306/firewall?useUnicode=true&characterEncoding=utf8" ,

                             "root" , "root" );

             Statement st = (Statement) conn.createStatement();

             ResultSet rs = st

                     .executeQuery( "select * from firewall.tb_operator" );

              if (! new ExcelUtil().DB2Excel(rs)){

                  log.info( "数据写入失败" );

              }

             rs.close();

             st.close();

             conn.close();

 

         } catch (Exception e) {

             e.printStackTrace();

         }

 

     }

 

}