201906G110/POI/ExcelFromDB.java

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


public class ExcelFromDB {
	public static void main(String[] args) throws Exception{
    Connection con = null ;      
    DBConnection db = new DBConnection();  
    con = db.getDB();  
    
    String sql ="select * from student";
    ResultSet rs = con.createStatement().executeQuery(sql);
    int CountColumnNum = rs.getMetaData().getColumnCount(); //获取总列数

    HSSFWorkbook wb = new HSSFWorkbook();                   //得到Excel工作簿对象( 创建Excel文档)
    HSSFSheet sheet = wb.createSheet("student表中的数据");      //得到Excel工作表对象
    HSSFRow firstrow = sheet.createRow(0);                  //创建表格中的第一行,下标为0
    HSSFCell[] firstcell = new HSSFCell[CountColumnNum];    //创建单元格
    String[] names = new String[CountColumnNum];
    names[0] ="user";
    names[1] ="name";
    names[2] ="password";
    names[3] ="sex";
    names[4] ="national";
    names[5]="telephone";
    names[6]="age";
    //给第一行的单元格赋值,即表头
    for(int j= 0;j<CountColumnNum;j++) {
        firstcell[j] = firstrow.createCell((short)j);
        firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
}
    int i =1;
    while(rs.next()){  	
       //创建表格的一行
       HSSFRow row = sheet.createRow(i);                //下标为1的行开始
       for(int j=0;j<CountColumnNum;j++){
       HSSFCell cell = row.createCell((short) j);       //在一行内循环
       //将结果集里的值放入表格中
       cell.setCellValue(new HSSFRichTextString(rs.getString(j+1)));}
       i++;
}
//创建文件输出流输出表格
OutputStream out = new FileOutputStream("C:\\Users\\ASUS\\Desktop\\student.xls");
wb.write(out);
out.close();
System.out.println("数据库导出成功") ;
rs.close();
con.close();
}

}