Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# .NET Office
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:
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)
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 21:52pm
Tisfy622
Edited 1-May-12 17:49pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi
Try using OLEDB it shoudl be easier to set up..
 
 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();
  Permalink  
v2
Comments
Tisfy at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try the following codes:
 
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)
  Permalink  
Comments
Tisfy at 1-May-12 4:40am
   
That I can create a instance with DataRow then I can use the following code.
Pandvi at 1-May-12 4:52am
   
yep. Good luck.
Tisfy at 1-May-12 5:16am
   
It works. Thanks.
Arjun Menon U.K at 25-May-13 11:54am
   
Thanks dude, i was looking for the same thing .. THumbs Up
A-MaxLee at 14-May-14 4:05am
   
Thank you for your solution which just saved my life~~
Salute to you bro ~~
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

Hi ,
check this linkExcel Data into datatable[^]
Best Regards
M.Mitwalli
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Dear,
Try Below solution this is very effective.
 
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
  Permalink  
v2
Comments
Tisfy at 1-May-12 4:42am
   
Hi, thanks RonalP. I don't know quite much about VB...
Rate this: bad
good
Please Sign up or sign in to vote.

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[^]
  Permalink  
Comments
DaisyArun at 10-Feb-14 2:50am
   
System.Runtime.InteropServices.COMException:
 
I have tried above code these throws the Exception "System.Runtime.InteropServices.COMException"
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

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

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

  Print Answers RSS
0 Peter Leow 255
1 Mika Wendelius 240
2 CHill60 195
3 TheRealSteveJudge 180
4 Zoltán Zörgő 178
0 Sergey Alexandrovich Kryukov 8,658
1 OriginalGriff 6,591
2 Peter Leow 3,982
3 Zoltán Zörgő 3,604
4 Richard MacCutchan 2,510


Advertise | Privacy | Mobile
Web02 | 2.8.150123.1 | Last Updated 1 May 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100