Java JSP生成Excel电子表格（XLS）下载
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/477886/
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
JSP generating Excel spreadsheet (XLS) to download
提问by fmsf
I have this application I'm developing in JSP and I wish to export some data from the database in XLS (MS Excel format).
我有一个正在 JSP 中开发的应用程序，我希望从数据库中以 XLS（MS Excel 格式）导出一些数据。
Is it possible under tomcat to just write a file as if it was a normal Java application, and then generate a link to this file? Or do I need to use a specific API for it?
在tomcat下是不是可以像普通的Java应用一样写一个文件，然后生成这个文件的链接？还是我需要为此使用特定的 API？
Will I have permission problems when doing this?
这样做时我会遇到权限问题吗？
采纳答案by Kevin
While you can use a full fledged library like JExcelAPI, Excel will also read CSV and plain HTML tables provided you set the response MIME Type to something like "application/vnd.ms-excel".
虽然您可以使用完整的库，如JExcelAPI，但只要您将响应 MIME 类型设置为“application/vnd.ms-excel”之类的内容，Excel 也将读取 CSV 和纯 HTML 表。
Depending on how complex the spreadsheet needs to be, CSV or HTML can do the job for you without a 3rd party library.
根据电子表格的复杂程度，CSV 或 HTML 可以为您完成这项工作，而无需第 3 方库。
回答by cletus
You will probably need a library to manipulate Excel files, like JExcelAPI("jxl") or POI. I'm more familiar with jxl and it can certainly write files. You can generate them and store them by serving a URL to them but I wouldn't. Generated files are a pain. They add complication in the form on concurrency, clean-up processes, etc.
您可能需要一个库来操作 Excel 文件，例如JExcelAPI("jxl") 或POI。我对jxl比较熟悉，它当然可以写文件。您可以通过向它们提供 URL 来生成它们并存储它们，但我不会。生成的文件很痛苦。它们以并发、清理过程等的形式增加了复杂性。
If you can generate the file on the fly and stream it to the client through the standard servlet mechanisms.
如果您可以动态生成文件并通过标准 servlet 机制将其流式传输到客户端。
If it's generated many, may times or the generation is expensive then you can cache the result somehow but I'd be more inclined to keep it in memory than as a file. I'd certainly avoid, if you can, linking directly to the generated file by URL. If you go via a servlet it'll allow you to change your impleemntation later. It's the same encapsualtion concept as in OO dsign.
如果它生成了很多次，或者生成很昂贵，那么你可以以某种方式缓存结果，但我更倾向于将它保存在内存中而不是作为文件。如果可以的话，我当然会避免通过 URL 直接链接到生成的文件。如果您通过 servlet，它将允许您稍后更改您的实现。它与 OO 设计中的封装概念相同。
回答by Boris Pavlovi?
maybe you should consider using some reporting tool with an option of exporting files into XLS format. my suggestion is JasperReports
也许您应该考虑使用一些报告工具，并提供将文件导出为 XLS 格式的选项。我的建议是 JasperReports
回答by BalusC
Don't use plain HTML tables with an application/vnd.ms-excelcontent type. You're then basically fooling Excel with a wrong content type which would cause failure and/or warnings in the latest Excel versions. It will also messup the original HTML source when you edit and save it in Excel. Just don't do that.
不要使用带有application/vnd.ms-excel内容类型的纯 HTML 表格。然后，您基本上是在用错误的内容类型欺骗 Excel，这会导致最新 Excel 版本中的失败和/或警告。当您在 Excel 中编辑和保存原始 HTML 源代码时，它也会弄乱原始 HTML 源代码。不要那样做。
CSV in turn is a standard format which enjoys default support from Excel without any problems and is in fact easy and memory-efficient to generate. Although there are libraries out, you can in fact also easily write one in less than 20 lines (funny for ones who can't resist). You just have to adhere the RFC 4180spec which basically contains only 3 rules:
CSV 反过来是一种标准格式，它享有 Excel 的默认支持，没有任何问题，实际上生成起来很容易且节省内存。尽管有一些库，但实际上您也可以轻松地用不到 20 行的代码编写一个（对于无法抗拒的人来说很有趣）。您只需要遵守RFC 4180规范，该规范基本上只包含 3 条规则：
- Fields are separated by a comma.
- If a comma occurs within a field, then the field has to be surrounded by double quotes.
- If a double quote occurs within a field, then the field has to be surrounded by double quotes and the double quote within the field has to be escaped by another double quote.
- 字段以逗号分隔。
- 如果字段中出现逗号，则该字段必须用双引号括起来。
- 如果一个字段中出现双引号，则该字段必须用双引号括起来，并且该字段中的双引号必须用另一个双引号转义。
Here's a kickoff example:
这是一个启动示例：
public static <T> void writeCsv (List<List<T>> csv, char separator, OutputStream output) throws IOException {
BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(output, "UTF-8"));
for (List<T> row : csv) {
for (Iterator<T> iter = row.iterator(); iter.hasNext();) {
String field = String.valueOf(iter.next()).replace("\"", "\"\"");
if (field.indexOf(separator) > -1 || field.indexOf('"') > -1) {
field = '"' + field + '"';
}
writer.append(field);
if (iter.hasNext()) {
writer.append(separator);
}
}
writer.newLine();
}
writer.flush();
}
Here's an example how you could use it:
这是一个如何使用它的示例：
public static void main(String[] args) throws IOException {
List<List<String>> csv = new ArrayList<List<String>>();
csv.add(Arrays.asList("field1", "field2", "field3"));
csv.add(Arrays.asList("field1,", "field2", "fie\"ld3"));
csv.add(Arrays.asList("\"field1\"", ",field2,", ",\",\",\""));
writeCsv(csv, ',', System.out);
}
And inside a Servlet (yes, Servlet, don't use JSP for this!) you can basically do:
在 Servlet（是的，Servlet，不要为此使用 JSP！）内，您基本上可以执行以下操作：
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String filename = request.getPathInfo().substring(1);
List<List<Object>> csv = someDAO().findCsvContentFor(filename);
response.setHeader("content-type", "text/csv");
response.setHeader("content-disposition", "attachment;filename=\"" + filename + "\"");
writeCsv(csv, ';', response.getOutputStream());
}
Map this servlet on something like /csv/*and invoke it as something like http://example.com/context/csv/filename.csv. That's all.
将这个 servlet 映射到类似的/csv/*东西上，然后像http://example.com/context/csv/filename.csv. 就这样。
Note that I added the possiblity to specify the separator character separately, because it may depend on the locale used whether Excel would accept a comma ,or semicolon ;as CSV field separator. Note that I also added the filename to the URL pathinfo, because a certain webbrowser developed by a team in Redmond otherwise wouldn't save the download with the proper filename.
请注意，我添加了单独指定分隔符的可能性，因为这可能取决于 Excel 是否接受逗号,或分号;作为 CSV 字段分隔符所使用的区域设置。请注意，我还在 URL 路径信息中添加了文件名，因为 Redmond 的一个团队开发的某个 webbrowser 否则不会使用正确的文件名保存下载。
回答by lv10
POI or JExcel are good APIs. I personally like better POI, plus POI is constantly updated. Furthermore, there are more resources online about POI than JExcel in case you have any questions. However, either of the two does a great job.
POI 或 JExcel 是很好的 API。我个人喜欢更好的 POI，而且 POI 会不断更新。此外，如果您有任何问题，在线关于 POI 的资源比 JExcel 还多。但是，两者中的任何一个都做得很好。
回答by Dilip Godhani
try {
String absoluteDiskPath = test.xls";
File f = new File(absoluteDiskPath);
response.setContentType("application/xlsx");
response.setHeader("Content-Disposition", "attachment; filename=" + absoluteDiskPath);
String name = f.getName().substring(f.getName().lastIndexOf("/") + 1, f.getName().length());
InputStream in = new FileInputStream(f);
out.clear(); //clear outputStream prevent illegalStateException write binary data to outputStream
ServletOutputStream outs = response.getOutputStream();
int bit = 256;
int i = 0;
try {
while ((bit) >= 0) {
bit = in.read();
outs.write(bit);
}
outs.flush();
outs.close();
in.close();
} catch (IOException ioe) {
ioe.printStackTrace();
} finally {
try {
if(outs != null)
outs.close();
if(in != null)
in.close();
}catch (Exception ioe2) {
ioe2.printStackTrace();
}
}
} catch (Exception ex) {
ex.printStackTrace();
}
