用POI把数据导出为Excel

（一）SheetDataSource．java代码

1. package com.javaeye.yongsky;
2. import java.sql.*;
3. import java.util.*;
5. import com.microsoft.jdbc.*;
7. public class SheetDataSource {
9. private static Connection con ;
11. private static final String DatabaseName = "POIDEMO";
13. private static final String userName = "sa";
15. private static final String password = "123";
17. public SheetDataSource()
18. {
19. try {
20. Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
21. try {
22. con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName="+DatabaseName,userName,password);
23. } catch (SQLException e) {
24. e.printStackTrace();
25. }
26. } catch (ClassNotFoundException e) {
27. e.printStackTrace();
28. }
29. }
31. public static ResultSet selectAllDataFromDB() throws SQLException
32. {
33. new SheetDataSource();
34. Statement stmt = con.createStatement();
35. return stmt.executeQuery("select * from COMPANY");
36. }
38. }

上面部分代码主要是从数据库里面取出数据，作为Excel的数据源．

（二）PoiDemo．java

代码

1. package com.javaeye.yongsky;
3. import java.io.FileOutputStream;
4. import java.io.IOException;
5. import java.io.OutputStream;
6. import java.sql.ResultSet;
7. import java.sql.SQLException;
8. import java.util.*;
10. import javax.swing.JOptionPane;
12. import org.apache.poi.hssf.usermodel.HSSFCell;
13. import org.apache.poi.hssf.usermodel.HSSFFooter;
14. import org.apache.poi.hssf.usermodel.HSSFHeader;
15. import org.apache.poi.hssf.usermodel.HSSFRow;
16. import org.apache.poi.hssf.usermodel.HSSFSheet;
17. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
19. public class PoiDemo {
21. //表头
22. public static final String[] tableHeader = {"企业中文名","所属国家","企业英文名","2003年排名","2004年排名","2005年排名",
23. "2006年排名","2007年排名","主要业务","2003年营业额","2004年营业额","2005年营业额","2006年营业额","2007年营业额","企业编号","名次升降",
24. "图片","状况"};
25. //创建工作本
26. public static HSSFWorkbook demoWorkBook = new HSSFWorkbook();
27. //创建表
28. public static HSSFSheet demoSheet = demoWorkBook.createSheet("The World's 500 Enterprises");
29. //表头的单元格个数目
30. public static final short cellNumber = (short)tableHeader.length;
31. //数据库表的列数
32. public static final int columNumber = 18;
33. /**
34. * 创建表头
35. * @return
36. */
37. public static void createTableHeader()
38. {
39. HSSFHeader header = demoSheet.getHeader();
40. header.setCenter("世界五百强企业名次表");
41. HSSFRow headerRow = demoSheet.createRow((short) 0);
42. for(int i = 0;i < cellNumber;i++)
43. {
44. HSSFCell headerCell = headerRow.createCell((short) i);
45. headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
46. headerCell.setCellValue(tableHeader[i]);
47. }
48. }
49. /**
50. * 创建行
51. * @param cells
52. * @param rowIndex
53. */
54. public static void createTableRow(List<string> cells,</string>short rowIndex)
55. {
56. //创建第rowIndex行
57. HSSFRow row = demoSheet.createRow((short) rowIndex);
58. for(short i = 0;i < cells.size();i++)
59. {
60. //创建第i个单元格
61. HSSFCell cell = row.createCell((short) i);
62. cell.setEncoding(HSSFCell.ENCODING_UTF_16);
63. cell.setCellValue(cells.get(i));
64. }
65. }
67. /**
68. * 创建整个Excel表
69. * @throws SQLException
70. *
71. */
72. public static void createExcelSheeet() throws SQLException
73. {
74. createTableHeader();
75. ResultSet rs = SheetDataSource.selectAllDataFromDB();
76. int rowIndex = 1;
77. while(rs.next())
78. {
79. List<string> list = new ArrayList<string>(); </string></string>
80. for(int i = 1;i <= columNumber;i++)
81. {
82. list.add(rs.getString(i));
83. }
84. createTableRow(list,(short)rowIndex);
85. rowIndex++;
86. }
87. }
88. /**
89. * 导出表格
90. * @param sheet
91. * @param os
92. * @throws IOException
93. */
94. public void exportExcel(HSSFSheet sheet,OutputStream os) throws IOException
95. {
96. sheet.setGridsPrinted(true);
97. HSSFFooter footer = sheet.getFooter();
98. footer.setRight("Page " + HSSFFooter.page() + " of " +
99. HSSFFooter.numPages());
100. demoWorkBook.write(os);
101. }
103. public static void main(String[] args) {
104. String fileName = "D://世界五百强企业名次表.xls";
105. FileOutputStream fos = null;
106. try {
107. PoiDemo pd = new PoiDemo();
108. pd.createExcelSheeet();
109. fos = new FileOutputStream(fileName);
110. pd.exportExcel(demoSheet,fos);
111. JOptionPane.showMessageDialog(null, "表格已成功导出到 : "+fileName);
112. } catch (Exception e) {
113. JOptionPane.showMessageDialog(null, "表格导出出错，错误信息 ："+e+"/n错误原因可能是表格已经打开。");
114. e.printStackTrace();
115. } finally {
116. try {
117. fos.close();
118. } catch (Exception e) {
119. e.printStackTrace();
120. }
121. }
122. }
123. }