Insert Formula in Cell using C# (OpenXML and EPPlus Library)

Sharing the sample code, we can use to insert forumala in a particular cell in Excel Spreadsheet.

First using Open XML SDK

public static void UpdateExcelUsingOpenXMLSDK(string fileName)
{
// Open the document for editing.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(fileName, true))
{
// Access the main Workbook part, which contains all references.
WorkbookPart workbookPart = spreadSheet.WorkbookPart;
// get sheet by name
Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").FirstOrDefault();

// get worksheetpart by sheet id
WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id.Value) as WorksheetPart;

// The SheetData object will contain all the data.
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild();

Cell formulaCell = InsertCellInWorksheet("B", 10, worksheetPart);
formulaCell.DataType = new EnumValue<CellValues>(CellValues.Number);
formulaCell.CellFormula = new CellFormula("SUM(B2:B7)");

// Save the worksheet.
worksheetPart.Worksheet.Save();

// for recacluation of formula
spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

}
}

private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string cellReference = columnName + rowIndex;

// If the worksheet does not contain a row with the specified row index, insert one.
Row row;
if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new Row() { RowIndex = rowIndex };
sheetData.Append(row);
}

// If there is not a cell with the specified column name, insert one.
if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
if (cell.CellReference.Value.Length == cellReference.Length)
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
}

Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);

worksheet.Save();
return newCell;
}
}

Using EPPlus

https://www.nuget.org/packages/EPPlus/

public void UpdateExcelUsingEPPlus(string fileName)
{
FileInfo fileInfo = new FileInfo(fileName);
ExcelPackage p = new ExcelPackage(fileInfo);
ExcelWorksheet myWorksheet = p.Workbook.Worksheets["Sheet1"];

myWorksheet.Cells["B15"].Formula = "SUM(B2:B7)";

p.Save();

}

Hope it helps..

Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology ! View all posts by Nishant Rana