C# .Net Excel (MiniExcel)

2023-06-13

1042
0
C#
2023-07-12

筆記下 MiniExcel

填充並存檔

使用 MiniExcel 填充 Excel 樣板來產生最終我們要的 Excel 檔案

P.S. 樣板填充支援 合併欄位 與 多 Sheets (會依序填充各個 Sheet 內的 {{xxxxx}} )

安裝

使用 nuget 安裝 MiniExcel 套件

樣板

結果

Sample Code

public async Task GenerateExcels(string path, string templatePath, List<Employee> input)
{
    await MiniExcel.SaveAsByTemplateAsync(path, templatePath, new { employees = input });
}

填充並回傳資料流

開發 API 時可以不實際儲存檔案，直接回傳 Stream

樣板

Sample Code

public async Task<IRemoteStreamContent> GenerateExcels(List<User> input)
{
    var provider = CachedServiceProvider.GetRequiredService<IVirtualFileProvider>();
    var file = provider.GetFileInfo(UserConsts.TemplatePath);
    var template = await file.ReadBytesAsync();

    var memoryStream = new MemoryStream();
    await memoryStream.SaveAsByTemplateAsync(template , new { V = input });
    memoryStream.Seek(0, SeekOrigin.Begin);
    return new RemoteStreamContent(memoryStream, "Sample.xlsx",
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
}

官方範例

//1. By POCO
var value = new
{
    employees = new[] {
        new {name="Jack",department="HR"},
        new {name="Lisa",department="HR"},
        new {name="John",department="HR"},
        new {name="Mike",department="IT"},
        new {name="Neo",department="IT"},
        new {name="Loan",department="IT"}
    }
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);

//2. By Dictionary
var value = new Dictionary<string, object>()
{
    ["employees"] = new[] {
        new {name="Jack",department="HR"},
        new {name="Lisa",department="HR"},
        new {name="John",department="HR"},
        new {name="Mike",department="IT"},
        new {name="Neo",department="IT"},
        new {name="Loan",department="IT"}
    }
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);

複雜填充

// 1. By POCO
var value = new
{
    title = "FooCompany",
    managers = new[] {
        new {name="Jack",department="HR"},
        new {name="Loan",department="IT"}
    },
    employees = new[] {
        new {name="Wade",department="HR"},
        new {name="Felix",department="HR"},
        new {name="Eric",department="IT"},
        new {name="Keaton",department="IT"}
    }
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);

// 2. By Dictionary
var value = new Dictionary<string, object>()
{
    ["title"] = "FooCompany",
    ["managers"] = new[] {
        new {name="Jack",department="HR"},
        new {name="Loan",department="IT"}
    },
    ["employees"] = new[] {
        new {name="Wade",department="HR"},
        new {name="Felix",department="HR"},
        new {name="Eric",department="IT"},
        new {name="Keaton",department="IT"}
    }
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);

加總

利用複雜填充可以實現填充清單並在其下方顯示總和

注意

以下程式碼會拋例外


var value = new
{
    V = new List<(string name, string department)>
    {
        new()
        {
            name = "Jake",
            department = "C"
        },
        new()
        {
            name = "Yo",
            department = "D"
        },
    }
};

MiniExcel.SaveAsByTemplate(path, templatePath, value);

猜測可能不支援 Tuple