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.

// 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();
row = new Row() { RowIndex = rowIndex };

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

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

return newCell;

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



Hope it helps..

Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology !

One thought on “Insert Formula in Cell using C# (OpenXML and EPPlus Library)”

Please share your thoughts

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.