下载服务端数据到本地保存为Excel

来源：互联网发布：淘宝天猫苏宁易购编辑：程序博客网时间：2024/05/18 11:28

引言

在项目中，往往会需要将查询出的数据导出成excel或者其他的文件形式，便于用户查看。因此就有了这篇博文，介绍如何下载服务器的数据并以excel的形式保存到本地。

整体思路

查询出目标数据，并生成对应的文件格式的文件。在本项目就是以Excel格式存储查询出的数据。
文件可以保存在服务器上，也可以直接以文件流的形式写入到Response的输出流中。本文分成两步，先保存到服务器，然后写入响应的输出流。建议直接写入Response，先写成文件再输出在并发时会导致阻塞。
设置响应头Header，在浏览器访问时，弹出打开/下载弹窗。

代码实现

ajax查询数据并将数据以Excel形式保存到服务器

//项目采用struts框架，因此分两步走public ActionForward exportData(ActionMapping mapping,        ActionForm form, HttpServletRequest request,        HttpServletResponse response) throws IOException {    int fileId = Integer.parseInt(request.getParameter("id"));    Software software = (Software)softwareDao.getById(Software.class, fileId);    //将文件名中的空格去除        String excelName = FileTools.formatFileName(software.getName());    List<Map<String, Object>> successInfo = recordDao.getSuccessInfo(fileId);    List<Map<String, Object>> failedInfo = recordDao.getFailedInfo(fileId);    String successKey = "成功记录";    String failedKey = "失败记录";    //待导出的数据        Map<String, List<Map<String, Object>>> data = new HashMap<>();    data.put(successKey, successInfo);    data.put(failedKey, failedInfo);    String serverPath = request.getSession().getServletContext().getRealPath("/");    String excelPath = serverPath+"download/"+excelName+".xls";    JSONObject object = new JSONObject();    try {            //使用poi导出数据        ExcelUtil.exportData(excelPath, data, successKey,failedKey);        //加密文件路径            String cryptPath = AESCrpyt.encryptString(excelPath, AESCrpyt.DEFAULT_KEY);        object.put("code", 0);        object.put("msg", cryptPath);    } catch(IndexOutOfBoundsException e){        object.put("code", -1);        object.put("msg", "没有相关升级记录，导出失败");    } catch (Exception e) {        log.error("导出失败");        log.error(e);        object.put("code", -1);        object.put("msg", "导出失败");    }    response.setCharacterEncoding(ProductConfig.CHARSET);    try {        response.getWriter().write(object.toString());        response.getWriter().flush();    } catch (IOException e) {        // TODO Auto-generated catch block        e.printStackTrace();        log.error("信息输出失败");    }       return null;}

/**    * 输出数据到EXCEL    * @param excelPath      输出Excel路径    * @param data           输出数据    * @param excelNames Excel文件中的多个表    * @throws Exception 导出时异常    */public static void exportData(String excelPath,        Map<String, List<Map<String, Object>>>data,        String...excelNames) throws Exception{    //检查数据中表的表名和参数表名是否一致    checkData(data, excelNames);    //检查导出路径    validatePath(excelPath);    HSSFWorkbook wb = new HSSFWorkbook();    for (String excelName : excelNames) {        List<Map<String, Object>> list = data.get(excelName);        HSSFSheet sheet = wb.createSheet(excelName);          HSSFRow row = sheet.createRow((int) 0);          HSSFCellStyle style = wb.createCellStyle();          style.setAlignment(HorizontalAlignment.CENTER);        HSSFCell cell;        Map<String, Object> item = list.get(0);        Set<String> keys = item.keySet();        Iterator<String> i = keys.iterator();        int num = 0;        while (i.hasNext()) {            String firstLine = i.next();            cell = row.createCell((short)num);            cell.setCellValue(firstLine);            cell.setCellStyle(style);              num++;        }        for (int j = 0; j < list.size(); j++)          {             row = sheet.createRow((int) j + 1);              Map<String, Object> itemMap = list.get(j);            Set<String> keySet = itemMap.keySet();            Iterator<String> k = keySet.iterator();            int rowNum = 0;            while (k.hasNext()) {                String firstLine = k.next();                cell = row.createCell((short)rowNum);                cell.setCellValue(itemMap.get(firstLine)==null?"":itemMap.get(firstLine).toString());                 rowNum++;            }        }    }        FileOutputStream fout = new FileOutputStream(excelPath);         wb.write(fout);         fout.close();       fout = null;}

//AES加密字符串public class AESCrpyt {public static final String DEFAULT_KEY = "defaultkey4crypt";private static String TYPE = "AES";private static int KeySizeAES128 = 16;private static int BUFFER_SIZE = 8192;public static final String VIPARA = "0102030405060708";private static Cipher getCipher(String key, int mode) throws Exception, InvalidAlgorithmParameterException {    // mode =Cipher.DECRYPT_MODE or Cipher.ENCRYPT_MODE    Cipher mCipher;    byte[] keyPtr = new byte[KeySizeAES128];    IvParameterSpec IvParameterSpecivParam = new IvParameterSpec(VIPARA.getBytes());    byte[] passPtr = key.getBytes();    mCipher = Cipher.getInstance(TYPE + "/CBC/PKCS5Padding");    for (int i = 0; i < KeySizeAES128; i++) {        if (i < passPtr.length)            keyPtr[i] = passPtr[i];        else            keyPtr[i] = 0;    }    SecretKeySpec keySpec = new SecretKeySpec(keyPtr, TYPE);    mCipher.init(mode, keySpec, IvParameterSpecivParam);    return mCipher;} public static String encryptString(String targetString,String encryptKey)throws Exception{    String result="";    Cipher enCipher = getCipher(encryptKey, Cipher.ENCRYPT_MODE);    if(enCipher==null){        throw new Exception("encrypt init failed");    }       result =parseByte2HexStr(enCipher.doFinal(targetString.getBytes()));    return result;}public static String decryptString(String encryptString,String encryptKey) throws         Exception{    String result=null;    Cipher enCipher = getCipher(encryptKey, Cipher.DECRYPT_MODE);    if(enCipher==null){        throw new Exception("decrypt init failed");    }    result = new String(enCipher.doFinal(parseHexStr2Byte(encryptString)));    return result;}private static String parseByte2HexStr(byte buf[]) {         StringBuffer sb = new StringBuffer();         for (int i = 0; i < buf.length; i++) {                 String hex = Integer.toHexString(buf[i] & 0xFF);                 if (hex.length() == 1) {                         hex = '0' + hex;                 }                 sb.append(hex.toUpperCase());         }         return sb.toString();  } private static byte[] parseHexStr2Byte(String hexStr) {         if (hexStr.length() < 1)                 return null;         byte[] result = new byte[hexStr.length()/2];         for (int i = 0;i< hexStr.length()/2; i++) {                 int high = Integer.parseInt(hexStr.substring(i*2, i*2+1), 16);                 int low = Integer.parseInt(hexStr.substring(i*2+1, i*2+2), 16);                 result[i] = (byte) (high * 16 + low);         }         return result;  } }

//ajax查询数据，如果查询成功并生成excel，则转到servlet进行下载。$("i").bind("click",function(){                var id = $(this).parent().siblings(":first").text();                $.ajax({                    type:"POST",                    url:"software.do",                    dataType:"json",                    data:{                            method:"exportData",                            id:id                            },                    success:function(data)                        {                            var code = data.code;                            if(code==0){                                var path = data.msg;                                //此处加密过的路径可能会超出url规定的最大长度                                window.location.href='ExportExcel?path='+path;                             } else {                                layer.alert(data.msg);                            }                        }                });            });

转到一个新的Servlet:ExportExcel 将Excel文件写入到Response输出流。

package com.bydota.product.servlet;import java.io.BufferedOutputStream;import java.io.FileInputStream;import java.io.IOException;import java.io.OutputStream;import java.net.URLEncoder;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.servlet.http.HttpUtils;import com.bydota.product.tools.AESCrpyt;import com.bydota.product.tools.FileTools;/*** Servlet implementation class ExportExcel*/public class ExportExcel extends HttpServlet {private static final long serialVersionUID = 1L;   /**    * @see HttpServlet#HttpServlet()    */   public ExportExcel() {       super();       // TODO Auto-generated constructor stub   }/** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {    // TODO Auto-generated method stub    String cryptPath = request.getParameter("path");    String excelPath = "";    try {        excelPath = AESCrpyt.decryptString(cryptPath, AESCrpyt.DEFAULT_KEY);    } catch (Exception e) {        // TODO Auto-generated catch block        e.printStackTrace();    }    //截取文件名    String excelName = FileTools.getFileName(excelPath);    response.reset();      //下载文件格式    response.setHeader("Content-Type", "application/vnd.ms-excel");    //防止下载时出现的中文乱码    //即filename*=charset'lang'value。charset则是给浏览器指明以什么编码方式来还原中文文件名。 value为编码后的元数据       String ua = request.getHeader("User-Agent");    System.out.println(ua);    //兼容问题    if(ua.contains("MSIE")){        response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(excelName,"UTF-8"));     } else {        response.setHeader("Content-Disposition","attachment;filename*=UTF-8''"+ URLEncoder.encode(excelName,"UTF-8"));//指定下载的文件名      }       //此处必须设置无缓存，否则IE中会出问题       response.setHeader("Pragma", "no-cache");         response.setHeader("Cache-Control", "no-cache");         response.setDateHeader("Expires", 0);         OutputStream output = response.getOutputStream();         BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);         bufferedOutPut.flush();         FileInputStream in = new FileInputStream(excelPath);       byte[] b = new byte[1024];       int length = 0;       while((length = in.read(b))!=-1){        bufferedOutPut.write(b, 0, length);       }       in.close();       bufferedOutPut.close(); }/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {    // TODO Auto-generated method stub    doGet(request, response);}}

注意

响应头Header最好采用filename*=charset’lang’value，以解决中文乱码问题。
针对火狐，filename不能有空格，否则会出现下载文件不带后缀的情况。
有关POI和可用的POI资源可以参考我的另一篇文章。因为项目中是封装好对map数据的处理，如果你想自己输出Bean数据，那就要你重新封装。

参考文章

HTTP协议header中Content-Disposition中文文件名乱码
POI导出EXCEL
可用的POI资源
并发时文件读写

阅读全文

0 0