表中的Excel从SQL Server存储过程与工作簿中的参数字段 [英] Table in Excel from SQL Server stored procedure with parameter field in workbook
问题描述
我需要使用Excel 2010和SQL Server创建动态报告。从其他人创建的以前的版本中，我发现如何创建数据连接，并在连接属性定义选项卡中使用具有特定参数的存储过程。其他报告在命令定义中使用？，并使用工作簿中的单元格定义参数。
I need to create a dynamic report using Excel 2010 and SQL Server. Going off of previous ones created by others I figured out how to create the data connection and use the stored procedure with a specific parameter in the connection properties definition tab. The other reports use a ? in the command definition and use a cell in the workbook to define the parameter.
我该如何做？
创建连接/报告/参数选择或任何帮助非常感激。
Specific steps to creating the connection/report/parameter selection or any help much appreciated.
推荐答案
我使用以下链接的博客文章中提供的步骤使其工作。
I got it to work using the steps provided at the following linked blog post.
http ：//codebyjoshua.blogspot.com/2012/01/get-data-from-sql-server-stored.html
在这里复制的步骤
Excel 2007说明：
Excel 2007 Instructions:
-
选择Excel功能区上的数据选项卡，然后在获取外部数据组中选择从其他来源下拉列表。然后选择从Microsoft查询
Select the Data tab on Excel's Ribbon, then within the Get Exernal Data group choose the "From other Sources" drop-down. Then Choose "From Microsoft Query"
在选择数据源弹出框中，选择您的SQL Server，然后点击确定。
Within "Choose Data Source" pop-up box, select your SQL Server, then hit OK.
如有必要，关闭添加表弹出窗口。
Close the "Add Tables" popup if necessary.
点击SQL按钮，或选择查看>SQL打开SQL弹出式编辑器。
Click on the "SQL" button, or choose View > SQL to open the SQL pop-up editor.
输入以下语法：{CALL myDatabaseName.dbo.myStoredProc（？，？，？ }
Enter the following syntax: {CALL myDatabaseName.dbo.myStoredProc (?, ?, ?)}
例如：{CALL northwind.dbo.spGetMaxCost（？，？，？）}
For example: {CALL northwind.dbo.spGetMaxCost (?, ?, ?)}
肯定会在调用语句周围包括大括号。每个问号（？）表示一个参数。如果您的存储过程需要更多或更少的参数，请根据需要添加或减去问号。
Be sure to include the squiggly braces around the call statement. Each Question Mark (?) indicates a parameter. If your stored procedure calls for more or less parameters, add or subtract question marks as needed.
点击确定按钮。弹出一个问题框，说SQL查询不能用图形表示，还是继续吗？，只需点击确定按钮。
Hit the OK button. A question box should pop-up saying "SQL Query can't be represented graphically, continue anyway?", just hit the OK button.
要求您提供上述每个问号的样品参数。输入正在查询的数据的有效参数值。
You will now be asked for sample parameters for each question mark you included above. Enter valid parameter values for the data you are querying.
输入最后一个参数后，应该在Microsoft Query中返回一些结果。如果看起来不错，请关闭Microsoft Query。
Once you have entered the last parameter, you should get some results back in Microsoft Query. If they look good, close Microsoft Query.
您现在应该查看导入数据弹出窗口。单击属性按钮，弹出连接属性。
You should now be looking at an "Import Data" pop-up. Click the Properties button, which will bring up the "Connection Properties" pop-up.
选择定义选项卡，然后选择参数按钮。您现在应该看到一个参数弹出窗口，您可以将参数连接到特定的单元格。
Select the Definition tab, then select the Parameters button. You should now see a "Parameters" pop-up, where you can connect the parameter to a specific cell.
选择从以下单元格获取值，然后连接到Excel中的一个适当的单元格，将保存您的参数，单击箭头的小框。
Select Get the value from the following cell, and then connect to an appropriate cell in Excel that will hold your parameter, by clicking the little box with the arrow.
如果您希望数据刷新每个时间更改包含参数的单元格，选中如果单元格值更改时自动刷新的框。
If you want the data to refresh every time you change the cell containing the parameter, check the box stating "Refresh automatically when cell value changes"
继续以上其他参数。完成后，单击确定，返回到连接属性弹出窗口。单击确定返回导入数据弹出窗口，然后再次单击确定。
Continue as above for the other parameters. When finished, click OK, to return to the Connection Properties pop-up. Click OK to return to the Import Data pop-up, and click OK again.
您现在应该从存储过程直接获取一些数据。 >
You should now have some data straight from your stored procedure.
这篇关于表中的Excel从SQL Server存储过程与工作簿中的参数字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
