利用POI将数据表导入Excel
来源:互联网 发布:阿里云节点评测 编辑:程序博客网 时间:2024/06/16 14:16
<script type="text/javascript">google_ad_client = "pub-8800625213955058";/* 336x280, 创建于 07-11-21 */google_ad_slot = "0989131976";google_ad_width = 336;google_ad_height = 280;//</script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>* * 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";}