设置命名范围公式
Contents
[
Hide
]
为命名范围设置公式
与Excel应用程序类似,Aspose.Cells API在使用其RefersTo属性时,提供了为命名范围指定公式的能力。这个功能可能有许多可用性场景,其中一些详细列出如下。
为命名范围设置简单公式
简单公式可能是对同一(或不同)工作表中另一个单元格的引用。下面的示例创建一个新电子表格中的命名范围,并将其引用设置为另一个单元格。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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函数创建一个动态范围,根据其位置从列表中获取值。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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个单元格的值进行求和。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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"); |