none
c# import multiple worksheets into one datatable RRS feed

  • Question

  • I have an excel file with many worksheets. Each worksheet has 5 columns,but the number of rows are different. I want to import each worksheet, one after one, into a single datatable. So if my file contains 10 worksheets, then my datatable should contain 50 columns. This is what I tried:

    DataTable dt = new DataTable();
                int i = 1; 
                foreach (var ws in pck.Workbook.Worksheets)
                {
                    foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                    {
                        dt.Columns.Add("Column "+firstRowCell.Start.Column + i); i++;
                    }
                    for (int rowNum = 2; rowNum <= ws.Dimension.End.Row; rowNum++)
                    {
                        var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                        DataRow row = dt.Rows.Add();
                        foreach (var cell in wsRow)
                        {
                            row[cell.Start.Column - 1] = cell.Text;
                        }
                    } 
                }

    but it puts next worksheet data after last row not after last column in my datatable. I don't know how to add each column from each worksheet into datatable. If someone can show me how to code right.


    Saturday, December 10, 2016 7:29 PM

All replies

  • Hi BONIETE,

    Thank you for posting here.

    According to your question is more related to Excel, I will move it to Excel for Developers Forum for suitable support.

    The Visual C# Forum discuss and ask the question about the C# programming language, IDE, libraries, samples and tools.

    If you have some grammar or code errors, please feel free to contact us. We will try our best to give you a solution.

    Thanks for your understanding and cooperation.

    Best Regards,

    Wendy


    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 12, 2016 2:55 AM
  • Hi,

    Microsoft.Office.Interop.Excel does not contain the any method like ws.Cells[1, 1, 1, ws.Dimension.End.Column]. Where does it come from? What dll do you refer?


    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.

    Thursday, December 15, 2016 2:02 AM
    Moderator