Excel C# OpenXML : how can i get the position of the cell in excel after adding new row,(Row row=new Row()); without using excel Interpol or Macro

Question

my question is about getting the position of the cell in excel without using excel Interpol or Macro. just c# and OpenXML. position like(A3).

Answer 1

When you have your cell object do this to get string "A1" for example :

myCell.CellReference.Value

If you want to find a cell by its reference do this :

public static Cell getCellByReference(string cellReference, Worksheet ws)
{
    return ws.Descendants<Cell>().Where(c => c.CellReference.Value == cellReference).FirstOrDefault();
}

Cell myCell = getCellByReference("A1", actualWorksheet)

Answer 2

There is a helpful e-book called Open XML Explained that was written a while back(2007?). It explains many of the complicated details of the OpenXML specification.

The e-book says there are 2 ways to determine the position of a cell: with the CellRefernece property or without the CellReference property.

With CellReference property

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">  (from p.60)
    <sheetData> 
        <row r="5"> 
            <c r="E5"> 
                <v>1234</v> 
            </c> 
         </row> 
     </sheetData> 
</worksheet>

This is the simpler case. When you read Cell data, there can exist a CellReference property like in the r attribute of the Cell and Row tags above. If so, this CellReferernce property is a string in the form of "A1" or "G32", where the letter(s) denotes the Column and number denote the row. The cell above has CellReference of "E5" which corresponds to Column E, Row 5 of the spreadsheet. You will want to read this value to determine the position of the cell. Here is an SO question and answers that can help you parse the CellReference string and get the column index to help determine position.

Without CellReference property

<worksheet xmlns="http://.../spreadsheetml/2006/main"> (see p. 58)
    <sheetData> 
        <row> 
            <c> 
                <v>42</v> 
            </c> 
         </row> 
    </sheetData> 
</worksheet>

The CellRefence property is not guaranteed, like in the above xml. If the CellReference is not there, then you need to determine the position by the position of the data in the file. So, the first row of data corresponds to the first row of the spreadsheet. Same as with the cell position within a row.