excel 连接 mysql_使用 Excel 进行连接 - Azure SQL Database & SQL Managed Instance | Microsoft Docs...

您现在访问的是微软AZURE全球版技术文档网站，若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站，请访问 https://docs.azure.cn.

将 Excel 连接到 Azure SQL 数据库中的数据库或 Azure SQL 托管实例，并创建报表Connect Excel to a database in Azure SQL Database or Azure SQL Managed Instance, and create a report

05/29/2020

本文内容

适用于：

Azure SQL 数据库

Azure SQL 托管实例

可以将 Excel 连接到数据库，然后导入数据并根据数据库中的值来创建表和图表。You can connect Excel to a database and then import data and create tables and charts based on values in the database. 在本教程中，用户将设置 Excel 与数据库表之间的连接，保存用于存储 Excel 的数据和连接信息的文件，并根据数据库值创建分析数据透视图。In this tutorial you will set up the connection between Excel and a database table, save the file that stores data and the connection information for Excel, and then create a pivot chart from the database values.

在开始之前，需要创建一个数据库。You'll need to create a database before you get started. If you don't have one, see Create a database in Azure SQL Database and Create server-level IP firewall to get a database with sample data up and running in a few minutes.

在本文中，会将该文章中的示例数据导入 Excel，但可以使用自己的数据执行类似的步骤。In this article, you'll import sample data into Excel from that article, but you can follow similar steps with your own data.

还需要 Excel 的副本。You'll also need a copy of Excel.

连接 Excel 并加载数据Connect Excel and load data

要将 Excel 连接到 SQL 数据库中的数据库，请打开 Excel，然后创建新的工作簿或打开现有的 Excel 工作簿。To connect Excel to a database in SQL Database, open Excel and then create a new workbook or open an existing Excel workbook.

在页面顶部的菜单栏中，依次选择“数据”选项卡、“获取数据”、“从 Azure 获取”，然后选择“从 Azure SQL 数据库获取” 。In the menu bar at the top of the page, select the Data tab, select Get Data, select From Azure, and then select From Azure SQL Database.

在 " SQL Server 数据库" 对话框中，键入要连接到的服务器名称， database.windows.net"。In the SQL Server database dialog box, type the Server name you want to connect to in the form .database.windows.net. 例如“msftestserver.database.windows.net”****。For example, msftestserver.database.windows.net. 输入数据库名称(可选)。Optionally, enter in the name of your database. 选择“确定”以打开凭据窗口。Select OK to open the credentials window.

在“SQL Server 数据库”对话框中，选择左侧的“数据库”，然后输入要连接的服务器的用户名和密码 。In the SQL Server database dialog box, select Database on the left side, and then enter in your User Name and Password for the server you want to connect to. 选择“连接”以打开“导航器” 。Select Connect to open the Navigator.

提示

根据网络环境，可能无法连接；如果服务器不允许来自客户端 IP 地址的流量，可能会断开连接。Depending on your network environment, you may not be able to connect or you may lose the connection if the server doesn't allow traffic from your client IP address. 转到 Azure 门户，依次单击“SQL 服务器”、服务器、“设置”下面的“防火墙”，然后添加客户端 IP 地址。Go to the Azure portal, click SQL servers, click your server, click firewall under settings and add your client IP address. 有关详细信息，请参阅 如何配置防火墙设置 。

在“导航器”中，从列表中选择想要使用的数据库，选择想要使用的表格或视图(我们选择的是“vGetAllCategories”)，然后选择“加载”以将数据从数据库移至 Excel 电子表格 。In the Navigator, select the database you want to work with from the list, select the tables or views you want to work with (we chose vGetAllCategories), and then select Load to move the data from your database to your Excel spreadsheet.

将数据导入 Excel 并创建数据透视图Import the data into Excel and create a pivot chart

建立连接后，有多种加载数据的方式可供选择。Now that you've established the connection, you have several different options with how to load the data. 例如，以下步骤基于来自 SQL 数据库中数据库的数据创建数据透视表。For example, the following steps create a pivot chart based on the data found in your database in SQL Database.

按照前一部分中的步骤进行操作，不过这次不再选择“加载”，而是改为从“加载”下拉列表中选择“加载至” 。Follow the steps in the previous section, but this time, instead of selecting Load, select Load to from the Load drop-down.

然后选择该数据在工作簿中的显示方式。Next, select how you want to view this data in your workbook. 将此数据添加到数据模型。We chose PivotChart. 也可以选择创建新工作表或将此数据添加到数据模型。You can also choose to create a New worksheet or to Add this data to a Data Model. 有关数据模型的详细信息，请参阅在 Excel 中创建数据模型。For more information on Data Models, see Create a data model in Excel.

工作表现在包含空白的数据透视表和图表。The worksheet now has an empty pivot table and chart.

在“数据透视表字段”下，选中要查看的所有字段的复选框。Under PivotTable Fields, select all the check-boxes for the fields you want to view.

提示

如果想将其他 Excel 工作簿和工作表与数据库连接，请选择“数据”选项卡，然后选择“最近使用的源”以启动“最近使用的源”对话框 。If you want to connect other Excel workbooks and worksheets to the database, select the Data tab, and select Recent Sources to launch the Recent Sources dialog box. 从该对话框中的列表中选择之前创建的连接，然后单击“打开”。From there, choose the connection you created from the list, and then click Open.

使用 .odc 文件创建永久连接Create a permanent connection using .odc file

若要永久保存连接详细信息，可以创建一个 .odc 文件，并将此连接作为“现有连接”对话框中的一个选项。To save the connection details permanently, you can create an .odc file and make this connection a selectable option within the Existing Connections dialog box.

在页面顶部的菜单栏中选择“数据”选项卡，然后选择“现有连接”以启动“现有连接”对话框 。In the menu bar at the top of the page, select the Data tab, and then select Existing Connections to launch the Existing Connections dialog box.

选择“浏览更多”以打开“选择数据源”对话框 。Select Browse for more to open the Select Data Source dialog box.

选择“+NewSqlServerConnection.odc”文件并选择“打开”以打开“数据连接向导” 。Select the +NewSqlServerConnection.odc file and then select Open to open the Data Connection Wizard.

在“数据连接向导”中键入服务器名称和 SQL 数据库凭据。In the Data Connection Wizard, type in your server name and your SQL Database credentials. 选择“下一步”。Select Next.

从下拉列表中选择包含数据的数据库。Select the database that contains your data from the drop-down.

选择感兴趣的表格或视图。Select the table or view you're interested in. 我们选择的是“vGetAllCategories”。We chose vGetAllCategories.

选择“下一步”。Select Next.

在数据连接向导的下一个屏幕中选择文件位置、文件名以及友好名称 。Select the location of your file, the File Name, and the Friendly Name in the next screen of the Data Connection Wizard. 还可以选择将密码保存在文件中，但这样做可能会将数据泄露给未经允许的访问。You can also choose to save the password in the file, though this can potentially expose your data to unwanted access. 准备就绪后，选择“完成”。Select Finish when ready.

选择所需的数据导入方式。Select how you want to import your data. 我们选择制作一个数据透视表。We chose to do a PivotTable. 还可以通过选择“属性”修改连接的属性。You can also modify the properties of the connection by select Properties. 准备就绪后，选择“确定”。Select OK when ready. 如果未选择将密码保存在文件中，系统会提示输入凭据。If you did not choose to save the password with the file, then you will be prompted to enter your credentials.

通过展开“数据”选项卡并选择“现有连接”可以验证新的连接是否已保存 。Verify that your new connection has been saved by expanding the Data tab, and selecting Existing Connections.

后续步骤Next steps