C# how to get cell value by passing row and column in openXML?

Question

Net core and I am trying to read excel by open xml. I am trying to get cell value by passing certain row and column details.

Below is my implementation.

private DataTable ConvertExcelToDataTable(IFormFile uploadRegistration)
{
    //Create a new DataTable.
    var table = new DataTable();

    using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(uploadRegistration.OpenReadStream(), false))
    {
        WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
        IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
        string relationshipId = sheets.First().Id.Value;
        WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
        Worksheet workSheet = worksheetPart.Worksheet;
        SheetData sheetData = workSheet.GetFirstChild<SheetData>();
        IEnumerable<Row> rows = sheetData.Descendants<Row>();
        foreach (Cell cell in rows.ElementAt(0))
        {
            table.Columns.Add(GetCellValue(spreadSheetDocument, cell));
        }
        foreach (Row row in rows)
        {
            DataRow tempRow = table.NewRow();
            for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
            {
                // tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                Console.WriteLine(GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i))); // Here i am able to display cell value but not row and column details
            }
            table.Rows.Add(tempRow);
        }
    }
    table.Rows.RemoveAt(0);
    return table;
}

I have method which is trying to get row and column details as below

public static (int row, int col) ParseCellAddress(string s)
{
     StringBuilder stringBuilderCol = new StringBuilder();
     StringBuilder stringBuilderRow = new StringBuilder();

     foreach (char c in s)
     {
         if (char.IsLetter(c))
         {
             if (stringBuilderRow.Length > 0)
             {
                 throw new ArgumentException($"Invalid Cell adress {s}.");
             }

             stringBuilderCol.Append(c);
         }
         else if (char.IsDigit(c))
         {
             if (stringBuilderCol.Length < 1)
             {
                 throw new ArgumentException($"Invalid Cell adress {s}.");
             }

             stringBuilderRow.Append(c);
         }
         else
         {
             throw new ArgumentException($"Invalid Cell adress {s}.");
         }
     }

     if (stringBuilderRow.Length == 0 || stringBuilderCol.Length == 0)
     {
         throw new ArgumentException($"Invalid Cell adress {s}.");
     }

     int rowIdx = int.Parse(stringBuilderRow.ToString());

     // calc column index from column name

     string columnName = stringBuilderCol.ToString();

     int colIdx = 0;
     int pow = 1;
     for (int i = columnName.Length - 1; i >= 0; i--)
     {
         colIdx += (columnName[i] - 'A' + 1) * pow;
         pow *= 26;
     }

     return (rowIdx, colIdx);
 }

In the above method I am able to get row and column details.

Now I am looking is if i pass specific row and details for example, Row 2 column 4 then I would like to get cell value. I am looking for some method which will take row and column as input and get cell value

private string GetCellValue(int row, int column){
//return data for this particular row and column
}

Can someone help me with this. Any help would be appreciated. Thanks

Answer 1

You can try to get Specific Cell at Specific row using below

 private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
{
    Row row = worksheet.GetFirstChild<SheetData>().Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
    if (row != null)
    {
        return row.Elements<Cell>().FirstOrDefault(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0);
    }
    return null;
}

Sample code (Column names are A,B,C etc)

Cell cell = GetCell(worksheetPart.Worksheet, "A", 3);// Gets value for Column A at row#3