管理 Excel 文件的公式

介绍

Microsoft Excel 引人注目的功能之一是它能够使用公式和函数处理数据。 Microsoft Excel 提供了一组内置函数和公式，可帮助用户快速执行复杂的计算。 Aspose.Cells 还提供了大量内置函数和公式，可帮助开发人员轻松计算值。 Aspose.Cells还支持插件功能。此外，Aspose.Cells 支持 Aspose.Cells 中的数组和 R1C1 公式。

使用公式和函数

Aspose.Cells提供了一个类，工作簿，代表一个 Microsoft Excel 文件。这工作簿类包含一个工作表允许访问 Excel 文件中每个工作表的集合。工作表由工作表班级。这工作表类提供了Cells收藏。 Cells 集合中的每个项目代表Cell班级。

可以使用提供的属性和方法将公式应用于单元格Cell类，下面更详细地讨论。

• 使用内置函数。
• 使用附加功能。
• 使用数组公式。
• 创建 R1C1 公式。

使用内置函数

内置函数或公式作为现成的函数提供，以减少开发人员的工作量和时间。看内置函数列表由 Aspose.Cells 支持。功能按字母顺序列出。未来将支持更多功能。

Aspose.Cells 支持 Microsoft Excel 提供的大部分公式或函数。开发人员可以通过 API 或设计师电子表格. Aspose.Cells 支持大量的数学、字符串、布尔、日期/时间、统计、数据库、查找和参考公式。

使用Cell班级'公式属性将公式添加到单元格。复杂的公式， 例如

 = H7*(1+IF(P7 = $L$3,$M$3, (IF(P7=$L$4,$M$4,0))))

在 Aspose.Cells 中也受支持。将公式应用于单元格时，始终以等号 (=) 开头字符串，就像在 Microsoft Excel 中创建公式时所做的那样，并使用逗号 (,) 分隔函数参数。

在下面的示例中，一个复杂的公式应用于工作表的第一个单元格Cells收藏。该公式使用内置如果Aspose.Cells提供的功能。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();

// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[0];

// Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);

// Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);

// Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);

// Adding a SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";

// Calculating the results of formulas
workbook.CalculateFormula();

// Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();

// Saving the Excel file
workbook.Save(dataDir + "output.xls");

使用插件功能

我们可以有一些用户定义的公式，我们希望将其作为 excel 加载项包含在内。设置 cell.Formula 函数时，内置函数可以正常工作，但是需要使用附加函数设置自定义函数或公式。

Aspose.Cells 提供使用以下功能注册添加功能的功能工作表.RegisterAddInFunction().之后，当我们设置 cell.Formula = anyFunctionFromAddIn 时，输出的 Excel 文件包含从 AddIn 函数计算出的值。

在下面的示例代码中注册插件功能需要下载XLAM文件。同样，可以下载输出文件“test_udf.xlsx”来检查输出。

测试UDF.xlam

测试_udf.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// Create empty workbook
Workbook workbook = new Workbook();

// Register macro enabled add-in along with the function name
int id = workbook.Worksheets.RegisterAddInFunction(sourceDir + @"TESTUDF.xlam", "TEST_UDF", false);

// Register more functions in the file (if any)
workbook.Worksheets.RegisterAddInFunction(id, "TEST_UDF1"); //in this way you can add more functions that are in the same file

// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Access first cell
var cell = worksheet.Cells["A1"];

// Set formula name present in the add-in
cell.Formula = "=TEST_UDF()";

// Save workbook to output XLSX format.
workbook.Save(outputDir +  @"test_udf.xlsx", Aspose.Cells.SaveFormat.Xlsx);

使用数组公式

数组公式是将数组而不是单个数字作为构成公式的函数的参数的公式。当显示数组公式时，它被大括号 ({}) 包围。

一些 Microsoft Excel 函数返回值数组。要使用数组公式计算多个结果，请将数组输入到一系列单元格中，这些单元格的行数和列数与数组参数相同。

可以通过调用Cell班级'设置数组公式方法。这设置数组公式方法采用以下参数：

• 数组公式，数组公式。
• 行数，要填充数组公式结果的行数。
• 列数，用于填充数组公式结果的列数。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();

// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];

// Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);

// Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);

// Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);
            
// Adding a value to B1
worksheet.Cells["B1"].PutValue(4);

// Adding a value to "B2" cell
worksheet.Cells["B2"].PutValue(5);

// Adding a value to "B3" cell
worksheet.Cells["B3"].PutValue(6);

// Adding a value to C1
worksheet.Cells["C1"].PutValue(7);

// Adding a value to "C2" cell
worksheet.Cells["C2"].PutValue(8);

// Adding a value to "C3" cell
worksheet.Cells["C3"].PutValue(9);

// Adding a SUM formula to "A4" cell
worksheet.Cells["A6"].SetArrayFormula("=LINEST(A1:A3,B1:C3,TRUE,TRUE)", 5, 3);

// Calculating the results of formulas
workbook.CalculateFormula();

// Get the calculated value of the cell
string value = worksheet.Cells["A6"].Value.ToString();

// Saving the Excel file
workbook.Save(dataDir + "output.xls");

使用 R1C1 公式

添加一个R1C1将样式公式引用到具有Cell班级'R1C1公式财产。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "Book1.xls");

Worksheet worksheet = workbook.Worksheets[0];

// Setting an R1C1 formula on the "A11" cell, 
// Row and Column indeces are relative to destination index
worksheet.Cells["A11"].R1C1Formula = "=SUM(R[-10]C[0]:R[-7]C[0])";

// Saving the Excel file
workbook.Save(dataDir + "output.xls");

推进主题

• 先例和家属
• 在公式中设置外部链接
• 在新行中输入数据时自动在表或列表对象中传播公式
• 命名范围的设置公式
• 设置公式 - 非英语用户须知
• 设置共享公式
• 指定共享公式的最大行数
• 支持的 Excel 函数