ASP.NET MVC Import Export XLSX Using ClosedXML

說明
參考資料
相關連結

筆記 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>
}

說明

參考資料

相關連結