黑马程序员技术交流社区

标题: 【上海校区】poi根据模板导出多个excel文件并压缩层.zip [打印本页]

作者: 不二晨 时间: 2019-3-22 09:18
标题: 【上海校区】poi根据模板导出多个excel文件并压缩层.zip
/**
   * poi导出多个excel文件并压缩层.zip
   * 参数 startnum 开始编号
   * endnum 结束编号
   * 通过输入的编号范围查询出需要导出的数据
   * */
@RequestMapping(value = "/exportFixByMonths", method = RequestMethod.GET)
public void exportFixByMonths(ActivitiesBean bean,HttpServletResponse response,HttpServletRequest request,
@RequestParam String startnum,@RequestParam String endnum) throws IOException {
int STARTNUM= Integer.parseInt(startnum);
int ENDNUM=Integer.parseInt(endnum);
List<File> srcfile = new ArrayList<File>();  //声明一个集合，用来存放多个Excel文件路径及名称
for(int i=STARTNUM;i<=ENDNUM;i++){
bean.setCOMMITTEE(i);
// 文件模板路径
String rootpath = ((HttpServletRequest) request).getSession().getServletContext().getRealPath("/muban");
File file=new File(rootpath + File.separator + "tongjis.xlsx");
// 新的文件名
String filename="测试表" + new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date()) + ".xlsx";
File newFile = ExcelUtil.createNewFile(filename,file);
// 新文件写入数据，并下载*****************************************************
InputStream is = null;
XSSFWorkbook workbook = null;
//HSSFWorkbook workbook=null;
XSSFSheet sheet = null;
try {
is = new FileInputStream(newFile);// 将excel文件转为输入流
workbook = new XSSFWorkbook(is);// 创建个workbook，
//workbook = new HSSFWorkbook(is);// 创建个workbook，
// 获取第一个sheet
sheet = workbook.getSheetAt(0);
} catch (Exception e1) {
e1.printStackTrace();
}
if (sheet != null) {
try {
// 写数据
FileOutputStream fos = new FileOutputStream(newFile);
XSSFRow row = sheet.getRow(1);
if (row == null) {
row = sheet.createRow(1);
}
XSSFCell cell = row.getCell(0);
if (cell == null) {
cell = row.createCell(0);
}

//根据id查询数据
TestBean testBean = activitiesService.queryceshi(bean);
if(testBean!=null && !"".equals(testBean)){
//姓名
String committeename=testBean.getCOMMITTEENAME()!= null? testBean.getCOMMITTEENAME()+" ":" ";
//
String cppccsession=testBean.getCPPCCSESSION()!= null? testBean.getCPPCCSESSION()+" ":" ";
//职位
String positions=testBean.getPOSITIONS()!= null? testBean.getPOSITIONS()+" ":" ";

cell = sheet.getRow(1).getCell(0);
cell.setCellValue(committeename);
cell = sheet.getRow(1).getCell(1);
cell.setCellValue(cppccsession);
cell = sheet.getRow(1).getCell(2);
cell.setCellValue(i);
cell = sheet.getRow(2).getCell(3);
cell.setCellValue(positions);
}

workbook.write(fos);
fos.flush();
fos.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != is) {
is.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
srcfile.add(newFile);
}
}
//指定磁盘目录
String strpath=SysParamInit.zipPath+File.separator;//例如D://file
//指定.zip格式和名称
String pathname=new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date())  + ".zip";
File zipfile = new File(strpath+pathname);
//压缩多个excel文件为.zip格式并删除
ExcelUtil.zipFiles(srcfile,zipfile);
ExcelUtil.deleteFiles(srcfile);
//下载.zip格式文件并删除
ExcelUtil.downFile(response,strpath,pathname);
ExcelUtil.deleteZip(zipfile);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
/**
   * 将多个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();
      }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
/**
   * 删除多个文件方法
   *
   * @param srcfile
   */
public static void deleteFiles(List<File> srcfile) {
for (File file : srcfile) {
if (file.exists()) {
file.delete();
}
}
}
1
2
3
4
5
6
7
8
9
10
11
12
public static void downFile(HttpServletResponse response,String serverPath, String str) {
      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/x-download");// 设置response内容的类型
            response.setHeader(
                     "Content-disposition",
                     "attachment;filename="
                              + URLEncoder.encode(str, "GBK"));// 设置头部信息
            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();
      }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/**
   * 删除zip
   */
public static void deleteZip(File path) {
if (path.exists()) {
path.delete();
}
}

1
2
3
4
5
6
7
8
9

---------------------
【转载，仅作分享，侵删】
作者：小志的博客
原文：https://blog.csdn.net/li1325169021/article/details/85334003
版权声明：本文为博主原创文章，转载请附上博文链接！

作者: 不二晨 时间: 2019-3-25 17:13

奈斯，感谢分享

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2