在这之前写过关于java读，写Excel的blog如下：

然而，这篇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 NULLAUTO_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 NULLAUTO_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 packagecom.b510.hongten.client;2

3 importjava.util.List;4

5 importcom.b510.hongten.db.SchoolDAO;6 importcom.b510.hongten.excel.WriteExcel;7 importcom.b510.hongten.vo.School;8

9 /**

10 *@authorhongten11 * @created Jun 16, 201612 */

13 public classClient {14

15 public static voidmain(String[] args) {16 List schools =SchoolDAO.getSchools();17 WriteExcel writeExcel = newWriteExcel();18 writeExcel.writeExcel(schools);19 }20

21 }

/ExcelHandler/src/com/b510/hongten/common/Common.java

1 packagecom.b510.hongten.common;2

3 /**

4 *@authorhongten5 * @created Jun 16, 20166 */

7 public classCommon {8

9 //MySQL database connection configuration, you could write in *.properties10 //file as also. For this demo, we write this configuration in this class11 //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 packagecom.b510.hongten.db;2

3 importjava.sql.Connection;4 importjava.sql.DriverManager;5 importjava.sql.SQLException;6

7 importorg.apache.log4j.Logger;8

9 importcom.b510.hongten.common.Common;10

11 /**

12 *@authorhongten13 * @created Jun 16, 201614 */

15 public classConnectionUtil {16

17 static Logger logger = Logger.getLogger(ConnectionUtil.class);18

19 public staticConnection 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 returnconnection;42

43 }44

45 public static voidcloseConn(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 packagecom.b510.hongten.db;2

3 importjava.sql.Connection;4 importjava.sql.ResultSet;5 importjava.sql.SQLException;6 importjava.util.ArrayList;7 importjava.util.List;8

9 importorg.apache.log4j.Logger;10

11 importcom.b510.hongten.vo.School;12 importcom.b510.hongten.vo.Student;13 importcom.mysql.jdbc.PreparedStatement;14

15 /**

16 *@authorhongten17 * @created Jun 16, 201618 */

19 public classSchoolDAO {20

21 static Logger logger = Logger.getLogger(SchoolDAO.class);22

23 public static ListgetSchools() {24 Connection conn = null;25 PreparedStatement preparedStatement = null, preStat = null;26 ResultSet rs = null, rs_student = null;27 List schools = new ArrayList();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 = newSchool();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 students = new ArrayList<>();49 while(rs_student.next()) {50 Student student = newStudent();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 returnschools;84 }85 }

/ExcelHandler/src/com/b510/hongten/excel/WriteExcel.java

1 packagecom.b510.hongten.excel;2

3 importjava.io.FileNotFoundException;4 importjava.io.FileOutputStream;5 importjava.io.IOException;6 importjava.util.List;7

8 importorg.apache.log4j.Logger;9 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;10 importorg.apache.poi.ss.usermodel.Cell;11 importorg.apache.poi.ss.usermodel.Row;12 importorg.apache.poi.ss.usermodel.Sheet;13 importorg.apache.poi.ss.usermodel.Workbook;14

15 importcom.b510.hongten.common.Common;16 importcom.b510.hongten.vo.School;17 importcom.b510.hongten.vo.Student;18

19 /**

20 *@authorhongten21 * @created Jun 13, 201622 */

23 public classWriteExcel {24

25 static Logger logger = Logger.getLogger(WriteExcel.class);26

27 public void writeExcel(Listschools) {28 if (schools == null || schools.size() == 0) {29 return;30 }31 FileOutputStream fileOut = null;32 Workbook wb = newHSSFWorkbook();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 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 = newFileOutputStream(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 packagecom.b510.hongten.vo;2

3 importjava.util.ArrayList;4 importjava.util.List;5

6 /**

7 *@authorhongten8 * @created Jun 16, 20169 */

10 public classSchool {11

12 private intno;13 privateString name;14 privateString desc;15 privateString ranking;16 privateString addrss;17

18 private List students = new ArrayList();19

20 public intgetNo() {21 returnno;22 }23

24 public void setNo(intno) {25 this.no =no;26 }27

28 publicString getName() {29 returnname;30 }31

32 public voidsetName(String name) {33 this.name =name;34 }35

36 publicString getDesc() {37 returndesc;38 }39

40 public voidsetDesc(String desc) {41 this.desc =desc;42 }43

44 publicString getRanking() {45 returnranking;46 }47

48 public voidsetRanking(String ranking) {49 this.ranking =ranking;50 }51

52 publicString getAddrss() {53 returnaddrss;54 }55

56 public voidsetAddrss(String addrss) {57 this.addrss =addrss;58 }59

60 public ListgetStudents() {61 returnstudents;62 }63

64 public void setStudents(Liststudents) {65 this.students =students;66 }67

68 }

/ExcelHandler/src/com/b510/hongten/vo/Student.java

1 packagecom.b510.hongten.vo;2

3 /**

4 *@authorhongten5 * @created Jun 16, 20166 */

7 public classStudent {8

9 private intstudentNo;10 privateString name;11 privateString address;12 privateString birthdate;13 privateString note;14 privateString phone;15

16 public intgetStudentNo() {17 returnstudentNo;18 }19

20 public void setStudentNo(intstudentNo) {21 this.studentNo =studentNo;22 }23

24 publicString getName() {25 returnname;26 }27

28 public voidsetName(String name) {29 this.name =name;30 }31

32 publicString getAddress() {33 returnaddress;34 }35

36 public voidsetAddress(String address) {37 this.address =address;38 }39

40 publicString getBirthdate() {41 returnbirthdate;42 }43

44 public voidsetBirthdate(String birthdate) {45 this.birthdate =birthdate;46 }47

48 publicString getNote() {49 returnnote;50 }51

52 public voidsetNote(String note) {53 this.note =note;54 }55

56 publicString getPhone() {57 returnphone;58 }59

60 public voidsetPhone(String phone) {61 this.phone =phone;62 }63

64 }

/ExcelHandler/src/log4j.xml

1 <?xml version="1.0" encoding="UTF-8"?>

2

3

4 xmlns:log4j='http://jakarta.apache.org/log4j/'>

5

6

7

8

9 value="%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n" />

10

11

12

13

14

15

16

17

源码下载：

测试数据下载：

========================================================

More reading,and english is important.

I'm Hongten

大哥哥大姐姐，觉得有用打赏点哦！多多少少没关系，一分也是对我的支持和鼓励。谢谢。

Hongten博客排名在100名以内。粉丝过千。

Hongten出品，必是精品。

E | hongt[email protected]  B | http://www.cnblogs.com/hongten

========================================================