技術文章 » .Net Core 如何資料匯出 Excel？（EPPlus->OfficeOpenXml 實現固定列和動態列匯出）

.Net Core 如何資料匯出 Excel？（EPPlus->OfficeOpenXml 實現固定列和動態列匯出）

2023-07-07 18:00:36

〇、前言

對於將資料以 Excel 表格檔案輸出，還是比較常用的，也存在諸多情況，比如列固定或不固定、資料型別為 List<T>或 Json 物件等。

本文通過包 OfficeOpenXml.Extension.AspNetCore 實現兩個方法匯出列數固定和不固定的資料。

注意：OfficeOpenXml.Extension.AspNetCore 是一個基於 OfficeOpenXml 拓展，它依賴於 EPPlus，用於根據模板輸出 Excel。

包控制檯安裝：

NuGet> Install-Package OfficeOpenXml.Extension.AspNetCore -Version 1.0.0

一、根據已知型別物件 List 下載

本章節方法適用背景：

  資料列數固定，且可羅列。

對於物件 List<T> 的屬性，一般不會命名為漢字，那麼就需要將列名轉換為漢字，以方便資料清晰顯示。

如下為一個基於 WebAPI 專案的固定列數，動態行數的下載範例：

// 安裝包：OfficeOpenXml.Extension.AspNetCore // 支援 Core 3.1 及以上，Standard 2.0 及以上
using OfficeOpenXml;
using OfficeOpenXml.Style;
using Microsoft.AspNetCore.Mvc; // File 支援

[HttpGet]
public FileContentResult DownloadInfo()
{
    try
    {
        string sql_datalist = "";
        var resultlist = dapperFactory.Query<ShujuMXModel>(sql_datalist).ToList();
        string sFileName = $"{Guid.NewGuid()}.xlsx";
        FileInfo fileinfo = new FileInfo(System.IO.Path.Combine("C:/Files", sFileName));
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using (ExcelPackage package = new ExcelPackage(fileinfo))
        {
            // 新增 worksheet
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("明細表");
            // 新增頭
            worksheet.Cells[1, 1].Value = "序號";
            worksheet.Cells[1, 2].Value = "公司";
            worksheet.Cells[1, 3].Value = "日期";
            // 新增值
            for (int i = 0; i < resultlist.Count; i++)
            {
                worksheet.Cells["A" + (i + 2)].Value = resultlist[i].xuhao;
                worksheet.Cells["B" + (i + 2)].Value = resultlist[i].gongsimc;
                worksheet.Cells["C" + (i + 2)].Value = resultlist[i].riqi.Substring(0,10);
            }
            worksheet.Column(1).Width = 10; // 設定列寬，從第 1 列開始
            worksheet.Column(2).Width = 30;
            worksheet.Column(3).Width = 15;
            worksheet.Row(1).Style.Font.Bold = true; // 給第一行內容設定加粗
            worksheet.Cells.Style.WrapText = true; // 自動換行
            worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
            // 給有資料的區域新增邊框
            using (ExcelRange excelRange = worksheet.Cells[1, 1, resultlist.Count + 1, 3]) // [從第一行開始，從第一列開始，到第幾行，到第幾列]
            {
                r.Style.Border.Top.Style = ExcelBorderStyle.Thin; // 實線
                r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black); // 黑色
                r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
                r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
                r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
            }
            worksheet.Row(1).Style.Font.Bold = true;
            worksheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // 第一行內容水平居中
            package.Save();
        }
        using (FileStream fs= fileinfo.OpenRead())
        {
            BinaryReader br = new BinaryReader(fs);
            br.BaseStream.Seek(0, SeekOrigin.Begin); // 將檔案指標設定到檔案開
            byte[] fileBytes = br.ReadBytes((int)br.BaseStream.Length);
            return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, sFileName); // 返回檔案物件，前端可直接進行下載動作
        }
    }
    catch (Exception ex)
    {
        return null;
    }
}

 二、動態列資料庫下載

本章節方法適用背景：

  資料列數不固定，且可迴圈取出，表頭和對應的資料順序相同。

動態列就是，列總數不固定，程式根據傳入的列數確定第一行表頭。

下面是一個根據 json 字串，以 Excel 檔案形式儲存資料的範例。其中包含 「tableheader」節點，來指定動態表頭；「tablebody」代表全部資料列表。

// 安裝包：OfficeOpenXml.Extension.AspNetCore // 支援 Core 3.1 及以上，Standard 2.0 及以上
// 由於 Excel 2003 版本 和 2007 之後版本檔案結構的差異性，當前擴充套件無法同時相容兩種模式，僅支援 *.xlsx 檔案
using OfficeOpenXml;
using OfficeOpenXml.Style;

public void DownloadByJsonstr(string xiazaisj)
{
    // string jsonstr = "{\"tableheader\":[{\"mingcheng\":\"列名一\",\"daima\":\"bumenx1\",\"shifoutz\":true},{\"mingcheng\":\"列名二\",\"daima\":\"bumenx2\",\"shifoutz\":true}],\"tablebody\":[{\"kemumc\":\"科目一\",\"bumenx1\":0.12,\"bumenx2\":6.0,\"heji\":6.12,\"erjimx\":[{\"kemumc\":\"科目一明細科目1\",\"bumenx1\":0.0,\"bumenx2\":9.82,\"heji\":9.82},{\"kemumc\":\"科目一明細科目2\",\"bumenx1\":0.12,\"bumenx2\":6.18,\"heji\":6.3}]}]}";
    var baobiaosj_json = Json_Object.StrToJson(xiazaisj);
    var tableheader = xiazaisj_json["tableheader"];
    // 前兩列表頭固定
    List<string> headersname = new List<string>(){ "科目", "合計" }; // 用於顯示
    List<string> headerscode = new List<string>(){ "kemumc", "heji" }; // 用於取值
    // 從第三列開始，按預設順序加入後續表頭
    foreach (var thj in tableheader)
    {
        headersname.Add(thj["mingcheng"].ToString());
        headerscode.Add(thj["daima"].ToString());
    }
    string sFileName = $"{Guid.NewGuid()}.xlsx";
    FileInfo fileinfo = new FileInfo(System.IO.Path.Combine("C:/Log", sFileName));
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    List<TableRowModel> tableRowModels = new List<TableRowModel>();
    using (ExcelPackage package = new ExcelPackage(fileinfo))
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("資料表");
        EpplusHelper.AddHeader(worksheet, headersname.ToArray()); // 新增表頭
        var tablebody = xiazaisj_json["tablebody"];
        TableRowModel tableRowModel = new TableRowModel();
        Dictionary<string, string> keyValuePairs = new Dictionary<string, string>();
        foreach (var tablebody_first in tablebody)
        {
            tableRowModel = new TableRowModel();
            keyValuePairs = new Dictionary<string, string>();
            tableRowModel.kemumc = tablebody_first["kemumc"].ToString(); // "kemumc": "成本"
            tableRowModel.heji = tablebody_first["heji"].ToString();
            for (int ii = 2; ii < headerscode.Count; ii++)
            {
                keyValuePairs.Add(headerscode[ii], tablebody_first[headerscode[ii]].ToString()); // "bumenx1": 0.0
            }
            tableRowModel.dict_lie = keyValuePairs;
            tableRowModels.Add(tableRowModel);
            if (tablebody_first["erjimx"] != null)
            {
                foreach(var tablebody_second in tablebody_first["erjimx"])
                {
                    tableRowModel = new TableRowModel();
                    keyValuePairs = new Dictionary<string, string>();
                    tableRowModel.kemumc = tablebody_second["kemumc"].ToString(); // "kemumc": "成本"
                    tableRowModel.heji = tablebody_second["heji"].ToString();
                    for (int ii = 2; ii < headerscode.Count; ii++)
                    {
                        keyValuePairs.Add(headerscode[ii], tablebody_second[headerscode[ii]].ToString()); // "bumenx1": 0.0
                    }
                    tableRowModel.dict_lie = keyValuePairs;
                    tableRowModels.Add(tableRowModel);
                }
            }
        }
        if (tableRowModels.Count > 0)
        {
            //新增動態資料
            EpplusHelper.AddObjects(worksheet, 2, tableRowModels, headerscode);
        }
        worksheet.Column(1).Width = 20; // 設定列寬
        worksheet.Column(2).Width = 20;
        for (int ii = 3; ii <= headerscode.Count; ii++) // 為動態列設定統一列寬
        {
            worksheet.Column(ii).Width = 16;
        }
        //worksheet.Cells.Style.WrapText = true; // 自動換行
        worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
        using (ExcelRange r = worksheet.Cells[1, 1, tableRowModels.Count + 1, headersname.Count])
        {
            r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            r.Style.Border.Right.Style = ExcelBorderStyle.Thin;

            r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
            r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
            r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
            r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
        }
        worksheet.View.FreezePanes(2, 3); // 凍結第一行，以及前兩列
        worksheet.Row(1).Style.Font.Bold = true; // 第一行加粗
        worksheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;// 第一行水平居中
        package.Save();
    }
}

 EpplusHelper.cs 類，用作迴圈新增表頭和資料。

using System;
using System.Collections.Generic;
using OfficeOpenXml;

public static class EpplusHelper
{
    /// <summary>
    /// 通過 名稱陣列 新增表頭
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="headertexts"></param>
    public static void AddHeader(ExcelWorksheet sheet, params string[] headertexts)
    {
        for (var i = 0; i < headertexts.Length; i++)
        {
            AddHeader(sheet, i + 1, headertexts[i]);
        }
    }
    /// <summary>
    /// 新增動態資料
    /// </summary>
    /// <param name="worksheet"></param>
    /// <param name="startrowindex">從第幾行開始</param>
    /// <param name="items">行資料列表</param>
    /// <param name="headerscode">列名程式碼列表，用於取資料</param>
    public static void AddObjects(ExcelWorksheet worksheet, int startrowindex, IList<TableRowModel> items, List<string> headerscode)
    {
        for (var i = 0; i < items.Count; i++)
        {
            worksheet.Cells[i + startrowindex, 1].Value = items[i].kemumc; // 注意此處為相容前兩列固定列
            worksheet.Cells[i + startrowindex, 2].Value = items[i].heji;
            for (var j = 2; j < headerscode.Count; j++) // headercode:kemumc,heji,bumenx1,bumenx2...
            {
                worksheet.Cells
                    [i + startrowindex,
                    j + 1]
                    .Value
                    = items[i].dict_lie[headerscode[j]];
            }
        }
    }
}

程式碼參考：C# 使用Epplus匯出Excel [2]：匯出動態列資料