vba 如何在从 Web 服务器保存 Excel 文件时解决 [1] IE 错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/120497/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to work around the [1] IE bug while saving an excel file from a Web server?
提问by paulgreg
I've noticed that Internet Explorer adds a number in square brackets to files downloaded from the internet (usually [1]). This creates a big problem with downloading Excel spreadsheets as square brackets are not a valid filename character inside Excel worksheet name. That problem is IE specific, others browsers are keeping same file name.
我注意到 Internet Explorer 在从 Internet 下载的文件中添加了方括号中的数字（通常是 [1]）。这会在下载 Excel 电子表格时产生一个大问题，因为方括号不是 Excel 工作表名称中的有效文件名字符。这个问题是 IE 特有的，其他浏览器保持相同的文件名。
So, if you have a pivot table auto-refreshed on file opening for example, you'll get an error message saying the name "file[1].yourPivotTableName" is not valid.
因此，例如，如果您有一个在打开文件时自动刷新的数据透视表，您将收到一条错误消息，指出名称“file[1].yourPivotTableName”无效。
Is there any solution to that problem ?
有没有办法解决这个问题？
EDIT : It seems that whatever the filename suggested by HTTP directives, IE adds [1] in all cases, which cause the problem ! (So, answers about filenames aren't helpful in that case)
编辑：似乎无论HTTP 指令建议的文件名是什么，IE 都会在所有情况下添加 [1]，这会导致问题！（因此，在这种情况下，有关文件名的答案无济于事）
EDIT : I've tried some VBA code to save file under another name when it'll open. However, it doesn't work (same error message than before). Do you think there's a way to fix that with VBA ?
编辑：我尝试了一些 VBA 代码，以便在文件打开时以另一个名称保存文件。但是，它不起作用（与以前相同的错误消息）。你认为有办法用 VBA 解决这个问题吗？
采纳答案by Seb Nilsson
I've got it working using VBA provided by this cool guy (think of him fondly). It renames the file and then reattaches the pivots.
我已经使用这个很酷的家伙提供的 VBA 让它工作了（深情地想他）。它重命名文件，然后重新附加枢轴。
http://php.kennedydatasolutions.com/blog/2008/02/05/internet-explorer-breaks-excel-pivot-tables/
http://php.kennedydatasolutions.com/blog/2008/02/05/internet-explorer-breaks-excel-pivot-tables/
回答by Liam
I think that this happens when you open the spreadsheet in IE and IE saves it to a temporary file. And I think it only happens when the spreadsheet's filename has more than one dot in it. Try it with a simple "sample.xls". Another workaround is to tell users to save the file to the desktop and then open it.
我认为当您在 IE 中打开电子表格并且 IE 将其保存到临时文件时会发生这种情况。而且我认为只有当电子表格的文件名中包含多个点时才会发生这种情况。用一个简单的“sample.xls”试试。另一种解决方法是告诉用户将文件保存到桌面然后打开它。
回答by Seb Nilsson
It's a built-in feature in Internet Explorer.
它是 Internet Explorer 的内置功能。
Stop using "Open", start using "Save" in the file-download window, otherwise IE will append "[1]" to filename of the file that it places in some temporary folder.
停止使用“打开”，在文件下载窗口开始使用“保存”，否则 IE 会将“[1]”附加到它放在某个临时文件夹中的文件的文件名。
You could build some .NET application using System.IO.FileSystemWatcherthat catches the event of the creation of the downloaded file or something and renames the file.
您可以使用System.IO.FileSystemWatcher构建一些 .NET 应用程序，以捕获创建下载文件或其他内容的事件并重命名文件。
回答by AnirbanDebnath
I have solved this issue by using method where we pass 3 parameters: Filename, file extension(without the .dot) and the HTTP request); then doing the UTF-8 encoding of the filename and extension. Sample Code:
我通过使用传递 3 个参数的方法解决了这个问题：文件名、文件扩展名（不带 .dot）和 HTTP 请求）；然后对文件名和扩展名进行 UTF-8 编码。示例代码：
public static String encoding(String fileName, String extension, HttpServletRequest request)
{
String user = request.getHeader( "user-agent" );
boolean isInternetExplorer = ( user.indexOf( "MSIE" ) > -1 );
String var = "";
try
{
fileName = URLEncoder.encode( fileName, "UTF-8" );
fileName = fileName.trim().replaceAll( "\+", " " );
extension = URLEncoder.encode( extension, "UTF-8" );
extension = extension.trim().replaceAll( "\+", " " );
if ( isInternetExplorer )
{
disposition = "attachment; filename=\"" + fileName+"."+extension+"\"";
}
else
{
var = "attachment; filename*=UTF-8''" + fileName+"."+extension;
}
}
catch ( UnsupportedEncodingException ence )
{
var = "attachment; filename=\"" + fileName+"."+extension;
ence.printStackTrace();
}
return var;
}
This worked just fine in my case. Hope it will help you all.
这在我的情况下工作得很好。希望对大家有所帮助。
回答by Seb Nilsson
Actually, the correct .NET-code is as following:
实际上，正确的 .NET 代码如下：
Response.AppendHeader("content-disposition", "attachment;filename=file.xls");
Response.ContentType = "application/vnd.ms-excel";
Note: AppendHeader, not AddHeader, which I think only works in debug web-server and IIS7.
注意：AppendHeader，而不是AddHeader，我认为它只适用于调试网络服务器和 IIS7。
回答by Seb Nilsson
The following has worked for me:
以下对我有用：
private string EncodeFileName(string fileName)
{
fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8).Replace("+", " ");
if (HttpContext.Current.Request.UserAgent.ToLower().Contains("msie"))
{
var res = new StringBuilder();
var chArr = fileName.ToCharArray();
for (var j = 0; j < chArr.Length; j++)
{
if (chArr[j] == '.' && j != fileName.LastIndexOf("."))
res.Append("%2E");
else
res.Append(chArr[j]);
}
fileName = res.ToString();
}
return "\"" + fileName + "\"";
}
回答by Saurabh Pal
You could just make sure that in the options box for the pivot the auto refresh is switched off. Now even when opened from the server the pivot will work perfectly
您可以确保在枢轴的选项框中关闭自动刷新。现在，即使从服务器打开，支点也能完美运行
回答by Arvind Kumar
Put these four lines in your code:
将这四行代码放入您的代码中：
response.reset();
response.setHeader("Expires", "0");
response.setHeader("Cache-Control","must-revalidate,post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
Hope this helps.
希望这可以帮助。
回答by ?ygimantas
I have encountered the same problem and came up with (imo) a better solution that does not need any VBA.
我遇到了同样的问题，并提出了（imo）一个不需要任何 VBA 的更好的解决方案。
If you set "Content-Disposition" header to "attachment; filename=<...>" instead of "inline; filename=<...>" the normal browsers will open dialog that will allow to save or open a file with a filename defined in a header, but Internet Explorer will behave in kind of weird way. It will open file download dialog and if you press Saveit will suggest a filename that is defined in the header, but if you press Openit will save file to a temporary folder and open it with a name that is the same as your URN(without 'namespace'), e.g. if your URI is http://server/folder/file.html, so IE will save your file as file.html(no brackets, woo hoo!). This leads us to a solution:
如果您将“Content-Disposition”标头设置为“attachment; filename=<...>”而不是“inline; filename=<...>”，则普通浏览器将打开允许保存或打开文件的对话框在标题中定义的文件名，但 Internet Explorer 会以一种奇怪的方式运行。它将打开文件下载对话框，如果您按“保存”，它将建议在标题中定义的文件名，但如果您按“打开”，它将把文件保存到一个临时文件夹并使用与您的 URN 相同的名称打开它（没有“命名空间”），例如，如果您的 URI 是http://server/folder/file.html，那么 IE 会将您的文件保存为file.html（没有括号，哇哦！）。这导致我们找到一个解决方案：
Write a script that handles request from http://server/folder/* and when you need to serve an XLS file just redirect to that script (use your filename instead of asterisk) with Content-Dispositionset to inline.
编写一个脚本来处理来自http://server/folder/* 的请求，当您需要提供 XLS 文件时，只需重定向到该脚本（使用您的文件名而不是星号）并将Content-Disposition设置为inline。
回答by hollystyles
In .NET I have found from experience only this seems to work for me:
在 .NET 中，我从经验中发现只有这对我有用：
Response.AddHeader("Content-Disposition", "attachment; filename=excel.xls");
Response.AddHeader("Content-Type", "application/vnd.ms-excel");
Response.ContentType = "application/vnd.ms-excel";
The duplication smells, but so far I have never got to the bottom of it (maybe Sebs post explains this). Also the "content-Disposition" value appears very finicky use a : instead of a ; or ommit the space between it and 'filename' and it blows!
重复有味道，但到目前为止我还没有深入了解它（也许 Sebs 的帖子解释了这一点）。此外，“content-Disposition”值看起来非常挑剔，使用 : 而不是 ; 或者省略它和“文件名”之间的空格，它会爆炸！
Also if you have compression enabled on IIS this may fix things for you:
此外，如果您在 IIS 上启用了压缩，这可能会为您解决问题：
Response.ClearHeaders()
