将excel里面的资料导入到datagrid中出现的问题,大虾们,帮忙哈`````

下面为问题描述:
要求将一个excel档案里面的资料导入到webform上的datagrid中去,后面用的语言是vb.net调用下列代码,却出现错误:
 代码如下:
Imports System.Data.OleDb
Imports System.Data

 Dim FileName As String
 Dim myDataSet As New DataSet
 Dim da As OleDbDataAdapter
 If CheckIsExcelFile(HalfFileToUpload.PostedFile.FileName) = False Then //检查是否为--//excel檔
 lblMsg.Text = "请您选择excel档案径行上载!!"
 Exit Sub
 Else
 FileName = MaterialFileToUpload.PostedFile.FileName
 Dim myOleDbConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=" & FileName & ";" & _
 "Extended Properties=""Excel 8.0;""")
 Dim myOleDbCommand As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", myOleDbConnection)
 Dim myData As OleDbDataAdapter = New OleDbDataAdapter(myOleDbCommand)
 myData.Fill(myDataSet)
 DGHalf.DataSource = myDataSet.Tables(0).DefaultView
 DGHalf.DataBind()
 End If

执行到myData.Fill(myDataSet)时候出现报错.
具体报错信息如下:
{System.Data.OleDb.OleDbException}
 [System.Data.OleDb.OleDbException]: {System.Data.OleDb.OleDbException}
 HelpLink: Nothing
 InnerException: Nothing
 Message: "外部资料表不是预期的格式。"
 Source: "Microsoft JET Database Engine"
 StackTrace: " at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
 at System.Data.OleDb.OleDbConnection.InitializeProvider()
 at System.Data.OleDb.OleDbConnection.Open()
 at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
 at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
 at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
 at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
 at TMEF.TMSEF800_default.BtnPartLoad_Click(Object sender, EventArgs e) in D:\wjvhrp\tmsef800\Default.aspx.vb:line 2573"
 TargetSite: {System.Reflection.RuntimeMethodInfo}

代码源自于网上,确实不知怎么解决这个问题,请各位大虾赐教.

8 个解决方案

#1

FileName = MaterialFileToUpload.PostedFile.FileName

你必须先保存到服务器的硬盘上才可以用。
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName & ";" & _
"Extended Properties=""Excel 8.0;""")

需要物理文件地址

#2

Private Sub DemoForm9_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' 建立一个数据连接
        Dim myConnection As New OleDbConnection( _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Application.StartupPath & "\订单数据.xls" & _
            ";" & "Extended Properties=Excel 8.0;")

        Try
            ' 建立一个数据适配器以便针对数据源执行 SELECT 语句来提取出要填入数据集的数据记录
            Dim myAD As New OleDbDataAdapter("SELECT * FROM `发票$`", myConnection)

            ' 将数据填入数据集
            myAD.Fill(ds, "发票")

            ' 将 DataGrid 控件绑定至 ds 数据集的发票数据表
            DataGridInvoice.SetDataBinding(ds, "发票")
        Catch ex As Exception
            MessageBox.Show(ex.Message, "请注意", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            ' 关闭连接
            myConnection.Close()
        End Try
    End Sub
你小看一下这段代码！！！！！！！！！！！

================================================================
此帖通过csdn小助手回复。
    CSDN小助手是使用vb.net(开源)编写的CSDN论坛脱机“外挂”，她能够在
脱离IE的情况下使用Csdn论坛。程序只加载最核心的数据，所以显示更
快，产生的流量更小。

    下载地址：http://qqwwee.com/csdn.rar
================================================================

#3

net_lover:
  保存到服务器硬盘才可用,这个具体怎么搞?
regedit362451dennis:
  看了这段c#的代码,感觉好象跟我不太多哈
  到底怎么搞哈

#4

mark

#5

misyan (test),呵呵,我也是才实现成功的,给代码你看看
Dim strCon As OleDbConnection = New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
      "data source=C:\book4.XLS; " & _
      "Extended Properties=Excel 8.0;")

        Dim sqlstr As String = "select * from [Sheet1$]"
        myConn.Open()

        myAdapter = New OleDbDataAdapter(sqlstr, strCon)
        myDataSet = New DataSet
        myAdapter.Fill(myDataSet)

        myConn.Close()
其中C:\book4.XLS是我的EXCEL文档,

#6

客户端的XLS不是服务器端的XLS.你要先上载到服务器然后对服务器上的XLS操作.

#7

樓上說的對

#8

谢谢各位的帮忙,~~问题已经解决
出现问题原因是 1 datagrid中的格式拦位与excel里面格式拦位不一致.且datagrid中AutoGenerateColumns属性设置false,所以出现刚才这个报错

至于各位提出的上栽到服务器然后再对xls操作的问题,我再后续也碰到了,的确如各位所说.
下面是我实现后的代码
Private Sub BtnHalfLoad_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnHalfLoad.Click
 Dim FileName As String
 Dim DS As New DataSet
 Dim FlowCode As String = "800"
 Dim FP As String
 Dim PFP As String
 Dim Address As String
 Dim MyCommand As OleDbDataAdapter
 Dim MyConnection As OleDbConnection
 Try
 FileName = MaterialFileToUpload.PostedFile.FileName
 FP = GetFileName(FileName)
 PFP = Request.PhysicalApplicationPath & basedata1.InitiateEmpCode & "\FileUp\" & FlowCode
 Address = PFP & FP

 If Trim(FileName) = "" Then
 lblMsg.Text = "请选择您要上载的Excel檔"
 Exit Sub
 End If

 '检查是否已存在该文件路径
 If Not Directory.Exists(PFP) Then
 Directory.CreateDirectory(PFP)
 End If

 '保存至对应server中
 MaterialFileToUpload.PostedFile.SaveAs(PFP & FP)
 FileName = HalfFileToUpload.PostedFile.FileName

 If Trim(FileName) = "" Then
 lblMsg.Text = "请选择您要上载的Excel檔"
 Exit Sub
 End If

 If CheckIsExcelFile(HalfFileToUpload.PostedFile.FileName) = False Then
 lblMsg.Text = "请您选择excel档案径行上载!!"
 Exit Sub
 Else
 MyConnection = New System.Data.OleDb.OleDbConnection( _
 "provider=Microsoft.Jet.OLEDB.4.0; " & _
 "data source=" & Address & "; " & _
 "Extended Properties=Excel 8.0;")

 MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
 "select * from [Sheet1$]", MyConnection)

 DS = New System.Data.DataSet
 MyCommand.Fill(DS)
 DGHalf.DataSource = DS.Tables(0).DefaultView
 ViewState("Half") = DS.Tables(0)
 DGHalf.DataBind()
 DGHalf.Columns(0).Visible = False
 DGHalf.Columns(6).Visible = False
 End If
 '加总此窗体总金额
 GetCancelTotalAmount()
 Catch ex As Exception
 lblMsg.Text = "请确认上载的excel档案格式是否正确!!"
 Finally
 If Not MyConnection Is Nothing Then
 If MyConnection.State = ConnectionState.Open Then
 MyConnection.Close()
 End If
 End If
 End Try
 End Sub

#1

#2

#3