What do I need to do to make Excel access a Web Qu

问题:

I'm trying to implement a page that allows Excel users to use the data it provides via the Web Query feature provided by Excel.

It's all working out pretty nicely, as long as I use HTTP (even BASIC user authentication works).

As soon as I switch that over to HTTPS Excel won't download the data anymore (it's even a fully official SSL certificate, so it's not a problem with a self-signed one).

This Microsoft knowledge base article pretty much describes the problem.

Now the part that makes me wonder is this:

This issue occurs when Excel cannot initiate a connection because of the settings on the secure Web server.

This seems to imply that there is some way to get this working, but there's not even a hint at the direction I need to look at.

Should the "because of the settings on the secure Web server" be taken at face value, or is it just a Microsoft way of saying "this won't work unless you buy the right software from us"?

回答1:

It seems I've found the problem:

MS Excel seems to be unable to use the data on the page if the HTTP headers of the page specify that it should not be cache and it is transfered via HTTPS (the same headers sent via HTTP seem to get ignored).

So by not sending these headers, Excel was suddenly able to access the data:

Pragma: no-cache
Cache-Control: no-cache

回答2:

Joachim's answer solved the problem for me. The server-side web framework (PHP5 / Expression Engine 1.6.7) was sending a Pragma: no-cache on every request (even though my web-query results page set Pragma: public, I guess the framework overrode it). Once I removed it, everything started working.

IE and Office behavior for Pragma: no-cache is similar to that described in MS KB Article: Internet Explorer is unable to open Office documents from an SSL Web site

See also this caching tutorial's Warning: Pragma no-cache Deprecated. With this in mind I set Expression Engine' Output and Debugging > Generate HTTP Page Headers? option to No. (Other frameworks have similar config options). But some of the other automatically sent headers were needed for successfully caching the rest of the site, so I opted for commenting out the Pragma: no-cache lines in the framework source code.

If you do not have the option of modifying the HTTP headers sent by your web server / framework, the only MS-Office-client-side-only option will be to use VBA macros to automate an Internet Explorer component to get around Office's caching behavior. See Different Ways of Using Web Queries in Microsoft Office Excel 2003 as a starting point.