连接数据库类:
1
2 import java.sql.*;
3 import java.util.*;
4
5 import com.microsoft.jdbc.*;
6
7 public class SheetDataSource {
8
9 private static Connection con;
10
11 private static final String DatabaseName = "RIS";
12
13 private static final String userName = "ris";
14
15 private static final String password = "123";
16
17 public SheetDataSource() {
18 try {
19 Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
20 try {
21 con = DriverManager.getConnection(
22 "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName="
23 + DatabaseName, userName, password);
24 } catch (SQLException e) {
25 e.printStackTrace();
26 }
27 } catch (ClassNotFoundException e) {
28 e.printStackTrace();
29 }
30 }
31
32 public static ResultSet selectAllDataFromDB() throws SQLException {
33 new SheetDataSource();
34 Statement stmt = con.createStatement();
35 return stmt.executeQuery("select * from T_MIRC_UI_DEFINE");
36 }
37
38 }
39
40
写入到excel中
1 import java.io.FileOutputStream;
2 import java.io.IOException;
3 import java.io.OutputStream;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.util.*;
7
8 import javax.swing.JOptionPane;
9
10 import org.apache.poi.hssf.usermodel.HSSFCell;
11 import org.apache.poi.hssf.usermodel.HSSFFooter;
12 import org.apache.poi.hssf.usermodel.HSSFHeader;
13 import org.apache.poi.hssf.usermodel.HSSFRow;
14 import org.apache.poi.hssf.usermodel.HSSFSheet;
15 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
16
17 public class PoiDemo {
18
19 //表头
20 public static final String[] tableHeader = { "F_UI_ID", "F_ITEM_INDEX", "F_ITEM_TYPE",
21 "F_ITEM_CONTEXT", "F_COL_ID", "F_ITEM_FLAG", "F_ITEM_X", "F_ITEM_Y", "F_ITEM_WIDTH",
22 "F_ITEM_HEIGHT", "F_COLOR", "F_FONT_NAME", "F_FONT_SIZE", "F_FONT_WEIGHT", "F_ENABLED_FLAG" };
23 //创建工作本
24 public static HSSFWorkbook demoWorkBook = new HSSFWorkbook();
25 //创建表
26 public static HSSFSheet demoSheet = demoWorkBook
27 .createSheet("MIRC UI ");
28 //表头的单元格个数目
29 public static final short cellNumber = (short) tableHeader.length;
30 //数据库表的列数
31 public static final int columNumber = 15;
32
33 /** *//**
34 * 创建表头
35 * @return
36 */
37 public static void createTableHeader() {
38 HSSFHeader header = demoSheet.getHeader();
39 header.setCenter("MIRC的UI界面");
40 HSSFRow headerRow = demoSheet.createRow((short) 0);
41 for (int i = 0; i < cellNumber; i++) {
42 HSSFCell headerCell = headerRow.createCell((short) i);
43 headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
44 headerCell.setCellValue(tableHeader[i]);
45 }
46 }
47
48 /** *//**
49 * 创建行
50 * @param cells
51 * @param rowIndex
52 */
53 public static void createTableRow(List<String> cells, short rowIndex) {
54 //创建第rowIndex行
55 HSSFRow row = demoSheet.createRow((short) rowIndex);
56 for (short i = 0; i < cells.size(); i++) {
57 //创建第i个单元格
58 HSSFCell cell = row.createCell((short) i);
59 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
60 cell.setCellValue(cells.get(i));
61 }
62 }
63
64 /** *//**
65 * 创建整个Excel表
66 * @throws SQLException
67 *
68 */
69 public static void createExcelSheeet() throws SQLException {
70 createTableHeader();
71 ResultSet rs = SheetDataSource.selectAllDataFromDB();
72 int rowIndex = 1;
73 while (rs.next()) {
74 List<String> list = new ArrayList<String>();
75 for (int i = 1; i <= columNumber; i++) {
76 list.add(rs.getString(i));
77 }
78 createTableRow(list, (short) rowIndex);
79 rowIndex++;
80 }
81 }
82
83 /** *//**
84 * 导出表格
85 * @param sheet
86 * @param os
87 * @throws IOException
88 */
89 public void exportExcel(HSSFSheet sheet, OutputStream os)
90 throws IOException {
91 sheet.setGridsPrinted(true);
92 HSSFFooter footer = sheet.getFooter();
93 footer.setRight("Page " + HSSFFooter.page() + " of "
94 + HSSFFooter.numPages());
95 demoWorkBook.write(os);
96 }
97
98 public static void main(String[] args) {
99 String fileName = "MIRC的UI界面.xls";
100 FileOutputStream fos = null;
101 try {
102 PoiDemo pd = new PoiDemo();
103 pd.createExcelSheeet();
104 fos = new FileOutputStream(fileName);
105 pd.exportExcel(demoSheet, fos);
106 JOptionPane.showMessageDialog(null, "表格已成功导出到 : " + fileName);
107 } catch (Exception e) {
108 JOptionPane.showMessageDialog(null, "表格导出出错,错误信息 :" + e
109 + "\n错误原因可能是表格已经打开。");
110 e.printStackTrace();
111 } finally {
112 try {
113 fos.close();
114 } catch (Exception e) {
115 e.printStackTrace();
116 }
117 }
118 }
119 }
120
121
|