在這之前寫過關於java讀，寫Excel的blog如下：
Excel轉Html
java的poi技術讀，寫Excel[2003-2007,2010]
java的poi技術讀取Excel[2003-2007,2010]
java的poi技術讀取Excel數據到MySQL
java的jxl技術導入Excel
java的poi技術讀取和導入Excel
然而，這篇blog主要內容是關於Excel裡面怎樣去寫Sheet數據。
那麼在Excel裡面什麼叫做Sheet呢？如下圖紅色框裡面的內容就是Excel的Sheet了。
我們需要知道怎樣創建一個Sheet，下面是一個Sample：
1 Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();
2 Sheet sheet1 = wb.createSheet("new sheet");
3 Sheet sheet2 = wb.createSheet("second sheet");
4
5
6 // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
7 // for a safe way to create valid names, this utility replaces invalid characters with a space (' ')
8 String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales "
9 Sheet sheet3 = wb.createSheet(safeName);
10
11 FileOutputStream fileOut = new FileOutputStream("workbook.xls");
12 wb.write(fileOut);
13 fileOut.close();
看了以後，會不會覺得So easy！這是因為Apache的poi給我們封裝了很多方法，提供了很好的接口。
下面是我做的一個Demo，這個Demo的數據流如下：
MySQL數據庫 -- > Demo 程序 -- > Excel 文件
我們的Demo程序會從MySQL數據庫中讀取數據，然後把數據寫入到Excel文件中。
項目結構：
注意：紅色框裡面的jar包，你在下載源碼後，這些jar包不會存放到源碼裡面，需要手動下載！
在MySQL數據庫中，我們會用到兩張表： t_school, t_student.
1 -- Table "t_school" DDL 2 3 CREATE TABLE `t_school` ( 4 `no` int(16) NOT NULL AUTO_INCREMENT, 5 `name` varchar(50) COLLATE utf8_bin NOT NULL, 6 `desc` varchar(500) COLLATE utf8_bin DEFAULT NULL, 7 `ranking` int(3) DEFAULT NULL, 8 `address` varchar(200) COLLATE utf8_bin DEFAULT NULL, 9 PRIMARY KEY (`no`) 10 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 11 12 -- Table "t_student" DDL 13 14 CREATE TABLE `t_student` ( 15 `student_no` int(16) NOT NULL AUTO_INCREMENT, 16 `school_no` int(16) NOT NULL, 17 `name` varchar(100) COLLATE utf8_bin DEFAULT NULL, 18 `address` varchar(200) COLLATE utf8_bin DEFAULT NULL, 19 `birthdate` varchar(15) COLLATE utf8_bin DEFAULT NULL, 20 `phone` varchar(15) COLLATE utf8_bin DEFAULT NULL, 21 PRIMARY KEY (`student_no`,`school_no`), 22 KEY `school_no` (`school_no`), 23 CONSTRAINT `school_no` FOREIGN KEY (`school_no`) REFERENCES `t_school` (`no`) 24 ) ENGINE=InnoDB AUTO_INCREMENT=100000023 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
在Excel文件中，我們要做的是在'School Summary' Sheet裡面寫入School的信息，而對於其他的Sheet，我們會把Shool的名稱命名為Sheet的Name，然後在每一個Sheet裡面寫入在這個Shool裡面的Student的信息。
兩張表裡面數據，大家可以通過blog末尾的下載鏈接獲得。
---------------------------------------------
代碼部分
---------------------------------------------
/ExcelHandler/src/com/b510/hongten/client/Client.java
1 package com.b510.hongten.client;
2
3 import java.util.List;
4
5 import com.b510.hongten.db.SchoolDAO;
6 import com.b510.hongten.excel.WriteExcel;
7 import com.b510.hongten.vo.School;
8
9 /**
10 * @author hongten
11 * @created Jun 16, 2016
12 */
13 public class Client {
14
15 public static void main(String[] args) {
16 List<School> schools = SchoolDAO.getSchools();
17 WriteExcel writeExcel = new WriteExcel();
18 writeExcel.writeExcel(schools);
19 }
20
21 }
/ExcelHandler/src/com/b510/hongten/common/Common.java
1 package com.b510.hongten.common;
2
3 /**
4 * @author hongten
5 * @created Jun 16, 2016
6 */
7 public class Common {
8
9 // MySQL database connection configuration, you could write in *.properties
10 // file as also. For this demo, we write this configuration in this class
11 // file. By the way, I don't recommend this way ^_^.
12 public static String URL = "jdbc:mysql://localhost:3306/school";
13 public static String USER_NAME = "root";
14 public static String PASSWORD = "password1";
15
16 public static String TARGET_FILE_PATH = "lib/excel_resources/school.xls";
17
18 public static String SUMMARY = "Shool Summary";
19 public static String[] TITLES = { "S/N", "Name", "Description", "Ranking", "Address" };
20 public static String[] STUDENT_TITLE = { "S/N", "Name", "Birth Date", "Phone", "Address" };
21 }
/ExcelHandler/src/com/b510/hongten/db/ConnectionUtil.java
1 package com.b510.hongten.db;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.SQLException;
6
7 import org.apache.log4j.Logger;
8
9 import com.b510.hongten.common.Common;
10
11 /**
12 * @author hongten
13 * @created Jun 16, 2016
14 */
15 public class ConnectionUtil {
16
17 static Logger logger = Logger.getLogger(ConnectionUtil.class);
18
19 public static Connection getConn() {
20 logger.debug("-------- MySQL JDBC Connection Testing ------------");
21
22 Connection connection = null;
23 try {
24 Class.forName("com.mysql.jdbc.Driver");
25 } catch (ClassNotFoundException e) {
26 logger.error("Where is your MySQL JDBC Driver?");
27 e.printStackTrace();
28 }
29
30 logger.info("MySQL JDBC Driver Registered!");
31
32 try {
33 connection = DriverManager.getConnection(Common.URL, Common.USER_NAME, Common.PASSWORD);
34 if(connection != null){
35 logger.info("connecte successfully!");
36 }
37 } catch (SQLException e) {
38 logger.error("Connection Failed! Check output console");
39 e.printStackTrace();
40 }
41 return connection;
42
43 }
44
45 public static void closeConn(Connection conn) {
46 if (conn != null) {
47 try {
48 logger.info("closing connection begin!");
49 conn.close();
50 logger.info("closing connection end!");
51 } catch (SQLException e) {
52 e.printStackTrace();
53 }
54 } else {
55 logger.info("connection is not null!");
56 }
57 }
58 }
/ExcelHandler/src/com/b510/hongten/db/SchoolDAO.java
1 package com.b510.hongten.db;
2
3 import java.sql.Connection;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.util.ArrayList;
7 import java.util.List;
8
9 import org.apache.log4j.Logger;
10
11 import com.b510.hongten.vo.School;
12 import com.b510.hongten.vo.Student;
13 import com.mysql.jdbc.PreparedStatement;
14
15 /**
16 * @author hongten
17 * @created Jun 16, 2016
18 */
19 public class SchoolDAO {
20
21 static Logger logger = Logger.getLogger(SchoolDAO.class);
22
23 public static List<School> getSchools() {
24 Connection conn = null;
25 PreparedStatement preparedStatement = null, preStat = null;
26 ResultSet rs = null, rs_student = null;
27 List<School> schools = new ArrayList<School>();
28
29 try {
30 conn = ConnectionUtil.getConn();
31 String sql = "select * from t_school order by no";
32 preparedStatement = (PreparedStatement) conn.prepareStatement(sql);
33 rs = preparedStatement.executeQuery();
34 while (rs.next()) {
35 int school_no = rs.getInt(1);
36 if (school_no > 0) {
37 School school = new School();
38 school.setNo(school_no);
39 school.setName(rs.getString("name"));
40 school.setAddrss(rs.getString("address"));
41 school.setDesc(rs.getString("desc"));
42 school.setRanking(rs.getString("ranking"));
43
44 String studentSQL = "select * from t_student where school_no = ? ";
45 preStat = (PreparedStatement) conn.prepareStatement(studentSQL);
46 preStat.setInt(1, school_no);
47 rs_student = preStat.executeQuery();
48 List<Student> students = new ArrayList<>();
49 while (rs_student.next()) {
50 Student student = new Student();
51 int std_no = rs_student.getInt(1);
52 student.setStudentNo(std_no);
53 student.setName(rs_student.getString("name"));
54 student.setBirthdate(rs_student.getString("birthdate"));
55 student.setPhone(rs_student.getString("phone"));
56 student.setAddress(rs.getString("address"));
57 students.add(student);
58 }
59 school.setStudents(students);
60 schools.add(school);
61 }
62 }
63 } catch (SQLException e) {
64 e.printStackTrace();
65 logger.error(e.getMessage());
66 } finally {
67 if (rs != null) {
68 try {
69 rs.close();
70 } catch (SQLException e) {
71 e.printStackTrace();
72 }
73 }
74 if (preparedStatement != null) {
75 try {
76 preparedStatement.close();
77 } catch (SQLException e) {
78 e.printStackTrace();
79 }
80 }
81 ConnectionUtil.closeConn(conn);
82 }
83 return schools;
84 }
85 }
/ExcelHandler/src/com/b510/hongten/excel/WriteExcel.java
1 package com.b510.hongten.excel;
2
3 import java.io.FileNotFoundException;
4 import java.io.FileOutputStream;
5 import java.io.IOException;
6 import java.util.List;
7
8 import org.apache.log4j.Logger;
9 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
10 import org.apache.poi.ss.usermodel.Cell;
11 import org.apache.poi.ss.usermodel.Row;
12 import org.apache.poi.ss.usermodel.Sheet;
13 import org.apache.poi.ss.usermodel.Workbook;
14
15 import com.b510.hongten.common.Common;
16 import com.b510.hongten.vo.School;
17 import com.b510.hongten.vo.Student;
18
19 /**
20 * @author hongten
21 * @created Jun 13, 2016
22 */
23 public class WriteExcel {
24
25 static Logger logger = Logger.getLogger(WriteExcel.class);
26
27 public void writeExcel(List<School> schools) {
28 if (schools == null || schools.size() == 0) {
29 return;
30 }
31 FileOutputStream fileOut = null;
32 Workbook wb = new HSSFWorkbook();
33 Sheet shool_sheet = wb.createSheet(Common.SUMMARY);
34 shool_sheet.setAutobreaks(true);
35 // Create a row and put some cells in it. Rows are 0 based.
36 Row row = shool_sheet.createRow(0);
37 String[] titles = Common.TITLES;
38 int num = 0;
39 for (String title : titles) {
40 // Create a cell
41 Cell cell = row.createCell(num++);
42 cell.setCellValue(title);
43 }
44 int rowNum = 1;
45 for (School school : schools) {
46 row = shool_sheet.createRow(rowNum++);
47 Cell cell = row.createCell(0);
48 cell.setCellValue(school.getNo());
49 cell = row.createCell(1);
50 cell.setCellValue(school.getName());
51 cell = row.createCell(2);
52 cell.setCellValue(school.getDesc());
53 cell = row.createCell(3);
54 cell.setCellValue(school.getRanking());
55 cell = row.createCell(4);
56 cell.setCellValue(school.getAddrss());
57
58 List<Student> students = school.getStudents();
59 if (students != null && students.size() > 0) {
60 Sheet student_sheet = wb.createSheet(school.getName());
61 student_sheet.setAutobreaks(true);
62 // Create a row and put some cells in it. Rows are 0 based.
63 Row student_row = student_sheet.createRow(0);
64 String[] student_titles = Common.STUDENT_TITLE;
65 num = 0;
66 for (String title : student_titles) {
67 // Create a cell
68 Cell student_cell = student_row.createCell(num++);
69 student_cell.setCellValue(title);
70 }
71 int stuRowNum = 1;
72 for (Student student : students) {
73 student_row = student_sheet.createRow(stuRowNum++);
74 Cell student_cell = student_row.createCell(0);
75 student_cell.setCellValue(student.getStudentNo());
76 student_cell = student_row.createCell(1);
77 student_cell.setCellValue(student.getName());
78 student_cell = student_row.createCell(2);
79 student_cell.setCellValue(student.getBirthdate());
80 student_cell = student_row.createCell(3);
81 student_cell.setCellValue(student.getPhone());
82 student_cell = student_row.createCell(4);
83 student_cell.setCellValue(student.getAddress());
84 }
85 }
86 }
87
88 try {
89 fileOut = new FileOutputStream(Common.TARGET_FILE_PATH);
90 } catch (FileNotFoundException e) {
91 e.printStackTrace();
92 }
93 try {
94 wb.write(fileOut);
95 } catch (IOException e1) {
96 e1.printStackTrace();
97 }
98 try {
99 fileOut.close();
100 } catch (IOException e) {
101 e.printStackTrace();
102 }
103 logger.info("done");
104 }
105
106 }
/ExcelHandler/src/com/b510/hongten/vo/School.java
1 package com.b510.hongten.vo;
2
3 import java.util.ArrayList;
4 import java.util.List;
5
6 /**
7 * @author hongten
8 * @created Jun 16, 2016
9 */
10 public class School {
11
12 private int no;
13 private String name;
14 private String desc;
15 private String ranking;
16 private String addrss;
17
18 private List<Student> students = new ArrayList<Student>();
19
20 public int getNo() {
21 return no;
22 }
23
24 public void setNo(int no) {
25 this.no = no;
26 }
27
28 public String getName() {
29 return name;
30 }
31
32 public void setName(String name) {
33 this.name = name;
34 }
35
36 public String getDesc() {
37 return desc;
38 }
39
40 public void setDesc(String desc) {
41 this.desc = desc;
42 }
43
44 public String getRanking() {
45 return ranking;
46 }
47
48 public void setRanking(String ranking) {
49 this.ranking = ranking;
50 }
51
52 public String getAddrss() {
53 return addrss;
54 }
55
56 public void setAddrss(String addrss) {
57 this.addrss = addrss;
58 }
59
60 public List<Student> getStudents() {
61 return students;
62 }
63
64 public void setStudents(List<Student> students) {
65 this.students = students;
66 }
67
68 }
/ExcelHandler/src/com/b510/hongten/vo/Student.java
1 package com.b510.hongten.vo;
2
3 /**
4 * @author hongten
5 * @created Jun 16, 2016
6 */
7 public class Student {
8
9 private int studentNo;
10 private String name;
11 private String address;
12 private String birthdate;
13 private String note;
14 private String phone;
15
16 public int getStudentNo() {
17 return studentNo;
18 }
19
20 public void setStudentNo(int studentNo) {
21 this.studentNo = studentNo;
22 }
23
24 public String getName() {
25 return name;
26 }
27
28 public void setName(String name) {
29 this.name = name;
30 }
31
32 public String getAddress() {
33 return address;
34 }
35
36 public void setAddress(String address) {
37 this.address = address;
38 }
39
40 public String getBirthdate() {
41 return birthdate;
42 }
43
44 public void setBirthdate(String birthdate) {
45 this.birthdate = birthdate;
46 }
47
48 public String getNote() {
49 return note;
50 }
51
52 public void setNote(String note) {
53 this.note = note;
54 }
55
56 public String getPhone() {
57 return phone;
58 }
59
60 public void setPhone(String phone) {
61 this.phone = phone;
62 }
63
64 }
/ExcelHandler/src/log4j.xml
1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
3 <log4j:configuration debug="true"
4 xmlns:log4j='http://jakarta.apache.org/log4j/'>
5
6 <appender name="console" class="org.apache.log4j.ConsoleAppender">
7 <layout class="org.apache.log4j.PatternLayout">
8 <param name="ConversionPattern"
9 value="%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n" />
10 </layout>
11 </appender>
12
13 <root>
14 <level value="DEBUG" />
15 <appender-ref ref="console" />
16 </root>
17 </log4j:configuration>
源碼下載：
http://files.cnblogs.com/files/hongten/ExcelHandler.rar
測試數據下載：
http://files.cnblogs.com/files/hongten/t_shool_and_t_student_data.rar
========================================================
More reading,and english is important.
I'm Hongten
E | [email protected] B | http://www.cnblogs.com/hongten
========================================================
