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<Sheet>().Where(s => 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<CellValues>(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<SheetData>();<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<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)<br /> {<br /> row = sheetData.Elements<Row>().Where(r => 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<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)<br /> {<br /> return row.Elements<Cell>().Where(c => 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<Cell>())<br /> {<br /> if (cell.CellReference.Value.Length == cellReference.Length)<br /> {<br /> if (string.Compare(cell.CellReference.Value, cellReference, true) > 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..
You posted this a bit ago but it was EXACTLY what I needed with EPPlus so THANK YOU very much!
LikeLike
Hello. I am using ZetExcel’s help. Try them. I learned a lot
LikeLike