locked
Merge Column RRS feed

  • Question

  • User-1499457942 posted

    Hi

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

    Thanks

    Friday, June 15, 2018 6:11 AM

All replies

  • 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

    Friday, June 15, 2018 8:43 AM
  • 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

    Monday, June 18, 2018 5:57 AM
  • 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();
                    }
                }
            }
    Tuesday, June 19, 2018 7:35 AM