常見需求:使用者上傳 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);
}
}
| 問題 | 原因 | 解決 |
|---|---|---|
| 「找不到可安裝的 ISAM」 | ACE Driver 未安裝或位數不符 | 安裝對應位數 ACE Driver |
| 數字欄位讀成 null | Excel 前 8 列混雜文字/數字,IMEX=1 強制文字 | 連接字串加 `IMEX=1`,或預先格式化 Excel |
| 大量資料記憶體飆高 | DataTable 全載入記憶體 | 改用 `IDataReader` 逐列 `SqlBulkCopy.WriteToServer(reader)` |
| 日期格式錯誤 | Excel 儲存為數值序號 | 讀取後 `DateTime.FromOADate(val)` 轉換 |