jxl生成excel并提供给网页弹出保存对话框下载

1、首先第一步是生成excel文件的方法

引入相关的包

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

//创建新的Excel文件
    public String createNewExcel(List<Map> emList,String type){
        
        Date date = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHssmm");
        WritableWorkbook   tempExcelFile = null;
        File myFile = new File(templatePath,type+"暂存文件"+format.format(date)+".xls");//EXCEL文件
        try {
            tempExcelFile =  Workbook.createWorkbook(myFile);
        }  catch (IOException e) {
            log.error("创建文件出错");
            e.printStackTrace();
        }
        WritableSheet sheet = tempExcelFile.createSheet(type, 0);//创建工作表sheet，sheet名为type，0表示第一个sheet
        if(type.equals("内部转账")){
            try {//第一行显示内容
                sheet.addCell(new Label(0,0,"付款单位账号"));
                sheet.addCell(new Label(1,0,"付款单位名称"));
                sheet.addCell(new Label(2,0,"收款单位账号"));
                sheet.addCell(new Label(3,0,"收款单位名称"));
                sheet.addCell(new Label(4,0,"币种"));
                sheet.addCell(new Label(5,0,"钞汇标志"));
                sheet.addCell(new Label(6,0,"账户属性"));
                sheet.addCell(new Label(7,0,"发生额"));
                sheet.addCell(new Label(8,0,"用途摘要"));
                sheet.addCell(new Label(9,0,"备注"));
                //接下来每行对应的单元格中显示内容
                for(int i=1;i<=emList.size();i++){
                    Map tmap = new HashMap();
                    tmap = emList.get(i-1);
                    sheet.addCell(new Label(0,i,tmap.get("A")+""));
                    sheet.addCell(new Label(1,i,tmap.get("B")+""));
                    sheet.addCell(new Label(2,i,tmap.get("C")+""));
                    sheet.addCell(new Label(3,i,tmap.get("D")+""));
                    sheet.addCell(new Label(4,i,tmap.get("E")+""));
                    sheet.addCell(new Label(0,i,tmap.get("F")+""));
                    sheet.addCell(new Label(6,i,tmap.get("G")+""));
                    sheet.addCell(new Label(7,i,tmap.get("H")+""));
                    sheet.addCell(new Label(8,i,tmap.get("I")+""));
                    sheet.addCell(new Label(9,i,tmap.get("J")+""));
                }
                try {
                    tempExcelFile.write();
                    tempExcelFile.close();
                } catch (IOException e) {
                    log.error("写入excel文件出错");
                    e.printStackTrace();
                }
            } catch (RowsExceededException e) {
                e.printStackTrace();
            } catch (WriteException e) {
                e.printStackTrace();
            }
        }
        return myFile.getName();//返回文件保存路径和名字
    }

调用该方法后，自动在保存路径 templatePath 下生成对应的excel文件

 

2、创建GetExcel.jsp文件下载excel，文件内容如下

<%
  java.io.BufferedInputStream bis=null;
  java.io.BufferedOutputStream  bos=null;
try{
 String filename=request.getParameter("fname");
 String dirPath=request.getParameter("fpath");
             filename=new String(filename.getBytes("iso8859-1"),"gb2312");
 response.setContentType("application/x-msdownload");
 
 response.setHeader("Content-disposition","attachment; filename="+new String(filename.getBytes("gb2312"),"iso8859-1"));
 bis =new java.io.BufferedInputStream(new java.io.FileInputStream(dirPath+filename));
 bos=new java.io.BufferedOutputStream(response.getOutputStream()); 
 byte[] buff = new byte[2048];
 int bytesRead;
 while(-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
  bos.write(buff,0,bytesRead);
 }
}
catch(Exception e){
 e.printStackTrace();
}
finally {
out.clear();
out = pageContext.pushBody();
 if (bis != null)bis.close();
 if (bos != null)bos.close();
}
%> 

 

3、在需要下载的页面上加入js

function download(){

　　location.href="GetExcel.jsp?fname="+"第一步生成的excel文件名&fpath="+"保存路径";

}

 

生成按钮，点击调用以上方法