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..
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