Sharing a sample code that updates a particular cell’s value in Excel Spreadsheet.
Here we are updating cell B4
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 cell = GetCell(worksheetPart.Worksheet, "B", 4); cell.CellValue = new CellValue("10"); cell.DataType = new EnumValue<CellValues>(CellValues.Number); // 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 GetCell(Worksheet worksheet, string columnName, uint rowIndex) { Row row = GetRow(worksheet, rowIndex); if (row == null) return null; var FirstRow = row.Elements<Cell>().Where(c => string.Compare (c.CellReference.Value, columnName + rowIndex, true) == 0).FirstOrDefault(); if (FirstRow == null) return null; return FirstRow; } private static Row GetRow(Worksheet worksheet , uint rowIndex) { Row row = worksheet.GetFirstChild<SheetData>(). Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex); if (row == null) { throw new ArgumentException(String.Format("No row with index {0} found in spreadsheet", rowIndex)); } return row; }
Now the same code 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[4, 2].Value = 10; p.Save(); }
Hope it helps..
Hi ,
I am impressed with this EPPlus, but i am facing a problem that is i am using SpreadsheetDocument as my input, so can you pls guide me how to load ExcelWorksheet from SpreadsheetDocument ?
Thanks In Advance
LikeLike
Hi Nishant,
Great Post bro. I have Excel file in my project folder and I want to change all chart data. How can I perform in asp.net mvc using Open XML.
Thanks in advance.
LikeLike
I am using http://www.ZetExcel.com
Try it
It helped me a lot !
LikeLike