ExcelHelper.cs

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Linq;

namespace ExcelHelper
{
    public static class ExcelHelper
    {
        public static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
        {
            var sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>()
                .Elements<Sheet>().Where(s => s.Name == sheetName);

            if (sheets.Count() == 0)
            {
                return null;
            }
            string id = sheets.First().Id.Value;
            return (WorksheetPart)document.WorkbookPart.GetPartById(id);
        }

        public static Cell GetCell(Worksheet workSheet, string column, uint rowIndex)
        {
            string cellReference = column + rowIndex;

            // 1. Get row
            Row row = GetRow(workSheet, rowIndex);

            // 2. Get Cell by cellReference
            Cell cell = row.Elements<Cell>().Where(c => c.CellReference == cellReference).FirstOrDefault();

            // 3. If cell not exist, create new one.
            if(cell == null)
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach(Cell c in row.Elements<Cell>())
                {
                    if (c.CellReference.Value.Length == cellReference.Length)
                    {
                        if (string.Compare(c.CellReference.Value, cellReference, true) > 0)
                        {
                            refCell = cell;
                            break;
                        }
                    }
                }
                cell = new Cell() { CellReference = cellReference };
                row.InsertBefore(cell, refCell);
                workSheet.Save();
            }

            // 4. Return Cell
            return cell;
        }

        public static Row GetRow(Worksheet workSheet, uint rowIndex)
        {
            // 1. Get row by rowIndex
            Row row = workSheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();

            // 2. If row not exist, create new one
            if(row == null)
            {
                row = new Row() { RowIndex = rowIndex };
                var index = workSheet.GetFirstChild<SheetData>().Elements<Row>().Where(e => e.RowIndex < rowIndex).Count();
                workSheet.GetFirstChild<SheetData>().InsertAt(row, index);
                workSheet.Save();
            }

            // 3. Return row
            return row;
            
        }
        
         // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
        // If the cell already exists, returns it. 
        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.  
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);

                worksheet.Save();
                return newCell;
            }
        }

        public static void UpdateCell(Worksheet workSheet, string column, uint rowIndex, string text)
        {
            Cell cell = GetCell(workSheet, column, rowIndex);
            cell.CellValue = new CellValue(text);
            cell.DataType = new EnumValue<CellValues>(CellValues.String);
            workSheet.Save();
        }
        
    }
}