Merge Column

Question

User-1499457942 posted

Hi

  How we can merge 2 columns in excel using c# and then display value.

Thanks

Answer 1

User1086429607 posted

Again without seeing your code of what you already have you can try

ExcelWorksheetSheet.Range[eWSheet.Cells[1, 1], eWSheet.Cells[4, 1]].Merge();

Not sure if this would be applicable to you: https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/interop/how-to-access-office-onterop-objects

Answer 2

User283571144 posted

Hi JagjitSingh,

How we can merge 2 columns in excel using c# and then display value.

According to you description, I sugggest you could try to use Nopi libarary to help you achieve merging the 2 colums in the excel.

I suggest you could firstly create the CellRangeAddress and use AddMergedRegion method to merge the columns.

More details, you could refer to below codes:

Install-Package NPOI -Version 2.3.0

Notice: I used the LastRowNum to get current excel's rows to achieve merge 2 column.

            using (FileStream stream = new FileStream(@"D:\Merge.xls", FileMode.Create, FileAccess.Write))
            {
                IWorkbook wb = new HSSFWorkbook();
                ISheet sheet = wb.CreateSheet("new sheet");

                IRow row = sheet.CreateRow(0);
                ICell cell = row.CreateCell(0);
                ICell cell2 = row.CreateCell(1);
                cell.SetCellValue("This is a test of merging");
                IRow row1 = sheet.CreateRow(1);
                ICell cell1 = row1.CreateCell(0);
                ICell cell3 = row1.CreateCell(1);
                cell1.SetCellValue("This is a test of merging2");
                for (int i = 0; i < sheet.LastRowNum; i++)
                {
                    sheet.AddMergedRegion(new CellRangeAddress(
                       i, //first row (0-based)
                       i, //last row  (0-based)
                       0, //first column (0-based)
                       1  //last column  (0-based)
                 ));
                }
                wb.Write(stream);
             }

Result:

Best Regards,

Brando

Answer 3

User1359092739 posted

  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();
                }
            }
        }