Unmerge cells and fill down missing values

GemBox.Spreadsheet
1

Hello,

Is there a way to unmerge cells and fill down missing values as follows:

Merged_cell [0,65535] Prod-SCS-VOBC-REQ-03448
Prod-SCS-VOBC-REQ-03886
Prod-SCS-VOBC-REQ-02929

desired result:

Merged_cell [0,65535] Prod-SCS-VOBC-REQ-03448
Merged_cell [0,65535] Prod-SCS-VOBC-REQ-03886
Merged_cell [0,65535] Prod-SCS-VOBC-REQ-02929
2

Hi,

Try this:

var workbook = ExcelFile.Load("Merged.xlsx");
var worksheet = workbook.Worksheets[0];

// Get the first cell of the merged cells.
var firstCell = worksheet.Cells["A1"];

var range = worksheet.Cells.GetSubrange(firstCell.MergedRange.Name);
range.Merged = false;

foreach (var cell in range)
    cell.Value = firstCell.Value;

workbook.Save("Unmerged.xlsx");

I hope this helps.

Regards,
Mario

3

I need to browse all cells and all worksheets, is this correct?
How to browse all cells in a sheet? Please edit the code below

For Each xlWorkSheet In xlWorkBook.Worksheets
    Dim iRows As Integer = xlWorkSheet.Rows.Count
    Dim iColumns = xlWorkSheet.CalculateMaxUsedColumns

    For i = 0 To iRows - 1
        For j = 0 To iColumns - 1
            Dim oCell = xlWorkSheet.Cells(i, j)
            If Not oCell.MergedRange Is Nothing Then

            Dim oRange = xlWorkSheet.Cells.GetSubrange(oCell.MergedRange.Name)
            oRange.Merged = False

            For Each cell In oRange
                cell.Value = oCell.Value
            Next
        End If
      Next
    Next
Next
4

Yes, that should work, I just changed the loops a bit (added ‘- 1’).

1 Like