Importing data from Excel - VB.NET
I am trying to import some data from an excel spreadsheet, using VB.net
my steps are:
first the user uploads the file to the server
then i want to read the file from the server to then populate a gridview
this is what i have:
Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
Dim filepath As String = ""
If FileUpload1.HasFile Then
Try
If (FileUpload1.PostedFile.ContentType = "application/vnd.ms-excel") Then
Dim filename As String = Path.GetFileName(FileUpload1.FileName)
'Session("userid") & "-" & Date.Now()
filepath = "\excel\" & Session("userid") & "_" & Now.Date().ToString("Mdy") & "_" & filename
FileUpload1.SaveAs(Server.MapPath("~/") & filepath)
ReadExcel(filepath)
Else
StatusLabel.Text = "Only Excel file types are accepted"
End If
Catch ex As Exception
StatusLabel.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message
End Try
End If
End Sub
Sub ReadExcel(ByVal filepath As String)
Dim MyConnection As System.Data.OleDb.OleDbConnection
开发者_开发技巧 Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & filepath & "';Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [NSTS]", MyConnection)
MyCommand.TableMappings.Add("Table", "Net-informations.com")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
gwResults.DataSource = DtSet.Tables(0)
MyConnection.Close()
End Sub
the error happens with "MyConnection", it tried to look on the "C:/" instead of on the server:
'c:\excel\3_41911_Sample.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
how can i set the OleDb connection to get the source file from the server instead?
thank you!
nevermind, i got it
i added: Server.MapPath("~/") & filepath and now it works. however, now i'm getting the error: The Microsoft Jet database engine could not find the object 'NSTS'. Make sure the object exists and that you spell its name and the path name correctly.
NSTS is the name of my first spreadsheet. what am i doing wrong? :(
i was missing a studip dollar sign :) ahh, it all works now!
"select * from [NSTS$]"
thanks!
Use a $
in your sheet's name in the query:
"select * from [NSTS$]"
Your c:\excel\ path is not local path but it is path local to where you are running your application.
If you are running this application from local machine, In order to map c:\excel\ path, you should either map server drive to your windows and use that drive name OR use \\excel as path value.
First - do you know exactly where on the server path the file is being saved to? I'd begin by hardcoding the path to make sure that there isn't anything else squirrely going on.
Looking at your code you're saving the file here...
FileUpload1.SaveAs(Server.MapPath("~/") & filepath)
So... first, are you sure it's saving there? If so then look as where you're reading the file with this call...
ReadExcel(filepath)
Have you tried -
ReadExcel(Server.MapPath("~/") & filepath)?
精彩评论