首先需要明确一件事情,页面中不同的头会有不同的效果(<meta>具体的头请度娘),下载框其实也是修改页面的头,让浏览器知道打开的是个什么样的效果(下载框还是普通文本等等)。那么实现方案就很明确了:无非就是客户点击某个button的时候,让服务器端代码告诉浏览器端——你需要打开一个下载框,然后把编辑好的内容,通过输出流,以某个具体的名称格式写到指定的位置。也就是说,摆明了需要用到response,接下来看实现代码:
ps:由于我们公司蛋疼的框架,这次只能用过滤器来实现
public class ExportExcelFilter implements IFilter{
private static final long serialVersionUID = 1L;
@Override
public void destroy() {
}
@Override
public boolean doFilter(ServletRequest _$req, ServletResponse _$res,
NFilterChain chain) {
HttpServletRequest request = (HttpServletRequest) _$req;
HttpServletResponse response = (HttpServletResponse) _$res;
String impl = RequestUtil.getVarStringValue(request, "impl");
AbsExprotService service;
try {
service = (AbsExprotService)Class.forName(impl).newInstance();
} catch (InstantiationException e1) {
service = null;
e1.printStackTrace();
} catch (IllegalAccessException e1) {
service = null;
e1.printStackTrace();
} catch (ClassNotFoundException e1) {
service = null;
e1.printStackTrace();
}
HSSFWorkbook wb = new HSSFWorkbook();
service.setWorkbook(wb);
Exporter exporter = service.exprot(request);
if(exporter.getList() == null){
String msg = " 没有可用于下载的数据。";
System.out.println(msg);
response.setContentType("text/html;charset=GBK");
PrintWriter out = null;
try {
out = response.getWriter();
} catch (IOException e) {
e.printStackTrace();
}
out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
out.println("<HTML>");
out.println(" <HEAD><TITLE>出错了</TITLE></HEAD>");
out.println(" <BODY>");
out.print(msg);
out.println(" </BODY>");
out.println("</HTML>");
out.flush();
out.close();
return true;
}
//-------------------------------------------------------------------
String fileName = exporter.getFileName();
fileName = (fileName == null || "".equals(fileName.trim())) ? "Excel文件.xls" : fileName.trim()+".xls";
//==========================================
String contentType = "application/vnd.ms-excel";//根据文件名取得输入类型
response.setContentType(contentType);
try {
fileName = new String(fileName.getBytes("GBK"),"ISO-8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setHeader("Content-Disposition", "inline; filename=" + fileName); // 文件名应该编码
OutputStream os_dest;
try {
os_dest = response.getOutputStream();
writeDatasToExcel(new Exporter[]{exporter}, os_dest,wb);
os_dest.flush();
os_dest.close();
} catch (IOException e) {
e.printStackTrace();
}
return true;
}
@Override
public void init(NFilterConfig config) {
}
/**
* 写一个结果集到excel的一个表格
* @param dto 要输出的结果集
* @param sheetName 工作表名称
* @param cols 要显示的字段
* @param wb
*/
private static void writeDataToExcel(List<JSONObject> list, String sheetName, Exporter exporter, HSSFWorkbook wb){
HSSFSheet sheet1 = wb.createSheet(sheetName);
/
int len_cols = 2;
String[] colNames = exporter.getColNames();// “列名”.getBytes().length*2*256
colNames = (colNames==null?new String[0]:colNames);
int lineHeight = exporter.getLineHeight();
lineHeight = (lineHeight==0?25:lineHeight);
int titleHeight = exporter.getTitleHeight();
titleHeight = (titleHeight==0?lineHeight:titleHeight);
///
//输出工作表表头
HSSFRow row = sheet1.createRow(0);
row.setHeight((short) (titleHeight * 20));
//if(exporter.getStyle(1, 0)!=null)row.setRowStyle(exporter.getStyle(1, 0));
for(int j = 0; j < len_cols; j++){
HSSFCellStyle style = exporter.getStyle(1, j+1);
if(exporter.getStyle(0, j+1)!=null)sheet1.setDefaultColumnStyle(j, exporter.getStyle(0, j+1));
if(style==null)style = exporter.getStyle(1, 0);
if(style==null){
style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
}
HSSFCell cell = row.createCell(j);
cell.setCellType(1);
cell.setCellStyle(style);
cell.setCellValue(colNames.length>j?colNames[j]:"");
}
//设置列的宽度
int[] colWidths = exporter.getColWidths();
colWidths = (colWidths==null?new int[0]:colWidths);
for(int j=0;j<len_cols;j++){
if(colWidths.length>j)sheet1.setColumnWidth(j, colWidths[j]*2*256);
else if(colWidths.length>0)sheet1.setColumnWidth(j, colWidths[0]*2*256);
}
HSSFRow row_body = sheet1.createRow(0);
HSSFCell cell = row_body.createCell(0);
cell.setCellType(1);
cell.setCellStyle(exporter.getStyle(1, 1) == null ? exporter.getStyle(1, 0) : exporter.getStyle(1, 1));
cell.setCellValue("账号");
cell = row_body.createCell(1);
cell.setCellType(1);
cell.setCellStyle(exporter.getStyle(1, 1) == null ? exporter.getStyle(1, 0) : exporter.getStyle(1, 1));
cell.setCellValue("密码");
//输出工作表内容
for(int i = 0; i < list.size(); i++){
row_body = sheet1.createRow(i+1);
// if(exporter.getStyle(i+1, 0)!=null){row_body.setRowStyle(exporter.getStyle(i+1, 0));}
row_body.setHeight((short) (lineHeight * 20));
HSSFCell cell1 = row_body.createCell(0);
HSSFCell cell2 = row_body.createCell(1);
cell1.setCellType(1);
cell2.setCellType(1);
HSSFCellStyle style = exporter.getStyle(i+1, 1);
if(style==null)style = exporter.getStyle(i+1, 0);
if(style!=null)cell1.setCellStyle(style);
cell1.setCellValue((String)list.get(i).get("user_code"));
cell2.setCellValue((String)list.get(i).get("user_pass"));
}
}
/**
* 写一批数据到excel
* @param exporter
* @param os_dest
* @return
*/
private static boolean writeDatasToExcel(Exporter[] exporter, OutputStream os_dest,HSSFWorkbook wb)
{
if(os_dest == null) return false;
try{
for(int i=0,len=exporter.length;i<len;i++){
List<JSONObject> list = exporter[i].getList();
String sheetName = exporter[i].getSheetName();
sheetName = (sheetName == null) ? "sheet" + i : sheetName;
writeDataToExcel(list, sheetName, exporter[i], wb);
}
wb.write(os_dest);
}
catch(Exception ex){
ex.printStackTrace();
return false;
}
return true;
}
}
这个是完整的代码,下面的poi代码都不需要看,因为它只是用来确定你导入到excel的具体样式的,这个根据需求具体来写,网上也有很多demo,不懂的话可以百度。
Exporter是一个特殊的对象,里面有需要遍历的数据的集合,
writeDatasToExcel(new Exporter[]{exporter}, os_dest,wb);
这里面的new Exporter[]{exporter}其实就是传入需要写入excel的数据集合。
重头戏在下面:
此代码中出现了三次response的方法调用,有两次是调用serContentType,这个方法其实就是设置头,返回告诉用户的浏览器,需要以什么方式打开。所以此方法里面第一个给的是
text/html;charset=GBK
因为这次只是返回一个报错的信息。
第二个是:
application/vnd.ms-excel
这个就是以下载excel的样式导出,有这个东西浏览器就知道给一个路径选择框,让用户选择路径
第三个是:
response.setHeader("Content-Disposition", "inline; filename=" + fileName);
这个是设置导出文件的默认名称。
最后再用response的输出流输出就OK了。