设置命名范围公式

为命名范围设置公式

与Excel应用程序类似,Aspose.Cells API在使用其RefersTo属性时,提供了为命名范围指定公式的能力。这个功能可能有许多可用性场景,其中一些详细列出如下。

为命名范围设置简单公式

简单公式可能是对同一(或不同)工作表中另一个单元格的引用。下面的示例创建一个新电子表格中的命名范围,并将其引用设置为另一个单元格。

// 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 an instance of Workbook
Workbook book = new Workbook();
// Get the WorksheetCollection
WorksheetCollection worksheets = book.Worksheets;
// Add a new Named Range with name "NewNamedRange"
int index = worksheets.Names.Add("NewNamedRange");
// Access the newly created Named Range
Name name = worksheets.Names[index];
// Set RefersTo property of the Named Range to a formula. Formula references another cell in the same worksheet
name.RefersTo = "=Sheet1!$A$3";
// Set the formula in the cell A1 to the newly created Named Range
worksheets[0].Cells["A1"].Formula = "NewNamedRange";
// Insert the value in cell A3 which is being referenced in the Named Range
worksheets[0].Cells["A3"].PutValue("This is the value of A3");
// Calculate formulas
book.CalculateFormula();
// Save the result in XLSX format
book.Save(dataDir + "output_out.xlsx");

为命名范围设置复杂公式

复杂公式可以是任何内容,例如动态范围或跨不同工作表多个单元格的公式。下面的示例使用INDEX函数创建一个动态范围,根据其位置从列表中获取值。

// 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 an instance of Workbook
Workbook book = new Workbook();
// Get the WorksheetCollection
WorksheetCollection worksheets = book.Worksheets;
// Add a new Named Range with name "data"
int index = worksheets.Names.Add("data");
// Access the newly created Named Range from the collection
Name data = worksheets.Names[index];
// Set RefersTo property of the Named Range to a cell range in same worksheet
data.RefersTo = "=Sheet1!$A$1:$A$10";
// Add another Named Range with name "range"
index = worksheets.Names.Add("range");
// Access the newly created Named Range from the collection
Name range = worksheets.Names[index];
// Set RefersTo property to a formula using the Named Range data
range.RefersTo = "=INDEX(data,Sheet1!$A$1,1):INDEX(data,Sheet1!$A$1,9)";
// Save the workbook
book.Save(dataDir + "output_out.xlsx");

这是另一个示例,使用命名范围对不同工作表中的2个单元格的值进行求和。

// 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 an instance of Workbook
Workbook book = new Workbook();
// Get the WorksheetCollection
WorksheetCollection worksheets = book.Worksheets;
// Insert some data in cell A1 of Sheet1
worksheets["Sheet1"].Cells["A1"].PutValue(10);
// Add a new Worksheet and insert a value to cell A1
worksheets[worksheets.Add()].Cells["A1"].PutValue(10);
// Add a new Named Range with name "range"
int index = worksheets.Names.Add("range");
// Access the newly created Named Range from the collection
Name range = worksheets.Names[index];
// Set RefersTo property of the Named Range to a SUM function
range.RefersTo = "=SUM(Sheet1!$A$1,Sheet2!$A$1)";
// Insert the Named Range as formula to 3rd worksheet
worksheets[worksheets.Add()].Cells["A1"].Formula = "range";
// Calculate formulas
book.CalculateFormula();
// Save the result in XLSX format
book.Save(dataDir + "output_out.xlsx");