Introduction

This code can be used to add updated rows and cell values in an Excel sheet (.xlsx) using open XML and C#.
For that, you need to add open XML to your application. 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. string fileName = @ "file name with .xlsx extension and file path ";
  3. using(SpreadsheetDocument spreadSheet =
  4. SpreadsheetDocument.Open(fileName, true)) {
  5. AddUpdateCellValue(spreadSheet, "test sheet1", 8, "A", "test data1");
  6. AddUpdateCellValue(spreadSheet, "test sheet2", 8, "B", "test data2");
  7. AddUpdateCellValue(spreadSheet, "test sheet3", 8, "A", "test data3");
  8. }
  9. }
  11. public void AddUpdateCellValue(SpreadsheetDocument spreadSheet, string sheetname,
  12. uint rowIndex, string columnName, string text) {
  13. // Opening document for editing
  14. WorksheetPart worksheetPart =
  15. RetrieveSheetPartByName(spreadSheet, sheetname);
  16. if (worksheetPart != null) {
  17. Cell cell = InsertCellInSheet(columnName, (rowIndex + 1), worksheetPart);
  18. cell.CellValue = new CellValue(text);
  19. //cell datatype
  20. cell.DataType =
  21. new EnumValue < CellValues > (CellValues.String);
  22. // Save the worksheet.
  23. worksheetPart.Worksheet.Save();
  24. }
  25. }
  26. //retrieve sheetpart
  27. public WorksheetPart RetrieveSheetPartByName(SpreadsheetDocument document,
  28. string sheetName) {
  29. IEnumerable < Sheet > sheets =
  30. document.WorkbookPart.Workbook.GetFirstChild < Sheets > ().
  31. Elements < Sheet > ().Where(s => s.Name == sheetName);
  32. if (sheets.Count() == 0)
  33. return null;
  35. string relationshipId = sheets.First().Id.Value;
  36. WorksheetPart worksheetPart = (WorksheetPart)
  37. document.WorkbookPart.GetPartById(relationshipId);
  38. return worksheetPart;
  39. }
  41. //insert cell in sheet based on column and row index
  42. public Cell InsertCellInSheet(string columnName, uint rowIndex, WorksheetPart worksheetPart) {
  43. Worksheet worksheet = worksheetPart.Worksheet;
  44. SheetData sheetData = worksheet.GetFirstChild < SheetData > ();
  45. string cellReference = columnName + rowIndex;
  46. Row row;
  47. //check whether row exist or not
  48. //if row exist
  49. if (sheetData.Elements < Row > ().Where(r => r.RowIndex == rowIndex).Count() != 0)
  50. row = sheetData.Elements < Row > ().Where(r => r.RowIndex == rowIndex).First();
  51. //if row does not exist then it will create new row
  52. else {
  53. row = new Row() {
  54. RowIndex = rowIndex
  55. };
  56. sheetData.Append(row);
  57. }
  58. //check whether cell exist or not
  59. //if cell exist
  60. if (row.Elements < Cell > ().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
  61. return row.Elements < Cell > ().Where(c => c.CellReference.Value == cellReference).First();
  62. //if cell does not exist
  63. else {
  64. Cell refCell = null;
  65. foreach(Cell cell in row.Elements < Cell > ()) {
  66. if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) {
  67. refCell = cell;
  68. break;
  69. }
  70. }
  71. Cell newCell = new Cell() {
  72. CellReference = cellReference
  73. };
  74. row.InsertBefore(newCell, refCell);
  75. worksheet.Save();
  76. return newCell;
  77. }
  78. }
  80. // retrieve cell based on column and row index
  81. public Cell RetreiveCell(Worksheet worksheet,
  82. string columnName, uint rowIndex) {
  83. Row row = RetrieveRow(worksheet, rowIndex);
  84. var newRow = new Row() {
  85. RowIndex = (uint) rowIndex + 1
  86. };
  87. //adding new row
  88. worksheet.InsertAt(newRow, Convert.ToInt32(rowIndex + 1));
  89. //create cell with value
  90. Cell cell = new Cell();
  91. cell.CellValue = new CellValue("");
  92. cell.DataType =
  93. new EnumValue < CellValues > (CellValues.String);
  94. newRow.AddAnnotation(cell);
  95. worksheet.Save();
  97. row = newRow;
  98. if (row == null)
  99. return null;
  100. return row.Elements < Cell > ().Where(c => string.Compare(c.CellReference.Value, columnName +
  101. (rowIndex + 1), true) == 0).First();
  102. }
  104. // it will return a row based on worksheet and rowindex
  105. public Row RetrieveRow(Worksheet worksheet, uint rowIndex) {
  106. return worksheet.GetFirstChild < SheetData > ().
  107. Elements < Row > ().Where(r => r.RowIndex == rowIndex).First();
  108. }