Asked by:
Merge Column

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