用Java操作数据库和excel（一）

最近在写一些关于数据库的批量操作，用java写的，需要下载poi包具体代码示例如下：

/**
 * 这个主程序OK
 * 从数据库写入到excel中
 * 
 */
package packagename;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class Testwriteintoexcel {

 //import java.io.FileInputStream;  
  private static String dbURL = "jdbc:mysql://localhost:3306/library?" +"auotReconnnect=true&useUnicode=true&characterEncoding=utf8";//数据库连接串
  private static String userName = "root";//数据库用户名
  private static String password = "root";//数据库密码
  PreparedStatement ps = null; 
  public static String fileToBeWrite = "E:\\MySQL\\MySQL中的数据等待导出的文件地址
  
  
   public static void main(String args[]) {
   /*there record some variables*/
   int ln;//记录列数
   
   
   PreparedStatement ps = null;    // 创建对Excel工作簿文件的引用;
   HSSFWorkbook workbook;
   
   String ISBN;
   String Bname;
   String Author;
   String Publisher;
   int Edition;
   Date Publishtime;
   int Btype;
   float Price;
   short Page;
   String Introduction;
   String Keyword;
   
   /*those code used to connect to database library ，this is a subfunction*/
    Connection conn = getConnection();
   /*those code used to connect to database library*/
   
    try{
     workbook  = new HSSFWorkbook();
     conn.setAutoCommit(false);//let Commit by hand   
     try{
      FileOutputStream fileout=new FileOutputStream(fileToBeWrite);  //定义一个输出流
      HSSFSheet hssfsheet = workbook.createSheet("frist"); //创建一个first表，用hssfsheet记录一般默认的是Sheet1，
    /*get the struct of the table*/
     String sql="select * from book_information";
     ps = conn.prepareStatement(sql);
     ResultSetMetaData resMeta = ps.getMetaData();
     ln=resMeta.getColumnCount();//得到该结果的列数
    
     String ls[]=new String[ln];
     
    
     for(int i=1;i<=ln;i++){
      ls[i-1]= resMeta.getColumnName(i);//得到每列的属性名
      }
     System.out.println(ls[0]+"\t"+"\t"+ls[1]+"\t"+"\t"+"\t"+"\t"+ls[2]+"\t"+ls[3]+"\t"+ls[4]+"\t"+ls[5]+"\t"+ls[6]+"\t"+ls[7]+"\t"+ls[8]+"\t"+ls[9]+"\t"+ls[10]);//显示一下第一行的属性
      ResultSet Res = ps.executeQuery();
      //Res.findColumn(columnLabel);//找到某一列的列号
     int count=0;
     HSSFRow hssfrowt = hssfsheet.createRow(count);//在first表中创建第0行，
      hssfrowt.createCell(0).setCellValue(ls[0]);
               hssfrowt.createCell(1).setCellValue(ls[1]);
      hssfrowt.createCell(2).setCellValue(ls[2]);
               hssfrowt.createCell(3).setCellValue(ls[3]);
               hssfrowt.createCell(4).setCellValue(ls[4]);
              
      hssfrowt.createCell(5).setCellValue(ls[5]);
               hssfrowt.createCell(6).setCellValue(ls[6]);
               hssfrowt.createCell(7).setCellValue(ls[7]);

      hssfrowt.createCell(8).setCellValue(ls[8]);
               hssfrowt.createCell(9).setCellValue(ls[9]);
               hssfrowt.createCell(10).setCellValue(ls[10]);
     
      while(Res.next())
      {
      ISBN=Res.getString(ls[0]);
      Bname=Res.getString(ls[1]);
      Author=Res.getString(ls[2]);
      Publisher=Res.getString(ls[3]);
      Edition=Res.getInt(ls[4]);
      Publishtime=Res.getDate(ls[5]);
      
      System.out.println("ls[5]="+ls[5]);
      
      Btype=Res.getShort(ls[6]);
      Price=Res.getFloat(ls[7]);
      Page=Res.getShort(ls[8]);
      Introduction=Res.getString(ls[9]);
      Keyword=Res.getString(ls[10]);
      count++;
      System.out.println(ISBN+"\t"+Bname+"\t\t"+Author+"\t"+Publisher+"\t"+Edition+"\t"+Publishtime+"\t"+Btype+"\t"+Price+"\t"+Page+"\t"+Introduction+"\t"+Keyword);

//陆续输出从数据库中查到的东西
      
        HSSFRow hssfrow = hssfsheet.createRow(count);       
       
        hssfrow.createCell(0).setCellValue(ISBN);
                 hssfrow.createCell(1).setCellValue(Bname);
        hssfrow.createCell(2).setCellValue(Author);
                 hssfrow.createCell(3).setCellValue(Publisher);
                 hssfrow.createCell(4).setCellValue(Edition);

        hssfrow.createCell(5).setCellValue(Publishtime);
                 hssfrow.createCell(6).setCellValue(Btype);
                 hssfrow.createCell(7).setCellValue(Price);

        hssfrow.createCell(8).setCellValue(Page);
                 hssfrow.createCell(9).setCellValue(Introduction);
                 hssfrow.createCell(10).setCellValue(Keyword);
            
      }
    
      //FileOutputStream fileout = new FileOutputStream(fileToBeWrite); //定义一个输出流
      workbook.write(fileout); //将workbook通过输出流输出到相应地址
        fileout.close();     //关闭输出流
     }catch(Exception e){
      e.getMessage(); 
     }

    
    
    }catch(Exception e)
    {
     System.out.println(e.getMessage());
    }
    }
  /*those code used to connect to database library*/
    public static Connection getConnection() {

     Connection conn = null;
     try {
     
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection(dbURL,userName,password);
    System.out.println("链接成功");
     } catch (Exception e) {
      e.printStackTrace();
     }
     return conn;

    }

 }