Rising Sun

BlogJava :: 首頁 :: 新隨筆 :: 聯系 :: 聚合

:: 管理 ::

148 隨筆 :: 0 文章 :: 22 評論 :: 0 Trackbacks

利用POI將數據表導入Excel

/*
?*?QuickExcel.java
?*?作者：楊慶成
?*?Created?on?2004年11月22日,?下午4:05
?*?在實際應用中經常要將數據庫中的表導入Excel
?*?本人在Apache的POI基礎上寫了一個簡單的類
?*?有不當指出請指正，謝謝！
?*?
?*/
package ?yqc.poi;
import ?java.sql.*;
import ?java.util.*;
import ?java.io.*;
import ?java.io.ByteArrayInputStream;
import ?java.io.FileInputStream;
import ?java.io.FileOutputStream;
import ?java.io.IOException;
import ?org.apache.poi.hssf.usermodel.*;
import ?org.apache.poi.poifs.filesystem.POIFSFileSystem;
import ?org.apache.poi.hssf.record.*;
import ?org.apache.poi.hssf.model.*;
import ?org.apache.poi.hssf.usermodel.*;
import ?org.apache.poi.hssf.util.*;import?yqc.sql.*;
/**
?*
?*?@author??Administrator
?*/
public ?class?QuickExcel?{
????
????/**?Creates?a?new?instance?of?QuickExcel?*/
????private?QuickExcel(String?file){
????????_file=file;
????}
????
????private?void?open()throws?IOException{
????????InputStream?stream?=?null;
????????Record[]?records?=?null;
????????POIFSFileSystem?fs?=
????????????new?POIFSFileSystem(new?FileInputStream(_file));
????????_wb?=?new?HSSFWorkbook(fs);
????}
????
????private?void?create(){
????????_wb=new?HSSFWorkbook();
????}
????
????public?static?QuickExcel?newInstance?(String?file){
????????QuickExcel?qe=new?QuickExcel(file);
????????qe.create();
????????return?qe;
????}
????
????public?static?QuickExcel?openInstance(String?file)?throws?IOException?{
????????QuickExcel?qe=new?QuickExcel(file);
????????qe.open();
????????return?qe;
????}
????
????public?void?close(){
????????try{
????????????FileOutputStream?fileOut?=?new?FileOutputStream(_file);
????????????_wb.write(fileOut);//把Workbook對象輸出到文件workbook.xls中
????????????fileOut.close();
????????}
????????catch?(Exception?ex){
????????????System.out.println(ex.getMessage());
????????}
????}
????
????private?void?removeSheet(String?sheetName){
????????int?i=_wb.getSheetIndex("sheetName");
????????if?(i>=0)?_wb.removeSheetAt(i);
????}
????
????public?int?fillSheet?(ResultSet?rs,String?sheetName)throws?SQLException?{
????????HSSFSheet?st=?_wb.createSheet(sheetName);
????????ResultSetMetaData?rsmd=?rs.getMetaData();
????????int?index=0;
????????int?result=0;
????????HSSFRow?row=st.createRow(index++);
????????for(int?i=1;i<=rsmd.getColumnCount();++i){
????????????HSSFCell?cell=row.createCell((short)(i-1));
????????????cell.setCellValue(rsmd.getColumnName(i));
????????}
????????while(rs.next())?{
????????????result++;
????????????row=st.createRow(index++);
????????????for(int?i=1;i<=rsmd.getColumnCount();++i){
????????????????HSSFCell?cell=row.createCell((short)(i-1));
????????????????cell.setEncoding(cell.ENCODING_UTF_16);
????????????????cell.setCellValue(rs.getString(i));
????????????}
????????}
????????return?result;
}
????
????public?static?void?main(String[]?args){
????????try{
????????????QuickConnection?qc=new?MssqlConnection("jdbc:microsoft:sqlserver://192.168.0.100:1433;DatabaseName=ls");
????????????QuickExcel?qe=QuickExcel.newInstance("a.xls");
????????????qc.connect();
????????????String?sql="select?*?from?ls.dbo.radio1_emcee";
????????????ResultSet?rs=qc.getStatement().executeQuery(sql);
????????????qe.fillSheet(rs,"MT");
????????????qe.close();
????????????qe=QuickExcel.openInstance("a.xls");
????????????qe.fillSheet(rs,"MO");
????????????qe.close();
????????????qc.close();
????????}
????????catch?(SQLException?ex){
????????????System.out.println(ex.getMessage());
????????}
????????catch?(IOException?ex){
????????????System.out.println(ex.getMessage());
????????}
????}
????
????HSSFWorkbook?_wb;
????String?_file="new.xls";
}

posted on 2006-07-26 15:30 brock 閱讀(462) 評論(0) 編輯收藏所屬分類: 處理Excel poi

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: excel poi 處理2 Busy Developers' Guide to HSSF Features (說明書) excel 處理 Poi 利用POI將數據表導入Excel 純java的Excel解決方案 Excel的java處理方式

Rising Sun

常用鏈接

留言簿(10)

隨筆分類

隨筆檔案

相冊

java--->ajax

搜索

最新評論

閱讀排行榜

評論排行榜