Question 87678 in ASP.NET Question posted in
The official documentation can be found here.

How to get a merged cell from Excel using DocumentFormat.OpenXML or ClosedXML C# – Asp.net

I need to get the area of the merged cell, the line number on which the area ends in Excel using only DocumentFormat.OpenXml or ClosedXML, how to do this for each cell?

enter image description here

Tags:

2

Answers


  1. 0

    I found this to be a little clunky but I believe this to be the correct approach.

    private static void GetMergedCells()
{
    var fileName = $"c:\temp\Data.xlsm";

    // Open the document.
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
    {
        // Get the WorkbookPart object.
        var workbookPart = document.WorkbookPart;

        // Get the first worksheet in the document.  You can change this as need be.
        var worksheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault();

        // Retrieve the WorksheetPart using the Part ID from the previous "Sheet" object.
        var worksheetPart = (WorksheetPart)workbookPart.GetPartById(worksheet.Id);

        // Retrieve the MergeCells element, this will contain all MergeCell elements.
        var mergeCellsList = worksheetPart.Worksheet.Elements<MergeCells>();

        // Now loop through and spit out each range reference for the merged cells.
        // You'll need to process the range either as a string or turn it into another
        // object that gives you the end row.
        foreach (var mergeCells in mergeCellsList)
        {
            foreach (MergeCell mergeCell in mergeCells)
            {
                Console.WriteLine(mergeCell.Reference);
            }
        }
    }
}

    If you couldn’t already tell, this is using DocumentFormat.OpenXml.Spreadsheet

    Login or Signup to reply.
  2. 0

    Using ClosedXML, this could be done with:

    var ws = workbook.Worksheet("Sheet 1");
var cell = ws.Cell("A2");
var mergedRange = cell.MergedRange();
var lastCell = mergedRange.LastCell();

// or 
var lastCellAddress = mergedRange.RangeAddress.LastAddress;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search