用vb.net和asp.net的Excel导入到SQL数据库 [英] Importing Excel to SQL Database using vb.net and asp.net

问题描述

林目前正在建设一个项目，允许用户通过Web界面（内置），这将文件保存到服务器（内置），然后将数据导入到SQL数据库取决于几个服务器上导入Excel文件用户选项（未建）。

Im currently building a project that lets users import Excel files via a web interface (built), which saves the file to the server (built), and then imports the data into the SQL Database on the server depending on a few of the user options (not built).

林不熟悉，在VS的SQL数据库工具在所有所以我一直在摸索周围进行了两天的大部分只是试图让一切都成立。林pretty知道我需要使用BulkCopy，但是我不太清楚如何使用它，我似乎无法找到解释它属于我的具体应用的具体例子。

Im not familiar with SQL database tools within VS at all so I have been fumbling around for the better part of two days just trying to get everything set up. Im pretty sure I need to use BulkCopy, but Im not quite sure how to use it and I can't seem to find specific examples that explain it pertaining to my specific application.

所以在我的App_Data文件夹中我有一个.MDF标题为设备数据库。在该数据库中我有三个表：Galaxy Nexus的，大力神和红宝石

So in my App_Data folder I have an .mdf title "Device Database." In that database I have three tables: "Galaxy Nexus", "Hercules" , and "Ruby"

我想从每个导入Excel工作表中四个单元导入到它们各自的表。

I am trying to import four cells from each imported excel sheet to their respective tables.

我想导入细胞（2,2）在表中列1，电池（2,3）到列2，电池（3,2），以栏3和细胞（1,1），以column4。

I would like to import cell(2,2) to column1 in the table, cell(2,3) to column2, cell(3,2) to column3 and cell(1,1) to column4.

在code，我试图用做到这一点是：

The code I am trying to accomplish this with is:

    Dim ExcelContentType As String = "application/vnd.ms-excel"
    Dim Excel2010ContentType As String = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    Dim excelConnectionString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", SavedFile)
    Using connection As New OleDbConnection(excelConnectionString)
        Dim Command As OleDbCommand = New OleDbCommand("Select * FROM [Sheet1$]", connection)
        connection.Open()
        Using reader As DbDataReader = Command.ExecuteReader()
            Dim sqlConnectionString As String = "Data Source=.\sqlexpress;Initial Catalog=ExcelDB;Integrated Security=True"
            Using bulkCopy As New SqlBulkCopy(sqlConnectionString)
                bulkCopy.DestinationTableName = DropDown1.SelectedItem.ToString
                bulkCopy.WriteToServer(reader)
            End Using
        End Using
    End Using

在哪里我有麻烦的是，我不知道如何从Excel工作表中选择某些细胞导入和我不知道如何将这些细胞对特定列复制指定的表。

Where I am having trouble is, I do not know how to select certain cells from the excel sheet to import and I do not know how to copy those cells to specific columns in the specified table.

任何及所有帮助始终是AP preciated。
谢谢，
扎克

Any and all help is always appreciated. Thanks, Zach

推荐答案

林张贴一个答案，这样，如果在此任何人绊倒，他们可能会帮助为好。

Im posting an answer so that if anyone else stumbles upon this, they might be helped as well.

这是什么让一切为我工作。 （喊出来凯文）

This is what got everything to work for me. (Shout out to kevin)

Protected Sub Button1_Click(sender As Object, e As System.EventArgs)
    Dim appPath As String = Request.PhysicalApplicationPath
        Dim con As New System.Data.SqlClient.SqlConnection
        con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & appPath & "App_Data\Devicedatabase.MDF;Integrated Security=True;User Instance=True;"
        con.Open()
        MsgBox("open")
        con.Close()
        MsgBox("close")
    End Sub

这得到了连接打开后大量尝试和沮丧。

This got the connection open after much trying and frustration.

这得到了导入到数据库中的Excel的值：

This got the excel values imported to the database:

 Using con As New SqlClient.SqlConnection With
{
    .ConnectionString =
    "Data Source=.\SQLEXPRESS;AttachDbFilename=" & appPath & "App_Data\Devicedatabase.MDF;Integrated Security=True;User Instance=True;"
}
Using cmd As New SqlClient.SqlCommand With
    {
        .Connection = con,
        .CommandText = "INSERT INTO " & """" & DropDownList1.SelectedItem.ToString & """" & "ColumnName1, ColumnName2)VALUES (@Col1,@Col2)"
    }
    cmd.Parameters.Add(New SqlClient.SqlParameter With {.DbType = DbType.String, .ParameterName = "@Col1"})
    cmd.Parameters.Add(New SqlClient.SqlParameter With {.DbType = DbType.String, .ParameterName = "@Col2"})
    cmd.Parameters(0).Value = "Value obtained from Excel"
    cmd.Parameters(1).Value = "Value obtained from Excel"
    con.Open()
    Dim Result As Integer = cmd.ExecuteNonQuery
    If Result <> 1 Then
        MessageBox.Show("Insert failed.")
    Else
        MessageBox.Show("Row inserted.")
    End If

End Using
End Using

享受家伙！

这篇关于用vb.net和asp.net的Excel导入到SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！