我需要做什么才能使 Excel 通过 HTTPS 访问 Web 查询？
我正在尝试实现一个页面，允许 Excel 用户通过 Excel 提供的 Web 查询功能使用其提供的数据。
只要我使用 HTTP（甚至 BASIC 用户身份验证也能工作），一切都运行得很好。
一旦我将其切换到 HTTPS，Excel 将不再下载数据（它甚至是完全官方的 SSL 证书，因此自签名证书不是问题）。
这篇 Microsoft 知识库文章 几乎描述了该问题。
现在让我想知道的是：
当 Excel 由于安全 Web 服务器上的设置而无法启动连接时，就会出现此问题。
这似乎意味着有某种方法可以使其发挥作用，但甚至没有暗示我需要关注的方向。
“由于安全 Web 服务器上的设置”是否应该从字面上理解，或者这只是 Microsoft 的说法“除非您从我们这里购买正确的软件，否则这将不起作用”？
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"?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我似乎发现了问题：
如果页面的 HTTP 标头指定不应缓存并且它是通过 HTTPS 传输的，MS Excel 似乎无法使用页面上的数据（通过 HTTP 发送的相同标头似乎会被忽略）。
因此，通过不发送这些标头，Excel 突然能够访问数据：
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:
约阿希姆的回答为我解决了这个问题。服务器端 Web 框架（PHP5 / Expression Engine 1.6.7）在每个请求上发送 Pragma: no-cache （即使我的 Web 查询结果页面设置 Pragma: public，我猜框架会覆盖它）。一旦我删除它，一切就开始工作了。
IE 和 Office 的 Pragma 行为：no-cache 与 MS 知识库文章：Internet Explorer 无法访问中描述的类似从 SSL 网站打开 Office 文档
另请参阅此缓存教程的警告：Pragma no -cache 已弃用。考虑到这一点，我将表达式引擎的“输出和调试”设置为“生成 HTTP 页面标头？选项为否。（其他框架也有类似的配置选项）。但成功缓存站点的其余部分需要其他一些自动发送的标头，因此我选择注释掉框架源代码中的 Pragma: no-cache 行。
如果您无法修改 Web 服务器/框架发送的 HTTP 标头，则唯一的 MS-Office 客户端选项将是使用 VBA 宏来自动化 Internet Explorer 组件，以绕过 Office 的缓存行为。请参阅在 Microsoft Office Excel 2003 中使用 Web 查询的不同方法作为起点。
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.