Click here to Skip to main content
15,877,915 members
Please Sign up or sign in to vote.
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
Updated 1-May-12 16:49pm
v2

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)
 
Share this answer
 
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 ~~
Hi ,
check this linkExcel Data into datatable[^]
Best Regards
M.Mitwalli
 
Share this answer
 
v2
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();
 
Share this answer
 
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.
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
 
Share this answer
 
v2
Comments
Tisfy 1-May-12 4:42am    
Hi, thanks RonalP. I don't know quite much about VB...
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[^]
 
Share this answer
 
Comments
DaisyArun 10-Feb-14 2:50am    
System.Runtime.InteropServices.COMException:

I have tried above code these throws the Exception "System.Runtime.InteropServices.COMException"
The article offers a solution, have a view Import Excel File to DataSet[^]
 
Share this answer
 
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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900