none
how to merge excel columns using c#

Answers

  • Hi ammu,

    How is everything going ?
    I've figured out the solution, and this solution is used to merge columns for a Workbook that is already open.
    You'll see it underneath.

    Code Snippet:

            static void Main(string[] args)
            {
                Excel.Application myExcelApp = null;
                try
                {
                    myExcelApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Excel.Application;
                }
                catch (System.Runtime.InteropServices.COMException e)
                {
                    MessageBox.Show(string.Format("Excel application was not running:{0}", e.Message));
                    return;
                }
                if (myExcelApp != null)
                {
                    myExcelApp.Visible = true;
                    Excel.Range cell = myExcelApp.Cells[1, 1];//Represent A1
                    
                    long lastRow = myExcelApp.ActiveSheet.UsedRange.Rows.Count;
                    Excel.Range column =cell.EntireColumn.Range[cell, myExcelApp.Cells[lastRow, 1]];//Represent the column A
                                    
                    foreach (Excel.Range r in column.Rows)
                    {
                        Excel.Range TobeMerged = myExcelApp.Union(r,r.Offset[0,1]);
                        r.Value = r.Value + r.Offset[0, 1].Value;
                        r.Offset[0, 1].Value = null;
                        TobeMerged.Merge();
                    }
                }
            }

    Columns before and after:
    before

    after

    Hope these info could help you.

    Regards,
    Fermin

    Wednesday, April 11, 2012 2:18 AM

All replies

  • we can use merge function  in excel.

    ammu



    • Edited by ammu sree Tuesday, April 10, 2012 6:03 AM
    Tuesday, April 10, 2012 6:03 AM
  • Hi ammu,

    I'm new here and interested in this issue. I'll try to figure out this problem using automation executable.

    Tuesday, April 10, 2012 6:26 AM
  • Amu/Fermin,

    To merge "Columns" you can use .Merge() in C#

    See this VB.Net example. I am sure you can convert it to C#.

    Topic: VB.Net and Excel (See point 17)

    Link: http://www.siddharthrout.com/vb-dot-net-and-excel/


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.





    Tuesday, April 10, 2012 10:54 AM
    Moderator
  • Hi ammu,

    How is everything going ?
    I've figured out the solution, and this solution is used to merge columns for a Workbook that is already open.
    You'll see it underneath.

    Code Snippet:

            static void Main(string[] args)
            {
                Excel.Application myExcelApp = null;
                try
                {
                    myExcelApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Excel.Application;
                }
                catch (System.Runtime.InteropServices.COMException e)
                {
                    MessageBox.Show(string.Format("Excel application was not running:{0}", e.Message));
                    return;
                }
                if (myExcelApp != null)
                {
                    myExcelApp.Visible = true;
                    Excel.Range cell = myExcelApp.Cells[1, 1];//Represent A1
                    
                    long lastRow = myExcelApp.ActiveSheet.UsedRange.Rows.Count;
                    Excel.Range column =cell.EntireColumn.Range[cell, myExcelApp.Cells[lastRow, 1]];//Represent the column A
                                    
                    foreach (Excel.Range r in column.Rows)
                    {
                        Excel.Range TobeMerged = myExcelApp.Union(r,r.Offset[0,1]);
                        r.Value = r.Value + r.Offset[0, 1].Value;
                        r.Offset[0, 1].Value = null;
                        TobeMerged.Merge();
                    }
                }
            }

    Columns before and after:
    before

    after

    Hope these info could help you.

    Regards,
    Fermin

    Wednesday, April 11, 2012 2:18 AM
  • Hi ammu,

    Welcome to the MSDN forum!

    So how is it going with the problem currently?

    Please feel free to let us know if you need any help.

    Have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, April 11, 2012 3:42 AM
    Moderator