萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP.NET 程式設計

C# 將 Excel 資料匯入 SQL Server 資料庫完整實作 (OLEDB + SqlBulkCopy)

常見需求:使用者上傳 Excel (.xls/.xlsx),後端讀取並批次寫入資料庫。本文示範 Microsoft.ACE.OLEDB.12.0 讀取 Excel 轉 DataTable,再以 SqlBulkCopy 高效批次寫入 SQL Server。

資料表結構對應

Excel 欄位:LNG, LAT, OFFSET_LNG, OFFSET_LAT
資料表 DBToExcel:Id (PK, Identity), LNG, LAT, OFFSET_LNG, OFFSET_LAT

核心類別

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

public class ExcelImporter
{
    private readonly string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

    /// <summary> 讀取 Excel 第一個工作表轉 DataTable </summary>
    public DataTable ReadExcel(string filePath)
    {
        string ext = Path.GetExtension(filePath).ToLower();
        string provider = ext == ".xls" 
            ? "Microsoft.Jet.OLEDB.4.0" 
            : "Microsoft.ACE.OLEDB.12.0";
        string connStr = $@"Provider={provider};Data Source={filePath};Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";

        using (var conn = new OleDbConnection(connStr))
        {
            conn.Open();
            var schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheet = schema.Rows[0]["TABLE_NAME"].ToString(); // 第一個 Sheet

            string sql = $"SELECT * FROM [{sheet}]";
            using (var da = new OleDbDataAdapter(sql, conn))
            {
                var dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }
    }

    /// <summary> SqlBulkCopy 批次寫入 </summary>
    public int BulkInsert(DataTable dt, string tableName, string[] columnMappings)
    {
        using (var sqlConn = new SqlConnection(_connStr))
        using (var bulk = new SqlBulkCopy(sqlConn))
        {
            bulk.DestinationTableName = tableName;
            bulk.BatchSize = 5000;
            bulk.BulkCopyTimeout = 300;

            foreach (var map in columnMappings)
            {
                var parts = map.Split(':');
                bulk.ColumnMappings.Add(parts[0], parts[1]); // Excel欄:DB欄
            }

            sqlConn.Open();
            bulk.WriteToServer(dt);
            return dt.Rows.Count;
        }
    }
}

呼叫範例

protected void BtnImport_Click(object sender, EventArgs e)
{
    if (!fuExcel.HasFile) return;
    string ext = Path.GetExtension(fuExcel.FileName).ToLower();
    if (ext != ".xls" && ext != ".xlsx") { ShowAlert("格式錯誤"); return; }

    string savePath = Server.MapPath($"~/Uploads/{Guid.NewGuid()}{ext}");
    fuExcel.SaveAs(savePath);

    try
    {
        var importer = new ExcelImporter();
        DataTable dt = importer.ReadExcel(savePath);

        // 欄位對應:Excel欄位名:資料庫欄位名
        string[] maps = { "LNG:LNG", "LAT:LAT", "OFFSET_LNG:OFFSET_LNG", "OFFSET_LAT:OFFSET_LAT" };
        int count = importer.BulkInsert(dt, "DBToExcel", maps);

        ShowAlert($"匯入成功 {count} 筆");
    }
    catch (Exception ex)
    {
        ShowAlert("匯入失敗:" + ex.Message);
    }
    finally
    {
        if (File.Exists(savePath)) File.Delete(savePath);
    }
}

部署環境需求

  • 伺服器安裝 Microsoft Access Database Engine 2010/2016 Redistributable** (含 ACE.OLEDB.12.0)。
  • IIS 應用程式集區:**啟用 32 位元應用程式 = True** (若安裝 32 位 ACE);或安裝 64 位 ACE 並關閉 32 位模式。
  • 權限:應用程式集區身分需具備上傳資料夾寫入權限、資料庫寫入權限。

避坑指南

問題原因解決
「找不到可安裝的 ISAM」ACE Driver 未安裝或位數不符安裝對應位數 ACE Driver
數字欄位讀成 nullExcel 前 8 列混雜文字/數字,IMEX=1 強制文字連接字串加 `IMEX=1`,或預先格式化 Excel
大量資料記憶體飆高DataTable 全載入記憶體改用 `IDataReader` 逐列 `SqlBulkCopy.WriteToServer(reader)`
日期格式錯誤Excel 儲存為數值序號讀取後 `DateTime.FromOADate(val)` 轉換
copyright © 萬盛學電腦網 all rights reserved