从困难的Servlet发送Excel工作簿 [英] difficulty sending Excel Workbook from Servlet
问题描述
我的项目包括创建一个具有表的HTML页面，并把标有导出到Excel页面上的按钮。的整个目的是将表数据转换为可以从servlet被下载的Excel文件。使用jQuery我没有问题，从表中收集的所有数据，并使用以下code发送它关闭了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 code：
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 code看到的，我很容易就能在它创建一个工作簿和工作场所都通过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的狼/ 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
另外，在响应选项卡下的萤火虫我收到了一堆乱码：
Also in FireBug under the Response tab I get a bunch of garbage characters:
我想这是某种类型的错误。从jQuery阿贾克斯误差函数获取： 语法错误：JSON.parse：意外的字符的。我不知道那是什么???左右浏览器不提示我保存文件的任何地方。我试图在两个Firefox和IE浏览器，其结果是一样的。我试着改变的ContentType之间来回：的应用程序/ vnd.openxml 的和的应用程序/ 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.
推荐答案
我有真的老了codeBase的工作和公司的政策是如果这是不是破不解决它，所以这里的如何我们的servlet响应对于XLSX出口的样子。
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的应该只是滚吧。我相信mime类型和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屋！
