How to Add/Update cell in excel using Open XML and C#

Introduction

This code used to add update rows and cell values in the excel sheet (.xlsx) using open XML and C#.

for that, you need to add open XML in your application.

To add please follow below steps:

 go to add references -> right-click on it - > click on manage nuget packages
-> Browse open XML -> you will see DocumentFormat.OpenXml -> click on install.

Code  

1. public void UpdateExcelSheetData()          
2.           {          
3.               string fileName = @"file name with .xlsx extension and file path ";          
4.               using (SpreadsheetDocument spreadSheet =          
5.                      SpreadsheetDocument.Open(fileName, true))          
6.               {          
7.                   AddUpdateCellValue(spreadSheet, "test sheet1", 8, "A", "test data1");          
8.                   AddUpdateCellValue(spreadSheet, "test sheet2", 8, "B", "test data2");          
9.                   AddUpdateCellValue(spreadSheet, "test sheet3", 8, "A", "test data3");          
10.               }          
11.           }          
12. public void AddUpdateCellValue(SpreadsheetDocument spreadSheet, string sheetname,          
13.               uint rowIndex, string columnName, string text)          
14.           {          
15.               // Opening document for editing          
16.               WorksheetPart worksheetPart =          
17.                         RetrieveSheetPartByName(spreadSheet, sheetname);          
18.               if (worksheetPart != null)          
19.               {          
20.                   Cell cell = InsertCellInSheet(columnName, (rowIndex + 1), worksheetPart);          
21.                   cell.CellValue = new CellValue(text);          
22.                   //cell datatype          
23.                   cell.DataType =          
24.                       new EnumValue<CellValues>(CellValues.String);          
25.                   // Save the worksheet.          
26.                   worksheetPart.Worksheet.Save();          
27.               }          
28.           }          
29.  //retrieve sheetpart          
30. public WorksheetPart RetrieveSheetPartByName(SpreadsheetDocument document,          
31.                string sheetName)          
32.           {          
33.               IEnumerable<Sheet> sheets =          
34.                  document.WorkbookPart.Workbook.GetFirstChild<Sheets>().          
35.                  Elements<Sheet>().Where(s => s.Name == sheetName);          
36.               if (sheets.Count() == 0)          
37.                   return null;          
38.           
39.               string relationshipId = sheets.First().Id.Value;          
40.               WorksheetPart worksheetPart = (WorksheetPart)          
41.                    document.WorkbookPart.GetPartById(relationshipId);          
42.               return worksheetPart;          
43.           }          
44.           
45.  //insert cell in sheet based on column and row index          
46. public Cell InsertCellInSheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)          
47.           {          
48.               Worksheet worksheet = worksheetPart.Worksheet;          
49.               SheetData sheetData = worksheet.GetFirstChild<SheetData>();          
50.               string cellReference = columnName + rowIndex;          
51.               Row row;          
52.               //check whether row exist or not          
53.               //if row exist          
54.               if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)          
55.                   row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();          
56.               //if row does not exist then it will create new row          
57.               else          
58.               {          
59.                   row = new Row() { RowIndex = rowIndex };          
60.                   sheetData.Append(row);          
61.               }          
62.               //check whether cell exist or not          
63.               //if cell exist          
64.               if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)          
65.                   return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();          
66.               //if cell does not exist          
67.               else          
68.               {          
69.                   Cell refCell = null;          
70.                   foreach (Cell cell in row.Elements<Cell>())          
71.                   {          
72.                       if (GetColumnIndex(cell.CellReference.Value) > GetColumnIndex(cellReference))  
73.                       {          
74.                           refCell = cell;          
75.                           break;          
76.                       }          
77.                   }          
78.                   Cell newCell = new Cell() { CellReference = cellReference };          
79.                   row.InsertBefore(newCell, refCell);          
80.                   worksheet.Save();          
81.                   return newCell;          
82.               }          
83.           }          
84.  // retrieve cell based on column and row index          
85. public Cell RetreiveCell(Worksheet worksheet,          
86.                     string columnName, uint rowIndex)          
87.           {          
88.               Row row = RetrieveRow(worksheet, rowIndex);          
89.               var newRow = new Row()          
90.               {          
91.                   RowIndex = (uint)rowIndex + 1          
92.               };          
93.               //adding new row          
94.               worksheet.InsertAt(newRow, Convert.ToInt32(rowIndex + 1));          
95.               //create cell with value          
96.               Cell cell = new Cell();          
97.               cell.CellValue = new CellValue("");          
98.               cell.DataType =          
99.                      new EnumValue<CellValues>(CellValues.String);          
100.               newRow.AddAnnotation(cell);          
101.               worksheet.Save();          
102.           
103.               row = newRow;          
104.               if (row == null)          
105.                   return null;          
106.               return row.Elements<Cell>().Where(c => string.Compare          
107.                      (c.CellReference.Value, columnName +          
108.                      (rowIndex + 1), true) == 0).First();          
109.           }          
110.  // it will return a row based on worksheet and rowindex          
111.  public Row RetrieveRow(Worksheet worksheet, uint rowIndex)          
112.           {          
113.               return worksheet.GetFirstChild<SheetData>().          
114.                 Elements<Row>().Where(r => r.RowIndex == rowIndex).First();          
115.           }     
116.   
117. private static int? GetColumnIndex(string cellRef)  
118. {  
119.     if (string.IsNullOrEmpty(cellRef))  
120.         return null;  
121.     cellRef = cellRef.ToUpper();  
122.     int columnIndex = -1;  
123.     int mulitplier = 1;  
124.   
125.     foreach (char c in cellRef.ToCharArray().Reverse())  
126.     {  
127.         if (char.IsLetter(c))  
128.         {  
129.             columnIndex += mulitplier * ((int)c - 64);  
130.             mulitplier = mulitplier * 26;  
131.         }  
132.     }  
133.     return columnIndex;  
134. }