java操作excel库有几种_用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中的数据\\testout2Excel.xls";//等待导出的文件地址

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;

}

}