ASP.NET 將 Excel 匯入資料庫：OleDb 讀取 .xls/.xlsx 批次寫入 SQL Server 完整實作

整體流程

1. 前端 FileUpload 上傳 Excel 檔 (.xls/.xlsx)
2. 後端儲存暫存檔 → 用 OleDb 連接 Excel 當作資料來源讀取
3. 逐列驗證 → 使用 SqlBulkCopy 或參數化 INSERT 批次寫入 SQL Server
4. 交易控制：全成功 Commit、失敗 Rollback
5. 刪除暫存檔、回傳匯入筆數

前端頁面

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ImportExcel.aspx.cs" Inherits="Demo.ImportExcel" %>
<html>
<head>
    <script type="text/javascript">
        function checkFile() {
            var fu = document.getElementById('<%= fuExcel.ClientID %>');
            if (!fu.value) { alert('請選擇檔案'); return false; }
            var ext = fu.value.split('.').pop().toLowerCase();
            if (ext !== 'xls' && ext !== 'xlsx') { alert('僅支援 .xls 或 .xlsx'); return false; }
            return true;
        }
    </script>
</head>
<body>
    <form id="form1" runat="server" enctype="multipart/form-data">
        <asp:FileUpload ID="fuExcel" runat="server" />
        <asp:Button ID="btnImport" runat="server" Text="匯入" OnClientClick="return checkFile()" OnClick="btnImport_Click" />
        <asp:Label ID="lblMsg" runat="server" ForeColor="Green" />
    </form>
</body>
</html>

後端核心程式碼

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Web.UI;

public partial class ImportExcel : Page
{
    private const string TempFolder = "~/App_Data/TempExcel/";

    protected void btnImport_Click(object sender, EventArgs e)
    {
        if (!fuExcel.HasFile) { ShowMsg("請選擇檔案"); return; }

        string fileName = Path.GetFileName(fuExcel.FileName);
        string savePath = Server.MapPath(TempFolder);
        string fullPath = Path.Combine(savePath, fileName);

        // 確保目錄存在
        if (!Directory.Exists(savePath)) Directory.CreateDirectory(savePath);
        // 覆寫同名檔
        if (File.Exists(fullPath)) File.Delete(fullPath);

        fuExcel.SaveAs(fullPath);

        try
        {
            int imported = ImportExcelToDb(fullPath);
            ShowMsg($"匯入成功：{imported} 筆");
        }
        catch (Exception ex)
        {
            ShowMsg("匯入失敗：" + ex.Message);
        }
        finally
        {
            // 清理暫存檔
            if (File.Exists(fullPath)) File.Delete(fullPath);
        }
    }

    private int ImportExcelToDb(string filePath)
    {
        // 1. 建立 OleDb 連接字串
        string ext = Path.GetExtension(filePath).ToLower();
        string connStr = ext == ".xlsx"
            ? $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'"
            : $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";

        // HDR=YES：第一列為標題；IMEX=1：混合型別欄位以文字讀取，避免數字欄位讀空值

        using (var oleConn = new OleDbConnection(connStr))
        {
            oleConn.Open();
            // 取得第一個工作表名稱
            var schema = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheetName = schema.Rows[0]["TABLE_NAME"].ToString(); // 如 [Sheet1$]

            // 2. 讀取資料到 DataTable
            string query = $"SELECT * FROM [{sheetName}]";
            using (var adapter = new OleDbDataAdapter(query, oleConn))
            {
                var dt = new DataTable();
                adapter.Fill(dt);

                if (dt.Rows.Count == 0) return 0;

                // 3. 批次寫入 SQL Server (使用 SqlBulkCopy 高效能)
                return BulkInsertToSql(dt);
            }
        }
    }

    private int BulkInsertToSql(DataTable dt)
    {
        string sqlConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        using (var sqlConn = new SqlConnection(sqlConnStr))
        {
            sqlConn.Open();
            using (var tran = sqlConn.BeginTransaction())
            using (var bulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, tran))
            {
                bulkCopy.DestinationTableName = "dbo.ImportData"; // 目標資料表
                bulkCopy.BatchSize = 500;
                bulkCopy.BulkCopyTimeout = 300;

                // 欄位對應：Excel 欄位名 -> 資料表欄位名
                bulkCopy.ColumnMappings.Add("姓名", "Name");
                bulkCopy.ColumnMappings.Add("年齡", "Age");
                bulkCopy.ColumnMappings.Add("地址", "Address");
                // 若 Excel 標題與資料表欄位同名，可略過手動對應

                try
                {
                    bulkCopy.WriteToServer(dt);
                    tran.Commit();
                    return dt.Rows.Count;
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }
    }

    private void ShowMsg(string msg)
    {
        lblMsg.Text = msg;
        lblMsg.ForeColor = msg.StartsWith("匯入失敗") ? System.Drawing.Color.Red : System.Drawing.Color.Green;
    }
}

關鍵技術點解析

進階：使用 EPPlus/NPOI 免裝驅動程式

OleDb 需伺服器安裝 Access Database Engine，部署麻煩。改用純 .NET 程式庫：

// EPPlus 範例 (.xlsx)
using (var package = new ExcelPackage(new FileInfo(filePath)))
{
    var ws = package.Workbook.Worksheets[0];
    var dt = new DataTable();
    // 讀標題
    for (int col = 1; col <= ws.Dimension.End.Column; col++)
        dt.Columns.Add(ws.Cells[1, col].Text);
    // 讀資料
    for (int row = 2; row <= ws.Dimension.End.Row; row++)
    {
        var dr = dt.NewRow();
        for (int col = 1; col <= ws.Dimension.End.Column; col++)
            dr[col - 1] = ws.Cells[row, col].Text;
        dt.Rows.Add(dr);
    }
    return BulkInsertToSql(dt); // 同樣用 SqlBulkCopy
}

常見問題與解決

1. 「未在本機電腦上註冊 Microsoft.Jet.OLEDB.4.0 提供者」：IIS 應用程式集區啟用 32 位元應用程式 = True，或改用 ACE 12.0 + 64-bit。
2. 數字欄位讀成 null：加 IMEX=1，或在 Excel 該欄位設為「文字」格式。
3. 大檔案記憶體爆炸：OleDbDataAdapter.Fill 會一次載入所有資料。超過 10 萬列建議用 OleDbDataReader 逐列讀取配合 SqlBulkCopy 分批寫入，或用 EPPlus ws.Cells["A2:Z100000"] 區塊讀取。
4. 日期格式錯誤：Excel 日期為 OLE Automation Date (Double)，讀取後需 DateTime.FromOADate(val) 轉換。

總結

Excel 匯入資料庫標準流程：上傳 → OleDb/EPPlus 讀取 → DataTable → SqlBulkCopy 交易寫入 → 清理暫存檔。掌握連接字串、IMEX=1、Schema 取得 Sheet、SqlBulkCopy 欄位對應與交易控制，即可建立穩健、高效能的匯入功能。生產環境建議改用 EPPlus/NPOI 免除驅動程式相依。