Excel 檔案匯入是企業系統常見需求。本文提供完整實作:通用檔案上傳元件 (支援副檔名/大小驗證、GUID 重命名)、Excel 讀取轉 DataTable (使用 `XmlExcelReport` 或 `EPPlus`)、GridView 綁定顯示、錯誤處理與使用者回饋。
<asp:FileUpload ID="FileUpload1" runat="server" Accept=".xls,.xlsx" />
<asp:Button ID="btnUpload" runat="server" Text="上傳並匯入" OnClick="btnUpload_Click" />
<asp:Label ID="lblMsg" runat="server" CssClass="text-danger" />
<asp:GridView ID="gvExcel" runat="server" CssClass="table table-bordered" AutoGenerateColumns="true" />
protected void btnUpload_Click(object sender, EventArgs e)
{
if (!FileUpload1.HasFile)
{
lblMsg.Text = "✗ 請先選擇檔案";
return;
}
string newFileName;
string errorMsg = UploadHelper.UpLoadFile(
FileUpload1,
".xls|.xlsx", // 允許副檔名
5 * 1024, // 最大 5MB (單位 KB)
Server.MapPath("~/Uploads/Excel/"), // 儲存路徑
1, // 1=自動命名(GUID), 0=原檔名
out newFileName
);
if (!string.IsNullOrEmpty(errorMsg))
{
lblMsg.Text = "✗ " + errorMsg;
return;
}
lblMsg.Text = "✓ 檔案上傳成功,正在讀取資料...";
try
{
string fullPath = Server.MapPath("~/Uploads/Excel/") + newFileName;
// 讀取 Excel 轉 DataTable (需引用 XmlExcelReport 或 EPPlus)
DataTable dt = ReadExcelToDataTable(fullPath);
if (dt != null && dt.Rows.Count > 0)
{
ViewState["ExcelData"] = dt; // 暫存供分頁/匯出使用
gvExcel.DataSource = dt;
gvExcel.DataBind();
lblMsg.Text = $"✓ 匯入成功,共 {dt.Rows.Count} 筆資料";
}
else
{
lblMsg.Text = "⚠ Excel 檔案無資料或讀取失敗";
}
}
catch (Exception ex)
{
lblMsg.Text = "✗ 匯入失敗:" + ex.Message;
// 記錄完整堆疊追蹤至 Log
}
}
public static class UploadHelper
{
///
/// 通用檔案上傳
///
/// FileUpload 控制項
/// 允許副檔名,以 | 分隔,如 ".jpg|.png|.pdf"
/// 最大檔案大小 (KB)
/// 伺服器實體儲存路徑 (需以 \ 結尾)
/// 1=GUID重命名, 0=原檔名
/// 輸出:實際儲存的檔名
/// 錯誤訊息,null 或空字串代表成功
public static string UpLoadFile(FileUpload fu, string allowedExtensions, int maxSizeKb, string savePath, int nameType, out string newFileName)
{
newFileName = null;
// 1. 檢查檔案大小
int fileSizeKb = fu.PostedFile.ContentLength / 1024;
if (fileSizeKb > maxSizeKb)
return $"檔案過大 ({fileSizeKb} KB),限制 {maxSizeKb} KB";
// 2. 檢查副檔名
string ext = Path.GetExtension(fu.FileName).ToUpper();
if (string.IsNullOrEmpty(ext)) return "無法識別副檔名";
string[] allowed = allowedExtensions.ToUpper().Split('|');
if (!allowed.Contains(ext)) return $"副檔名 {ext} 不在允許清單中";
// 3. 產生儲存檔名
string fileName;
if (nameType == 1) // GUID 重命名
{
string timestamp = DateTime.Now.ToString("yyyyMMddHHmmssfff");
int rand = new Random().Next(1000, 9999);
fileName = $"{timestamp}_{rand}{ext}";
newFileName = fileName;
}
else // 原檔名
{
fileName = fu.FileName;
newFileName = fileName;
if (File.Exists(Path.Combine(savePath, fileName)))
return "檔案已存在,請重新命名或啟用自動命名";
}
// 4. 確保目錄存在
if (!Directory.Exists(savePath)) Directory.CreateDirectory(savePath);
// 5. 儲存檔案
try
{
fu.SaveAs(Path.Combine(savePath, fileName));
return null; // 成功
}
catch (Exception ex)
{
return "儲存失敗:" + ex.Message;
}
}
}
NuGet 安裝:`Install-Package EPPlus`
using OfficeOpenXml;
using System.Data;
public static DataTable ReadExcelToDataTable(string filePath, int sheetIndex = 0, int headerRow = 1)
{
FileInfo file = new FileInfo(filePath);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet sheet = package.Workbook.Worksheets[sheetIndex];
if (sheet == null) return null;
DataTable dt = new DataTable();
int colCount = sheet.Dimension.End.Column;
int rowCount = sheet.Dimension.End.Row;
// 建立欄位 (使用標題列)
for (int col = 1; col <= colCount; col++)
{
string colName = sheet.Cells[headerRow, col].Text.Trim();
if (string.IsNullOrEmpty(colName)) colName = $"Column{col}";
dt.Columns.Add(colName);
}
// 讀取資料列
for (int row = headerRow + 1; row <= rowCount; row++)
{
DataRow dr = dt.NewRow();
bool hasData = false;
for (int col = 1; col <= colCount; col++)
{
dr[col - 1] = sheet.Cells[row, col].Text;
if (!string.IsNullOrWhiteSpace(dr[col - 1].ToString())) hasData = true;
}
if (hasData) dt.Rows.Add(dr);
}
return dt;
}
}
NuGet:`Install-Package NPOI`
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel; // .xls
using NPOI.XSSF.UserModel; // .xlsx
public static DataTable ReadExcelNPOI(string filePath, int sheetIndex = 0)
{
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = filePath.EndsWith(".xlsx")
? (IWorkbook)new XSSFWorkbook(fs)
: new HSSFWorkbook(fs);
ISheet sheet = workbook.GetSheetAt(sheetIndex);
if (sheet == null) return null;
DataTable dt = new DataTable();
IRow headerRow = sheet.GetRow(0);
if (headerRow != null)
{
foreach (ICell cell in headerRow)
dt.Columns.Add(cell.ToString());
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null) continue;
DataRow dr = dt.NewRow();
bool hasData = false;
for (int c = 0; c < dt.Columns.Count; c++)
{
ICell cell = row.GetCell(c);
dr[c] = cell?.ToString() ?? "";
if (!string.IsNullOrWhiteSpace(dr[c].ToString())) hasData = true;
}
if (hasData) dt.Rows.Add(dr);
}
return dt;
}
}
protected void gvExcel_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvExcel.PageIndex = e.NewPageIndex;
gvExcel.DataSource = ViewState["ExcelData"] as DataTable;
gvExcel.DataBind();
}
List errors = new List();
foreach (DataRow row in dt.Rows)
{
if (string.IsNullOrWhiteSpace(row["必要欄位"].ToString()))
errors.Add($"第 {dt.Rows.IndexOf(row)+1} 列:必要欄位不可為空");
// 更多驗證規則...
}
if (errors.Count > 0)
{
// 顯示錯誤清單或匯出錯誤報表
gvErrors.DataSource = errors.Select((e, i) => new { Row = i+1, Error = e });
gvErrors.DataBind();
}
總結:ASP.NET Excel 匯入核心流程:**通用上傳元件驗證/儲存 → EPPlus/NPOI 讀取轉 DataTable → GridView 綁定顯示 → 資料驗證/入庫**。關鍵在於上傳元件的健壯性 (大小、副檔名、重命名)、Excel 讀取庫的選擇 (EPPlus 首選)、以及異常處理與資源釋放。本文提供的 `UploadHelper` 與 `ReadExcelToDataTable` 可直接整合至專案中使用。