java 压缩导出多个excel
简单介绍下我实现的功能,首先是我的excel上传,它是以blob字段存储在oracel数据库中的,我实现的是循环遍历blob字段并使用io流进行打包下载,如有需要可自行修改
使用技术有,springMVC,mabits , bootstrap
1.前台
这里给出主要代码块
<div class="row"> <div class="col-md-12 "> <a href="tab-trainer-info.htm?status=1" class="btn btn-danger" title='#springMessage("back")'> <i class="glyphicon glyphicon-step-backward"></i></a> <a href="downloadPreHomeWorkZIP.htm?sessionId=$!sessionId" class="btn btn-info downloadPreZip" title='#springMessage("preWorkZip")'> <i class="glyphicon glyphicon-download-alt"></i> #springMessage("preWorkZip")</a> <a href="downloadAfterHomeWorkZIP.htm?sessionId=$!sessionId" class="btn btn-info downloadAfterZip" title='#springMessage("afterWorkZip")'> <i class="glyphicon glyphicon-download-alt"></i> #springMessage("afterWorkZip")</a> </div> </div>
注意这里点击按钮时用的超链接,不要用的ajax提交,ajax慎用,我自己一开始使用的ajax,最后无法下载,搞了半天,后来百度,说ajax下载无法调用浏览器的下载机制,如果有必要也可以使用(要用form封装,我这里不再具体介绍,有尝试的小伙伴,可以共享一下,哈哈)
2. Controller 读取数据
@RequestMapping(value = "downloadPreHomeWorkZIP") public void downloadLetterZIP(trainTraineeWorkModel query, HttpServletResponse response, HttpServletRequest request) throws IOException, SQLException { String serverPath = request.getSession().getServletContext().getRealPath("/") + "\\upload\\tempExcel"; //设置下载excel的临时文件夹 List<File> srcfile = new ArrayList<File>(); //声明一个集合,用来存放多个Excel文件路径及名称 // 得到路径下的文件,如果不存在创建文件夹 File file = new File(serverPath); if (!file.exists()) { file.mkdir(); } List<Map> employees = employeeService // 查询学员上传的课前课后作业 (这里根据自己的需要获取数据,写入流中) .getMapTraineesBySessionId(query.getSessionId()); if (employees.size() != 0) { for (Map map : employees) { BLOB blob = (BLOB) map.get("BEFORECLASS_WORK"); if (blob != null && blob.length() != 0) { String employeeCode = (String) map.get("EMPLOYEE_CODE"); String filename = employeeCode + "_" + DateUtil.getExportDate(); //定义现在excel文件名称,注意这里不是压缩包的名称 ZipUtils.execute(serverPath +"\\"+ filename+ ".xls", blob); //ZipUtils是我自己定义的一个工具类,因为用到的下载太多了,往下看 String encodedfileName = new String(filename.getBytes(), "UTF-8"); srcfile.add(new File(serverPath + "\\" + encodedfileName + ".xls")); //存放到List集合中 } } // 将服务器上存放Excel的文件夹打成zip包 File zipfile = new File(serverPath+"\\" + "PreWork" + ".zip"); ZipUtils.zipFiles(srcfile, zipfile);// 实现将多个excel打包成zip文件 //下载 ZipUtils.downFile(response, serverPath, "PreWork" + ".zip"); //实现将压缩包写入流中,下载到本地,并删除临时文件中的压缩包及文件 } }
3.使用工具类
这里是我第二步说的自己定义的工具类,我用的maven,需要导入一些Io流包
package common.util; import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.net.URLEncoder; import java.util.List; import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream; import javax.servlet.http.HttpServletResponse; import oracle.sql.BLOB; public class ZipUtils { /** * 将多个Excel打包成zip文件 * @param srcfile * @param zipfile */ public static void zipFiles(List<File> srcfile, File zipfile) { byte[] buf = new byte[1024]; try { // Create the ZIP file ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile)); // Compress the files for (int i = 0; i < srcfile.size(); i++) { File file = srcfile.get(i); FileInputStream in = new FileInputStream(file); // Add ZIP entry to output stream. out.putNextEntry(new ZipEntry(file.getName())); // Transfer bytes from the file to the ZIP file int len; while ((len = in.read(buf)) > 0) { out.write(buf, 0, len); } // Complete the entry out.closeEntry(); in.close(); } // Complete the ZIP file out.close(); } catch (IOException e) { e.printStackTrace(); } } //filename为单个excel的路径和excel的名称,blob就是获取的blob数据 public static int execute(String filename, BLOB blob) { int success = 1; try { File blobFile = new File(filename); FileOutputStream outStream = new FileOutputStream(blobFile); InputStream inStream = blob.getBinaryStream(); int length = -1; int size = blob.getBufferSize(); byte[] buffer = new byte[size]; while ((length = inStream.read(buffer)) != -1) { outStream.write(buffer, 0, length); outStream.flush(); } inStream.close(); outStream.close(); } catch (Exception e) { e.printStackTrace(); success = 0; } finally { return success; } } /** * 删除目录下所有的文件; * @param path */ public static boolean deleteExcelPath(File file){ String[] files = null; if(file != null){ files = file.list(); } if(file.isDirectory()){ for(int i =0;i<files.length;i++){ boolean bol = deleteExcelPath(new File(file,files[i])); if(bol){ System.out.println("删除成功!"); }else{ System.out.println("删除失败!"); } } } return file.delete(); } public static void downFile(HttpServletResponse response,String serverPath, String str) { //下面注释代码虽然少,但是慎用,如果使用,压缩包能下载,但是下载之后临时文件夹会被锁住被jvm占用,不能删除 // response.setCharacterEncoding("utf-8"); // try { // File file=new File(serverPath,str); // response.setHeader("Content-Disposition", // "attachment; filename="+ StringUtil.encodingFileName(str)); // response.setContentType("application/octet-stream; charset=utf-8"); // InputStream in1 =new FileInputStream(file.getPath()); // IOUtils.copy(in1, response.getOutputStream()); // // } // catch (IOException ex) { // ex.printStackTrace(); // } try { String path = serverPath +"\\"+ str; File file = new File(path); if (file.exists()) { InputStream ins = new FileInputStream(path); BufferedInputStream bins = new BufferedInputStream(ins);// 放到缓冲流里面 OutputStream outs = response.getOutputStream();// 获取文件输出IO流 BufferedOutputStream bouts = new BufferedOutputStream(outs); response.setContentType("application/ostet-stream");// 设置response内容的类型 response.setHeader( "Content-disposition", "attachment;filename=" + URLEncoder.encode(str, "UTF-8"));// 设置头部信息 int bytesRead = 0; byte[] buffer = new byte[8192]; //开始向网络传输文件流 while ((bytesRead = bins.read(buffer, 0, 8192)) != -1) { bouts.write(buffer, 0, bytesRead); } bouts.flush();// 这里一定要调用flush()方法 ins.close(); bins.close(); outs.close(); bouts.close(); } else { response.sendRedirect("../error.jsp"); } } catch (IOException e) { e.printStackTrace(); }finally{ File file1=new File(serverPath); deleteExcelPath(file1); //删除临时目录 } } }