从 Excel 工作表将数据写回 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/750565/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Writing data back to SQL from Excel sheet
提问by Rob1n
I know it is possible to get data from a SQL database into an excel sheet, but i'm looking for a way to make it possible to edit the data in excel, and after editing, writing it back to the SQL database.
我知道可以将数据从 SQL 数据库获取到 excel 表中，但我正在寻找一种方法可以在 excel 中编辑数据，并在编辑后将其写回 SQL 数据库。
It appears this is not a function in excel, and google didn't come up with much usefull.
看来这不是excel中的函数，谷歌也没有想出太多有用的东西。
采纳答案by barrowc
If you want to have the Excel file do all of the work (retrieve from DB; manipulate; update DB) then you could look at ActiveX Data Objects (ADO). You can get an overview at:
如果您想让 Excel 文件完成所有工作（从 DB 中检索；操作；更新 DB），那么您可以查看 ActiveX 数据对象 (ADO)。您可以在以下位置获得概览：
http://msdn.microsoft.com/en-us/library/ms680928(VS.85).aspx
http://msdn.microsoft.com/en-us/library/ms680928(VS.85).aspx
回答by RobS
You want the Import/Export wizard in SQL Management Studio. Depending on which version of SQL Server you are using, open SSMS (connect to the SQL instance you desire), right click on the database you want to import into and select Tasks.. "Import Data".
您需要 SQL Management Studio 中的导入/导出向导。根据您使用的 SQL Server 版本，打开 SSMS（连接到您想要的 SQL 实例），右键单击您要导入的数据库并选择任务...“导入数据”。
In the wizard, click Next (past the intro screen) and from the Data Source drop list select "Microsoft Excel". You specify the path and file name of the Excel spreadsheet, whether you have column headings or not.. then press Next. Just follow the wizard through, it'll set up the destination (can be SQL Server or another destination) etc.
在向导中，单击下一步（经过介绍屏幕）并从数据源下拉列表中选择“Microsoft Excel”。您指定 Excel 电子表格的路径和文件名，无论您是否有列标题。然后按下一步。只需按照向导进行操作，它将设置目标（可以是 SQL Server 或其他目标）等。
There is help available for this process in SQL Server Books Onlineand more (a walkthrough) from MSDN.
SQL Server 联机丛书和MSDN 中的更多内容（演练）中提供了有关此过程的帮助。
If you need something deployable/more robust (or less wizard driven) then you'd need to take a look at SQL Server Integration Services (for a more "Enterprise" and security conscious approach). It's probably overkill for what you want to accomplish though.
如果您需要一些可部署的/更强大的（或更少的向导驱动），那么您需要查看 SQL Server 集成服务（以获得更具“企业”意识和安全意识的方法）。不过，对于您想要完成的工作来说，这可能有点过头了。
回答by Tansu KAYRAK
There is a new Excel plug-in named "MySQL for Excel" : http://www.mysql.com/why-mysql/windows/
有一个名为“MySQL for Excel”的新 Excel 插件：http: //www.mysql.com/why-mysql/windows/
回答by Andrew Partridge
I just had a need to do this, and this thread has been quiet for a long time, so I thought it might be useful to supply a recent data point.
我只是需要这样做，这个线程已经安静了很长时间，所以我认为提供一个最近的数据点可能会有用。
In my application roving salespeople use a copy of an Excel workbook that tracks the progress of a prospect through a loan application. The current stage of the application needs to be automatically saved back to a remote SQL database so that we can run reporting on it.
在我的申请中，流动销售人员使用 Excel 工作簿的副本，通过贷款申请跟踪潜在客户的进度。应用程序的当前阶段需要自动保存回远程 SQL 数据库，以便我们可以对其运行报告。
Rejected methods for updating the database from Excel:
拒绝从 Excel 更新数据库的方法：
SSISand OpenRowSetare both methods for allowing SQL Server to pull the data from Excel, and don't work very well when the Excel workbook is sitting in an undefined location on a user's computer, and certainly not when the workbook is currently open in Excel.
SSIS和OpenRowSet都是允许 SQL Server 从 Excel 中提取数据的方法，当 Excel 工作簿位于用户计算机上的未定义位置时效果不佳，当然当工作簿当前在 Excel 中打开时效果不佳.
ADOis now, if not actually deprecated, nevertheless looking very long in the tooth. Also, I wanted the solution to be robust in the face of the user possibly not being connected to the internet.
ADO现在，如果不是真的被弃用，但看起来很长。此外，我希望该解决方案在用户可能未连接到 Internet 的情况下是健壮的。
I also considered running a web APIon the destination server. Macros in the Excel workbook connect to the web API to transfer data. However, it can sometimes be painful to allow a web API to talk to the outside world. Also, the code to make it robust in the face of temporary loss of internet connection is painful.
我还考虑在目标服务器上运行Web API。Excel 工作簿中的宏连接到 Web API 以传输数据。但是，有时允许 Web API 与外部世界对话会很痛苦。此外，在暂时失去互联网连接的情况下使其健壮的代码是痛苦的。
The adopted solution:
采用的解决方案：
The solution I plan to adopt is low-tech: email. Excel emails the data to an address hosted on an Exchange server. Everyone in the company has Outlook installed, so the emails are sent by programmatically adding them to the Outlook Outbox. Outlook nicely handles the case when the user is offline. At the server end, a custom C# executable, fired up at regular intervals by the Task Scheduler, polls the inbox and processes the emails.
我计划采用的解决方案是低技术含量的：电子邮件。Excel 将数据通过电子邮件发送到 Exchange 服务器上托管的地址。公司中的每个人都安装了 Outlook，因此通过以编程方式将电子邮件添加到 Outlook 发件箱来发送电子邮件。当用户离线时，Outlook 可以很好地处理这种情况。在服务器端，自定义 C# 可执行文件由任务计划程序定期启动，轮询收件箱并处理电子邮件。
回答by SamH
You could use try these add-ins :
您可以尝试使用这些插件：
www.QueryCell.com (I created this one) www.SQLDrill.com www.Excel-DB.net
www.QueryCell.com（我创建了这个） www.SQLDrill.com www.Excel-DB.net
回答by pmarflee
You can use the OPENROWSET function to manipulate Excel data from a T-SQL script. Example usage would be:
您可以使用 OPENROWSET 函数从 T-SQL 脚本操作 Excel 数据。示例用法是：
UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=c:\MySpreadsheet.xls', 'Select * from MyTable') SET Field1='Value1' WHERE Field2 = 'Value2'
更新 OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=c:\MySpreadsheet.xls', 'Select * from MyTable') SET Field1='Value1' WHERE Field2 = 'Value2'
