Solved

vb.net import excel file

Posted on 2007-04-02
7
15,751 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Object to array 7 27
Object Oriented Best Practice 5 32
Create XML 5 30
Iterate a dictionnary to change values 4 18
Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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 synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now