ASP.NET MVC Import Export XLSX Using ClosedXML

2024-10-24

筆記 ASP.NET 透過 ClosedXML 匯入與匯出 Excel 檔案的資料處理方式。

說明

安裝 ClosedXML 原則上只需要在 Nuget 上搜尋 ClosedXML 並安裝即可。

但要注意的是有時候安裝 ClosedXML 會出現相依元件的錯誤問題，例如 System.Numerics.Vectors，這個時候可以先安裝 System.Numerics.Vectors 再安裝 ClosedXML，有時就能順利解決問題。

實際要匯出 XLSX 相當直覺，步驟上基本是建立一個 Workbook，再建立一個 Worksheet，然後將資料填入 Worksheet 中，

從 Row 的層級建立標題，再依序建立資料，最後將 Workbook 存成 Stream 即可，視情況回傳 FileResult。

using ClosedXML.Excel;

public ActionResult ExportToExcel()
{
    var employees = new List<Employee>
    {
        new Employee { 
          Id = 1, 
          Name = "John Doe", 
          Position = "Software Engineer", 
          Department = "Development", 
          Salary = 60000 },
        new Employee { Id = 2, 
          Name = "Jane Smith", 
          Position = "Project Manager", 
          Department = "Management", 
          Salary = 80000 },
        new Employee { Id = 3, 
          Name = "Samuel Lee", 
          Position = "QA Engineer", 
          Department = "Testing", 
          Salary = 55000 }
    };

    using (var workbook = new XLWorkbook())
    {
        var worksheet = workbook.Worksheets.Add("Employees");
        worksheet.Cell(1, 1).Value = "ID";
        worksheet.Cell(1, 2).Value = "Name";
        worksheet.Cell(1, 3).Value = "Position";
        worksheet.Cell(1, 4).Value = "Department";
        worksheet.Cell(1, 5).Value = "Salary";

        for (int i = 0; i < employees.Count; i++)
        {
            worksheet.Cell(i + 2, 1).Value = employees[i].Id;
            worksheet.Cell(i + 2, 2).Value = employees[i].Name;
            worksheet.Cell(i + 2, 3).Value = employees[i].Position;
            worksheet.Cell(i + 2, 4).Value = employees[i].Department;
            worksheet.Cell(i + 2, 5).Value = employees[i].Salary;
        }

        using (var stream = new MemoryStream())
        {
            workbook.SaveAs(stream);
            stream.Position = 0;
            return File(
              stream.ToArray(), 
              "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", 
              "Employees.xlsx");
        }
    }
}

匯入 XLSX 透過使用者上傳檔案，接著使用 InputStream 讀取檔案，再透過 Workbook 及 Worksheet 讀取檔案資料。

[HttpPost]
public ActionResult Upload(HttpPostedFileBase file)
{
    if (file != null && file.ContentLength > 0 && (file.FileName.EndsWith(".xlsx")))
    {
        var eventCases = new List<Case>();

        using (var stream = file.InputStream)
        {
            using (var workbook = new XLWorkbook(stream))
            {
                var worksheet = workbook.Worksheet(1);
                var rows = worksheet.RowsUsed();

                // 🦐 處理資料 🐠
            }
        }

        return View(eventCases);
    }

    throw new Exception("Invalid file format");
}

實際的資料處理就是逐行的資料型別，透過 Cell 取得資料，再透過 GetString 取得字串，

需要注意的是日期時間的處理，因為 Excel 的日期時間格式可能不同，所以需要透過 TryParseExact 來解析 😃

在渲染的 View 上，會將使用者上傳的 Excel 轉換為多筆的資料 Input

foreach (var row in rows.Skip(1)) // Skip header row
{
    string.Join(",", row.Cells().Select(c => c.GetString()));

    var eventCase = new Case
    {
        Topic = row.Cell(3).GetString().Trim(),
        Cause = row.Cell(4).GetString().Trim(),
        Improvement = row.Cell(5).GetString().Trim(),
        Result = row.Cell(6).GetString().Trim(),
    };

    // 日期資料處理 📅
    string dateString = row.Cell(1).GetString();
    DateTime parsedDate;
    if (!DateTime.TryParseExact(
        dateString, "yyyy/M/d tt hh:mm:ss", new CultureInfo("zh-TW"), DateTimeStyles.None, out parsedDate))
    {
        parsedDate = DateTime.MinValue;
    }
    eventCase.Date = parsedDate.Date;

    // 時間資料處理 🕘
    string timeString = row.Cell(2).GetString();
    DateTime parsedTime;
    if (!DateTime.TryParseExact(
        timeString, "yyyy/MM/dd tt hh:mm:ss", new CultureInfo("zh-TW"), DateTimeStyles.None, out parsedTime))
    {
        parsedTime = DateTime.MinValue;
    }
    TimeSpan timeSpan = parsedTime.TimeOfDay;
    eventCase.Time = timeSpan;

    eventCases.Add(eventCase);
}

另外一支 Action 則是將使用者確認上傳的資料送回後端進行批次的新增。

[HttpPost]
public ActionResult BulkInsert(IEnumerable<Case> model)
{
    int count = 0;
    foreach (var item in model)
    {
        item.EditDateTime = DateTime.Now;

        try
        {
            db.Cases.Add(item);
            count++;
        }
        catch (Exception)
        {
            // write to log insert exception
        }
    }
    db.SaveChanges();

    TempData["toast"] = $"批次新增 {count} 筆資料完成 😃";
    return RedirectToAction("Index", "Case");
}

View 要呈現使用者上傳的資料，提供 Edit 的功能對使用者更友善：

@for (int i = 0; i < Model.Count(); i++)
{
    var item = Model.ElementAt(i);
    <tr>
        <td style="width:7%">
            <input type="date" name="[@i].Date" value="@item.Date.Value.ToString("yyyy-MM-dd")" />
        </td>
        <td style="width:8%">
            <input type="time" name="[@i].Time" value="@item.Time" />
        </td>
        <td style="width:15%">
            <textarea name="[@i].Topic" class="form-control" cols=10 rows="5">@item.Topic</textarea>
        </td>
        <td style="width:15%">
            <textarea name="[@i].Cause" class="form-control" cols=10 rows="5">@item.Cause</textarea>
        </td>
        <td style="width:15%">
            <textarea name="[@i].Improvement" class="form-control" cols=10 rows="5">@item.Improvement</textarea>
        </td>
        <td style="width:15%">
            <textarea name="[@i].Result" class="form-control" cols=10 rows="5">@item.Result</textarea>
        </td>
        <td style="width:15%">
            <textarea name="[@i].Report" class="form-control" cols=10 rows="5">@item.Report</textarea>
        </td>
        <td style="width:10%">
            <input type="text" class="form-control" name="[@i].Dep1" value="@item.Dep1" />
        </td>
    </tr>
}

參考資料

相關連結

ASP.NET MVC 從無到有打造一個應用系統

Visual Studio 入門教學