C# Open XML Find Row Offset of Excel

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

asked Jan 1 at 5:26

Sharanaiyya Swami

327

add a comment |

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

asked Jan 1 at 5:26

Sharanaiyya Swami

327

add a comment |

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

asked Jan 1 at 5:26

Sharanaiyya Swami

327

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

c# merge find openxml openxml-sdk

asked Jan 1 at 5:26

Sharanaiyya Swami

327

asked Jan 1 at 5:26

Sharanaiyya Swami

327

asked Jan 1 at 5:26

Sharanaiyya Swami

327

asked Jan 1 at 5:26

Sharanaiyya Swami

327

asked Jan 1 at 5:26

Sharanaiyya Swami

327

add a comment |

1 Answer
1

active

oldest

votes

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;

}

answered Jan 1 at 6:32

Mohamad Armoon

594825

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53993191%2fc-sharp-open-xml-find-row-offset-of-excel%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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;

}

answered Jan 1 at 6:32

Mohamad Armoon

594825

add a comment |

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;

}

answered Jan 1 at 6:32

Mohamad Armoon

594825

add a comment |

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;

}

answered Jan 1 at 6:32

Mohamad Armoon

594825

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;

}

answered Jan 1 at 6:32

Mohamad Armoon

594825

answered Jan 1 at 6:32

Mohamad Armoon

594825

answered Jan 1 at 6:32

Mohamad Armoon

594825

answered Jan 1 at 6:32

Mohamad Armoon

594825

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu