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

引言

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

整体思路

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

代码实现

1. 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);
                               }
                           }
                   });
               });

2. 转到一个新的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);
   }
   
   }
   

注意

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

参考文章

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