JXL (Java Excel API) 是輕量級的 Excel 操作庫，雖然僅支援舊版 .xls 格式，但在維護舊系統、簡單報表導出場景仍具價值。本文提供完整的 Java 專案與 Web 專案雙重實戰代碼，涵蓋讀取、寫入、樣式設定、合併儲存格等核心功能。
package servlet;
import jxl.*;
import jxl.write.*;
import jxl.format.*;
import java.io.*;
import java.util.*;
public class Excel {
// 寫入 Excel
public static void writeExcel() throws Exception {
String[] title = {"編號", "產品名稱", "產品價格", "產品數量", "生產日期", "產地", "是否出口"};
String filePath = "e:\\testJXL.xls";
WritableWorkbook wwb = Workbook.createWorkbook(new FileOutputStream(filePath));
WritableSheet sheet = wwb.createSheet("產品清單", 0);
// 寫入表頭（帶樣式）
for (int i = 0; i < title.length; i++) {
sheet.addCell(new Label(i, 0, title[i], getHeader()));
}
// 寫入資料行（示範多種資料類型）
sheet.addCell(new jxl.write.Number(0, 1, 20071001)); // 數字
sheet.addCell(new Label(1, 1, "金鴿瓜子")); // 字串
sheet.addCell(new jxl.write.Number(2, 1, 200000.45, priceFormat())); // 金額格式
sheet.addCell(new jxl.write.Number(3, 1, 200)); // 數量
sheet.addCell(new Label(4, 1, new SimpleDateFormat("yyyy-MM-dd").format(new Date()))); // 日期
sheet.addCell(new Label(5, 1, "陝西西安")); // 字串
sheet.addCell(new jxl.write.Boolean(6, 1, true)); // 布林值
// 合併儲存格
sheet.mergeCells(0, 3, 2, 3);
sheet.addCell(new Label(0, 3, "合併了三個儲存格"));
// 自定義字體
WritableFont font = new WritableFont(WritableFont.createFont("隸書"), 20);
sheet.addCell(new Label(2, 6, "隸書", new WritableCellFormat(font)));
wwb.write();
wwb.close();
}
// 讀取 Excel
public static void readExcel() throws Exception {
Workbook rwb = Workbook.getWorkbook(new FileInputStream("d:\\testJXL.xls"));
Sheet sheet = rwb.getSheet(0);
List data = new ArrayList<>();
for (int i = 0; i < sheet.getRows(); i++) {
String[] row = new String[sheet.getColumns()];
for (int j = 0; j < sheet.getColumns(); j++) {
row[j] = sheet.getCell(j, i).getContents();
}
data.add(row);
}
// 處理資料...
rwb.close();
}
// 表頭樣式：藍字、黃底、黑框、置中
private static WritableCellFormat getHeader() throws WriteException {
WritableFont font = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);
font.setColour(Colour.BLUE);
WritableCellFormat format = new WritableCellFormat(font);
format.setAlignment(Alignment.CENTRE);
format.setVerticalAlignment(VerticalAlignment.CENTRE);
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
format.setBackground(Colour.YELLOW);
return format;
}
// 價格格式：千分位、兩位小數
private static WritableCellFormat priceFormat() throws WriteException {
NumberFormat nf = new NumberFormat("#,###.00");
return new WritableCellFormat(nf);
}
}
引入 jxl-2.6.jar 與 servlet-api.jar。
package entity;
import java.util.Date;
import java.io.Serializable;
public class Member implements Serializable {
private String id, checkOrg, sn, memberName, sex, cardId;
private String duty, title, academic, special, workTime, memo, role;
private Date lastModify, regTime;
// 建構子、Getter/Setter 略...
}
package servlet;
import entity.Member;
import jxl.*;
import jxl.write.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
public class ExportExlServlet extends HttpServlet {
protected void service(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
String orgName = "XX單位";
List list = fetchFromDB(); // 實際從資料庫查詢
exportExcel(resp, list, orgName + "人員明細表");
}
private void exportExcel(HttpServletResponse resp, List list, String fileName)
throws IOException, WriteException {
resp.setContentType("application/ms-excel");
resp.addHeader("Content-Disposition", "attachment;filename="
+ new String(fileName.getBytes(), "iso8859-1") + ".xls");
WritableWorkbook wwb = Workbook.createWorkbook(resp.getOutputStream());
WritableSheet sheet = wwb.createSheet("人員明細", 0);
// 表頭
String[] titles = {"編號", "姓名", "性別", "身份證號", "學歷", "專業", "職稱", "職務", "角色", "備註"};
for (int i = 0; i < titles.length; i++) {
sheet.addCell(new Label(i, 0, titles[i], Excel.getHeader()));
}
// 資料行
WritableCellFormat dataFormat = createDataFormat();
WritableFont font = new WritableFont(WritableFont.createFont("隸書"), 20);
WritableCellFormat specialFormat = new WritableCellFormat(font);
int row = 1;
for (Member m : list) {
sheet.addCell(new Label(0, row, String.valueOf(row), dataFormat));
sheet.addCell(new Label(1, row, m.getMemberName(), dataFormat));
sheet.addCell(new Label(2, row, m.getSex(), dataFormat));
sheet.addCell(new Label(3, row, m.getCardId(), dataFormat));
sheet.addCell(new Label(4, row, m.getAcademic(), dataFormat));
sheet.addCell(new Label(5, row, m.getSpecial(), dataFormat));
sheet.addCell(new Label(6, row, m.getTitle(), dataFormat));
sheet.addCell(new Label(7, row, m.getDuty(), dataFormat));
sheet.addCell(new Label(8, row, m.getRole(), dataFormat));
sheet.addCell(new Label(9, row, m.getMemo(), specialFormat)); // 最後一欄特殊字體
row++;
}
wwb.write();
wwb.close();
}
private WritableCellFormat createDataFormat() throws WriteException {
WritableFont wf = new WritableFont(WritableFont.ARIAL, 13);
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
return wcf;
}
private List fetchFromDB() {
// 模擬資料，實際替換為 DAO 查詢
List list = new ArrayList<>();
Member m = new Member();
m.setMemberName("張三"); m.setSex("男"); m.setCardId("123456");
m.setAcademic("碩士"); m.setSpecial("計算機"); m.setTitle("工程師");
m.setDuty("開發"); m.setRole("Admin"); m.setMemo("備註");
list.add(m); list.add(m); list.add(m);
return list;
}
}
<servlet>
<servlet-name>ExportExlServlet</servlet-name>
<servlet-class>servlet.ExportExlServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ExportExlServlet</servlet-name>
<url-pattern>/ExportExlServlet</url-pattern>
</servlet-mapping>
<body>
<a href="ExportExlServlet">導出人員明細 Excel</a>
</body>
完整專案原始碼託管於 GitHub：https://github.com/ablejava/Jxl-Excel
注意：JXL 專案已停止維護，不支援 .xlsx 格式。新專案建議使用 Apache POI (支援 .xls/.xlsx) 或 EasyExcel (阿里巴巴開源、記憶體效率高)。
