Use Formulas Functions to Process Data in VSTO and Aspose.Cells

Adding Cell Formula

VSTO

 // Note: To help you better, the code uses full namespacing

private void AddingCellFormula()
{
	// Access VSTO application
	Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application;

	// Access workbook
	Microsoft.Office.Interop.Excel.Workbook workbook = app.ActiveWorkbook;

	// Access worksheet
	Microsoft.Office.Interop.Excel.Worksheet m_sheet = workbook.Worksheets[1];

	// Access VSTO worksheet
	Microsoft.Office.Tools.Excel.Worksheet worksheet = Globals.Factory.GetVstoObject(m_sheet);

	// Access cells A1, A2, A3, A4
	Microsoft.Office.Interop.Excel.Range cellA1 = worksheet.Range["A1"];
	Microsoft.Office.Interop.Excel.Range cellA2 = worksheet.Range["A2"];
	Microsoft.Office.Interop.Excel.Range cellA3 = worksheet.Range["A3"];
	Microsoft.Office.Interop.Excel.Range cellA4 = worksheet.Range["A4"];

	// Set integer values in cells A1, A2 and A3
	cellA1.Value = 10;
	cellA2.Value = 20;
	cellA3.Value = 30;

	// Add formula in cell A4
	cellA4.Formula = "=Sum(A1:A3)";

	// Set the font to bold in cell A4
	cellA4.Font.Bold = true;

	// Set the background color to Yellow in cell A4
	cellA4.Interior.Color = Excel.XlRgbColor.rgbYellow;

	// Save the workbook
	workbook.SaveAs("OutputVsto.xlsx");

	// Quit the application
	app.Quit();
}

Aspose.Cells

 private static void AddingCellFormula()
 {
	 // Create workbook
	 Workbook workbook = new Workbook();

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

	 // Access cells A1, A2, A3, A4
	 Cell cellA1 = worksheet.Cells["A1"];
	 Cell cellA2 = worksheet.Cells["A2"];
	 Cell cellA3 = worksheet.Cells["A3"];
	 Cell cellA4 = worksheet.Cells["A4"];

	 // Set integer values in cells A1, A2 and A3
	 cellA1.Value = 10;
	 cellA2.Value = 20;
	 cellA3.Value = 30;

	 // Add formula in cell A4
	 cellA4.Formula = "=Sum(A1:A3)";

	 // Set the font to bold in cell A4
	 // and set the background color to Yellow in cell A4
	 Style style = cellA4.GetStyle();
	 style.Font.IsBold = true;
	 style.Pattern = BackgroundType.Solid;
	 style.ForegroundColor = Color.Yellow;
	 cellA4.SetStyle(style);

	 // Save the workbook
	 workbook.Save("OutputAspose.xlsx", SaveFormat.Xlsx);
 }

Download Sample Code

• GitHub
• SourceForge
• Bitbucket