问题描述
我在询问如何将xls文件数据插入SQL Server。上传按钮和gridview控件放置在devexpress弹出控件内，并且该代码在没有弹出控件的情况下仍然有效，但在弹出控件中无效。当我插入xls文件并单击上载按钮时，标签显示将显示请选择要上载的文件！尽管我已经做到了。
I have enquiry on how to insert xls file data into SQL Server. The upload button, and gridview control are placed inside the devexpress popup control and the code works without the popup control but doesn't work with popup control. When I inserted the xls file and clicked the upload button, the label display showed "Please select a file to upload!" although I have did this.
ASP.NET标记：
ASP.NET markup:
<dx:ASPxPopupControl ID="popupControl" ClientInstanceName="popupControl"
AllowDragging="true" ShowOnPageLoad="false" runat="server" AllowResize="true" >
<ContentCollection>
<dx:PopupControlContentControl runat="server">
<div class="flexs">
<dx:ASPxUploadControl ID="XXUpload" runat="server" UploadMode="Auto" ValidationSettings-AllowedFileExtensions=".xls" width="500px" >
</dx:ASPxUploadControl>
<dx:ASPxLabel ID="Label2" runat="server" Text=""></dx:ASPxLabel>
<dx:ASPxButton ID="DataUpload" runat="server" Text="UPLOAD" OnClick="DataUpload_Click ">
</dx:ASPxButton>
<dx:ASPxGridView ID="UpdateSplitGrid" ClientIDMode="Static" ClientInstanceName="UpdateSplitGrid" runat="server" Width="200%" DataSourceID="dtSource2" Theme="DevEx" >
.....
</dx:ASPxGridView>
</div>
</dx:PopupControlContentControl>
</ContentCollection>
</dx:ASPxPopupControl>
Vb.net代码：
Function uploadExcel1(filePath As String) As String
Dim str As String = ""
Dim namestr1 As String = XXUpload.UploadedFiles.ToArray(0).FileName.ToString
If namestr1 <> "" Then
If Not XXUpload.UploadedFiles.ToArray(0).IsValid Then
str = "Fail to Upload " + namestr1
Else
XXUpload.UploadedFiles.ToArray(0).SaveAs(filePath)
str = " Successfully Uploaded!"
End If
Else
str = "Please select a File to upload!"
End If
Return str
End Function
Function getDTe(filename As String, ByVal sql As String) As DataTable
Dim dt As New DataTable()
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';"
Dim connection As New OleDbConnection(connectionString)
Dim adapter As New OleDbDataAdapter(sql, connection)
adapter.Fill(dt)
Return dt
End Function
Protected Sub DataUpload_Click(sender As Object, e As EventArgs)
Dim filepath As String = "C:\New folder\" + XXUpload.UploadedFiles.ToArray(0).FileName.ToString + ".xls"
Dim msg As String = uploadExcel1(filepath)
Label2.Text = msg
If msg.Contains("Successfully") Then
Dim sql As String = "select * from [Sheet1$]"
Dim dt As New DataTable
dt = getDTe(filepath, sql)
If dt.Rows.Count > 0 Then
Dim i As Integer = 0
Dim colname() As String = {"LotID", "Split_Cat", "Engr_Time", "PLANDESC", "STEPSEQ", "EQPTYPE", "PPID", "STEPDESC", "Split", "Recipe", "F11", "F12", "F13", "F14", "F15", "F16", "F17", "F18", "F19", "F20", "F21", "F22"}
For Each dc As DataColumn In dt.Columns
If dc.ColumnName.ToString <> colname(i) Then
Label2.Text = " File is not in correct format - Details: " + dc.ColumnName.ToString
Return
End If
i = i + 1
Next
For Each dr In dt.Rows
Try
Dim LotID As String = dr("LotID").ToString
Dim Split_Cat As String = dr("Split_Cat").ToString
Dim Engr_Time As String = dr("Engr_Time").ToString
Dim PLANDESC As String = dr("PLANDESC").ToString
Dim STEPSEQ As String = dr("STEPSEQ").ToString
Dim EQPTYPE As String = dr("EQPTYPE").ToString
Dim PPID As String = dr("PPID").ToString
Dim STEPDESC As String = dr("STEPDESC").ToString
Dim Split As String = dr("Split").ToString
Dim Recipe As String = dr("Recipe").ToString
Dim a As String = dr("F11").ToString
Dim b As String = dr("F12").ToString
Dim c As String = dr("F13").ToString
.............
Dim insertsql2 As String = ""
insertsql2 += "insert into PTHOME.dbo.SplitTable (LotID,Split_Cat,Engr_Time,PLANDESC,STEPSEQ,EQPTYPE,PPID,STEPDESC,Split,Recipe,[1],[2],[3]) values "
insertsql2 += "('" + LotID + "','" + Split_Cat + "','" + Engr_Time + "','" + PLANDESC + "','" + STEPSEQ + "','" + EQPTYPE + "','" + PPID + "','" + STEPDESC + "','" + Split + "','" + Recipe + "', "
insertsql2 += " '" + a + "','" + b + "','" + c + "') "
Dim conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(insertsql2, conn)
conn.Open()
Dim res As Integer = cmd.ExecuteNonQuery
conn.Close()
If res > 0 Then
Label2.Text = res.ToString + " Records Successfully Uploaded! "
UpdateSplitGrid.DataBind()
Else
Label2.Text = " NO Records Uploaded! "
End If
Catch ex As Exception
Label2.Text = " Failed to Upload, pls check your data or file format ! "
End Try
Next
End If
End If
End Sub
当我上传xls文件时，显示的标签
请选择要上传的文件！，我在弹出控件中错过了什么吗？
When i upload the xls file, the label displayed "Please select a File to upload!", anything i missed out in the popup control? Please guide me on this, thanks in advance.
编辑*
我已将上传控件更改为此并且不再显示请选择要上传的文件！现在出现另一个错误，显示文件格式不正确-详细信息：F11 ，该错误似乎位于[1]-[12]列上，但是excel列是1-12，数据库中的列是[1]-[12]。如何实现此目的？ （通过将数字更改为F11等来解决。）
I have changed the upload control to this and it no longer showed the "Please select a File to upload!" now there is another error showed "File is not in correct format - Details: F11", the error seems to lie on the column [1]-[12], but the excel column is 1-12 and the column in database is [1]-[12].how can i achieve this? ( Solved by changing the number to F11 and so on.)
<dx:ASPxUploadControl runat="server" ClientInstanceName="XXUpload" ID="XXUpload" Width="600px" >
<ValidationSettings AllowedFileExtensions=".xls"></ValidationSettings>
</dx:ASPxUploadControl>
编辑2 *
数据已成功上传到sql server，现在又出现了另一个问题，尽管我已在代码中声明了此信息，但上传到sql服务器的数据不会显示在UpdateSplitGrid网格视图中。
Edit 2*The data has uploaded successfully to sql server and now another problem occurred, the data uploaded to sql server wont show in the UpdateSplitGrid gridview although i have declared this in my code.
If res > 0 Then
Label2.Text = res.ToString + " Records Successfully Uploaded! "
UpdateSplitGrid.DataBind()
推荐答案
您的 ASPxUploadControl ， XXUpload ，上传完成后，没有任何功能可以执行。我建议您将 OnFileUploadComplete 事件与 FileUploadMode = OnPageLoad 一起使用。
Your ASPxUploadControl, the XXUpload, doesn't have any function to execute when upload is complete. I suggest you use OnFileUploadComplete event with FileUploadMode="OnPageLoad".
这篇关于如何在devexpress弹出控件中将xls文件的数据上传到SQL Server？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！