none
C# Or LINQ - how to merge two data sheets with different columns in c# RRS feed

  • Question

  • Hi,

    I want to merge two data sheets with different columns in c#..

    For example,

    Based on my sheets and column mention, it should merge the data and display in final output as datatable.

    Sheet1 ==> columnA data and columnC data

    sheet3 ==> columnD data and Column F data

    datatable result ==> merge of ColumnA & ColumnC as one column

                                   merge of ColumnD & ColumnF as another column

    my input string is sheet and column.

    for example,

    inPut string Sheets ==> "Sheet1" and "Sheet3"

    Input string Columns ==> ColumnA & ColumnC(Sheet1)

                                           ColumnD & ColumnF(Sheet3)...

    How to form code in either LINQ or C#..?

                                                 


    • Edited by Gani tpt Monday, December 18, 2017 5:52 AM
    Monday, December 18, 2017 5:51 AM

Answers

  • Hi Gani tpt,

    you can try to refer example below.

    I create 3 sheets.

    Sheet1 and sheet2 contains the data as per your requirement.

    when I click button it will copy and merge data in Sheet3.

    further you can use it as per your requirement.

    Data in Sheet1:

    Sheet2:

    Code:

     private void button1_Click(object sender, EventArgs e)
            {
                Excel.Application excelApplication = new Excel.Application();
                Excel.Workbook srcworkBook = excelApplication.Workbooks.Open(@"C:\Users\v-padee\Desktop\demo.xlsx");
                excelApplication.Visible = true;
                Excel.Worksheet srcworkSheet1 = srcworkBook.Worksheets.get_Item(1);
                Excel.Worksheet srcworkSheet2 = srcworkBook.Worksheets.get_Item(2);
                Excel.Worksheet srcworkSheet3 = srcworkBook.Worksheets.get_Item(3);
                int intTotalRows = srcworkSheet1.Rows.Count;
                int lastRow = srcworkSheet1.Cells[intTotalRows, 1].End(Excel.XlDirection.xlUp).Row;
                Excel.Range rng;
    
                rng = srcworkSheet1.Range["A1:A" + lastRow];
    
                foreach (Excel.Range cell in rng.Cells)
                {
                    srcworkSheet3.Range[cell.Address].Value = cell.Value + "," + cell.Offset[0, 2].Value;
                     //MessageBox.Show(cell.Value + cell.Offset[0, 2].Value);
                }
    
    
                int intTotalRows1 = srcworkSheet2.Rows.Count;
                int lastRow1 = srcworkSheet2.Cells[intTotalRows1, 4].End(Excel.XlDirection.xlUp).Row;
                Excel.Range rng1;
    
                rng1 = srcworkSheet2.Range["D1:D" + lastRow1];
    
                foreach (Excel.Range cell in rng1.Cells)
                {
                    srcworkSheet3.Range[cell.Address].Value = cell.Value + "," + cell.Offset[0, 2].Value;
                    //MessageBox.Show(cell.Value + cell.Offset[0, 2].Value);
                }
                srcworkBook.Save();
                srcworkBook.Close();
            }

    Output:

    further you can modify the code as per your need.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Tuesday, December 19, 2017 9:57 AM
    Tuesday, December 19, 2017 6:38 AM
    Moderator