import MS Excel to datatable

Question

4.67/5 (3 votes)

See more:

I have a huge collection of sales information in Excel files. I wish to store it in data tables? I only achieve the following things,
Load workbook:

C#

Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(@"D:\myOrder1.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;

Next I set my datatable (part of it)

C#

System.Data.DataTable dt = new System.Data.DataTable();
        dt.Columns.Add("FirstName");
        dt.Columns.Add("LastName");
        dt.Columns.Add("Mobile");
        dt.Columns.Add("Payment");
        dt.Columns.Add("Paydata");
        dt.Columns.Add("Email");

For the rest I can't figure how to processed. Can anyone help me?

Thank for all the reply, and since I have to do formatting so I can't use Oledb.But I thought it is a nice solution too.

Posted 30-Apr-12 20:52pm

Tisfy

Updated 1-May-12 16:49pm

v2

Add a Solution

7 solutions

Solution 2

Try the following codes:

C#

do  {
            rowIndex = 2 + index;
            row = dt.NewRow();
            row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
            row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
            row[2] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);
            row[3] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 4]).Value2);
            row[4] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 5]).Value2);
            index++;
            dt.Rows.Add(row);
        }
while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2 != null)

Posted 30-Apr-12 22:10pm

Pandvi

Comments

Tisfy 1-May-12 4:40am

That I can create a instance with DataRow then I can use the following code.

Pandvi 1-May-12 4:52am

yep. Good luck.

Tisfy 1-May-12 5:16am

It works. Thanks.

Arjun Menon U.K 25-May-13 11:54am

Thanks dude, i was looking for the same thing .. THumbs Up

A-MaxLee 14-May-14 4:05am

Thank you for your solution which just saved my life~~
Salute to you bro ~~

Member 11057420 26-Feb-15 3:23am

@pandvi
sory, why to declare "RowIndex,Index and row"
sory maybe this question so funy but im newbie
thx :-)

Solution 5

Hi ,
check this linkExcel Data into datatable[^]
Best Regards
M.Mitwalli

Posted 30-Apr-12 22:55pm

Mohamed Mitwalli

Updated 30-Apr-12 23:09pm

v2

Solution 1

Hi Try using OLEDB it shoudl be easier to set up..

C#

string myConnection ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\myOrder1.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

       OleDbConnection conn = new OleDbConnection(connstr);

       string strSQL = "SELECT * FROM [Sheet$]";
       OleDbCommand cmd = new OleDbCommand(strSQL, conn);

       DataSet dataset = new DataSet();
       OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
       adapter.Fill(dataset);

       GridViewXYZ.DataSource = dataset;
       GridViewXYZ.DataBind();

Posted 30-Apr-12 21:39pm

AiDoCo

Updated 30-Apr-12 22:56pm

v2

Comments

Tisfy 1-May-12 4:37am

Sure, I will just try OLEDB, but that may cause the format and setting being forbidden. That is why I apply "Interop" here.

Mark Zudeck 20-Apr-15 12:22pm

This OleDb solution can be a dangerous one. Depending on worksheet formatting, some cell values may not be loaded.

Solution 3

Dear,
Try Below solution this is very effective.

VB

Private Sub ReadExcelFile(ByRef objdt As DataTable, ByVal StrFilePath As String)
        Dim ExcelCon As New OleDbConnection
        Dim ExcelAdp As OleDbDataAdapter
        Dim ExcelComm As OleDbCommand
        Dim Col1 As DataColumn
        Try
            ExcelCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source= " & StrFilePath & _
                ";Extended Properties=""Excel 8.0;"""
            ExcelCon.Open()

            StrSql = "Select * From [Sheet1$]"
            ExcelComm = New OleDbCommand(StrSql, ExcelCon)
            ExcelAdp = New OleDbDataAdapter(ExcelComm)
            objdt = New DataTable()
            ExcelAdp.Fill(objdt)

            '--- Create Column With SRNo.
            Col1 = New DataColumn
            Col1.DefaultValue = 0
            Col1.DataType = System.Type.GetType("System.Decimal")
            Col1.Caption = "Sr No."
            Col1.ColumnName = "SrNo"
            objdt.Columns.Add(Col1)
            Col1.SetOrdinal(1)

            ExcelCon.Close()
        Catch ex As Exception
            
        Finally
            ExcelCon = Nothing
            ExcelAdp = Nothing
            ExcelComm = Nothing
        End Try
    End Sub

Regards Ronal

Posted 30-Apr-12 22:16pm

RonalP

Updated 30-Apr-12 22:51pm

v2

Comments

Tisfy 1-May-12 4:42am

Hi, thanks RonalP. I don't know quite much about VB...

Solution 4

you can follow this following link as well.
u have got most of the stuff, though u can also read this

http://forums.asp.net/t/1192930.aspx[^]

Posted 30-Apr-12 22:50pm

preet88

Comments

DaisyArun 10-Feb-14 2:50am

System.Runtime.InteropServices.COMException:

I have tried above code these throws the Exception "System.Runtime.InteropServices.COMException"

Solution 8

If you can use an open source solution, try ExcelDataReader:
exceldatareader.codeplex.com

An example is show here:
sites.google.com/site/dineshkumarwin/import-or-convert-excel-file-to-datatable#c5

I had cell values weren't being loaded when I used OLEDB. This library loaded them all.

Posted 20-Apr-15 7:43am

Mark Zudeck

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Chasfer · Accepted Answer · 2012-05-01T04:23:00

Solution 7

The article offers a solution, have a view Import Excel File to DataSet[^]

Posted 1-May-12 4:23am

Chasfer