难以从 Servlet 发送 Excel 工作簿 [英] difficulty sending Excel Workbook from Servlet
问题描述
我的项目涉及创建一个带有表格的 HTML 页面,并在该页面上放置一个标记为导出到 Excel"的按钮.整个目的是将表数据转换为可以从 servlet 下载的 Excel 文件.使用 JQuery,我从表中收集所有数据并使用以下代码将其发送到 Servlet 没有问题:
My project involves creating an HTML page that has a Table and placing a button on the page labelled "Export To Excel". The whole purpose is to convert the Table data into an Excel file that can be downloaded from the servlet. Using JQuery I had no problem collecting all the data from the Table and sending it off to the Servlet using the following code:
$("#export").click(function(){
var head = JSON.stringify({ header: header });
var table = JSON.stringify({ data: data });
//Combine the two into on big object
var obj = head.substring(0,head.length - 1) + "," + table.substring(1,table.length);
$.ajax({
type: "POST",
url: 'ExportToExcel',
data: obj,
dataType: "json",
contentType: "application/json; charset=utf-8",
mimeType: 'application/json',
error: function (xhr, ajaxOptions, thrownError) {
$('#result').html(thrownError + "<br/><br/>" +
xhr.responseText);
}
})
});
这是我的 Servlet 代码:
Here's my Servlet code:
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try{
BufferedReader br = new BufferedReader(new InputStreamReader(request.getInputStream()));
String json = "";
if(br != null){
json = br.readLine();
}
//System.out.println("json: " + json);
br.close();
Gson gson = new Gson();
Table table = gson.fromJson(json, Table.class);
ArrayList<String> header = table.getHeader();
ArrayList<String[]> data = table.getData();
XSSFWorkbook wb = new XSSFWorkbook();
//CreationHelper createHelper = wb.getCreationHelper();
XSSFSheet sheet = wb.createSheet("Sheet1");
//create the Header
XSSFRow rowhead = sheet.createRow(0);
for(int i = 0; i < header.size(); i++)
{
rowhead.createCell(i).setCellValue(header.get(i));
}
XSSFRow row = null;
XSSFCell cell = null;
String[] temp = null;
for(int i = 0; i < data.size(); i++)
{
temp = data.get(i);
row = sheet.createRow(i);
for(int j = 0; j < temp.length; j++)
{
cell = row.createCell(j);
cell.setCellType(XSSFCell.CELL_TYPE_STRING );
cell.setCellValue(temp[j]);
}
}
//response.setContentType("application/vnd.ms-excel");
response.setContentType("application/vnd.openxml");
//response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
String filename = "data.xlsx";
response.setHeader("Content-disposition", "attachment; filename=\"" + filename + "\"" );
ServletOutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
catch(Exception e){e.printStackTrace();}
}
class Table
{
private ArrayList<String> header;
private ArrayList<String[]> data;
public void setHeader(ArrayList<String> list)
{
header = list;
}
public ArrayList<String> getHeader()
{
return header;
}
public void setData(ArrayList<String[]> value)
{
data = value;
}
public ArrayList<String[]> getData()
{
return data;
}
}
正如您从 Servlet 代码中看到的,我可以轻松地创建一个工作簿并将通过 JSON 字符串发送的所有数据放入其中.到现在为止还挺好.
As you can see from the Servlet code, I am easily able to create a Workbook and place in it all the data sent via JSON string. So far so good.
对于从 Servlet 返回的响应,我在 FireBug 中得到以下信息:
For a response back from the Servlet I get the following in FireBug:
响应标头
内容处置附件;文件名="data.xlsx"
内容类型应用程序/vnd.openxml
日期 2014 年 3 月 3 日星期一 20:56:15 GMT
服务器 Apache-Coyote/1.1
传输编码分块
Response Headers
Content-Disposition attachment; filename="data.xlsx"
Content-Type application/vnd.openxml
Date Mon, 03 Mar 2014 20:56:15 GMT
Server Apache-Coyote/1.1
Transfer-Encoding chunked
也在 FireBug 的响应"选项卡下,我得到了一堆垃圾字符:
Also in FireBug under the Response tab I get a bunch of garbage characters:
我认为这是某种错误.从 JQuery Ajax 错误函数我得到:SyntaxError: JSON.parse: 意外字符.我不知道那是什么???浏览器不会提示我将文件保存在任何地方.我在 Firefox 和 IE 浏览器中都尝试过,结果是一样的.我试过在 application/vnd.openxml 和 application/vnd.ms-excel,但结果还是一样.
I assume this is an error of some sort. From the JQuery Ajax error function I get: SyntaxError: JSON.parse: unexpected character. I don't know what that's about??? The browser doesn't prompt me to save the file anywhere. I tried in both Firefox and IE browsers, and the result is the same. I've tried changing the ContentType back and forth between: application/vnd.openxml and application/vnd.ms-excel, but again the result is the same.
有人能告诉我哪里出错了吗?我希望浏览器提示我要将文件放在哪里.谢谢.
Is someone able to show me where I am going wrong? I want the browser to prompt me as to where I want to put the file. Thanks.
推荐答案
我使用的是一个非常旧的代码库,公司的政策是如果它没有坏就不要修复它",所以这里是我们对 XLSX 导出的 servlet 响应的方式看起来.
I work with a really old codebase and the company's policy is "If it aint broke don't fix it", so here's how our servlet response for XLSX Export looks.
response.setHeader("Expires", "0");
response.setHeader("Content-disposition", "attachment;filename=" + exportTitle + "_" + fileDate + ".xlsx"); // I noticed you had "/" before and after the filename, try removing that, and add the extension.
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); // Our legacy code actually has "application/xlsx" and it works fine - but [other answers indicate better](https://stackoverflow.com/questions/974079/setting-mime-type-for-excel-document)
...对于你的 AJAX,我会尝试删除 dataType
规范,因为你的响应头定义了它,jQuery 应该滚动"它.我相信 mimeType 和 contentType 用于定义发送到服务器的内容(您似乎已经表明它工作正常),所以我不会过多关注这一点.
... For your AJAX I would try removing dataType
specification, since your response header defines it, jQuery should just "roll" with it. I believe the mimeType and contentType are used to define what's sent to the server (which you've seemed to have indicated is working fine), so I wouldn't focus too much on that.
这篇关于难以从 Servlet 发送 Excel 工作簿的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!