none
C# with Excel - How to join to excel two sheets data into one datatable RRS feed

  • Question

  • Hi,

    I', using excel sheet. In that i have more sheets.

    i just want combine or join from two data sheets into one single datatable

    below screenshot for example and sample reference.

    I want output as datatable (after merging two sheet columns.

    1. Excel file is only one. In that we will have two sheet.

    2. from that excel we have to consider two sheets (sheet1 and sheet2 in single file)..not in two different excel file

    In that case how to proceed your code's...?.

    How to do this...?


    • Moved by CoolDadTx Thursday, December 14, 2017 2:50 PM Office related
    • Edited by Gani tpt Friday, December 15, 2017 6:22 AM
    Thursday, December 14, 2017 1:05 PM

Answers

  • Hi Gani tpt,

    After moving data to first row, please refer to below code.

    if (ws.Name == "SectionA")
                    {
                        lastRowIndex = ws.Cells[ws.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row;
                        if (lastRowIndex < 2) continue;
                        for (int i = 6; i <= lastRowIndex; i++)
                        {
                            //in Sheet SectionA, first column is EmpNo, Second column is Name
                            dt.Rows.Add(ws.Cells[i, 1].Value, ws.Cells[i, 2].Value);
                        }
                    }
                    else if (ws.Name == "SectionB")
                    {
                        lastRowIndex = ws.Cells[ws.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row;
                        if (lastRowIndex < 2) continue;
                        for (int i = 2; i <= lastRowIndex; i++)
                        {
                            //in Sheet SectionB, forth column is EmpNo, first column is Name
                            dt.Rows.Add(ws.Cells[i, 4].Value, ws.Cells[i, 1].Value);
                        }
                    }

    Best Regards,

    Terry


    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 Monday, December 18, 2017 11:43 AM
    Monday, December 18, 2017 9:33 AM

All replies

  • any update pls...

    1. Excel file is only one. In that we will have two sheet.

    2. from that excel we have to consider two sheets (sheet1 and sheet2 in single file)..not in two different excel file

    In that case how to proceed your code's...?.

    • Edited by Gani tpt Friday, December 15, 2017 6:22 AM
    Friday, December 15, 2017 4:55 AM
  • Hi Gani,

    I think you could share us two files through OneDrive, one is the source file which may contains two sheets based on your requirement, another is the result file you want.

    Regards,

    Tony


    Help each other

    Friday, December 15, 2017 7:59 AM
  • How to attached excel file...


    • Edited by Gani tpt Friday, December 15, 2017 8:23 AM
    Friday, December 15, 2017 8:23 AM
  • Upload to OneDrive, and share us link here with plain test

    Help each other

    Friday, December 15, 2017 8:39 AM
  • PFL...

    https://1drv.ms/x/s!AiSRcgO5FUmNaSOMY9ZaR1jR7rY


    pls. refer and find attached excel sheet for your reference..

    Attached Excel contains SectionA(Sheet),SectionB(sheet2) and Output as Datatable(Sheet3)

    So, i need output as datatable as mentioned in Output(sheet3)

    Friday, December 15, 2017 8:43 AM
  • Hello Gani tpt,

    We need know the data address so we could try to get the data value from the worksheet. Obviously, your screen shot does not show the actual layout. 

    I create sheets and put data like this.

    And then I used below code to combine data in different sheets into a datatable object. You could try to refer to it and adjust it for your need.

       Excel.Application xlApp = new Excel.Application();
                xlApp.Visible = true;
                Excel.Workbook wb = xlApp.Workbooks.Open(@"C:\Users\Administrator\Desktop\Test.xlsx");
                DataTable dt = new DataTable();
                dt.Columns.Add("EmpNo", typeof(int));
                dt.Columns.Add("Name", typeof(string));
                int lastRowIndex;
                foreach (Excel.Worksheet ws in wb.Worksheets) {
                    lastRowIndex = ws.Cells[ws.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row;
                    if (lastRowIndex < 2) continue;
                    for (int i = 2; i <= lastRowIndex; i++) {
                        dt.Rows.Add(ws.Cells[i, 1].Value, ws.Cells[i, 2].Value);
                    }
                }

    Here is the result.

    Best Regards,

    Terry


    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.

    Friday, December 15, 2017 8:49 AM
  • Thanks for your code.

    I have one simple question.

    I have Many sheets.(sheet1,sheet2,sheet3,etc) 

    is it possible to mention only particular tow sheet..?

    Every sheet has column with value.

    when i merge the two column in different sheet, shall i mention the Excel column name A,B,C,etc..

    because, everysheet column name is different.

    instead of using the coulmn title, shall i refer the excel column name like "A","B","C",etc...?

    Friday, December 15, 2017 9:32 AM
  • Hello,

    >>is it possible to mention only particular tow sheet..?

    Yes, you can. You could confirm if the sheet is specific sheet when foreaching it.

    Such as 

    if (ws.Name == "Sheet1" || ws.Name == "Sheet2")
                    {
                        lastRowIndex = ws.Cells[ws.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row;
                        if (lastRowIndex < 2) continue;
                        for (int i = 2; i <= lastRowIndex; i++)
                        {
                            dt.Rows.Add(ws.Cells[i, 1].Value, ws.Cells[i, 2].Value);
                        }
                    }

    >>shall i mention the Excel column name A,B,C

    What do you mean this? What's the column name? Why do you need refer excel column name?

    Best Regards,

    Terry


    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.

    Friday, December 15, 2017 9:56 AM
  • >> shall i mention the Excel column name A,B,C.

    >> What do you mean this? What's the column name? Why do you need refer excel column name?

    My template is like that.

    Like, In sheet1 i will have column name EmpNo,Name,Sub1,Sub2.

    But, In sheet2 i will have column name like PersonNo,PersonName,Subject1,Subject2, like that.

    But, the value both are same format. that's why.

    so my final intention is i want to merge the column with another sheet. that's it...

    Note : Also the column name order may be different columns in both sheets.

    pls. refer the updated excel.

    https://1drv.ms/x/s!AiSRcgO5FUmNapY8YSUzATUU4Ww



    • Edited by Gani tpt Friday, December 15, 2017 11:15 AM updated excel..
    Friday, December 15, 2017 10:06 AM
  • Any help pls...
    Monday, December 18, 2017 4:52 AM
  • Hello Gani tpt,

    I have downloaded your xlsx file. I'm wondering why your data in SectionA starts from Row 5 and data in SectionB starts from Row1, is it really your source file?

    Best Regards,

    Terry


    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.


    Monday, December 18, 2017 6:18 AM
  • Extremely sorry.

    Both section starts from Row1 only. i forgot to check that one.

    pls. change accordingly..

    let me know if you need any other help...

    Monday, December 18, 2017 9:18 AM
  • Hi Gani tpt,

    After moving data to first row, please refer to below code.

    if (ws.Name == "SectionA")
                    {
                        lastRowIndex = ws.Cells[ws.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row;
                        if (lastRowIndex < 2) continue;
                        for (int i = 6; i <= lastRowIndex; i++)
                        {
                            //in Sheet SectionA, first column is EmpNo, Second column is Name
                            dt.Rows.Add(ws.Cells[i, 1].Value, ws.Cells[i, 2].Value);
                        }
                    }
                    else if (ws.Name == "SectionB")
                    {
                        lastRowIndex = ws.Cells[ws.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row;
                        if (lastRowIndex < 2) continue;
                        for (int i = 2; i <= lastRowIndex; i++)
                        {
                            //in Sheet SectionB, forth column is EmpNo, first column is Name
                            dt.Rows.Add(ws.Cells[i, 4].Value, ws.Cells[i, 1].Value);
                        }
                    }

    Best Regards,

    Terry


    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 Monday, December 18, 2017 11:43 AM
    Monday, December 18, 2017 9:33 AM
  • Thanks. This is exact solution what i want..
    Monday, December 18, 2017 11:43 AM