I wish to create an addin for excel which can read the current sheets data and then insert it into database. The database connection requires the file location. Therefore that approach cant be applied as I have to send the file that I have currently opened and can't specify the file path.
我希望为excel创建一个插件,它可以读取当前的工作表数据,然后将其插入数据库。数据库连接需要文件位置。因此,该方法无法应用,因为我必须发送我当前打开的文件,并且无法指定文件路径。
And moreover I need the data of each column to be inserted into separate column of table in SQL Server database. Any suggestions and code to help?
而且我需要将每列的数据插入到SQL Server数据库中的表的单独列中。有什么建议和代码可以帮忙吗?
2 个解决方案
#1
You could create an Excel add-in with Excel using VBA. You need to add a reference to Microsoft ActiveX Data Objects (ADO) in the VBA editor (using Tools -> References), and then you'll have full access to the Connection, Command, RecordSet, etc objects.
您可以使用VBA使用Excel创建Excel加载项。您需要在VBA编辑器中添加对Microsoft ActiveX数据对象(ADO)的引用(使用工具 - >引用),然后您将拥有对Connection,Command,RecordSet等对象的完全访问权限。
If I was doing this, I'd have the user specify the database/table/columns for each import batch using a form. Then I'd create a Connection object and loop through each row of the data to create and execute an insert Command based on the data in the row.
如果我这样做,我会让用户使用表单为每个导入批次指定数据库/表/列。然后我创建一个Connection对象并循环遍历数据的每一行,以根据行中的数据创建并执行insert命令。
When you are done, you have the option of saving the finished workbook as an Excel Addin file (xla) which can be distributed to others.
完成后,您可以选择将已完成的工作簿保存为Excel Addin文件(xla),该文件可以分发给其他人。
Here is a sample of what the insert code may look like:
以下是插入代码的示例:
Dim conn As New Connection
Dim comm As New Command
conn.Open YourConnectionString
Set comm.ActiveConnection = conn
comm.CommandText = "insert <table> (<column1>, <column2>, <column3>, ..., <columnN>) values (" + <value1> + ", '" + <value2> + "', " + <value3> + ", '" + <valueN>+ "')"
comm.Execute
conn.Close
#2
Does it have to be done within Excel? You can do this with a simple ADO.NET app, specifying an OleDb provider for Excel, and another for SQL.
是否必须在Excel中完成?您可以使用简单的ADO.NET应用程序执行此操作,为Excel指定OleDb提供程序,为SQL指定另一个。
There are lots of articles on the technique. You can do the same thing with minimal code in SQL Server Integration Services (SSIS, previously known as DTS). HEre's an old article on that.
有很多关于这项技术的文章。您可以使用SQL Server Integration Services(SSIS,以前称为DTS)中的最少代码执行相同的操作。这是一篇关于它的旧文章。
You can also do this from within Excel. With VS2005 you would use VSTO - Visual Studio Tools for Office; I think the function of VSTO is included in VS2008 Professional. VS+VSTO (or VS2008) allows you to create AddIns for Office apps includin Excel. Within the AddIn you can do whatever you like in code, including using a System.Data.SqlClient to insert data into SQL Server.
您也可以在Excel中执行此操作。使用VS2005,您将使用VSTO - Visual Studio Tools for Office;我认为VSTO的功能包含在VS2008 Professional中。 VS + VSTO(或VS2008)允许您为Excel应用程序创建AddIns。在AddIn中,您可以在代码中执行任何操作,包括使用System.Data.SqlClient将数据插入SQL Server。
#1
You could create an Excel add-in with Excel using VBA. You need to add a reference to Microsoft ActiveX Data Objects (ADO) in the VBA editor (using Tools -> References), and then you'll have full access to the Connection, Command, RecordSet, etc objects.
您可以使用VBA使用Excel创建Excel加载项。您需要在VBA编辑器中添加对Microsoft ActiveX数据对象(ADO)的引用(使用工具 - >引用),然后您将拥有对Connection,Command,RecordSet等对象的完全访问权限。
If I was doing this, I'd have the user specify the database/table/columns for each import batch using a form. Then I'd create a Connection object and loop through each row of the data to create and execute an insert Command based on the data in the row.
如果我这样做,我会让用户使用表单为每个导入批次指定数据库/表/列。然后我创建一个Connection对象并循环遍历数据的每一行,以根据行中的数据创建并执行insert命令。
When you are done, you have the option of saving the finished workbook as an Excel Addin file (xla) which can be distributed to others.
完成后,您可以选择将已完成的工作簿保存为Excel Addin文件(xla),该文件可以分发给其他人。
Here is a sample of what the insert code may look like:
以下是插入代码的示例:
Dim conn As New Connection
Dim comm As New Command
conn.Open YourConnectionString
Set comm.ActiveConnection = conn
comm.CommandText = "insert <table> (<column1>, <column2>, <column3>, ..., <columnN>) values (" + <value1> + ", '" + <value2> + "', " + <value3> + ", '" + <valueN>+ "')"
comm.Execute
conn.Close
#2
Does it have to be done within Excel? You can do this with a simple ADO.NET app, specifying an OleDb provider for Excel, and another for SQL.
是否必须在Excel中完成?您可以使用简单的ADO.NET应用程序执行此操作,为Excel指定OleDb提供程序,为SQL指定另一个。
There are lots of articles on the technique. You can do the same thing with minimal code in SQL Server Integration Services (SSIS, previously known as DTS). HEre's an old article on that.
有很多关于这项技术的文章。您可以使用SQL Server Integration Services(SSIS,以前称为DTS)中的最少代码执行相同的操作。这是一篇关于它的旧文章。
You can also do this from within Excel. With VS2005 you would use VSTO - Visual Studio Tools for Office; I think the function of VSTO is included in VS2008 Professional. VS+VSTO (or VS2008) allows you to create AddIns for Office apps includin Excel. Within the AddIn you can do whatever you like in code, including using a System.Data.SqlClient to insert data into SQL Server.
您也可以在Excel中执行此操作。使用VS2005,您将使用VSTO - Visual Studio Tools for Office;我认为VSTO的功能包含在VS2008 Professional中。 VS + VSTO(或VS2008)允许您为Excel应用程序创建AddIns。在AddIn中,您可以在代码中执行任何操作,包括使用System.Data.SqlClient将数据插入SQL Server。