Estableciendo fórmula para rango con nombre
Estableciendo fórmula para rango con nombre
Al igual que la aplicación de Excel, las API de Aspose.Cells brindan la capacidad de especificar una fórmula para un rango con nombre al usar su propiedad RefersTo. Podría haber numerosos escenarios de usabilidad para esta característica, algunos de los cuales se detallan a continuación.
Estableciendo una fórmula simple para rango con nombre
Una fórmula simple podría ser una referencia a otra celda en la misma (o diferente) hoja de cálculo. El siguiente ejemplo crea un rango con nombre en una nueva hoja de cálculo y establece su referencia a otra celda.
// 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"); |
Estableciendo una fórmula compleja para rango con nombre
Una fórmula compleja podría ser cualquier cosa, como un rango dinámico o una fórmula que abarca múltiples celdas en diferentes hojas de cálculo. El siguiente ejemplo crea un rango dinámico utilizando la función INDEX para obtener el valor de una lista según su ubicación.
// 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"); |
Aquí hay otro ejemplo que utiliza un rango con nombre para sumar valores de 2 celdas en diferentes hojas de cálculo.
// 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"); |