java 下载 excel

 

比较好的做法：

response.setContentType("application/-excel");  
response.addHeader("Content-Disposition", new String(("filename=" + filename).getBytes("GBK"), "ISO-8859-1"));
OutputStream os = response.getOutputStream(); 
workbook.write(os);
os.flush();
os.close();

 

容易内存溢出做法：

ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook.write(os);
InputStream is = new ByteArrayInputStream(os.toByteArray());
os.flush();
os.close();
return is;

打开excel时发生错误：excel 在 {fileName} 中发现不可读的内容

我写错的写法

@RequestMapping("/download")
	public void download(HttpServletRequest request,
			HttpServletResponse response, String type) throws IOException {
		String fileName = "";
		InputStream is = null;
		if ("03".equals(type)) {
			fileName = "03模板.xls";
			is = request.getSession().getServletContext()
					.getResourceAsStream("/template/sheetImport/03模板.xls");
		} else if ("07".equals(type)) {
			fileName = "07模板.xlsx";
			is = request.getSession().getServletContext()
					.getResourceAsStream("/template/sheetImport/07模板.xlsx");
		}

		response.setContentType("application/vnd.ms-excel");
		response.addHeader("Content-Disposition", new String(
				("filename=" + fileName).getBytes("GBK"), "ISO-8859-1"));
		BufferedOutputStream bos = new BufferedOutputStream(
				response.getOutputStream());
		BufferedInputStream bis = new BufferedInputStream(is);
		byte[] b = new byte[1024];
		while (bis.read(b) != -1) {
			bos.write(b);
		}
		bis.close();
		bos.flush();
		bos.close();
	}

 

 

正确的写法

@RequestMapping("/download")
	public void download(HttpServletRequest request,
			HttpServletResponse response, String type) throws IOException {
		String fileName = "";
		InputStream is = null;
		if ("03".equals(type)) {
			fileName = "03模板.xls";
			is = request.getSession().getServletContext()
					.getResourceAsStream("/template/sheetImport/03模板.xls");
		} else if ("07".equals(type)) {
			fileName = "07模板.xlsx";
			is = request.getSession().getServletContext()
					.getResourceAsStream("/template/sheetImport/07模板.xlsx");
		}

		response.setContentType("application/vnd.ms-excel");
		response.addHeader("Content-Disposition", new String(
				("filename=" + fileName).getBytes("GBK"), "ISO-8859-1"));
		BufferedOutputStream bos = new BufferedOutputStream(
				response.getOutputStream());
		BufferedInputStream bis = new BufferedInputStream(is);
		byte[] b = new byte[1024];
		int i = 0;
		while ((i = bis.read(b)) != -1) {
			bos.write(b, 0, i);
		}
		bis.close();
		bos.flush();
		bos.close();
	}

错误分析

当最后一次读取数据的时候byte数组的长度不足1024，而bos的write方法
 public void write(byte b[]) throws IOException {
	write(b, 0, b.length);
    }
自动取了byte的1024个长度，假如最后一次读取的长度为500，则下载时会把多余的524个空byte下载到excel中，导致excel错误！

 

最后建议把 application/vnd.ms-excel 换成 application/x-download， 否则ie会把文件当成excel2003处理，下载2007的时候就会报错了！

多份excel压缩成zip导出

// 工程引入 apache ant.jar Import org.apache.tools.zip.* 处理jdk默认zip处理出现压缩文件中文乱码
@RequestMapping("/export")
public void export(HttpServletResponse response) throws Exception {
	response.setContentType("application/-excel");  
	response.addHeader("Content-Disposition", new String(("filename=附件.zip").getBytes("GBK"), "ISO-8859-1"));
	OutputStream os = response.getOutputStream(); 

	InputStream in1 = '模板1';
	InputStream in2 = '模板2';
	HSSFWorkbook workbook1 = new HSSFWorkbook(in1);
	// workbook1 处理业务
	HSSFWorkbook workbook2 = new HSSFWorkbook(in2);
	// workbook2 处理业务
	
	ZipOutputStream zip = new ZipOutputStream(os);
	zip.putNextEntry(new ZipEntry("详细.xls"));
	workbook1.write(zip);
	
	zip.putNextEntry(new ZipEntry("统计.xls"))；
	workbook2.write(zip);
	
	zip.setEncoding("GBK");
	zip.flush();
	zip.close();
	os.flush();
	os.close();
	in.close();
}