Solved

vb.net import excel file

Posted on 2007-04-02
7
15,763 Views
Last Modified: 2013-12-16
I have a file browser that will select an excel file.  How do I import it into the application?  Do I open and read each line, one at at time?  Can someone post some code?

Thanks
0
Comment
Question by:jackjohnson44
  • 4
  • 2
7 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18839958
What version of Visual Studio are you using?

What do you want to do with the Excel file once you've imported it?
0
 
LVL 21

Accepted Solution

by:
theGhost_k8 earned 500 total points
ID: 18841598
please reply to Chaosian !!

there are may solutions on EE it self:
you are not making urself clear enough.
here are few:

0) <link : http://www.experts-exchange.com/Programming/Misc/Q_21879423.html?sfQueryTermInfo=1+excel+file+net+read#a16864368>

Function ReadDataFromExcel(ByVal excelfilename As String) As DataSet
        Dim ds As New DataSet
        Dim da As OleDbDataAdapter
        Dim conn As OleDbConnection
        Try

            conn = New OleDbConnection( _
                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "data source=" & excelfilename & "; " & _
                  "Extended Properties=Excel 8.0;")

            da = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn)

            conn.Open()

            da.Fill(ds)


        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try
      ReadDataFromExcel=ds
    End Function

1) from prakash

please avoid Excel.Application in ASP.NET Application .
Because Excel.Application need a parent window handle on save a dialog.

It also decreases the performance of the web server

If u using Excel.Application u must have Office Installed on the Web Server.
U do not need Office for Oledb Driver...

for oledb try this sample link...
http://www.codeproject.com/csharp/Excel_using_OLEDB.asp

If u want to do richer functionality try aspose following Component..
http://www.aspose.com/
 

2)




http://blog.lab49.com/?p=196
0
 

Author Comment

by:jackjohnson44
ID: 18843292
I am making a vb.net windows application.  I will open it loop through each line put it in a dataset if it passes my validation, put it in another if it does not and either save the problems in another excel file, or the same one with a different name.
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:jackjohnson44
ID: 18843320
it looks like that code will only work with excel 8.0.
Is there an easier way to do this?

Thanks
0
 

Author Comment

by:jackjohnson44
ID: 18843333
sorry, typo, I meant more generic so I don't have to speficy version.  I really don't know what kind they will use.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18843385
What version of Visual Studio are you using?
0
 

Author Comment

by:jackjohnson44
ID: 18844664
sorry, vs2005.

I got the code above to work.  I can see the dataset in the dataset viewer with the column names being the top row of the excel file.  unfortunately, I can't seem to access the rows by name, which I need to do if I want to validate.

In the example below, I can see "FirstName" in the  viewer, I can access it by index, but I can't access it by name as seen below.

Any ideas?

        For Each dr As DataRow In dsImport.Tables(0).Rows
            If Len(dr("FirstName")) Then
                fail = True
            End If
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question