Coverting VB code to C# code to unmerge the merged cells and fill the same value to the cells of the merged area

Question

I would like to ask you could give the help,

I tried to covert VB code to C# to let the code work the same behavior.
The purpose of the code is unmerging of merged cells and fill the same value to the merged area.
The VB code worked as I expected, but the converted C# code does not work.
The C# code fills the first cell of the cell area with the value and the rest of the cells are empty, NOT filled with the previous value.

I think I need to change something in C# code. Could you advise me on the that point I need to revise?
I guess this part needs some more change in the below C# codes.

Thanks in advance,

(VB code) worked correctly

For Each Rng In excelcells
    If Rng.MergeCells Then
        With Rng.MergeArea
            .UnMerge
            .Formula = Rng.Formula
        End With
    End If
Next

=>
(C# code) does not work

foreach (Excel.Range Rng in excelcells)
{
    if (Rng.MergeCells)
    {
        Rng.MergeArea.UnMerge();
        Rng.MergeArea.Formula = Rng.Formula; 
    }
}

Full reference code:

(VB code)

Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Imports System.IO

Public Class Form1
Dim ExcelApp As Excel.Application = Nothing
Dim wb As Excel.Workbook = Nothing
Dim ws As Excel.Worksheet = Nothing
Dim rngAll As Range, xCell As Range
Dim excelcells As Excel.Range

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
     'Excel Write
     Try
         ExcelApp = New Excel.Application()
         wb = ExcelApp.Workbooks.Open("D:\test.xlsx")
         ws = CType(wb.Worksheets("GLOBAL ENDC"), Excel.Worksheet)
         ws.Activate()
         excelcells = ws.UsedRange
         'Set WorkRng = Application.Selection
         'Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
     
     
         ExcelApp.ScreenUpdating = False
         ExcelApp.DisplayAlerts = False
         For Each Rng In excelcells
             If Rng.MergeCells Then
                 With Rng.MergeArea
                     .UnMerge
                     .Formula = Rng.Formula
                 End With
             End If
         Next
         ExcelApp.DisplayAlerts = True
         ExcelApp.ScreenUpdating = True
         ..
         wb.SaveAs("D:\test_unmerged.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, True, False,
                                XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing)
         wb.Saved = True
         wb.Close(False, Excel.XlFileFormat.xlWorkbookNormal)
         ExcelApp.Quit()
     
     Catch ex As Exception
         ReleaseObject(ws)
         ReleaseObject(wb)
         ReleaseObject(ExcelApp)
         GC.Collect()
     Finally
         ReleaseObject(ws)
         ReleaseObject(wb)
         ReleaseObject(ExcelApp)
         GC.Collect()
     End Try
End Sub

Private Sub ReleaseObject(ByVal obj As Object)
     Try
         If Not obj Is Nothing Then
             Runtime.InteropServices.Marshal.ReleaseComObject(obj)
             obj = Nothing
         End If
     Catch
         obj = Nothing
     Finally
         GC.Collect()
     End Try
End Sub

(C# code )

private void UnmergeCell()
{
    //Instantiate the application object
    Excel.Application excelapp = new Excel.Application();
    excelapp.Visible = false;

    string fullPath = @"D:\test.xlsx";

    //Open the Excel file

    Excel.Workbook excelappworkbook = excelapp.Workbooks.Open(
        fullPath,
        Type.Missing, Type.Missing, true, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing);

    Excel.Worksheet excelworksheet = (Excel.Worksheet)excelappworkbook.Worksheets.get_Item("Sheet01");
    Excel.Range excelcells = excelworksheet.UsedRange;

    excelapp.ScreenUpdating = false;
    excelapp.DisplayAlerts = false;
    foreach (Excel.Range Rng in excelcells)
    {
        if (Rng.MergeCells)
        {
            Rng.MergeArea.UnMerge();
            Rng.MergeArea.Formula = Rng.Formula; 
        }
    }
    excelapp.DisplayAlerts = true;
    excelapp.ScreenUpdating = true;

    excelappworkbook.SaveAs(@"D:\test_unmerged.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false,
                           XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);

    //Quit the application
    excelappworkbook.Close(false, Type.Missing, Type.Missing);
    excelapp.Quit();
   finally
    {
        // Clean up
        ReleaseExcelObject(ws);
        ReleaseExcelObject(wb);
        ReleaseExcelObject(excelApp);
    }
}

private static void ReleaseExcelObject(object obj)
{
    try
    {
        if (obj != null)
        {
            Marshal.ReleaseComObject(obj);
            obj = null;
        }
    }
    catch (Exception ex)
    {
        obj = null;
        throw ex;
    }
    finally
    {
        GC.Collect();
    }
}

Have you debugged and stepped into the code yet to observe what it is actually doing in there? — Dan Csharpster, Dec 20 '20 at 14:03
The `With` clause captures the value of its argument when you enter it. Thus `With Rng.MergeArea` remembers the pre-Unmerge `MergeArea`, whereas in C# you are re-querying `Rng.MergeArea` after the unmerge, at which point it has changed. — GSerg, Dec 20 '20 at 14:12
Regarding your `void ReleaseExcelObject`, please see https://stackoverflow.com/a/25135685/11683. — GSerg, Dec 20 '20 at 14:13
@ GSerg thanks for the advice. you pointed out what I messed and also thanks for the useful link about ReleaseExcelObeject, you mean the current ReleaseExcelObject would make any issue right? — smartrobby, Dec 26 '20 at 05:29

score 1 · Accepted Answer · answered Dec 20 '20 at 14:04

1

Can you try this and see if this conversion works better for you?

            foreach (var Rng in excelcells)
            {
                if (Rng.MergeCells)
                {
                    {
                        var withBlock = Rng.MergeArea;
                        withBlock.UnMerge();
                        withBlock.Formula = Rng.Formula;
                    }
                }
            }

FYI, this is the converter I used.

answered Dec 20 '20 at 14:04

Dan Csharpster

2,662
1
26
50

1

Thanks a lot, I appreciate it. your code worked as I expected it. – smartrobby Dec 26 '20 at 05:20

Coverting VB code to C# code to unmerge the merged cells and fill the same value to the cells of the merged area

1 Answers1