JSP 上傳 Excel 並導入數據庫：commons-fileupload + JXL 完整實現流程

技術棧

• 後端：Servlet + JSP + MySQL
• 文件上傳：`commons-fileupload-1.3.1.jar` + `commons-io`
• Excel 解析：`jxl.jar` (僅支持 `.xls`，`.xlsx` 請用 Apache POI)

核心步驟

1. 前端 JSP 上傳表單

<form action="UploadServlet" method="post" enctype="multipart/form-data">
    <input type="file" name="excelFile" accept=".xls,.xlsx" />
    <input type="submit" value="導入" />
</form>

2. Servlet 處理上傳並解析

@WebServlet("/UploadServlet")
@MultipartConfig // Servlet 3.0+ 可替代 commons-fileupload
public class UploadServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        
        // 1. 獲取上傳文件 Part (Servlet 3.0 API)
        Part filePart = request.getPart("excelFile");
        if (filePart == null || filePart.getSize() == 0) {
            response.getWriter().write("未選擇文件");
            return;
        }
        
        // 2. 獲取輸入流
        try (InputStream inputStream = filePart.getInputStream()) {
            // 3. JXL 解析 .xls
            Workbook workbook = Workbook.getWorkbook(inputStream);
            Sheet sheet = workbook.getSheet(0); // 第一個工作表
            
            // 4. 遍歷行 (跳過標題行)
            for (int i = 1; i < sheet.getRows(); i++) {
                Cell[] row = sheet.getRow(i);
                if (row.length < 3) continue; // 列數不足跳過
                
                String col1 = row[0].getContents().trim();
                String col2 = row[1].getContents().trim();
                String col3 = row[2].getContents().trim();
                
                // 5. 插入數據庫 (使用連接池 PreparedStatement)
                saveToDatabase(col1, col2, col3);
            }
            workbook.close();
            response.getWriter().write("導入成功 " + (sheet.getRows()-1) + " 條記錄");
        } catch (Exception e) {
            e.printStackTrace();
            response.getWriter().write("導入失敗: " + e.getMessage());
        }
    }
    
    private void saveToDatabase(String c1, String c2, String c3) {
        // 使用 DataSource / 連接池 (Druid/HikariCP)
        String sql = "INSERT INTO my_table (col1, col2, col3) VALUES (?, ?, ?)";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, c1);
            ps.setString(2, c2);
            ps.setString(3, c3);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

3. 依賴配置 (Maven)

<dependencies>
    <!-- Servlet 3.0+ 內置上傳支持，無需 commons-fileupload -->
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>javax.servlet-api</groupId>
        <version>3.1.0</version>
        <scope>provided</scope>
    </dependency>
    <!-- JXL 僅支持 .xls -->
    <dependency>
        <groupId>net.sourceforge.jexcelapi</groupId>
        <artifactId>jxl</artifactId>
        <version>2.6.12</version>
    </dependency>
    <!-- MySQL 驅動 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
    <!-- 連接池 Druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.18</version>
    </dependency>
</dependencies>

關鍵注意事項

推薦資源

• JXL 官方教程：https://www.open-open.com/lib/view/open1415373154574.html
• Commons FileUpload 使用：https://www.the5fire.com/...
• POI 官方指南：https://poi.apache.org/...