From excel to datatables

Question

I want to export data from my excel tables to datatable but don't know how should i put the data to the DataTables. Can someone help me with it?

My code would be:

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(userSelectedFilePath2);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;

DataTable excelTb = new DataTable();

for (int i = 0; i <= xlRange.Rows.Count - 1; i++)
{
     for (int j = 1; j <= xlRange.Columns.Count; j++)
     {
          excelTb.Columns.Add(xlRange.Cells[0,j].Value2.ToString());
          foreach (DataRow extb in excelTb.Rows)
          {
              DataRow newDataRow = excelTb.NewRow();
              // Here should be something to put data in DataTable
          }
     }
}

because OleDb can't read more than 255 chars in cells, so i try with Microsoft.Office.Interop.Excel — Uni Le
– Uni Le, Commented Nov 12, 2012 at 9:37
I really found Microsoft.Office.Interop.Excel difficult and not so "userfriendly". I would suggest you to use EPPlus — Gianni B.
– Gianni B., Commented Nov 12, 2012 at 9:45
@NaNNy: i'll try later EPPlus, now i will give a try with Microsoft.Office.Interop.Excel, but thanks anyway — Uni Le
– Uni Le, Commented Nov 12, 2012 at 9:58

LMB · Accepted Answer · 2012-11-12 10:40:50Z

1

Try this:

  for (int row = 0; row < xlRange.Rows.Count; row++)
    {
        DataRow dataRow = null;
        if (row != 0) dataRow = excelTb.NewRow();

        for (int col = 0; col < xlRange.Columns.Count; col++)
        {
            if (row == 0) //Headers
            {
                excelTb.Columns.Add(xlRange.Cells[row + 1, col + 1].Value2.ToString());
            }
            else //Data rows
            {
                dataRow[col] = xlRange.Cells[row + 1, col + 1].Value2.ToString();

            }
        }
    }

edited Nov 12, 2012 at 10:40

answered Nov 12, 2012 at 9:46

LMB

1,1357 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LMB Over a year ago

I'm typing from my cell phone on the go =). When I get to work I'll check it.

Christian Sauer · Accepted Answer · 2012-11-12 09:45:28Z

If you only have to deal with xlsx and xlsm files use epplus, its indefinitely faster than interop. It is a package for reading / writing excel-files. Additionally, it has a very nice license and cost nothing.

For your datatable you could go along this lines (vb.net codE)

     Dim tbl As New DataTable
     for cols = 0 to j 'you have to define the columns first
         tbl.Columns.Add()
     next

    for cols = 0 to j
       for rows = 0 to your_rowcount
            Dim row = tbl.NewRow
'Here is the critical part: iterate over the worksheet, and take each value from the sheet. put the value into the datatable. The datatable needs to get a new row for each worksheet.row
    Dim value = yourworksheet.cells(cols+1,rows+1).value
     row(cols)=Value
   next
next

Derek · Accepted Answer · 2012-11-12 09:51:06Z

0

  private void grabData(string filename)
            {
                    // Clear DataTaable before generating new Table Data/
                    ds.Clear();

                    // Connection String to the previously selected file. 
                    // HDR=Yes advises that spreadsheet has columns. 

                    OleDbConnection con = new OleDbConnection(
                    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename +
                    ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");


                    // The criteria required to Build DataTable. Select specific columns                 //from spreadsheet where teh Site Statuse is LIVE.
                    string strSQL = "SELECT * FROM [YOURTABLE]";

                    // The Command that we will use with our DataAdapter.
                    OleDbCommand cmd = new OleDbCommand(strSQL, con);

                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    da.Fill(ds, "YourTable");


            }

answered Nov 12, 2012 at 9:51

Derek

8,68813 gold badges61 silver badges94 bronze badges

1 Comment

Gianni B. Over a year ago

OP said that he can't use OleDB because it can't read more than 255 chars in cells

Dan · Accepted Answer · 2012-11-12 09:55:37Z

0

Read the data from the worksheet into a 2D object array like this:

//Convert from interop object to native C# object, indexed 1 to length
object[,] data = (object[,])xlWorksheet.UsedRange.Value2;

Now you can add a cell to the DataRow like this:

   for (int row = 1; row < data.GetUpperBound(0); row++)
    {
        DataRow newDataRow = excelTb.NewRow();
        newDataRow["AStringColumn"] = data[row,1].ToString();
        newDataRow["ADoubleColumn"] = Convert.ToDouble(data[row, 2]);
        //repeat for each column OR replace with a column loop and use newDataRow[col] = data[row, col].ToString();

        excelTb.Rows.Add(newDataRow);
    }

note that the double array is indexed from 1 and not 0 like arrays normally are in C#.

answered Nov 12, 2012 at 9:55

Dan

45.8k20 gold badges98 silver badges169 bronze badges

5 Comments

Dan Over a year ago

BTW - you do also have to add the columns to the DataTable right in the beginning.

Uni Le Over a year ago

can you show me in your code, because i'm trying to to it, got many errors

Dan Over a year ago

What errors? Add columns like this: msdn.microsoft.com/en-us/library/hfx3s9wd(v=vs.80).aspx

Uni Le Over a year ago

I got NullRefereceException, can you show me complete code how to do it?

Dan Over a year ago

This really is basically the complete code. At what point did you get a null reference exception. You have to be very specific when asking for help with errors, you should provide the line the error occurred on, what the error said and the values (from the debugger) of important variables at the time of the error.

Collectives™ on Stack Overflow

From excel to datatables

4 Answers 4

1 Comment

Comments

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related