C# Open XML Find Row Offset of Excel

Question

I am using Open XML for excel manipulation in C#. I have scenario where I want Row number of merged cell using Cell Value like below is sheet and I will pass product name and it will return its row offset
Excel Template

I am using below linq query for that but it is not working in case of merged cell

Row trow= worksheetPart.Worksheet.Descendants<Row>().Where(r=>r.InnerText==ProductName).FirstOrDefault();

Row = (int)trow.RowIndex.Value;

Is there any way to find row index of merged cell

Thanks in advance

Answer 1

One of the things that is very important during use of OpenXML is you check the code you are writing with the XML Output of the Office Excel.
In order to do such, I suggest you install 7Zip and extract .Xlsx file and then check the content of Xmls inside the extracted folder.

Note: an office file included multiple XML files which packed as a single .Xlsx file.

Then go to Xl->Worksheet->Sheet1.xml

then you can see such an XML :

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    <dimension ref="A1:A4"/>
    <sheetViews>
        <sheetView tabSelected="1" workbookViewId="0">
            <selection sqref="A1:A4"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
    <sheetData>
        <row r="1" spans="1:1" x14ac:dyDescent="0.25">
            <c r="A1" s="1" t="s">
                <v>0</v>
            </c>
        </row>
        <row r="2" spans="1:1" x14ac:dyDescent="0.25">
            <c r="A2" s="1"/>
        </row>
        <row r="3" spans="1:1" x14ac:dyDescent="0.25">
            <c r="A3" s="1"/>
        </row>
        <row r="4" spans="1:1" x14ac:dyDescent="0.25">
            <c r="A4" s="1"/>
        </row>
    </sheetData>
    <mergeCells count="1">
        <mergeCell ref="A1:A4"/>
    </mergeCells>
    <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

In this excel file, I have merged A1 to A4 and you can see that in <mergeCells count="1"> at the end of XML. Also, I wrote Hello inside merged cells

So, I believe in order to check Merged cells you should use somehow.

inside the file SharedString.xml inside the extracted folder, you can see :

<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="1" uniqueCount="1"><si><t>Hello</t></si></sst>

so you can see that the count attribute's value is equal to <mergeCells /> tag's count value.

These were an introduction to tell you that OpenXml is just a proper Library that just parses an XML.

if you see the below code that I wrote just by reading the XML files and attention to the variables name you will see that i just tried to go through XML Tags and found my proper value.

using (SpreadsheetDocument document = SpreadsheetDocument.Open(@"D:\test.xlsx", true))
{
    WorkbookPart wbPart = document.WorkbookPart;
    List<WorksheetPart> wsParts = wbPart.WorksheetParts.ToList();
    if (wsParts != null && wsParts.Any())
    {
        WorksheetPart SheetPart1 = wsParts.First();
        MergeCells mergeCells = SheetPart1.Worksheet.Elements<MergeCells>().First();
        foreach (MergeCell mergeCell in mergeCells)
        {
            string[] mergedRow = mergeCell.Reference.Value.Split(new string[]{":"},StringSplitOptions.None);
            Cell theCell = SheetPart1.Worksheet.Descendants<Cell>().
                Where(c => c.CellReference == mergedRow[0]).FirstOrDefault();
            string value = GetCellValue(document, theCell);
        }
    }
}

adn then:

public string GetCellValue(SpreadsheetDocument document, Cell cell)
{
    SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
    string value = string.Empty;
    if (cell.CellValue != null)
    {
        value = cell.CellValue.InnerXml;
        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
        }

    }
    return value;
}