在 Aspose.Cells 中使用 Excel 2019 功能

众所周知，在 .NET 中处理 Excel 文档是出了名的棘手。事实上，在早期，这简直太难了。如今，.NET 开发人员在生成、修改、转换或渲染电子表格方面的工作要轻松得多。但是，即使有了可供使用的现代电子表格操作组件，如果您使用的组件跟不上最新的 Excel 功能，那么完成项目也可能会很困难。

借助 Aspose.Cells，创建电子表格简直是易如反掌，而且这个易于使用的 API 现在包含了处理 Excel 2019 公式功能。

在本文中，我将演示如何使用 Aspose.Cells 创建一个利用一些新的 Excel 2019 功能的电子表格。我演示的一些代码可能看起来冗长，但我想尽可能清晰地说明，而不隐藏任何逻辑。

设置您的项目

开始使用 Aspose.Cells 的最简单方法是使用 NuGet 下载所需的 DLL。我创建了一个简单的 WinForms 应用程序，它在按钮单击时调用一些用于创建 Excel 文档的代码。

要将 NuGet 包添加到您的项目中，请右键单击您的解决方案，然后单击“管理 NuGet 程序包”。搜索“Aspose.Cells”，NuGet 将其显示为第一个结果。现在您可以将最新版本的 Aspose.Cells 添加到您的项目中。

NuGet 安装完程序包后，您将看到 Aspose.Cells 引用已添加到您的项目中

现在让我们开始创建一些电子表格。

完整的解决方案

我们将创建的电子表格将使用以下公式

• MAXIFS
• MINIFS
• IFS
• SWITCH
• CONCAT
• TEXTJOIN

该项目还将说明如何将这些图表添加到电子表格中

• 箱形图
• 漏斗图
• 帕累托线图
• 旭日图
• 树状图
• 瀑布
• 映射

首先，让我们看一下公式。

我们将创建一个显示两个学期学生分数的电子表格。每个分数将获得一个等级。

要显示成绩，我们将使用 IFS 公式。

根据成绩，我们将使用 SWITCH 公式显示一些文本：再努力一些、一般、良好、优秀或极好。

然后，我们将使用 CONCAT 公式连接学生姓名、成绩和文本结果。

接下来，我们将显示每个学期的最高分和最低分。为此，我们将使用 MAXIFS 和 MINIFS 公式。

最后，我们将使用 TEXTJOIN 公式输出每个学生的完整姓名，忽略任何空白单元格。

编写代码

第一步是添加必需的 using 语句

using Aspose.Cells;

接下来，我们需要创建工作簿以及生成 Excel 文档应保存的路径

var wbook = new Workbook();
var pth = "C:/temp/aspose/";

现在创建一个名为 SetupFormulaWorkbookData 的方法，并将工作表作为参数传递给它，如下所示

private void SetupFormulaWorkbookData(Worksheet ws)
{
}

我们稍后将在此方法中添加代码。让我们先完成调用代码。添加对 SetupFormulaWorkbookData 方法的调用，并将工作表的名称设置为“Formulas”。然后以 .xlsx 格式保存文件，文件名为“1-Formulas.xlsx”

SetupFormulaWorkbookData(wbook.Worksheets[0]);
var ws = wbook.Worksheets[0];
ws.Name = "Formulas";
wbook.Save($"{pth}1-Formulas.xlsx", SaveFormat.Xlsx);

我们需要为 SetupFormulaWorkbookData 方法编写代码，但首先我们需要设置一些要使用的数据。对于这个示例，我们将硬编码这些值，但您可以从数据库或文件中读取数据。

添加所需的标题数据

ws.Cells["B2"].PutValue("Name");
ws.Cells["C2"].PutValue("Semester");
ws.Cells["D2"].PutValue("Score");
ws.Cells["E2"].PutValue("Grade");

这是我使用的硬编码学生姓名

#region Add Names
ws.Cells["B3"].PutValue("John");
ws.Cells["B4"].PutValue("Lidia");
ws.Cells["B5"].PutValue("Mark");
ws.Cells["B6"].PutValue("Anne");
ws.Cells["B7"].PutValue("Hayley");
ws.Cells["B8"].PutValue("Lane");
ws.Cells["B9"].PutValue("Peter");
ws.Cells["B10"].PutValue("James");
ws.Cells["B11"].PutValue("Mary");
ws.Cells["B12"].PutValue("John");
ws.Cells["B13"].PutValue("Lidia");
ws.Cells["B14"].PutValue("Mark");
ws.Cells["B15"].PutValue("Anne");
ws.Cells["B16"].PutValue("Hayley");
ws.Cells["B17"].PutValue("Lane");
ws.Cells["B18"].PutValue("Peter");
ws.Cells["B19"].PutValue("James");
ws.Cells["B20"].PutValue("Mary");
#endregion

接下来，将第一学期和第二学期添加到数据中

#region Add Semesters
ws.Cells["C3"].PutValue(1);
ws.Cells["C4"].PutValue(2);
ws.Cells["C5"].PutValue(1);
ws.Cells["C6"].PutValue(2);
ws.Cells["C7"].PutValue(1);
ws.Cells["C8"].PutValue(1);
ws.Cells["C9"].PutValue(2);
ws.Cells["C10"].PutValue(1);
ws.Cells["C11"].PutValue(2);
ws.Cells["C12"].PutValue(2);
ws.Cells["C13"].PutValue(1);
ws.Cells["C14"].PutValue(2);
ws.Cells["C15"].PutValue(1);
ws.Cells["C16"].PutValue(2);
ws.Cells["C17"].PutValue(2);
ws.Cells["C18"].PutValue(1);
ws.Cells["C19"].PutValue(2);
ws.Cells["C20"].PutValue(1);
#endregion

最后，添加学生分数：。

#region Add Scores
ws.Cells["D3"].PutValue(75);
ws.Cells["D4"].PutValue(65);
ws.Cells["D5"].PutValue(15);
ws.Cells["D6"].PutValue(75);
ws.Cells["D7"].PutValue(95);
ws.Cells["D8"].PutValue(56);
ws.Cells["D9"].PutValue(72);
ws.Cells["D10"].PutValue(88);
ws.Cells["D11"].PutValue(24);
ws.Cells["D12"].PutValue(61);
ws.Cells["D13"].PutValue(72);
ws.Cells["D14"].PutValue(97);
ws.Cells["D15"].PutValue(17);
ws.Cells["D16"].PutValue(63);
ws.Cells["D17"].PutValue(84);
ws.Cells["D18"].PutValue(48);
ws.Cells["D19"].PutValue(65);
ws.Cells["D20"].PutValue(68);
#endregion

当然，您可以使用循环并从文件或数据库读取数据来轻松创建此代码，但为了简单起见，我只是硬编码了数据。

IFS 公式

我们希望能够根据学生收到的分数显示其等级，因此我们将使用 IFS 函数。

请注意，在 Excel 中，您使用分号作为公式中的分隔符，但在 Aspose.Cells 中，您需要在代码中使用逗号而不是分号。要显示等级，我们将循环遍历 D3 到 D20 单元格，如果单元格值符合我们的条件，我们将输出“不及格”、“C”、“B”、“A”或“A+”的结果到 E 列

// IFS Formula
// eg: =IFS(D3<60;"Fail";D3<70;"C";D3<80;"B";D3<90;"A";D3>=90;"A+")
for (var i = 3; i <=20; i++)
{
    ws.Cells[$"E{i}"].Formula = $"=IFS(D{i}<60,\"Fail\",D{i}<70,\"C\",D{i}<80,\"B\",D{i}<90,\"A\",D{i}>=90,\"A +\")";
}

SWITCH 公式

将使用相同的循环逻辑来显示基于所收到的等级的一些文本。文本将显示在 F3 到 F20 列中，并且将基于 E3 到 E20 列中的等级

// SWITCH Formula
// eg: =SWITCH(E3;"Fail";"Try harder";"C";"Ok";"B";"Good";"A";"Great";"A+";"Excellent")
for (var i = 3; i <= 20; i++)
{
    ws.Cells[$"F{i}"].Formula = $"=SWITCH(E{i},\"Fail\",\"Try harder\",\"C\",\"Ok\",\"B\",\"Good\",\"A\",\"Great\",\"A +\",\"Excellent\")";
}

CONCAT 公式

CONCAT 公式将使用姓名单元格、成绩单元格和文本输出来创建一个字符串，向学生显示一条消息。同样，我们可以使用循环为每个单元格创建公式

// CONCAT Formula
// eg: =CONCAT(B3;" - Result: "; E3; " - "; F3)
for (var i = 3; i <= 20; i++)
{
    ws.Cells[$"G{i}"].Formula = $"=CONCAT(B{i},\" - Your result: \", E{i}, \" - \", F{i})";
}

MAXIFS 和 MINIFS 公式

现在我们要创建公式来显示每个学期的最高分和最低分。

首先，我们需要添加标题文本，然后添加每个学期的值，如下所示

#region Results
ws.Cells["J2"].PutValue("Semester");
ws.Cells["K2"].PutValue("Highest");
ws.Cells["L2"].PutValue("Lowest");

ws.Cells["J3"].PutValue(1);
ws.Cells["J4"].PutValue(2);
#endregion

接下来，我们为每个学期的最高分和最低分单元格创建 MAXIFS 和 MINIFS 公式

var maxFirstSemesterCell = ws.Cells["K3"];
maxFirstSemesterCell.Formula = "=MAXIFS(D3:D20,C3:C20,\"1\")";

var maxSecondSemesterCell = ws.Cells["K4"];
maxSecondSemesterCell.Formula = "=MAXIFS(D3:D20,C3:C20,\"2\")";

var minFirstSemesterCell = ws.Cells["L3"];
minFirstSemesterCell.Formula = "=MINIFS(D3:D20,C3:C20,\"1\")";

var minSecondSemesterCell = ws.Cells["L4"];
minSecondSemesterCell.Formula = "=MINIFS(D3:D20,C3:C20,\"2\")";

TEXTJOIN 公式

最后，我们想生成每个学生的完整姓名，包括他们的名字、姓氏，以及如果他们有的话，中间名。如果他们没有中间名，则需要忽略空白单元格。

首先，为标题创建数据——名字、中间名和姓氏

#region Name Details
ws.Cells["B23"].PutValue("First name");
ws.Cells["C23"].PutValue("Middle name");
ws.Cells["D23"].PutValue("Last name");
ws.Cells["E23"].PutValue("Full name");

ws.Cells["B24"].PutValue("John");
ws.Cells["B25"].PutValue("Lidia");
ws.Cells["B26"].PutValue("Mark");
ws.Cells["B27"].PutValue("Anne");
ws.Cells["B28"].PutValue("Hayley");
ws.Cells["B29"].PutValue("Lane");
ws.Cells["B30"].PutValue("Peter");
ws.Cells["B31"].PutValue("James");
ws.Cells["B32"].PutValue("Mary");

ws.Cells["C24"].PutValue("Reginald");
ws.Cells["C27"].PutValue("Mary");
ws.Cells["C28"].PutValue("Lindy");
ws.Cells["C30"].PutValue("Lee");

ws.Cells["D24"].PutValue("Van Zandt");
ws.Cells["D25"].PutValue("Cunningham");
ws.Cells["D26"].PutValue("Lester");
ws.Cells["D27"].PutValue("Joseph");
ws.Cells["D28"].PutValue("Miller");
ws.Cells["D29"].PutValue("Bower");
ws.Cells["D30"].PutValue("Sanders");
ws.Cells["D31"].PutValue("Williams");
ws.Cells["D32"].PutValue("Davis");
#endregion

现在使用另一个循环为每个学生创建完整姓名，忽略任何空白单元格

// TEXTJOIN Function
// eg: =TEXTJOIN(" "; TRUE; B24:D24)
for (var i = 24; i <= 32; i++)
{
    ws.Cells[$"E{i}"].Formula = $"=TEXTJOIN(\" \", TRUE, B{i}:D{i})";
}

设置完所有数据和公式后，运行您的应用程序将生成一个显示前面逻辑的 Excel 文档。

如您所见，使用 Aspose.Cells 可以非常轻松地创建带有公式的 Excel 文档。

Charts

使用 Aspose.Cells 创建图表也相当简单。传统上是一个棘手过程的任务，借助 Aspose.Cells 变得更加容易。事实上，许多相同的代码可以用于不同的图表。

我们将为每种图表类型创建新的 Excel 文档。让我们从箱形图开始。

箱形图

我们将用于生成箱形图的数据是三种作物（橙子、苹果、梨和葡萄）在三年（2014、2015 和 2016 年）的产量。

要使用这些数据，首先创建一个名为 SetupBoxWhiskerChart 的方法，该方法接受一个 Worksheet 参数

private void SetupBoxWhiskerChart(Worksheet ws)
{
}

接下来，为每列数据创建标题

ws.Cells["B2"].PutValue("Produce");
ws.Cells["C2"].PutValue("Year 2014");
ws.Cells["D2"].PutValue("Year 2015");
ws.Cells["E2"].PutValue("Year 2016");

现在我们将创建作物（橙子、苹果、梨和葡萄）的数据。为此，我们将稍作调整，使用一个带有 if 语句的循环来添加每种作物类型四次（假设这是每学期）。

for (var i = 3; i <= 18; i++)
{
    if (i == 3 || i == 7 || i == 11 || i == 15)
        ws.Cells[$"B{i}"].PutValue("Oranges");

    if (i == 4 || i == 8 || i == 12 || i == 16)
        ws.Cells[$"B{i}"].PutValue("Apples");

    if (i == 5 || i == 9 || i == 13 || i == 17)
        ws.Cells[$"B{i}"].PutValue("Pears");

    if (i == 6 || i == 10 || i == 14 || i == 18)
        ws.Cells[$"B{i}"].PutValue("Grapes");
}

接下来，我们将使用 .NET 内置的 Random 类用随机值填充年份列，代表该年的产量。请注意，如果您需要真正的随机值，您需要使用 RNGCryptoServiceProvider 创建安全随机数。我们不需要那么高的随机性，所以我们只使用伪随机数。

var rnd = new Random();

for (var i = 3; i <= 18; i++)
{
    ws.Cells[$"C{i}"].PutValue(rnd.Next(10000, 70000));
    ws.Cells[$"D{i}"].PutValue(rnd.Next(10000, 70000));
    ws.Cells[$"E{i}"].PutValue(rnd.Next(10000, 70000));
}

现在我们告诉工作表创建哪个图表，然后返回该图表的 chartIndex。ChartType.BoxWhisker 后面的值（6、6、25、15）指定了图表在 Excel 文档中的位置。

var chartIndex = ws.Charts.Add(ChartType.BoxWhisker, 6, 6, 25, 15);

将系列和类别数据添加到图表中。系列是包含产量数据的每一列，类别数据定义了作物类型（橙子、苹果、梨和葡萄）。

var chart = ws.Charts[chartIndex];

_ = chart.NSeries.Add("=C3:C18", true);
_ = chart.NSeries.Add("=D3:D18", true);
_ = chart.NSeries.Add("=E3:E18", true);

chart.NSeries.CategoryData = "=B3:B18";

现在我们编写调用代码

var wbook = new Workbook();
var pth = "C:/temp/aspose/";
SetupBoxWhiskerChart(wbook.Worksheets[0]);
var ws = wbook.Worksheets[0];
ws.Name = "Charts";
wbook.Save($"{pth}2-BoxWhisker.xlsx", SaveFormat.Xlsx);

如果您现在运行该应用程序，您将看到已创建了以下箱形图

在 Aspose.Cells 中生成图表就是这么简单！

漏斗图

让我们稍微改变一下。修改您的 SetupBoxWhiskerChart 方法，将其重命名为 SetupChart，并让它接受一个额外的 ChartType 参数。

private void SetupChart(Worksheet ws, ChartType chrtType)
{
}

现在，将所有代码保持完全相同，只需更改指定图表类型的行，并使用传递到方法中的 chrtType 参数。

var chartIndex = ws.Charts.Add(chrtType, 6, 6, 25, 15);

如果一切正常，您的方法应该如下所示

private void SetupChart(Worksheet ws, ChartType chrtType)
{
    ws.Cells["B2"].PutValue("Produce");
    ws.Cells["C2"].PutValue("Year 2014");
    ws.Cells["D2"].PutValue("Year 2015");
    ws.Cells["E2"].PutValue("Year 2016");

    for (var i = 3; i <= 18; i++)
    {
        if (i == 3 || i == 7 || i == 11 || i == 15)
            ws.Cells[$"B{i}"].PutValue("Oranges");

        if (i == 4 || i == 8 || i == 12 || i == 16)
            ws.Cells[$"B{i}"].PutValue("Apples");

        if (i == 5 || i == 9 || i == 13 || i == 17)
            ws.Cells[$"B{i}"].PutValue("Pears");

        if (i == 6 || i == 10 || i == 14 || i == 18)
            ws.Cells[$"B{i}"].PutValue("Grapes");
    }

    var rnd = new Random();

    for (var i = 3; i <= 18; i++)
    {
        ws.Cells[$"C{i}"].PutValue(rnd.Next(10000, 70000));
        ws.Cells[$"D{i}"].PutValue(rnd.Next(10000, 70000));
        ws.Cells[$"E{i}"].PutValue(rnd.Next(10000, 70000));
    }

    var chartIndex = ws.Charts.Add(chrtType, 6, 6, 25, 15);
    var chart = ws.Charts[chartIndex];

    _ = chart.NSeries.Add("=C3:C18", true);
    _ = chart.NSeries.Add("=D3:D18", true);
    _ = chart.NSeries.Add("=E3:E18", true);

    chart.NSeries.CategoryData = "=B3:B18";
}

现在您可以稍微修改您的调用代码来创建工作簿并设置图表数据，然后将图表类型传递给它以进行生成。保存新的 Excel 文件之前，请重命名工作表。

var wbook = new Workbook();
var pth = "C:/temp/aspose/";
SetupChart(wbook.Worksheets[0], ChartType.Funnel);
var ws = wbook.Worksheets[0];
ws.Name = "Charts";
wbook.Save($"{pth}3-Funnel.xlsx", SaveFormat.Xlsx);

创建 Excel 文档后，打开它，您将看到根据作物产量数据生成的漏斗图。

我们可以继续使用完全相同的方法创建不同的图表，只需更改传递给方法的图表类型，如下所示。

帕累托线图

使用与之前相同的方法，让我们创建一个 ParetoLine 图表。我们的调用代码将如下更改

SetupChart(wbook.Worksheets[0], ChartType.ParetoLine);
var ws = wbook.Worksheets[0];
ws.Name = "Charts";
wbook.Save($"{pth}4-Pareto.xlsx", SaveFormat.Xlsx);

这将生成以下图表

同样，相同的方法通过最少的代码更改生成了一个完全不同的图表类型。

旭日图

下一个我们将生成的图表类型是旭日图。修改调用代码如下

SetupChart(wbook.Worksheets[0], ChartType.Sunburst);
var ws = wbook.Worksheets[0];
ws.Name = "Charts";
wbook.Save($"{pth}5-Sunburst.xlsx", SaveFormat.Xlsx);

生成的图表将在保存的 Excel 文档中创建，看起来像这样

我们将使用我们的 SetupChart 方法生成另外两种图表：树状图和瀑布图。我相信到这个时候，您会注意到创建图表所需的代码是简单且一致的。这使得您可以轻松地设置代码来适应您的用户可能需要的任何图表类型。

树状图

修改调用代码如下

SetupChart(wbook.Worksheets[0], ChartType.Treemap);
var ws = wbook.Worksheets[0];
ws.Name = "Charts";
wbook.Save($"{pth}6-Treemap.xlsx", SaveFormat.Xlsx);

下图中显示的树状图在保存的 Excel 文档中创建。

瀑布图

与之前一样，修改调用代码以传递 Waterfall 图表类型

SetupChart(wbook.Worksheets[0], ChartType.Waterfall);
var ws = wbook.Worksheets[0];
ws.Name = "Charts";
wbook.Save($"{pth}7-Waterfall.xlsx", SaveFormat.Xlsx);

瀑布图，如下所示，在保存的 Excel 文档中创建。

地图图表

我们将要查看的最后一种图表类型是地图图表。这是我们将用于生成此图表的数据

首先，创建一个名为 SetupMapChart 的方法，该方法接受一个 Worksheet 参数

private void SetupMapChart(Worksheet ws)
{
}

接下来，创建列标题

ws.Cells["B2"].PutValue("Country");
ws.Cells["C2"].PutValue("Sales");

然后在“Country”标题下添加一些国家

ws.Cells[$"B3"].PutValue("South Africa");
ws.Cells[$"B4"].PutValue("Canada");
ws.Cells[$"B5"].PutValue("India");
ws.Cells[$"B6"].PutValue("France");

同样，我们使用 Random 类来生成每个国家的随机销售量。这些随机数将在 50,000 到 70,000 之间。

var rnd = new Random();

for (var i = 3; i <= 6; i++)
{
    ws.Cells[$"C{i}"].PutValue(rnd.Next(50000, 70000));
}

与之前一样，创建地图类型的图表并添加系列和类别数据

var chartIndex = ws.Charts.Add(ChartType.Map, 6, 6, 25, 15);
var chart = ws.Charts[chartIndex];

_ = chart.NSeries.Add("=C3:C6", true);

chart.NSeries.CategoryData = "=B3:B6";

现在调用 SetupMapChart 方法并按如下方式保存 Excel 文档

SetupMapChart(wbook.Worksheets[0]);
var ws = wbook.Worksheets[0];
ws.Name = "Charts";
wbook.Save($"{pth}8-Map.xlsx", SaveFormat.Xlsx);

这将导致生成以下地图图表。

您可以看到，随着销售量的增加，国家/地区以更深的蓝色突出显示。最浅的蓝色表示最低的销售量。

结论

本文仅简要介绍了使用 Aspose.Cells 可以创建的不同图表和公式，这只是您使用 Aspose.Cells 可以做的事情的一小部分。要了解更多关于 Aspose.Cells 可以为您做什么的信息，请查看他们的网页。