用POI把数据导出为Excel
最近在做我们的JAVA课程设计，我的设计里面涉及到了对信息的导出，包括导出Excel和Pdf．在网上查了有关Poi的资料查了很久终于写出了满足自己需求的程序．以下是程序的全部代码：（一）SheetDataSource．java
package com.javaeye.yongsky;import java.sql.*;import java.util.*;import com.microsoft.jdbc.*;public class SheetDataSource { private static Connection con ;private static final String DatabaseName = "POIDEMO";private static final String userName = "sa";private static final String password = "123";public SheetDataSource() {try { Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); try { con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName="+DatabaseName,userName,password); } catch (SQLException e) { e.printStackTrace(); }} catch (ClassNotFoundException e) { e.printStackTrace();} }public static ResultSet selectAllDataFromDB() throws SQLException{ new SheetDataSource(); Statement stmt = con.createStatement(); return stmt.executeQuery("select * from COMPANY"); } }
上面部分代码主要是从数据库里面取出数据，作为Excel的数据源．
（二）PoiDemo．java
package com.javaeye.yongsky;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.sql.ResultSet;import java.sql.SQLException;import java.util.*;import javax.swing.JOptionPane;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFFooter;import org.apache.poi.hssf.usermodel.HSSFHeader;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;public class PoiDemo {//表头public static final String[] tableHeader = {"企业中文名","所属国家","企业英文名","2003年排名","2004年排名","2005年排名","2006年排名","2007年排名","主要业务","2003年营业额","2004年营业额","2005年营业额","2006年营业额","2007年营业额","企业编号","名次升降","图片","状况"};//创建工作本public static HSSFWorkbook demoWorkBook = new HSSFWorkbook();//创建表public static HSSFSheet demoSheet = demoWorkBook.createSheet("The World's 500 Enterprises");//表头的单元格个数目public static final short cellNumber = (short)tableHeader.length;//数据库表的列数public static final int columNumber = 18;/** * 创建表头 * @return */public static void createTableHeader(){HSSFHeader header = demoSheet.getHeader();header.setCenter("世界五百强企业名次表");HSSFRow headerRow = demoSheet.createRow((short) 0);for(int i = 0;i < cellNumber;i++){HSSFCell headerCell = headerRow.createCell((short) i);headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);headerCell.setCellValue(tableHeader);}}/** * 创建行 * @param cells * @param rowIndex */public static void createTableRow(List<String> cells,short rowIndex){//创建第rowIndex行HSSFRow row = demoSheet.createRow((short) rowIndex);for(short i = 0;i < cells.size();i++){//创建第i个单元格HSSFCell cell = row.createCell((short) i);cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue(cells.get(i));}}/** * 创建整个Excel表 * @throws SQLException* */public static void createExcelSheeet() throws SQLException{createTableHeader();ResultSet rs = SheetDataSource.selectAllDataFromDB();int rowIndex = 1;while(rs.next()){List<String> list = new ArrayList<String>();for(int i = 1;i <= columNumber;i++){list.add(rs.getString(i));}createTableRow(list,(short)rowIndex);rowIndex++;}}/** * 导出表格 * @param sheet * @param os * @throws IOException */public void exportExcel(HSSFSheet sheet,OutputStream os) throws IOException{sheet.setGridsPrinted(true); HSSFFooter footer = sheet.getFooter(); footer.setRight("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages()); demoWorkBook.write(os);}public static void main(String[] args) {String fileName = "D:\\世界五百强企业名次表.xls"; FileOutputStream fos = null; try { PoiDemo pd = new PoiDemo(); pd.createExcelSheeet(); fos = new FileOutputStream(fileName); pd.exportExcel(demoSheet,fos); JOptionPane.showMessageDialog(null, "表格已成功导出到 : "+fileName); } catch (Exception e) { JOptionPane.showMessageDialog(null, "表格导出出错，错误信息 ："+e+"\n错误原因可能是表格已经打开。"); e.printStackTrace(); } finally { try { fos.close(); } catch (Exception e) { e.printStackTrace(); } }}}
另外在附件里面附带有本程序所用的所有资源，包括源代码文件，数据库和POI的JAR包，欢迎有兴趣的朋友参考，并提出宝贵的意见．
致谢 ：
本程序的数据库由兄弟温总提供，为了不让他免费为我们提供他辛辛苦苦做出数据库而感到心里不平衡，在此向他表示感谢！ http://www.agoit.com/images/smiles/icon_biggrin.gif
页:
[1]