Click here to Skip to main content
11,713,350 members (82,406 online)
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 20:52pm
Tisfy630
Edited 1-May-12 16:49pm
v2
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 ~~
Member 11057420 at 26-Feb-15 3:23am
   
@pandvi
sory, why to declare "RowIndex,Index and row"
sory maybe this question so funy but im newbie
thx :-)
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 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.
Mark Zudeck at 20-Apr-15 12:22pm
   
This OleDb solution can be a dangerous one. Depending on worksheet formatting, some cell values may not be loaded.
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  
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  

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

  Print Answers RSS
0 CHill60 335
1 Sergey Alexandrovich Kryukov 290
2 OriginalGriff 200
3 Andy Lanng 165
4 Maciej Los 130
0 CHill60 335
1 Sergey Alexandrovich Kryukov 300
2 OriginalGriff 230
3 Andy Lanng 160
4 Maciej Los 130


Advertise | Privacy | Mobile
Web02 | 2.8.150819.1 | Last Updated 20 Apr 2015
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