利用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 yqc.sql.*; import org.apache.poi.hssf.util.*; /** * * @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"; }