最近项目需求,要用java在后台导出数据表格excel,踩了一些坑,在这里记录一下。
1、要想在浏览器中显示下载框,前端请求不能用ajax方式请求,因为获取不到response流这里使用from 表单提交方式。
导出工具类ExcelUtils需要用到的jar包:
链接:https://pan.baidu.com/s/1Dq0o26W5fXLn6QftK67j3A
提取码:0o3n
在浏览器左下角显示效果图:
//导出excel
//Form 是 表单id
function excel() {
document.Form.action = "xfdetail/excelajax"; //设置请求路径
document.getElementById("Form").submit();//提交表单
}
2、后台controller
设置浏览器显示下载框
//设置文件名,boot.xls 是下载文件名
response.addHeader("Content-Disposition", "attachment;filename="+newString("boot.xls".getBytes()));
//设置输出流
OutputStream os= new BufferedOutputStream(response.getOutputStream());
//设置格式
response.setContentType("application/vnd.ms-excel;charset=gb2312");
controller方法
下载完之后返回null,因为response中存在文件流,会报错。
//导出exel测试
@RequestMapping(value = "/excelajax", produces = "text/plain;charset=UTF-8")
@ResponseBody
public Object saveajax(HttpServletResponse response) throws Exception {
try{
//存放信息的数组
HashMap<String,Object> paraHashMap = new HashMap<String, Object>();
HashMap<String,Object> paraHashMap1 = new HashMap<String, Object>();
HashMap<String,Object> paraHashMap2 = new HashMap<String, Object>();
ArrayList<HashMap<String,Object>> acountSelf_data1 = new ArrayList<HashMap<String,Object>>();
paraHashMap.put("buyer_name", "科技有限公司");
paraHashMap.put("start_date", "2016-11-11");
paraHashMap.put("end_date", "2016-11-11");
acountSelf_data1.add(paraHashMap);
paraHashMap1.put("buyer_name", "科技有限公司");
paraHashMap1.put("start_date", "2016-11-11");
paraHashMap1.put("end_date", "2016-11-11");
acountSelf_data1.add(paraHashMap1);
paraHashMap2.put("buyer_name", "有限公司");
paraHashMap2.put("start_date", "2016-11-11");
paraHashMap2.put("end_date", "2016-11-11");
acountSelf_data1.add(paraHashMap2);
//excel表头
String[] colNames = { "公司名称", "账期起始日期","账期停用日期"};
//表头对应的字段名称
String[] colParams = { "buyer_name", "start_date", "end_date"};
//设置浏览器显示下载框
response.addHeader("Content-Disposition", "attachment;filename="+ new String("boot.xls".getBytes()));
OutputStream os= new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=gb2312");
//25为一共几列,sheetname是sheet名称
ExcelUtils.setExcel(list,os,colNames,colParams,null,25);
}catch(Exception e){
logger.error(e.toString(), e);
}
return null;
}
3、导出表格工具类ExcelUtils
package com.fh.util;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletResponse;
import jxl.Sheet;
import jxl.Workbook;
import jxl.biff.DisplayFormat;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
* Excel处理工具类
*/
public class ExcelUtils extends HttpServlet {
private static SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
/**
* Excel模板下载
* @param filePath
* @param response
* @param fileName
* @return
* @throws Exception
*/
public static boolean downLoadFile(String filePath,HttpServletResponse response,
String fileName)throws Exception {