利用POI将数据库表导出到Excel

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。它的主要结构包括：HSSF，XSSF等等。我们一般都是用于导出Excel，其他的相对少一点。

从数据库中导出到Excel的源码及步骤如下： 
1、首先要有一张表和数据 

2、代码，利用JDBC连接数据库

import java.io.FileOutputStream;import java.sql.DriverManager;import java.sql.ResultSet;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.mysql.jdbc.Connection;import com.mysql.jdbc.Statement;public class ReadExcelFromDB {    public final static String outputFile="C:\\Users\\XueFei\\Desktop\\country.xlsx";    public final static String url="jdbc:mysql://192.168.3.235:3306/sop";    public final static String user="你的用户名";    public final static String password="你的密码";    public static void main(String[] args) {        try {            Class.forName("com.mysql.jdbc.Driver");            Connection conn=(Connection) DriverManager.getConnection(url, user, password);            Statement stat = (Statement) conn.createStatement();            ResultSet resultSet = stat.executeQuery("select * from t_bi_country;");            XSSFWorkbook workbook=new XSSFWorkbook();            XSSFSheet sheet=workbook.createSheet("countryDB");            XSSFRow row = sheet.createRow((short)0);            XSSFCell cell=null;            cell=row.createCell((short)0);            cell.setCellValue("code");            cell=row.createCell((short)1);            cell.setCellValue("shortname");            cell=row.createCell((short)2);            cell.setCellValue("name");            cell=row.createCell((short)3);            cell.setCellValue("englishname");            int i=1;            while(resultSet.next())            {                row=sheet.createRow(i);                cell=row.createCell(0);                cell.setCellValue(resultSet.getString("code"));                cell=row.createCell(1);                cell.setCellValue(resultSet.getString("shortName"));                cell=row.createCell(2);                cell.setCellValue(resultSet.getString("name"));                cell=row.createCell(3);                cell.setCellValue(resultSet.getString("englishName"));                i++;             }            FileOutputStream FOut = new FileOutputStream(outputFile);            workbook.write(FOut);            FOut.flush();            FOut.close();        } catch (Exception e) {            e.printStackTrace();        }    }}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

3、运行之后就得到了我们想要的Excel表了。