如何将各种表格从不同的Excel文件导入数据库 [英] How to import various sheets from defferent excel files into a database

问题描述

大家好，

我是CP的新成员。我试图从d / t文件导入excel表。我能够使用一个excel表，然后到我已经准备好的数据库了。



现在我想要的是从d / t位置输出更多的纸张。无论如何这是代码

< pre> 
 函数 ReadExcelFile（）
 ' 使用OleDbDataAdapter提供DataTable和OleDb数据源之间的通信 
  Dim  da  As  新 OleDbDataAdapter 
 
 ' 使用DataTable作为excel数据的存储 
  Dim  dt  As  < span class =code-keyword>新 DataTable 
 
 ' 使用OleDbCommand执行我们的SQL语句 
  Dim  cmd  As  新 OleDbCommand 
 
 ' 使用OleDbCommand将用于连接到Excel文件的OleDbConnection  
  Dim  xlsConn  As  OleDbConnection 
 
  Dim  sPath  As  字符串 = 字符串 .Empty 
 
 sPath = Form1.lbFilePath.Text 
 
 ' 创建新的连接实例并将数据源值设置为excel的路径 
 xlsConn = 新 OleDbConnection（  Provider = Microsoft .ACE.OLEDB.12.0;数据来源=& sPath&  ;扩展属性= Excel 12.0）
 
 ' 使用try catch块来处理 
 ' 给定代码块，同时仍在运行代码。 
 
 ' 尝试 
 ' 打开连接 
 xlsConn.Open（）
 
 ' 设置打开连接的命令连接 
 cmd.Connection = xlsConn 
 
 ' 将命令类型设置为CommandType.Text以使用此处构建的SQL语句 
 '   
 cmd.CommandType = CommandType.Text 
 
 ' 指定命令文本以查询excel，如下所示 
 cmd.CommandText =（  SELECT * FROM [sheet1 $]）
 
 '  将cmd分配给dataadapter  
 da.SelectCommand = cmd 
 
  ' 使用DataAdapter用excel文件中的数据填充数据表 
 da.Fill（dt）
 
 '  Catch  
 ' 此块处理异常。 
 '  MsgBox（ErrorToString） 
 ' 最后 
 ' 我们需要关闭连接并设置为空。即使代码引发错误，此代码仍会执行 
 xlsConn.Close（）
 xlsConn =  Nothing  
 < span class =code-comment>' 结束尝试 
 返回 dt 
 结束 功能 
 公共  Sub  SaveToDB（ ByVal  iRowIndex  As  长）
  Dim  conn  As  新 OleDbConnection 
  Dim  sConnString  As  字符串 
  Dim  cmd  As < /跨度> 新 OleDbCommand 
  Dim  sSQL  As  字符串 = 字符串 .Empty 
 
 ' 尝试 
 ' 检查路径在字符串末尾是否有反斜杠 
 如果 Microsoft.VisualBasic.Right（Application.StartupPath， 1 ）=   \ 然后 
 sConnString =   Provider = Microsoft.ACE.OLEDB.12.0 ;数据来源=& Application.StartupPath&   dbexport.accdb; Persist Security Info = False; 
  Else  
 sConnString =   Provider = Microsoft.ACE.OLEDB.12.0;数据来源=& Application.StartupPath&   \ dbexport.accdb; Persist Security Info = False; 
 结束 如果 
 
 ' < span class =code-comment>创建新的连接实例 
 conn = 新 OleDbConnection（sConnString）
 
 ' 打开命令对象使用的连接 
 conn.Open（）
 
 ' 设置命令与我们打开的连接的连接 
 cmd。 Connection = conn 
 
 ' 将命令类型设置为CommandType.Text以使用SQL语句这里构建 
 '   
 cmd.CommandType = CommandType.Text 
 
 ' 设置注释文本以将数据插入数据库 
 cmd.CommandText =   INSERT INTO eqtrate（item_no，equipment_type，hourly_cost）VALUES（@item_no，@ equipment_type，@ hurly_cost） 
 
 ' 添加参数以便在查询中设置值 
 cmd.Parameters.Add（  @ item_no，OleDbType.Numeric）.Value = dtExcelData.Rows（iRowIndex）（ 0  ）
 cmd.Parameters.Add（  @ equipment_type，OleDbType.VarChar）.Value = dtExcelData.Rows（iRowIndex）（ 1 ）
 ' 这只是如何检查字段是否为空的示例。 
 cmd.Parameters .Add（  @ hourly_cost，OleDbType.Numeric）.Value = IIf（不 IsDBNull（dtExcelData.Rows（iRowIndex）（ 2 ）），dtExcelData.Rows（iRowIndex）（ 2 ）， Nothing ）
 cmd.ExecuteNonQuery（）
 
 
 '  Catch ex As Exception  
 MsgBox（ErrorToString）
 ' 最后 
 conn.Close（）
 ' 结束尝试 
 结束  Sub  

解决方案

）

' 分配cmd到dataadapter
da.SelectCommand = cmd

' 填写使用DataAdapter从excel文件中获取数据的数据表
da.Fill（dt）

' Catch
' 此块处理异常。
' MsgBox（ErrorToString）
' 最后
' 我们需要关闭连接并设置为空。即使代码引发错误，此代码仍会执行
xlsConn.Close（）
xlsConn = Nothing
< span class =code-comment>' 结束尝试
返回 dt
结束 功能
公共 Sub SaveToDB（ ByVal iRowIndex As 长）
Dim conn As 新 OleDbConnection
Dim sConnString As 字符串
Dim cmd As < /跨度> 新 OleDbCommand
Dim sSQL As 字符串 = 字符串 .Empty

' 尝试
' 检查路径在字符串末尾是否有反斜杠
如果 Microsoft.VisualBasic.Right（Application.StartupPath， 1 ）= \ 然后
sConnString = Provider = Microsoft.ACE.OLEDB.12.0 ;数据来源=& Application.StartupPath& dbexport.accdb; Persist Security Info = False;
Else
sConnString = Provider = Microsoft.ACE.OLEDB.12.0;数据来源=& Application.StartupPath& \ dbexport.accdb; Persist Security Info = False;
结束 如果

' < span class =code-comment>创建新的连接实例
conn = 新 OleDbConnection（sConnString）

' 打开命令对象使用的连接
conn.Open（）

' 设置命令与我们打开的连接的连接
cmd。 Connection = conn

' 将命令类型设置为CommandType.Text以使用SQL语句这里构建
'
cmd.CommandType = CommandType.Text

' 设置注释文本以将数据插入数据库
cmd.CommandText = INSERT INTO eqtrate（item_no，equipment_type，hourly_cost）VALUES（@item_no，@ equipment_type，@ hurly_cost）

' 添加参数以便在查询中设置值
cmd.Parameters.Add（ @ item_no，OleDbType.Numeric）.Value = dtExcelData.Rows（iRowIndex）（ 0 ）
cmd.Parameters.Add（ @ equipment_type，OleDbType.VarChar）.Value = dtExcelData.Rows（iRowIndex）（ 1 ）
' 这只是如何检查字段是否为空的示例。
cmd.Parameters .Add（ @ hourly_cost，OleDbType.Numeric）.Value = IIf（不 IsDBNull（dtExcelData.Rows（iRowIndex）（ 2 ）），dtExcelData.Rows（iRowIndex）（ 2 ）， Nothing ）
cmd.ExecuteNonQuery（）


' Catch ex As Exception
MsgBox（ErrorToString）
' 最后
conn.Close（）
' 结束尝试
结束 Sub

看看这里：从Excel导入数据使用ASP.NET访问 [ ^ ]。在那里，你会找到最简单的方法来实现你想要的。

  void  ImportFromExcel（ string  Access， string  Excel）
 {
  //   string Access =FullFileNameAccess.mdb;  
  //   string Excel =FullFileNameExcel.xls;  
  string  connect =   Provider = Microsoft.Jet.OLEDB.4.0;数据源= + Excel +  ;扩展属性= Excel 8.0;; 
 使用（OleDbConnection conn =  new  OleDbConnection（connect））
 {
 使用（OleDbCommand cmd =  new  OleDbCommand（））
 {
 cmd .Connection = conn; 
 cmd.CommandText =   INSERT INTO [MS Access; Database = + Access + < span class =code-string> ]。[人员] SELECT * FROM [Sheet1 
 
 
 ; 
 conn.Open（）; 
 cmd.ExecuteNonQuery（）; 
} 
} 
）

在您的情况下，请替换： Provider = Microsoft.Jet.OLEDB.4.0 with Provider = Microsoft.ACE.OLEDB.12.0

Hi everyone,
I'm a new memeber at CP.I was trying to import excel sheets from d/t files.I was able to do it using a single excel sheet,then to the database that i had already prepared.

Now what i wanted is to export more than on sheet from d/t locations.Here's the code anyway

<pre>
Function ReadExcelFile()
        'Use OleDbDataAdapter  to provide communication between the DataTable and the OleDb Data Sources
        Dim da As New OleDbDataAdapter

        'Use DataTable as storage of data from excel
        Dim dt As New DataTable

        'Use OleDbCommand to execute our SQL statement
        Dim cmd As New OleDbCommand

        'Use OleDbConnection that will be used by OleDbCommand to connect to excel file
        Dim xlsConn As OleDbConnection

        Dim sPath As String = String.Empty

        sPath = Form1.lbFilePath.Text

        'Create a new instance of connection and set the datasource value to excel's path
        xlsConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & ";Extended Properties=Excel 12.0")

        'Use try catch block to handle some or all possible errors that may occur in a 
        'given block of code, while still running code.

        'Try
        'Open the connection
        xlsConn.Open()

        'Set the command connection to opened connection
        cmd.Connection = xlsConn

        'Set the command type to CommandType.Text in order to use SQL statment constructed here 
        'in code editor
        cmd.CommandType = CommandType.Text

        'Assigned the command text to query the excel as shown below
        cmd.CommandText = ("SELECT * FROM [sheet1$]")

        'Assign the cmd to dataadapter
        da.SelectCommand = cmd

        'Fill the datatable with data from excel file using DataAdapter
        da.Fill(dt)

        ' Catch
        'This block Handle the exception.
        'MsgBox(ErrorToString)
        'Finally
        'We need to close the connection and set to nothing. This code will still execute even the code raised an error
        xlsConn.Close()
        xlsConn = Nothing
        'End Try
        Return dt
    End Function
    Public Sub SaveToDB(ByVal iRowIndex As Long)
        Dim conn As New OleDbConnection
        Dim sConnString As String
        Dim cmd As New OleDbCommand
        Dim sSQL As String = String.Empty

        'Try
        'Check if the path has a backslash in the end of string
        If Microsoft.VisualBasic.Right(Application.StartupPath, 1) = "\" Then
            sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "dbexport.accdb;Persist Security Info=False;"
        Else
            sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\dbexport.accdb;Persist Security Info=False;"
        End If

        'create a new instance of connection
        conn = New OleDbConnection(sConnString)

        'open the connection to be used by command object
        conn.Open()
        
        'Set the command's connection to our opened connection
        cmd.Connection = conn

        'Set the command type to CommandType.Text in order to use SQL statment constructed here 
        'in code editor
        cmd.CommandType = CommandType.Text

        'Set the comment text to insert the data to database
        cmd.CommandText = "INSERT INTO eqtrate ( item_no, equipment_type, hourly_cost )  VALUES(@item_no, @equipment_type, @hourly_cost)"

        'Add parameters in order to set the values in the query
        cmd.Parameters.Add("@item_no", OleDbType.Numeric).Value = dtExcelData.Rows(iRowIndex)(0)
        cmd.Parameters.Add("@equipment_type", OleDbType.VarChar).Value = dtExcelData.Rows(iRowIndex)(1)
        'This is just a sample of how to check if the field is null.
        cmd.Parameters.Add("@hourly_cost", OleDbType.Numeric).Value = IIf(Not IsDBNull(dtExcelData.Rows(iRowIndex)(2)), dtExcelData.Rows(iRowIndex)(2), Nothing)
        cmd.ExecuteNonQuery()


        'Catch ex As Exception
        MsgBox(ErrorToString)
        'Finally
        conn.Close()
        'End Try
    End Sub

解决方案

") 'Assign the cmd to dataadapter da.SelectCommand = cmd 'Fill the datatable with data from excel file using DataAdapter da.Fill(dt) ' Catch 'This block Handle the exception. 'MsgBox(ErrorToString) 'Finally 'We need to close the connection and set to nothing. This code will still execute even the code raised an error xlsConn.Close() xlsConn = Nothing 'End Try Return dt End Function Public Sub SaveToDB(ByVal iRowIndex As Long) Dim conn As New OleDbConnection Dim sConnString As String Dim cmd As New OleDbCommand Dim sSQL As String = String.Empty 'Try 'Check if the path has a backslash in the end of string If Microsoft.VisualBasic.Right(Application.StartupPath, 1) = "\" Then sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "dbexport.accdb;Persist Security Info=False;" Else sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\dbexport.accdb;Persist Security Info=False;" End If 'create a new instance of connection conn = New OleDbConnection(sConnString) 'open the connection to be used by command object conn.Open() 'Set the command's connection to our opened connection cmd.Connection = conn 'Set the command type to CommandType.Text in order to use SQL statment constructed here 'in code editor cmd.CommandType = CommandType.Text 'Set the comment text to insert the data to database cmd.CommandText = "INSERT INTO eqtrate ( item_no, equipment_type, hourly_cost ) VALUES(@item_no, @equipment_type, @hourly_cost)" 'Add parameters in order to set the values in the query cmd.Parameters.Add("@item_no", OleDbType.Numeric).Value = dtExcelData.Rows(iRowIndex)(0) cmd.Parameters.Add("@equipment_type", OleDbType.VarChar).Value = dtExcelData.Rows(iRowIndex)(1) 'This is just a sample of how to check if the field is null. cmd.Parameters.Add("@hourly_cost", OleDbType.Numeric).Value = IIf(Not IsDBNull(dtExcelData.Rows(iRowIndex)(2)), dtExcelData.Rows(iRowIndex)(2), Nothing) cmd.ExecuteNonQuery() 'Catch ex As Exception MsgBox(ErrorToString) 'Finally conn.Close() 'End Try End Sub

Have a look here: Import Data From Excel to Access with ASP.NET[^]. There you'll find simplest way to achieve that what you want.

void ImportFromExcel(string Access, string Excel)
{
//string Access = "FullFileNameAccess.mdb";
//string Excel = "FullFileNameExcel.xls";
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel +";Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(connect))
{
  using (OleDbCommand cmd = new OleDbCommand())
  {
    cmd.Connection = conn;
    cmd.CommandText = "INSERT INTO [MS Access;Database=" + Access + "].[Persons] SELECT * FROM [Sheet1

"; conn.Open(); cmd.ExecuteNonQuery(); } } )

In your case, replace: Provider=Microsoft.Jet.OLEDB.4.0 with Provider=Microsoft.ACE.OLEDB.12.0

这篇关于如何将各种表格从不同的Excel文件导入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！