Gestire le formule dei file Excel

Introduzione

Una delle funzionalità più interessanti di Microsoft Excel è la sua capacità di elaborare dati con formule e funzioni. Microsoft Excel fornisce un insieme di funzioni e formule incorporate che aiutano gli utenti a eseguire rapidamente calcoli complessi. Aspose.Cells fornisce anche un’ampia serie di funzioni e formule incorporate che aiutano gli sviluppatori a calcolare facilmente i valori. Aspose.Cells supporta anche le funzioni add-in. Inoltre, Aspose.Cells supporta le formule matriciali e R1C1 in Aspose.Cells.

Come utilizzare formule e funzioni

Aspose.Cells fornisce una classe, Workbook, che rappresenta un file Microsoft Excel. La classe Workbook contiene una raccolta Worksheets che consente di accedere a ciascun foglio di lavoro nel file Excel. Un foglio di lavoro è rappresentato dalla classe Worksheet. La classe Worksheet fornisce una raccolta Cells. Ogni elemento nella raccolta Cells rappresenta un oggetto della classe Cell.

È possibile applicare formule alle celle utilizzando le proprietà e i metodi offerti dalla classe Cell, discussi in dettaglio di seguito.

Utilizzo di funzioni incorporate.
Utilizzo di funzioni add-in.
Lavorare con formule matriciali.
Creazione di una formula R1C1.

Come utilizzare le funzioni incorporate

Le funzioni incorporate o le formule vengono fornite come funzioni predefinite per ridurre gli sforzi e il tempo degli sviluppatori. Consulta un elenco di funzioni incorporate supportate da Aspose.Cells. Le funzioni sono elencate in ordine alfabetico. Saranno supportate più funzioni in futuro.

Aspose.Cells supporta la maggior parte delle formule o funzioni offerte da Microsoft Excel. Gli sviluppatori possono utilizzare queste formule tramite l’API o il foglio di calcolo del progettista. Aspose.Cells supporta un vasto insieme di formule matematiche, stringhe, Boolean, data/ora, statistiche, database, ricerca e riferimento.

Utilizza la proprietà Formula della classe Cell per aggiungere una formula a una cella. Formule complesse, per esempio

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

, sono supportate anche in Aspose.Cells. Quando si applica una formula a una cella, inizia sempre la stringa con un segno uguale (=) come fai quando crei una formula in Microsoft Excel e utilizza una virgola (,) per delimitare i parametri della funzione.

Nell’esempio seguente, viene applicata una formula complessa alla prima cella di una raccolta di Cells fogli di lavoro. La formula utilizza una funzione built-in IF fornita da 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");

view raw Examples-CSharp-Formulas-ProcessDataUsingBuiltinfunction-1.cs hosted with ❤ by GitHub

Come utilizzare le funzioni aggiuntive

Possiamo avere alcune formule definite dall’utente che vogliamo includere come un add-in di Excel. Quando si imposta la funzione cella.Formula, le funzioni built-in funzionano correttamente, tuttavia c’è bisogno di impostare le funzioni o formule personalizzate utilizzando le funzioni aggiuntive.

Aspose.Cells fornisce funzionalità per registrare le funzioni aggiuntive utilizzando Worksheets.RegisterAddInFunction(). Successivamente, quando impostiamo cella.Formula = anyFunctionFromAddIn, il file Excel di output contiene il valore calcolato dalla funzione AddIn.

Il seguente file XLAM deve essere scaricato per registrare la funzione add-in nel codice di esempio sottostante. Allo stesso modo, il file di output “test_udf.xlsx” può essere scaricato per controllare i risultati.

TestUDF.xlam

test_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);

view raw Examples-CSharp-Formulas-RegisterAndCallFuncFromAddIn-1.cs hosted with ❤ by GitHub

Come utilizzare la formula matriciale

Le formule matriciali sono formule che prendono array, invece di numeri singoli, come argomenti delle funzioni che compongono la formula. Quando una formula matriciale viene visualizzata, è racchiusa da parentesi graffe ({}).

Alcune funzioni di Microsoft Excel restituiscono array di valori. Per calcolare più risultati con una formula matriciale, inserisci l’array in un intervallo di celle con lo stesso numero di righe e colonne degli argomenti dell’array.

È possibile applicare una formula matriciale a una cella chiamando il metodo SetArrayFormula della classe Cell. Il metodo SetArrayFormula richiede i seguenti parametri:

Formula Matriciale, la formula matriciale.
Numero di righe, il numero di righe per popolare il risultato della formula matriciale.
Numero di colonne, il numero di colonne per popolare il risultato della formula matriciale.

	// 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");

view raw Examples-CSharp-Formulas-ProcessDataUsingArrayFunction-1.cs hosted with ❤ by GitHub

Come utilizzare la formula R1C1

Aggiungi una formula di stile di riferimento R1C1 a una cella con la proprietà R1C1Formula della classe 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);

	// 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");

view raw Examples-CSharp-Formulas-ProcessDataUsingR1C1-1.cs hosted with ❤ by GitHub

Argomenti avanzati

Formattare celle Calcola le formule