java-oracle写数据到excel的例子

来源：http://lzfhope.blog.163.com/blog/static/636399220096643726658/

 

package writetoexcel;

import oracle.jdbc.*;
//import oracle.jdbc.pool.*;
//import oracle.sql.*;
//import java.io.*;
//import java.sql.*;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.*;

/**
 * 本类用于往EXCEL文件中写数据,支持两种写方式，用单个表格，或者是用查询语句
 * 但暂时都不支持复杂类型，例如LOB
 * 适合被其它类调用，如果要应用到java中，完全可以添加一个类的静态函数，那是题外话.
 * @author lzf
 */
public class writeToExcel {


    /**工作表名称,如果非空，那么就使用这个，否则
     * 在使用SQL导出数据的时候，直接使用"导出数据"
     * @param sheetName 工作表名称
     */
    public void setSheetName(java.lang.String sheetName) {
        this._sheetName = sheetName;
    }

    /**设置字体颜色值，但这个值只会影响到正文，不会影响表格的标题
     * 值的范围为1-32767
     * @param fontColor the _fontColor to set
     */
    public void setFontColor(int fontColor) {
        this._fontColor = fontColor;
    }

    /**
     * 自定义的一个类
     */
    private class  colClass{
        protected String  Name;
        protected String  DataType;
        protected int     Length;
        public colClass(String _name,String _datatype,int _length){
            this.Name=_name;
            this.DataType=_datatype;
            this.Length=_length;
        }
    }

    private java.lang.String  _outDir;
    /**
     * 由于采取的是thin连接，所以，必须是"jdbc:oracle:thin:@prodHost:1521:ORCL"格式的连接串
     */
    private java.lang.String    _oracleConnstr;
    private java.lang.String    _dbUser;
    private java.lang.String    _dbPwd;
    private java.util.ArrayList colNames;
    private java.lang.Integer   colAmount;  
    private java.lang.String    _sheetName="";
    private int  _fontColor=HSSFFont.COLOR_NORMAL;


    /**设置文件全路径，包括文件名称,请尽量使用xls后缀.
     * @param outDir the _outDir to set
     */
    public void setOutDir(java.lang.String outDir) {
        this._outDir = outDir;
    }


    /**只支持thin连接,形如jdbc:oracle:thin:@prodHost:1521:ORCL
     * @param oracleConnstr the _oracleConnstr to set
     */
    public void setOracleConnstr(java.lang.String oracleConnstr) {
        this._oracleConnstr = oracleConnstr;
    }


    /**设置数据库连接用户
     * @param dbUser the _dbUser to set
     */
    public void setDbUser(java.lang.String dbUser) {
        this._dbUser = dbUser;
    }

    /**设置数据库连接密码
     * @param dbPwd the _dbPwd to set
     */
    public void setDbPwd(java.lang.String dbPwd) {
        this._dbPwd = dbPwd;
    }

    /**
     * 返回最终查询数据的SQL，如果isSQL=true那么直接返回SQL，否则采取别的方式
     * @param sqlValue  表格名称或者是一条SQL语句
     * @param isSQL     true表示是SQL，否则就是表名称
     * @return          返回最终查询语句
     */
    private String getQuerySql(String sqlValue,Boolean isSQL){
        if (isSQL){
            return sqlValue;
        }
        else{
            String sql="select ";
            //构建sql
            for(int i=0;i<this.colAmount;i++){
              if (i<this.colAmount-1){
                 sql=sql+((colClass)colNames.get(i)).Name+",";
              }
              else{
                 sql=sql+((colClass)colNames.get(i)).Name;
              }
            }
           sql=sql+" from "+sqlValue;
           return sql;
        }
    }
    /**
     * 使用一条SQL来读取列信息
     * 通过执行一个简单的查询来获得列名称.
     * 最终通过java的ResultSetMetaData类型来获得
     * @param conn
     * @param sql
     * @return 成功返回true，否则FALSE
     */
    private  java.lang.Boolean setColNamesWithSql(oracle.jdbc.OracleConnection conn,String sql){
        try{
            //暂时排除了几个难于对付的类型.
            String querySql="select * from  ("+sql+") where rownum=0";
            oracle.jdbc.OraclePreparedStatement pst=(OraclePreparedStatement)conn.prepareStatement(querySql);
            java.sql.ResultSet rst=pst.executeQuery();
           
            java.sql.ResultSetMetaData md=rst.getMetaData();
            this.colAmount=0;
            this.colNames=new ArrayList();
            for(int i=0;i<md.getColumnCount();i++){
                //填充数组
                String Name=md.getColumnLabel(i+1);
                String DataType=md.getColumnTypeName(i+1);
                //System.out.println(DataType);
                int    Length=md.getPrecision(i+1);
                if (!(DataType.equals("BLOB") || DataType.equals("CLOB")
                         || DataType.equals("LONG") || DataType.equals("RAW") )){
                    this.colAmount++;
                    colClass colItem=new colClass(Name,DataType,Length);
                    this.colNames.add(colItem);
                }
            }
            rst.close();
            pst.close();
            return true;
        }
        catch(Exception e){
            System.out.println(e.getMessage());
            return false;
        }
    }
    private  java.lang.Boolean setColNames(oracle.jdbc.OracleConnection conn,String tname){
        try{
            //暂时排除了几个难于对付的类型.
            String sql="Select COLUMN_NAME ,DATA_TYPE,data_length From User_Tab_Cols  Where table_name='"+tname+
                         "' and data_type not in  ('CLOB','BLOB','LONG') " +
                         " Order By INTERNAL_COLUMN_ID ";
            oracle.jdbc.OraclePreparedStatement pst=(OraclePreparedStatement)conn.prepareStatement(sql);
            java.sql.ResultSet rst=pst.executeQuery();
            this.colAmount=0;
            this.colNames=new ArrayList();
            while (rst.next()){
                //填充数组
                this.colAmount++;
                colClass colItem=new colClass(rst.getString(1)  ,rst.getString(2),rst.getInt(3));
                this.colNames.add(colItem);
            }
            rst.close();
            pst.close();
            return true;
        }
        catch(Exception e){
            System.out.println(e.getMessage());
            return false;
        }
    }

    /**
     * 设置excel的头--如果需要的话.
     * @param sheet  工作表
     * @param titleFont  字体
     */
    private void setFileHeader(org.apache.poi.hssf.usermodel.HSSFSheet  sheet,
            org.apache.poi.hssf.usermodel.HSSFFont titleFont){
        org.apache.poi.hssf.usermodel.HSSFRow row=sheet.createRow(0);
        //int cellType;
        colClass colItem;
        for(int i=0;i<this.colAmount;i++){
            colItem=(colClass)colNames.get(i);
            org.apache.poi.hssf.usermodel.HSSFCell cell=row.createCell(i, HSSFCell.CELL_TYPE_STRING);
            org.apache.poi.hssf.usermodel.HSSFRichTextString  richText= new org.apache.poi.hssf.usermodel.HSSFRichTextString(colItem.Name);
            richText.applyFont(titleFont);
            cell.setCellValue(richText);
        }
        return;
    }          
    /**
     * 功能：导出表格内容到excel文件。
     * 使用这个方式，需要一表格名称，同时文件路径不包含文件名称.
     * @param tname  oracle 数据库表格名称或者一条sql语句
     * @param isSql  是否为SQL，1表示是，0表示不是-即TABLE
     * @param displayHeader 是否显式表头，如果1表示显式，否则不显式.
     */
    public long outtoExcelFile(String tname,int isSql,int displayHeader){
        String tableName=tname;
        int startRow;
        try{
          oracle.jdbc.pool.OracleDataSource ods=new oracle.jdbc.pool.OracleDataSource();
          ods.setURL(this._oracleConnstr);
          ods.setUser(this._dbUser);
          ods.setPassword(this._dbPwd);
          oracle.jdbc.OracleConnection conn=(OracleConnection)ods.getConnection();
          //创建新的文件
          org.apache.poi.hssf.usermodel.HSSFWorkbook eBook=new org.apache.poi.hssf.usermodel.HSSFWorkbook();
          org.apache.poi.hssf.usermodel.HSSFFont titleFont= eBook.createFont();
          titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
          org.apache.poi.hssf.usermodel.HSSFSheet eSheet=null;
          //SHEET名称
          String sheetName=this.getSheetName(tableName, isSql);
          eSheet=eBook.createSheet(sheetName);
          //获取列名称及其类型
          if (!this.setcolNames(conn, isSql, tableName)){
              return -1;
          }
          //设置文件头
          if (displayHeader==1){
              this.setFileHeader(eSheet,titleFont);
              startRow=1;
          }
          else{
              startRow=0;
          }
          //读取数据
          org.apache.poi.hssf.usermodel.HSSFCellStyle cellStyle=eBook.createCellStyle();
          org.apache.poi.hssf.usermodel.HSSFFont  cellFont= eBook.createFont();

          String sql=this.getQuerySql(tableName, (isSql==1));
          oracle.jdbc.OraclePreparedStatement pst=(OraclePreparedStatement)conn.prepareStatement(sql);
          java.sql.ResultSet rst=pst.executeQuery();                   
          int cellType;
          while (rst.next()){
              //写一行内容
              org.apache.poi.hssf.usermodel.HSSFRow row=eSheet.createRow(startRow);
              for(int i=0;i<this.colAmount;i++){
                  org.apache.poi.hssf.usermodel.HSSFCell cell=row.createCell(i);
                  //设置样式,设置类型
                  colClass colItem=(colClass)colNames.get(i);
                  if (colItem.DataType.equals("NUMBER")){
                     cellType=HSSFCell.CELL_TYPE_NUMERIC;
                  }
                  else {
                      cellType=HSSFCell.CELL_TYPE_STRING;     
                  }
                  cell.setCellType(cellType);                 
                  //设置字体及其颜色                
                  cellFont.setColor((short)this._fontColor);
                  cellStyle.setFont(cellFont);
                  cell.setCellStyle(cellStyle);
                  //设置内容
                  if (cellType==HSSFCell.CELL_TYPE_NUMERIC){
                      cell.setCellValue(rst.getDouble(i+1));
                  }
                  else{
                      org.apache.poi.hssf.usermodel.HSSFRichTextString richText;             
                      richText=new org.apache.poi.hssf.usermodel.HSSFRichTextString(rst.getString(i+1));                                    
                      cell.setCellValue(richText);
                  }
              }
              startRow++;  //提取下一行
          }
          rst.close();
          pst.close();
          conn.close();
          //写文件到磁盘上
          String filePath=this._outDir;
          //如果文件存在，那么删除文件。
          java.io.File file=new java.io.File(filePath);
          if (file.exists()){
              file.delete();
          }
         
          java.io.FileOutputStream fo=new java.io.FileOutputStream(filePath);
          eBook.write(fo);
          fo.close();
          if (displayHeader==1){
              return startRow-1;
          }
          else{
              return startRow;
          }
      }
      catch(Exception e){
         System.out.println(e.getMessage());
         return -1;
      }
    }

    /**
     * 返回sheet名称
     * @param tname  表格或者SQL
     * @param isSql  是否为SQL，意思同outtoExcelFile
     * @return  返回Sheet名称
     */
    private String getSheetName(String tname,int isSql){
        String sheetName;
          if (this._sheetName.equals("")){
              if (isSql==0){
                  sheetName=tname;
              }
              else{
                  sheetName="数据导出";
              }
          }
          else{
              sheetName=this._sheetName;
          }
        return sheetName;
    }

    /**
     * 设置列名称数组，根据不同的情况来设置
     * @param conn      数据库连接对象
     * @param isSql     是否为SQL，含义同outtoExcelFile
     * @param tableName 表格名称或者SQL语句
     * @return  成功设置返回true，否则返回false
     */
    private Boolean  setcolNames(oracle.jdbc.OracleConnection conn,
            int isSql,String tableName){
        try{
            if (isSql==0){ //表格方式
                if (!this.setColNames(conn, tableName.toUpperCase())){
                    conn.close();
                }
            }
            else{ //SQL 方式
                if (!this.setColNamesWithSql(conn, tableName)){
                    conn.close();
                }
            }
            return true;
        }
        catch(Exception e){
            return false;
        }
    }

    public void  setParameters(String connstr,String dbuser,String dbpwd,
            String outDir,String sheetName,int fontColor){
        this.setOracleConnstr(connstr);
        this.setDbPwd(dbpwd);
        this.setDbUser(dbuser);
        this.setFontColor(fontColor);
        this.setOutDir(outDir);
        if (sheetName.equals("")){
            this.setSheetName("数据导出");
        }
        else{
            this.setSheetName(sheetName);
        }

    }
}

---

http://fty001.javaeye.com/blog/31882  的源码

---

需要的包为oracle 驱动包,apache下的poi包
import java.io.*;
import java.sql.*;
import org.apache.poi.hssf.usermodel.*;

public class AppMain {
    public AppMain() {
    }

    public static void main(String[] args) {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet =null;

           /**连接ORACLE的数据库,关键在于两个系统级的VIEW:
            all_tab_columns 记录着ORACLE所有的字段定义信息.
            DBA_COL_COMMENTS 记录着字段的注释信息,*/


            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection(
                    "jdbc:oracle:thin:@192.168.1.111:1521:ncdb", "system",   "fty001");
            Statement stmt = con.createStatement();
            StringBuffer strbuf = new StringBuffer();
            strbuf.append("SELECT A.*,B.comments");
            strbuf.append(" FROM all_tab_columns A,DBA_COL_COMMENTS B");
            strbuf.append(" WHERE A.owner=B.owner");
            strbuf.append(" AND A.table_name=B.table_name");
            strbuf.append(" AND A.COLUMN_NAME=B.COLUMN_NAME");
            //owner是建立表的用户名
            strbuf.append(" AND A.owner='SCOTT'");
            strbuf.append(" ORDER BY A.TABLE_NAME");
            ResultSet rs = stmt.executeQuery(strbuf.toString());

            String tb = "";
            int k = 0;
            while (rs.next()) {
                  //对每个表生成一个新的sheet,并以表名命名
                if (!tb.equals(rs.getString("TABLE_NAME"))) {
                    sheet = wb.createSheet(rs.getString("TABLE_NAME"));
                    //设置表头的说明
                    HSSFRow row = sheet.createRow(0);
                    setCellGBKValue(row.createCell((short) 0),"字段名");
                    setCellGBKValue(row.createCell((short) 1),"字段类型");
                    setCellGBKValue(row.createCell((short) 2),"字段长度");
                    setCellGBKValue(row.createCell((short) 3),"数字长度");
                    setCellGBKValue(row.createCell((short) 4),"小数位数");
                    setCellGBKValue(row.createCell((short) 5),"能否为NULL");
                    setCellGBKValue(row.createCell((short) 6),"字段说明");
                    k = 1;
                 } else {
                    k++;
                }
                tb = rs.getString("TABLE_NAME");
                HSSFRow row = sheet.createRow(k);
                row.createCell((short) 0).setCellValue(rs.getString(
                        "COLUMN_NAME"));
                row.createCell((short) 1).setCellValue(rs.getString("DATA_TYPE"));
                row.createCell((short) 2).setCellValue(rs.getString(
                        "DATA_LENGTH"));
                row.createCell((short) 3).setCellValue(rs.getString(
                        "DATA_PRECISION"));
                row.createCell((short) 4).setCellValue(rs.getString(
                        "DATA_SCALE"));
                row.createCell((short) 5).setCellValue(rs.getString("NULLABLE"));
                 setCellGBKValue(row.createCell((short) 6),rs.getString("COMMENTS"));

            }

             //把生成的EXCEL文件输出保存
            FileOutputStream fileOut = new FileOutputStream("E://数据字典.xls");
            wb.write(fileOut);
            fileOut.close();

            rs.close();
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

             /**

                这个函数是为了设置一个CELL为中文字符串

              */

   private static void setCellGBKValue(HSSFCell cell, String value) {
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
         //设置CELL的编码信息
        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(value);
    }
}

---

     上文其实已经提供了最有价值的内容，但更多的细节需要自己来完成.
     需要提供的是 1）写明细 ；2）导入到java中作为一个固定的函数.