在这之前写过关于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的数据流如下：

My sql 数据库 — > 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 = Connect io nUtil.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='
 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> 

源码下载：

测试数据下载：

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

More reading,and english is important.

I’m Hongten

E | hongtenzone@foxmail.com B |

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