本文提供一個完整的 Maven 專案範例，示範如何使用 Apache POI（HSSF/XSSF）從 MySQL 讀取「學校-學生」關聯資料，生成包含彙總 Sheet與多明細 Sheet的 Excel（.xls）檔案。涵蓋資料庫連線、VO 映射、POI 流式寫入、Sheet 動態創建、安全 Sheet 名稱處理等實務技巧。
ExcelHandler/
├── src/
│ ├── main/
│ │ ├── java/com/b510/hongten/
│ │ │ ├── client/Client.java # 入口
│ │ │ ├── common/Common.java # 常數配置
│ │ │ ├── db/ # DAO 與連線工具
│ │ │ ├── excel/WriteExcel.java # POI 核心寫入邏輯
│ │ │ └── vo/School.java, Student.java
│ │ └── resources/log4j.xml
│ └── test/
├── lib/ # POI、MySQL 驅動、Log4j JAR
└── pom.xml # Maven 依賴管理關鍵依賴（pom.xml）：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version> <!-- 或 4.x/5.x 對應 XSSF -->
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>-- 學校表
CREATE TABLE t_school (
no INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50), `desc` VARCHAR(500), ranking INT, address VARCHAR(200)
);
-- 學生表
CREATE TABLE t_student (
student_no INT, school_no INT,
name VARCHAR(100), address VARCHAR(200), birthdate VARCHAR(15), phone VARCHAR(15),
PRIMARY KEY (student_no, school_no),
FOREIGN KEY (school_no) REFERENCES t_school(no)
);WriteExcel.javapublic class WriteExcel {
private static final Logger log = Logger.getLogger(WriteExcel.class);
public void writeExcel(List schools) {
if (schools == null || schools.isEmpty()) return;
Workbook wb = new HSSFWorkbook(); // .xls 格式；.xlsx 用 XSSFWorkbook
// 1. 彙總 Sheet
Sheet summarySheet = wb.createSheet(Common.SUMMARY); // "School Summary"
summarySheet.setAutobreaks(true);
createHeaderRow(summarySheet, Common.TITLES); // S/N, Name, Description, Ranking, Address
int rowNum = 1;
for (School school : schools) {
Row row = summarySheet.createRow(rowNum++);
fillSchoolRow(row, school);
// 2. 每所學校一個明細 Sheet
List students = school.getStudents();
if (students != null && !students.isEmpty()) {
// 安全 Sheet 名稱：移除非法字元
String safeName = WorkbookUtil.createSafeSheetName(school.getName());
Sheet stuSheet = wb.createSheet(safeName);
stuSheet.setAutobreaks(true);
createHeaderRow(stuSheet, Common.STUDENT_TITLE); // S/N, Name, Birth Date, Phone, Address
int stuRow = 1;
for (Student stu : students) {
Row r = stuSheet.createRow(stuRow++);
fillStudentRow(r, stu);
}
}
}
// 3. 輸出檔案
try (FileOutputStream fos = new FileOutputStream(Common.TARGET_FILE_PATH)) {
wb.write(fos);
log.info("Excel 生成完成：" + Common.TARGET_FILE_PATH);
} catch (IOException e) {
log.error("寫入檔案失敗", e);
}
}
// 輔助方法：建立標題列、填充資料列...
} Workbook → Sheet → Row → Cell，索引皆從 0 開始。wb.createSheet(name)，名稱長度 ≤ 31 字元，不含 [\]:*?/\\。使用 WorkbookUtil.createSafeSheetName() 自動轉義。cell.setCellValue(String/Number/Boolean/Date)，POI 自動推斷 CellType。SXSSFWorkbook（流式寫入，僅保留近期行在記憶體）。CellStyle + DataFormat 統一日期顯示格式。public class SchoolDAO {
public static List getSchools() {
List schools = new ArrayList<>();
try (Connection conn = ConnectionUtil.getConn();
PreparedStatement ps = conn.prepareStatement("SELECT * FROM t_school ORDER BY no")) {
ResultSet rs = ps.executeQuery();
while (rs.next()) {
School school = mapSchool(rs);
// 查詢該校學生
try (PreparedStatement ps2 = conn.prepareStatement("SELECT * FROM t_student WHERE school_no = ?")) {
ps2.setInt(1, school.getNo());
ResultSet rs2 = ps2.executeQuery();
List students = new ArrayList<>();
while (rs2.next()) students.add(mapStudent(rs2));
school.setStudents(students);
}
schools.add(school);
}
} catch (SQLException e) { /* log & handle */ }
return schools;
}
} WorkbookUtil.createSafeSheetName，否則 POI 拋 IllegalArgumentException。XSSFWorkbook) 或 SXSSFWorkbook。cell.setCellType(CellType.NUMERIC) 或寫入 Double 型別。Workbook 實作 Closeable，建議 try-with-resources 管理。Apache POI 提供豐富的 Excel 操作 API，配合合理的 VO 設計與分層架構，可快速構建企業級報表匯出功能。掌握 WorkbookUtil、SXSSF、CellStyle 等進階特性，能應對大數據量、複雜格式、多 Sheet 等實務挑戰。
