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

</p>
<p>public static void UpdateExcelUsingOpenXMLSDK(string fileName)<br />
{<br />
// Open the document for editing.<br />
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(fileName, true))<br />
{<br />
// Access the main Workbook part, which contains all references.<br />
WorkbookPart workbookPart = spreadSheet.WorkbookPart;<br />
// get sheet by name<br />
Sheet sheet = workbookPart.Workbook.Descendants&lt;Sheet&gt;().Where(s =&gt; s.Name == "Sheet1").FirstOrDefault();</p>
<p>// get worksheetpart by sheet id<br />
WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id.Value) as WorksheetPart;</p>
<p>// The SheetData object will contain all the data.<br />
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild();</p>
<p>Cell formulaCell = InsertCellInWorksheet("B", 10, worksheetPart);<br />
formulaCell.DataType = new EnumValue&lt;CellValues&gt;(CellValues.Number);<br />
formulaCell.CellFormula = new CellFormula("SUM(B2:B7)");</p>
<p>// Save the worksheet.<br />
worksheetPart.Worksheet.Save();</p>
<p>// for recacluation of formula<br />
spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;<br />
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;</p>
<p>}<br />
}</p>
<p>private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)<br />
{<br />
Worksheet worksheet = worksheetPart.Worksheet;<br />
SheetData sheetData = worksheet.GetFirstChild&lt;SheetData&gt;();<br />
string cellReference = columnName + rowIndex;</p>
<p>// If the worksheet does not contain a row with the specified row index, insert one.<br />
Row row;<br />
if (sheetData.Elements&lt;Row&gt;().Where(r =&gt; r.RowIndex == rowIndex).Count() != 0)<br />
{<br />
row = sheetData.Elements&lt;Row&gt;().Where(r =&gt; r.RowIndex == rowIndex).First();<br />
}<br />
else<br />
{<br />
row = new Row() { RowIndex = rowIndex };<br />
sheetData.Append(row);<br />
}</p>
<p>// If there is not a cell with the specified column name, insert one.<br />
if (row.Elements&lt;Cell&gt;().Where(c =&gt; c.CellReference.Value == columnName + rowIndex).Count() &gt; 0)<br />
{<br />
return row.Elements&lt;Cell&gt;().Where(c =&gt; c.CellReference.Value == cellReference).First();<br />
}<br />
else<br />
{<br />
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.<br />
Cell refCell = null;<br />
foreach (Cell cell in row.Elements&lt;Cell&gt;())<br />
{<br />
if (cell.CellReference.Value.Length == cellReference.Length)<br />
{<br />
if (string.Compare(cell.CellReference.Value, cellReference, true) &gt; 0)<br />
{<br />
refCell = cell;<br />
break;<br />
}<br />
}<br />
}</p>
<p>Cell newCell = new Cell() { CellReference = cellReference };<br />
row.InsertBefore(newCell, refCell);</p>
<p>worksheet.Save();<br />
return newCell;<br />
}<br />
}</p>
<p>

Using EPPlus

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

</p>
<p>public void UpdateExcelUsingEPPlus(string fileName)<br />
{<br />
FileInfo fileInfo = new FileInfo(fileName);<br />
ExcelPackage p = new ExcelPackage(fileInfo);<br />
ExcelWorksheet myWorksheet = p.Workbook.Worksheets["Sheet1"];</p>
<p>myWorksheet.Cells["B15"].Formula = "SUM(B2:B7)";</p>
<p>p.Save();</p>
<p>}</p>
<p>

Hope it helps..

Author: Nishant Rana

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