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
</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 cell = GetCell(worksheetPart.Worksheet, "B", 4);</p> <p>cell.CellValue = new CellValue("10");<br /> cell.DataType = new EnumValue<CellValues>(CellValues.Number);</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 GetCell(Worksheet worksheet,<br /> string columnName, uint rowIndex)<br /> {<br /> Row row = GetRow(worksheet, rowIndex);</p> <p>if (row == null) return null;</p> <p>var FirstRow = row.Elements<Cell>().Where(c => string.Compare<br /> (c.CellReference.Value, columnName +<br /> rowIndex, true) == 0).FirstOrDefault();</p> <p>if (FirstRow == null) return null;</p> <p>return FirstRow;<br /> }</p> <p>private static Row GetRow(Worksheet worksheet , uint rowIndex)<br /> {<br /> Row row = worksheet.GetFirstChild<SheetData>().<br /> Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);<br /> if (row == null)<br /> {<br /> throw new ArgumentException(String.Format("No row with index {0} found in spreadsheet", rowIndex));<br /> }<br /> return row;<br /> }</p> <p>
Now the same code 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"];<br /> myWorksheet.Cells[4, 2].Value = 10;<br /> p.Save();</p> <p>}</p> <p>
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