How to – Update Cell value in Excel Spreadsheet using C# (Open XML and EPPlus library)

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&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 cell = GetCell(worksheetPart.Worksheet, "B", 4);</p>
<p>cell.CellValue = new CellValue("10");<br />
cell.DataType = new EnumValue&lt;CellValues&gt;(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&lt;Cell&gt;().Where(c =&gt; 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&lt;SheetData&gt;().<br />
Elements&lt;Row&gt;().FirstOrDefault(r =&gt; 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..

Author: Nishant Rana

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