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