Sql Server to Excel via a Web Service

Question

I would like to know the best way to transfer data from a web service to an excel worksheet (office 2007). I am not interested in outputing a report to excel from a web site. In our scenario a user will be using excel (with a number of macros and such) and will request about 11000 rows of data from the server to a worksheet.

Anyone have any experience with this?

thanks in advance.

Answer 1

A simple solution I've used in the past for getting data from a database to Excel via a web server is to simply expose the data as a table in a webpage. This can be done using any technology you like, e.g. ASP.NET, which can connect to the database, retreive the data and put the data in a GridView control.

You can then set up a 'Web Query' in Excel. This is done by using the menu item Data -> Import External Data -> New Web Query. You can then navigate to your webpage, select the table as your data source, and choose the range to which it should be written.

There are options for auto-refresh, query string parameters (from cells in the worksheet), etc.

You can automate the refreshment of your Web Query as part of a larger process by using VBA, e.g.

Sheet1.QueryTables(1).Refresh