当然Excel用于电子表格，但您知道可以将Excel连接到外部数据源吗？在本文中，我们将讨论如何将Excel电子表格连接到MySQL数据库表，并使用数据库表中的数据填充我们的电子表格。为了准备这种连接，您需要做一些事情。
准备(Preparation)
首先，您必须为MySQL(MySQL)下载最新的开放式数据库连接(Database Connectivity)( ODBC ) 驱动程序。MySQL的当前ODBC驱动程序可以位于
https://dev.mysql.com/downloads/connector/odbc/
确保(Make)在下载文件后对照下载页面上列出的文件检查文件的 md5 哈希值。
接下来，您将需要安装刚刚下载的驱动程序。 双击(Double)该文件开始安装过程。安装过程完成后，您需要创建一个数据库源名称(Database Source Name)( DSN ) 以与Excel一起使用。
创建 DSN(Creating the DSN)
DSN将包含使用MySQL数据库表所需的所有连接信息。在Windows系统上，您需要单击Start，然后是Control Panel，然后是Administrative Tools，然后是Data Sources (ODBC)。您应该看到以下信息：
请注意(Notice)上图中的选项卡。用户DSN(User DSN)仅对创建它的用户可用。任何可以登录机器的人都可以使用系统DSN 。(System DSN)文件 DSN(File DSN)是一个.DSN 文件，可以传输到安装了相同操作系统和驱动程序的其他系统上并在其上使用。
要继续创建DSN，请单击右上角附近的添加按钮。(Add)
您可能需要向下滚动才能看到MySQL ODBC 5.x 驱动程序(MySQL ODBC 5.x Driver)。如果它不存在，则说明在本文的准备(Preparation)部分安装驱动程序时出现问题。要继续创建DSN，请确保突出显示MySQL ODBC 5.x 驱动程序(Driver)并单击完成(Finish)按钮。您现在应该会看到一个类似于下面列出的窗口：
接下来，您需要提供填写上述表格所需的信息。我们在这篇文章中使用的MySQL数据库和表位于开发机器上，并且仅供一个人使用。(MySQL)对于“生产”环境，建议您创建一个新用户并仅授予新用户SELECT权限。将来，如有必要，您可以授予其他权限。
提供数据源配置的详细信息后，您应该单击“测试(Test)”按钮以确保一切正常。接下来，单击“确定(OK)”按钮。您现在应该看到您在ODBC 数据源管理(ODBC Data Source Administrator)器窗口中列出的上一组表单中提供的数据源名称：
创建电子表格连接
现在您已经成功地创建了一个新的DSN，您可以关闭ODBC 数据源管理(ODBC Data Source Administrator)器窗口并打开Excel。打开Excel后，单击数据(Data)功能区。对于较新版本的Excel，单击获取数据(Get Data)，然后从其他来源(From Other Sources)，然后从 ODBC(From ODBC)。
在旧版本的Excel 中(Excel)，这更像是一个过程。首先，您应该看到如下内容：
下一步是单击选项卡列表中“数据(Data)”一词正下方的“连接”链接。(Connections)Connections链接的位置在上图中以红色圈出。您应该看到Workbook Connections窗口：
下一步是单击“添加(Add)”按钮。这将向您显示现有连接(Existing Connections)窗口：
显然，您不想处理列出的任何连接。因此，单击Browse for More...按钮。这将显示“选择数据源(Select Data Source)”窗口：
就像前面的Existing Connections窗口一样，您不想使用Select Data Source窗口中列出的连接。因此，您需要双击+Connect to New Data Source.odc文件夹。这样做时，您现在应该会看到“数据连接向导( Data Connection Wizard)”窗口：
鉴于列出的数据源选项，您希望突出显示ODBC DSN并单击Next。数据连接向导(Data Connection Wizard)的下一步将显示您正在使用的系统上可用的所有ODBC数据源。(ODBC)
希望如果一切按计划进行，您应该会在ODBC数据源 中看到您在前面步骤中创建的DSN 。突出显示(Highlight)它并单击Next。
数据连接向导(Data Connection Wizard)的下一步是保存并完成。文件名字段应为您自动填写。您可以提供描述。示例中使用的描述对于可能使用它的任何人来说都是不言自明的。接下来，单击窗口右下角的完成按钮。(Finish)
您现在应该回到工作簿连接(Workbook Connection)窗口。应列出您刚刚创建的数据连接：
导入表数据(Importing the Table Data)
您可以关闭工作簿连接(Workbook Connection)窗口。我们需要单击Excel数据(Data)功能区中的现有连接(Existing Connections)按钮。现有连接(Connections)按钮应位于数据(Data)功能区的左侧。
单击Existing Connections按钮应该会显示Existing Connections窗口。您在前面的步骤中已经看到了此窗口，现在的不同之处在于您的数据连接应该列在顶部附近：
确保(Make)您在前面的步骤中创建的数据连接已突出显示，然后单击“打开(Open)”按钮。您现在应该看到“导入数据(Import Data)”窗口：
出于本文的目的，我们将使用“导入数据(Import Data)”窗口中的默认设置。接下来，单击“确定(OK)”按钮。如果一切顺利，您现在应该在工作表中看到MySQL数据库表数据。
对于这篇文章，我们正在使用的表有两个字段。第一个字段是一个名为 ID的自增INT字段。(INT)第二个字段是VARCHAR (50)，标题为 fname。我们最终的电子表格如下所示：
您可能已经注意到，第一行包含表列名。您还可以使用列名称旁边的下拉箭头对列进行排序。
包起来(Wrap-Up)
在这篇文章中，我们介绍了在哪里可以找到最新的MySQL ODBC 驱动程序、如何创建DSN 、如何使用(DSN)DSN创建电子表格数据连接以及如何使用电子表格数据连接将数据导入Excel电子表格。享受！
Connecting Excel to MySQL
Sure Excel is used for sprеadshеets, but did you know you can connect Εxcel to external data sources? In this аrticle we’re going to discusѕ how to connect an Excel spreadsheet to a MySQL database table and use the data in the datаbase table to рopulate our spreadsheet. There are a few things you need to do in order to prepare for this connection.
Preparation
First, you must download the most recent Open Database Connectivity (ODBC) driver for MySQL. The current ODBC driver for MySQL can be located at
https://dev.mysql.com/downloads/connector/odbc/
Make sure after you download the file that you check the file’s md5 hash against that listed on the download page.
Next, you will need to install the driver you just downloaded. Double click the file to start the install process. Once the install process is complete you will need to create a Database Source Name (DSN) to use with Excel.
Creating the DSN
The DSN will contain all of the connection information necessary to use the MySQL database table. On a Windows system, you will need to click on Start, then Control Panel, then Administrative Tools, then Data Sources (ODBC). You should see the following information:
Notice the tabs in the image above. A User DSN is only available to the user that created it. A System DSN is available to anyone that can log into the machine. A File DSN is a .DSN file that can be transported to and used on other systems that have the same OS and drivers installed.
To continue creating the DSN, click on the Add button near the top right corner.
You will probably have to scroll down to see the MySQL ODBC 5.x Driver. If it’s not present, something went wrong with installing the driver in the Preparation section of this post. To continue creating the DSN, make sure MySQL ODBC 5.x Driver is highlighted and click on the Finish button. You should now see a window similar to the one listed below:
Next you will need to supply the information necessary to complete the form shown above. The MySQL database and table we’re using for this post is on a development machine and is only used by one person. For “production” environments, it is suggested you create a new user and grant the new user SELECT privileges only. In the future, you can grant additional privileges if necessary.
After you have supplied the details for your data source configuration, you should click on the Test button to make sure everything is in working order. Next, click on the OK button. You should now see the data source name you supplied on the form in the previous set listed on the ODBC Data Source Administrator window:
Creating the Spreadsheet Connection
Now that you have successfully created a new DSN, you can close the ODBC Data Source Administrator window and open Excel. Once you have opened Excel, click on the Data ribbon. For newer versions of Excel, click on Get Data, then From Other Sources, then From ODBC.
In older versions of Excel, it’s a bit more of a process. Firstly, you should see something like this:
The next step is to click on the Connections link located right under the word Data in the tab list. The location of the Connections link is circled in red in the above image. You should be presented with the Workbook Connections window:
The next step is to click on the Add button. This will present you with the Existing Connections window:
Obviously you don’t want to work on any of the connections listed. Therefore, click on the Browse for More… button. This will present you with the Select Data Source window:
Just like the previous Existing Connections window, you do not want to use the connections listed in the Select Data Source window. Therefore, you want to double click on the +Connect to New Data Source.odc folder. In doing so, you should be now see the Data Connection Wizard window:
Given the data source choices listed, you want to highlight ODBC DSN and click Next. The next step of the Data Connection Wizard will display all of the ODBC data sources available on the system you are using.
Hopefully, if all as gone according to plan, you should see the DSN that you created in previous steps listed among the ODBC data sources. Highlight it and click on Next.
The next step in the Data Connection Wizard is to save and finish. The file name field should be auto filled for you. You can supply a description. The description used in the example is pretty self explanatory for anyone that might use it. Next, click on the Finish button in the lower right of the window.
You should now be back at the Workbook Connection window. The data connection you just created should be listed:
Importing the Table Data
You can close the Workbook Connection window. We need to click on the Existing Connections button in the Data ribbon of Excel. The Existing Connections button should be located to the left on the Data ribbon.
Clicking on the Existing Connections button should present you with the Existing Connections window. You’ve seen this window in previous steps, the difference now is that your data connection should be listed near the top:
Make sure the data connection you created in the previous steps is highlighted and then click on the Open button. You should now see the Import Data window:
For the purposes of this post, we are going to use the default settings on the Import Data window. Next, click on the OK button. If everything worked out for you, you should now be presented with the MySQL database table data in your worksheet.
For this post, the table we were working with had two fields. The first field is an auto-increment INT field titled ID. The second field is VARCHAR(50) and is titled fname. Our final spreadsheet looks likes like this:
As you’ve probably noticed, the first row contains the table column names. You can also use the drop down arrows next to the column names to sort the columns.
Wrap-Up
In this post we covered where to find the latest ODBC drivers for MySQL, how to create a DSN, how to create a spreadsheet data connection using the DSN and how to use the spreadsheet data connection to import data into an Excel spreadsheet. Enjoy!
