0%

Java导入，导出，多层文件夹压缩

发表于 分类于 阅读次数： 阅读次数： Valine：
本文字数： 48k 阅读时长 ≈ 44 分钟

写在前面：

如本文描述有错误，希望读到这篇文章的您能够提出批评指正。 联系方式：172310978@qq.com

参考文章：

  1. https://blog.csdn.net/qq_42207808/article/details/106927101

1. Zip工具类

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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
package com.pty.bip.common.util;

import java.io.*;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

public class ZipUtils {
    private static final int  BUFFER_SIZE = 2 * 1024;

        /**
         * 压缩成ZIP 方法1
         * @param srcDir 压缩文件夹路径
         * @param out    压缩文件输出流
         * @param KeepDirStructure  是否保留原来的目录结构,true:保留目录结构;
         *                          false:所有文件跑到压缩包根目录下(注意：不保留目录结构可能会出现同名文件,会压缩失败)
         * @throws RuntimeException 压缩失败会抛出运行时异常
         */
    public static void toZip(String srcDir, OutputStream out, boolean KeepDirStructure)
        throws RuntimeException{
        long start = System.currentTimeMillis();
        ZipOutputStream zos = null ;
        try {
            zos = new ZipOutputStream(out);
            File sourceFile = new File(srcDir);
            compress(sourceFile,zos,sourceFile.getName(),KeepDirStructure);
            long end = System.currentTimeMillis();
            System.out.println("压缩完成，耗时：" + (end - start) +" ms");
        } catch (Exception e) {
            throw new RuntimeException("zip error from ZipUtils",e);
        }finally{
            if(zos != null){
                try {
                    zos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }

        /**
         * 压缩成ZIP 方法2
         * @param srcFiles 需要压缩的文件列表
         * @param out           压缩文件输出流
         * @throws RuntimeException 压缩失败会抛出运行时异常
         */
    public static void toZipList(List<File> srcFiles , OutputStream out)throws RuntimeException {
        long start = System.currentTimeMillis();
        ZipOutputStream zos = null ;
        try {
            zos = new ZipOutputStream(out);
            for (File srcFile : srcFiles) {
                byte[] buf = new byte[BUFFER_SIZE];
                zos.putNextEntry(new ZipEntry(srcFile.getName()));
                int len;
                FileInputStream in = new FileInputStream(srcFile);
                while ((len = in.read(buf)) != -1){
                    zos.write(buf, 0, len);
                }
                zos.closeEntry();
                in.close();
            }
            long end = System.currentTimeMillis();
            System.out.println("压缩完成，耗时：" + (end - start) +" ms");
        } catch (Exception e) {
            throw new RuntimeException("zip error from ZipUtils",e);
        }finally{
            if(zos != null){
                try {
                    zos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


        /**
         * 递归压缩方法
         * @param sourceFile 源文件
         * @param zos        zip输出流
         * @param name       压缩后的名称
         * @param KeepDirStructure  是否保留原来的目录结构,true:保留目录结构;
         *                          false:所有文件跑到压缩包根目录下(注意：不保留目录结构可能会出现同名文件,会压缩失败)
         * @throws Exception
         */
    private static void compress(File sourceFile, ZipOutputStream zos, String name,
                                 boolean KeepDirStructure) throws Exception{
        byte[] buf = new byte[BUFFER_SIZE];
        if(sourceFile.isFile()){
            // 向zip输出流中添加一个zip实体，构造器中name为zip实体的文件的名字
            zos.putNextEntry(new ZipEntry(name));
            // copy文件到zip输出流中
            int len;
            FileInputStream in = new FileInputStream(sourceFile);
            while ((len = in.read(buf)) != -1){
                zos.write(buf, 0, len);
            }
            // Complete the entry
            zos.closeEntry();
            in.close();
        } else {
            File[] listFiles = sourceFile.listFiles();
            if(listFiles == null || listFiles.length == 0){
                // 需要保留原来的文件结构时,需要对空文件夹进行处理
                if(KeepDirStructure){
                    // 空文件夹的处理
                    zos.putNextEntry(new ZipEntry(name + "/"));
                    // 没有文件，不需要文件的copy
                    zos.closeEntry();
                }
            }else {
                for (File file : listFiles) {
                    // 判断是否需要保留原来的文件结构
                    if (KeepDirStructure) {
                        // 注意：file.getName()前面需要带上父文件夹的名字加一斜杠,
                        // 不然最后压缩包中就不能保留原来的文件结构,即：所有文件都跑到压缩包根目录下了
                        compress(file, zos, name + "/" + file.getName(),KeepDirStructure);
                    } else {
                        compress(file, zos, file.getName(),KeepDirStructure);
                    }

                }
            }
        }
    }
}

1.导入excel

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
 //表头下载
@GetMapping(path = "/excelExport/{id}")
@ApiOperation(value = "表头下载")
@SuppressWarnings("resource")
public void ExcelExport(@PathVariable("id") String id, HttpServletResponse response, HttpServletRequest request) {
    HashMap<String, Object> params = (HashMap) cacheManager.getCache(ehcacheExcelName).get(id).get();
    if (null == params) {
        try {
            response.getWriter().write("");
            return;
        } catch (IOException e) {
            log.error("查询导出excel参数异常:{}", e.getMessage());
        }
    }

    List list = (List<ExcelExportVo>) params.get("title");
    BipDepCheckRef bipDepCheckRef = new BipDepCheckRef();
    bipDepCheckRef.setKeyword(params.get("keyword").toString());
    bipDepCheckRef.setFiscal(params.get("fiscal").toString());
    checkRefService.excelExport(list,bipDepCheckRef,response, request);
}   

@PostMapping(path = "/uploadExcel")
@ApiOperation(value = "导入excle")
public Response uploadExcel(HttpServletRequest request, ExcelExportVo excelExportBean) {
    MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
    Iterator iterator = multiRequest.getFileNames();
    // 解析文件
    if (iterator.hasNext()) {
        String name = (String) iterator.next();
        MultipartFile file = multiRequest.getFile(name);
        CheckMsg check = checkRefService.uploadExcel(file, excelExportBean);
        if (check.isSuccess()) {
            return Response.success().setMsg(check.getMsgInfo());
        } else {
            return Response.fail().setMsg(check.getMsgInfo());
        }
    }
    return null;
}
1
2
//导入excel
    CheckMsg uploadExcel(MultipartFile file, ExcelExportVo excelExportBean);
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
97
98
99
100
101
102
103
104
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
 
    @Override
    @Transactional
    public CheckMsg uploadExcel(MultipartFile file, ExcelExportVo params) {
        try {
            JSONArray jsonArray = JSONArray.fromObject(params.getExcelExportVoList());
            List<ExcelExportVo> list2 = (List) JSONArray.toCollection(jsonArray, ExcelExportVo.class);
            File excel = null;
            if (file.equals("") || file.getSize() <= 0) {
                file = null;
            } else {
                InputStream ins = file.getInputStream();
                excel = new File(file.getOriginalFilename());
                BipBasicExpService.inputStreamToFile(ins, excel);
            }
            if (excel.isFile() && excel.exists()) { //判断文件是否存在

                String[] split = excel.getName().split("\\."); //.是特殊字符，需要转义！！！！！
                Workbook wb = null;
                //根据文件后缀（xls/xlsx）进行判断
                if ("xls".equals(split[1])) {
                    FileInputStream fis = new FileInputStream(excel); //文件流对象
                    wb = new HSSFWorkbook(fis);
                } else if ("xlsx".equals(split[1])) {
                    wb = new XSSFWorkbook(excel);
                } else {
                    return null;
                }

                //开始解析
                Sheet sheet = wb.getSheetAt(0); //读取sheet 0
                //读取第一行
                Row row1 = sheet.getRow(0);
                //获取总列数
                short lastCellNum = row1.getLastCellNum();
                //如果表头与导入excel列数不一致，则模板错误
                if (list2.size() != lastCellNum) {
                    return CheckMsg.fail("模板格式错误，请选择正确的模板重新导入");
                }
                //循环判断表头与excel列表头是否一致
                for (int i = 0; i < list2.size(); i++) {
                    Cell cell1 = row1.getCell(i);
                    if (!list2.get(i).getDesc().equals(String.valueOf(cell1))) {
                        return CheckMsg.fail("模板格式错误，请选择正确的模板重新导入");
                    }
                }
                int firstRowIndex = sheet.getFirstRowNum() + 1; //第一行是列名，所以不读
                int lastRowIndex = sheet.getLastRowNum();
                List<BipDepCheckRef> checkRefs = new ArrayList<>();
                //遍历行  就证明有 新增多少条数据
                for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
                    BipDepCheckRef checkRef = new BipDepCheckRef();
                    checkRef.setFiscal(params.getFiscal());
                    checkRef.setRefId(IDGenerator.UUID());
                    Row row = sheet.getRow(rIndex);
                    if (row != null) {
                        //  根据  遍历的 表头 跟  每列的值 进行  一会计划 赋值
                        for (int a = 0; a < list2.size(); a++) {
                            //遍历列
                            // cell 相当于每个 单元格的值。
                            Cell cell = row.getCell(a);

                            if (cell != null) {
                                cell.setCellType(CellType.STRING);
                                String stringCellValue = cell.getStringCellValue();
                                BeanUtils.setProperty(checkRef, list2.get(a).getValue(), stringCellValue);
                            }
                        }
                        File del = new File(excel.toURI());
                        del.delete();
                        checkRefs.add(checkRef);
                    }
                }
                if (checkRefs.size() > 0) {
                    BipDepCheckRef checkRef = new BipDepCheckRef();
                    checkRef.setFiscal(params.getFiscal());
                    depCheckRefDao.del(checkRef);
                    //分批批量插入，oracle10g只支持coulum*size<=1000
                    int insertLength = checkRefs.size();
                    int i = 0;
                    while (insertLength > 20) {
                        depCheckRefDao.insertBatch(checkRefs.subList(i, i + 20));
                        i = i + 20;
                        insertLength = insertLength - 20;
                    }
                    if (insertLength > 0) {
                        depCheckRefDao.insertBatch(checkRefs.subList(i, i + insertLength));
                    }
                    return CheckMsg.success("成功导入" + checkRefs.size() + "条");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return CheckMsg.fail("找不到指定的文件");
    }

2.导出excel

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
import org.springframework.cache.CacheManager;
import java.io.IOException;

@Autowired
private CacheManager cacheManager;


private final String ehcacheExcelName = "excel";


//缓存  表头数据
@ApiOperation(value = "缓存请求参数数据", notes = "缓存导出excel请求参数数据")
@PostMapping(value = "/excelParams")
public Response saveExcelParams(@RequestBody HashMap<String, Object> params) {
  String id = StringUtil.getUUID();
  cacheManager.getCache(ehcacheExcelName).put(id, params);
  return Response.success().setData(id);
}

//表头下载
@GetMapping(path = "/excelExport/{id}")
@ApiOperation(value = "表头下载")
@SuppressWarnings("resource")
public void ExcelExport(@PathVariable("id") String id, HttpServletResponse response, HttpServletRequest request) {
  HashMap<String, Object> params = (HashMap) cacheManager.getCache(ehcacheExcelName).get(id).get();
  if (null == params) {
    try {
      response.getWriter().write("");
      return;
    } catch (IOException e) {
      log.error("查询导出excel参数异常:{}", e.getMessage());
    }
  }

  List<ExcelExportVo> list = (List<ExcelExportVo>) params.get("title");
  List<BipSchePlan> value = (List<BipSchePlan>) params.get("value");

  String userCode = (String) params.get("userCode");
  String agyCode = (String) params.get("agyCode");
  Integer fiscal = (Integer) params.get("fiscal");
  Integer agyType = (Integer) params.get("agyType");
  String fundType =  (String)params.get("fundType");
  budSchePlanService.excelToExport(list, value, agyType,fundType, fiscal, response, request);
}
1
2
3
//导出excel
void excelToExport(List title, List value, Integer agyType,String fundType, Integer fiscal, HttpServletResponse response,
  HttpServletRequest request);
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
import java.util.*;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream; 

import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;

 
@Override
    public void excelToExport(List title, List value, Integer agyType,String fundType, Integer fiscal, HttpServletResponse response,
                            HttpServletRequest request) {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("导出excel");
            // 第一行
            HSSFRow row = sheet.createRow(0);
            HSSFRow row1 = sheet.createRow(1);
            HSSFRow row2 = sheet.createRow(2);
            HSSFRow row3 = sheet.createRow(3);
            HSSFRow row5 = sheet.createRow(4);
            //CellStyle style = CatalogExcelUtil.getHeadStyle(wb);
            String fundTypeName;
            if("1".equals(fundType)){
                fundTypeName="当年";
            }else {
                fundTypeName="上年";
            }
            HSSFFont font = wb.createFont();
            font.setFontHeightInPoints((short) 12);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
            font.setFontName("新宋体");
            font.setColor(HSSFColor.RED.index);
            font.setBoldweight((short) 10);

            HSSFCellStyle border = wb.createCellStyle();
            border.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            border.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            border.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            border.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
            //1.生成字体对象
            HSSFCellStyle cellFontStyle = wb.createCellStyle();
            cellFontStyle.setFont(font);

            HSSFCellStyle cellStyleRight = wb.createCellStyle();
            cellStyleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//right
            HSSFCellStyle setFont2 = wb.createCellStyle();
            HSSFFont font2 = wb.createFont();
            font2.setFontName("仿宋_GB2312");
            font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
            font2.setFontHeightInPoints((short) 18);

            HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
            cellStyle.setFont(font2);

            HSSFCellStyle setBorder = wb.createCellStyle();
            setBorder.setWrapText(true);//设置自动换行
            setBorder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
            setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, title.size() - 1));
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, title.size() - 1));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, title.size() - 1));
            sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, title.size() - 1));
            sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 5));
            sheet.addMergedRegion(new CellRangeAddress(4, 4, 30, 31));
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("1、绿色表格部分公式已设定，请勿随意改动。");
            cell.setCellStyle(cellFontStyle);

            HSSFCell cell1 = row1.createCell(0);
            cell1.setCellValue("2、请各单位按照表式认真填列，不得有空项。");
            cell1.setCellStyle(cellFontStyle);

            HSSFCell cell2 = row2.createCell(0);
            if("1".equals(fundType)){
                cell2.setCellValue("3、单位整体6月累计执行计划不低于45%；8月不低于66%；10月不低于83%；全年累计执行计划达到100%。");
            }
            else {
                cell2.setCellValue("");
            }
            cell2.setCellStyle(cellFontStyle);
            HSSFCell cell3 = row3.createCell(0);
            cell3.setCellValue("国家体育总局及直属事业单位（协会）" + fiscal + fundTypeName + "预算执行计划表（含彩票公益金）");
            cell3.setCellStyle(cellStyle);

            HSSFCell cell4 = row5.createCell(30);
            cell4.setCellValue("单位：万元");
            cell4.setCellStyle(cellStyleRight);
            sheet.setColumnWidth(0,10*256);
            sheet.setColumnWidth(1,30*256);
            sheet.setColumnWidth(2,22*256);
            sheet.setColumnWidth(3,20*256);
            sheet.setColumnWidth(4,8*256);
            sheet.setColumnWidth(5,20*256);
            sheet.setColumnWidth(6,20*256);
            HSSFRow rows = sheet.createRow(5);
            HSSFCellStyle canEditStyle = wb.createCellStyle();
            canEditStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            canEditStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            canEditStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            canEditStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
            canEditStyle.setLocked(true); //设置列的锁定状态为锁定

            HSSFCellStyle noCanEdit = wb.createCellStyle();
            noCanEdit.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            noCanEdit.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            noCanEdit.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            noCanEdit.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
            noCanEdit.setLocked(false); //设置列的锁定状态为锁定
            //得到前端数据值集
            List<Map<String, Object>> list = value;
            //得到单位预算码列
            List<Map> map =budSchePlanDao.selectMadAgys();
            //循环遍历两个结果集，进行匹配替换预算码
            if (list.size() > map.size()) {
                for (int i = 1; i < list.size(); i++) {
                    for(int y = 0; y < map.size(); y++){
                        if(list.get(i).get("agyCode").equals(map.get(y).get("agyName"))){
                            list.get(i).remove("agyCode");
                            list.get(i).put("agyCode",map.get(y).get("agyCode"));
                        }
                    }
                }
            }
            else{
                for (int i = 0; i < map.size(); i++) {
                    for(int y = 1; y < list.size(); y++){
                        if(list.get(y).get("agyCode").equals(map.get(i).get("agyName"))){
                            list.get(y).remove("agyCode");
                            list.get(y).put("agyCode",map.get(i).get("agyCode"));
                        }
                    }
                }
            }
            //条件匹配完毕进入过滤条件项
            List<Map<String, Object>> collect =list;
            collect.stream().forEach(col->{
                if("人员经费".equals(col.get("projectName")) || "公用经费".equals(col.get("projectName")) ){
                    col.remove("projectName");
                    col.put("projectName","");
                }
                if("99901".equals(col.get("projectCode")) || "99902".equals(col.get("projectCode")) ){
                    col.remove("projectCode");
                    col.put("projectCode","");
                }
            });
            int num = 6;
            for (int i = 0; i < collect.size(); i++) {
                HSSFRow row4 = sheet.createRow(num++);
                for (int a = 0; a < title.size(); a++) {
                    HSSFCell rowsCell = rows.createCell(a);
                    rowsCell.setCellStyle(setBorder);
                    rowsCell.setCellValue(((LinkedHashMap) title.get(a)).get("desc").toString());
                    LinkedHashMap object = (LinkedHashMap) title.get(a);
                    String object2 = object.get("value").toString();
                    HSSFCell createCell = row4.createCell(a);
                    if (a < 8) {
                        createCell.setCellStyle(canEditStyle);
                    }
                    if (a >= 8 && a < 33) {
                        createCell.setCellStyle(noCanEdit);
                    }
                    if(a==9 || a==11 || a==13 || a==15 || a==17 || a==19|| a==21 || a==23 || a==25 || a==27 || a==29 || a==31){
                        createCell.setCellValue((collect.get(i)).get(object2) == null ? ""
                                : (collect.get(i)).get(object2).toString().concat("%"));
                    }
                    else{
                        createCell.setCellValue((collect.get(i)).get(object2) == null ? ""
                                : (collect.get(i)).get(object2).toString());
                    }
                }
            }
            // 必须在单元格设值以后进行
            // 设置为根据内容自动调整列宽
/*            for (int k = 0; k < 5; k++) {
                sheet.autoSizeColumn(k);
            }*/
            sheet.protectSheet("123");
            String saveFileName = "";
            SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
            saveFileName += format.format(Calendar.getInstance().getTime())+fundTypeName+ "预算执行计划表.xls";
            response.reset();
            response.setContentType("application/octet-stream;charset=UTF-8");
            String userAgent = request.getHeader("user-agent");
            Boolean b = userAgent.indexOf("Edge") == -1
                    && (userAgent.indexOf("Firefox") >= 0 || userAgent.indexOf("Chrome") >= 0 || userAgent.indexOf("Safari") >= 0);
            if (b) {
                saveFileName = new String((saveFileName).getBytes(), "ISO8859-1");
            } else {
                //其他浏览器
                saveFileName = URLEncoder.encode(saveFileName, "UTF8");
            }
            response.setHeader("Content-Disposition", "attachment;filename=" + saveFileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
        }
    }

3.导出zip

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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
    @Override
    public void collectExcelExport(List title,String isAllAgy, BipSchePlan bip, Integer fiscal, HttpServletResponse response, HttpServletRequest request) {
        try {
            // 将excel导出的文件位置
            String filePath = "C:"+File.separator+"Users"+File.separator+"Public" + File.separator+"Downloads" + File.separator + "tyzj";
            //创建临时文件夹
            createFile(filePath);
            //压缩导出
            excelExport(title,filePath,isAllAgy,bip,fiscal,response,request);
            try {
/*                delAllFile(filePath); // 删除完里面所有内容
                filePath = filePath.toString();
                java.io.File myFilePath = new java.io.File(filePath);
                myFilePath.delete(); // 删除空文件夹
                System.out.println("缓存文件删除完毕");*/
                delFolder(filePath);
                System.out.println("缓存文件删除完毕");
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
        }

    }
 /**
     * 导出
     */
    private void excelExport(List title,String filePath,String isAllAgy,
                              BipSchePlan bip, Integer fiscal, HttpServletResponse response, HttpServletRequest request) throws IOException {
            //年度
            String finalFundTypeName = "";
            if("1".equals(bip.getFundType())){
                finalFundTypeName="当年";
            }
            if("0".equals(bip.getFundType())){
               finalFundTypeName="上年";
             }
            //生成excel
            List<String> fileNames =createExcel(title,isAllAgy,fiscal,bip);
            //导出压缩文件的全路径
            List<BipSchePlan> exportAgyName = new ArrayList<>();
            if(!StringUtils.isEmpty(bip.getFiscal()) && !StringUtils.isEmpty(bip.getAgyCode())){
                exportAgyName=budSchePlanDao.selExportAgy(bip);
            }
            //zip处理
            String saveFileZipName = "";
            SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
            saveFileZipName += format.format(Calendar.getInstance().getTime()) +exportAgyName.get(0).getAgyName()+finalFundTypeName+"预算执行计划表"+ ".zip";
            String zipFilePath = filePath+File.separator+saveFileZipName;
            //导出zip
            File zip = new File(zipFilePath);
            //将excel文件生成压缩文件
            File srcfile[] = new File[fileNames.size()];
            for (int j = 0, n1 = fileNames.size(); j < n1; j++) {
                srcfile[j] = new File(fileNames.get(j));
            }
            ZipFiles(srcfile, zip);
            System.out.println("压缩文件生成目录："+zipFilePath);
            System.out.println("压缩文件生成完毕");
            String url = "";
            url = URLEncoder.encode(zip.getName(), "UTF8");
            response.setContentType("application/zip");
            response.setHeader("Location", url);
            response.setHeader("Content-Disposition", "attachment; filename=" + url);
            OutputStream outputStream = response.getOutputStream();
            InputStream inputStream = new FileInputStream(zipFilePath);
            byte[] buffer = new byte[1024];
            int i = -1;
            while ((i = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, i);
            }
            outputStream.flush();
            outputStream.close();
            inputStream.close();
            System.out.println("导出完毕");
    }
/**
     * 生成excel
     */
    private List<String> createExcel(List title,String isAllAgy,Integer fiscal,BipSchePlan bip){
        // 用于存放生成的excel文件名称
        List<String> fileNames = new ArrayList<String>();
        if(StringUtils.isEmpty(bip.getFundType()) || bip.getFundType()==""){
            bip.setFundType("1");
            List<String> lastExcel = createExcelByFiscal(title,isAllAgy,fiscal,bip);
            bip.setFundType("0");
            List<String> excel = createExcelByFiscal(title,isAllAgy,fiscal,bip);
            fileNames.addAll(lastExcel);
            fileNames.addAll(excel);
            return fileNames;
        }
        else{
            List<String> excel = createExcelByFiscal(title,isAllAgy,fiscal,bip);
            fileNames.addAll(excel);
            return fileNames;
        }
    }
 /**
     * 分年度createExcel
     */
    private List<String> createExcelByFiscal(List title,String isAllAgy,Integer fiscal,BipSchePlan bip){
        //输入流
        InputStream in = null;
        //输出流
        FileOutputStream o = null;
        // 用于存放生成的excel文件名称
        List<String> fileNames = new ArrayList<String>();
        // 导出Excel文件路径
        String fullFilePath = "";
        //年度
        String fundTypeName = "";
        if("1".equals(bip.getFundType())){
            fundTypeName="当年";
        }
        if("0".equals(bip.getFundType())){
            fundTypeName="上年";
        }
        //如果是单位的就导出单位的数据
        List<BipSchePlan> bips =budSchePlanDao.selectExcel(bip);
        if(bips.size()>0){
            bips.stream().forEach(b->{
                if("人员经费".equals(b.getProjectName()) || "公用经费".equals(b.getProjectName())){
                    b.setProjectName("");
                }
                if("99901".equals(b.getProjectCode()) || "99902".equals(b.getProjectCode())){
                    b.setProjectCode("");
                }
            });
            JSONArray json = JSONArray.fromObject(bips);
            List<Map<String, Object>> list = json;
            //单位的
            List<Map<String, Object>> collectDanWei = list.stream().filter(ee -> StringUtils.isEmpty(ee.get("departmentCode"))).collect(Collectors.toList());
            //协会
            Map<String, List<Map<String, Object>>> listValue = list.stream().filter(ee -> !StringUtils.isEmpty(ee.get("departmentCode")))
                    .collect(Collectors.groupingBy(e -> e.get("departmentCode").toString()));
            //总体
            listValue.put("agy", collectDanWei);
            //年度判断
            String finalFundTypeName = fundTypeName;
            listValue.forEach((agy, col) -> {
                FileOutputStream os = null;
                try {
                    HSSFWorkbook wb = new HSSFWorkbook();
                    HSSFSheet sheet = wb.createSheet("导出excel");
                    // 第一行
                    HSSFRow row = sheet.createRow(0);
                    HSSFRow row1 = sheet.createRow(1);
                    HSSFRow row2 = sheet.createRow(2);
                    CellStyle style = CatalogExcelUtil.getHeadStyle(wb);
                    HSSFFont font = wb.createFont();
                    font.setFontHeightInPoints((short) 12);
                    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
                    font.setFontName("新宋体");
                    font.setColor(HSSFColor.RED.index);
                    font.setBoldweight((short) 10);
                    //1.生成字体对象
                    HSSFCellStyle cellFontStyle = wb.createCellStyle();
                    cellFontStyle.setFont(font);

                    HSSFCellStyle setFont2 = wb.createCellStyle();
                    HSSFFont font2 = wb.createFont();
                    font2.setFontName("仿宋_GB2312");
                    font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
                    font2.setFontHeightInPoints((short) 18);

                    HSSFFont font3 = wb.createFont();
                    font3.setFontName("仿宋_GB2312");
                    font3.setFontHeightInPoints((short) 18);

                    HSSFCellStyle cellStyleRight = wb.createCellStyle();
                    cellStyleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//right
                    cellStyleRight.setFont(font2);

                    HSSFCellStyle cellStyleRight_wy = wb.createCellStyle();
                    cellStyleRight_wy.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//right

                    HSSFCellStyle cellStyleLeft = wb.createCellStyle();
                    cellStyleLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);//left
                    cellStyleLeft.setFont(font2);

                    HSSFCellStyle cellStyleLeft_fj = wb.createCellStyle();
                    cellStyleLeft_fj.setAlignment(HSSFCellStyle.ALIGN_LEFT);//left
                    cellStyleLeft_fj.setFont(font3);

                    HSSFCellStyle cellStyleLeft_hj = wb.createCellStyle();
                    cellStyleLeft_hj.setAlignment(HSSFCellStyle.ALIGN_LEFT);//left

                    HSSFCellStyle cellStyleLeft_hj_align = wb.createCellStyle();
                    cellStyleLeft_hj_align.setAlignment(HSSFCellStyle.ALIGN_LEFT);//left
                    cellStyleLeft_hj_align.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
                    cellStyleLeft_hj_align.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                    cellStyleLeft_hj_align.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                    cellStyleLeft_hj_align.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

                    HSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
                    cellStyle.setFont(font2);

                    HSSFCellStyle setBorder = wb.createCellStyle();
                    setBorder.setWrapText(true);//设置自动换行
                    setBorder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
                    setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
                    setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                    setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                    setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

                    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, title.size() - 1));
                    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, title.size() - 1));
                    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 5));
                    sheet.addMergedRegion(new CellRangeAddress(2, 2, 30, 31));
                    HSSFCell cell = row.createCell(0);
                    cell.setCellValue("附件");
                    cell.setCellStyle(cellStyleLeft_fj);
                    HSSFCell cell3 = row1.createCell(0);
                    cell3.setCellValue("国家体育总局及直属事业单位（协会）" + fiscal + finalFundTypeName + "预算执行计划表（含彩票公益金）");
                    cell3.setCellStyle(cellStyle);
                    String cell4Name = "";
                    if (StringUtils.isEmpty(col.get(0).get("departmentCode"))) {
                        cell4Name = col.get(0).get("agyName").toString();
                    } else {
                        cell4Name = col.get(0).get("departmentName").toString();
                    }
                    HSSFCell cell4 = row2.createCell(0);
                    cell4.setCellValue("单位：" + cell4Name);
                    cell4.setCellStyle(cellStyleLeft);
                    HSSFCell cell4_last = row2.createCell(30);
                    cell4_last.setCellValue("单位：万元");
                    cell4_last.setCellStyle(cellStyleRight_wy);
                    sheet.setColumnWidth(0,10*256);
                    sheet.setColumnWidth(1,30*256);
                    sheet.setColumnWidth(2,22*256);
                    sheet.setColumnWidth(3,20*256);
                    sheet.setColumnWidth(4,8*256);
                    sheet.setColumnWidth(5,20*256);
                    sheet.setColumnWidth(6,20*256);
                    HSSFRow rows = sheet.createRow(3);
                    HSSFCellStyle canEditStyle = wb.createCellStyle();
                    canEditStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
                    canEditStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                    canEditStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                    canEditStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                    canEditStyle.setLocked(true); //设置列的锁定状态为锁定
                    HSSFCellStyle sumCellStyle = wb.createCellStyle();
                    sumCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
                    sumCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                    sumCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                    sumCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                    //创建行4
                    HSSFRow sumAmtRow = sheet.createRow(4);
                    sumAmtRow.setRowStyle(cellStyleLeft_hj);
                    HSSFCell sumAmtCell5 = sumAmtRow.createCell(6);
                    sumAmtCell5.setCellValue("合计");
                    sumAmtCell5.setCellStyle(cellStyleLeft_hj_align);
                    //新建集合用来map转bean放入List计算合计
                    List<BipSchePlan> lsToBean = new ArrayList<>();
                    col.forEach(k->{
                                BipSchePlan b=new BipSchePlan();
                                transMap2Bean2(k,b);
                                lsToBean.add(b);
                            }
                    );
                    //合计方法
                    BipSchePlan sumBip = sumMonthSchedule(lsToBean);
                    //设定
                    sumPlanAmt(sumBip,sumAmtRow,sumCellStyle);
                    HSSFCellStyle noCanEdit = wb.createCellStyle();
                    noCanEdit.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
                    noCanEdit.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                    noCanEdit.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                    noCanEdit.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                    noCanEdit.setLocked(false); //设置列的锁定状态为锁定
                    int num = 5;
                    for (int i = 0; i < col.size(); i++) {
                        HSSFRow row4 = sheet.createRow(num++);
                        for (int a = 0; a < title.size(); a++) {
                            HSSFCell rowsCell = rows.createCell(a);
                            rowsCell.setCellStyle(setBorder);
                            rowsCell.setCellValue(((LinkedHashMap) title.get(a)).get("desc").toString());
                            LinkedHashMap object = (LinkedHashMap) title.get(a);
                            String object2 = object.get("value").toString();
                            HSSFCell createCell = row4.createCell(a);
                            if (a < 8) {
                                createCell.setCellStyle(canEditStyle);
                            }
                            if (a >= 8 && a < 32) {
                                createCell.setCellStyle(noCanEdit);
                            }
                            //合计添加百分号
                            if(a==9 || a==11 || a==13 || a==15 || a==17 || a==19|| a==21 || a==23 || a==25 || a==27 || a==29 || a==31){
                                createCell.setCellValue((col.get(i)).get(object2) == null ? "0%"
                                        : (col.get(i)).get(object2).toString().concat("%"));
                            }else{
                                createCell.setCellValue((col.get(i)).get(object2) == null ? ""
                                        : (col.get(i)).get(object2).toString());
                            }

                        }
                    }
                    //设置边框
                    for (int i = 0; i <= 5; i++) {
                        sumAmtRow.createCell(i).setCellStyle(sumCellStyle);
                    }
                    // 必须在单元格设值以后进行
                    // 设置为根据内容自动调整列宽
/*                    for (int k = 0; k < 5; k++) {
                        sheet.autoSizeColumn(k);
                    }*/
                    sheet.protectSheet("123");
                    String saveFileName = "";
                    SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
                    saveFileName += format.format(Calendar.getInstance().getTime()) + "预算执行批复表.xls";
                    // 导出excel的全路径
                    String excName = "";
                    if (StringUtils.isEmpty(col.get(0).get("departmentCode"))) {
                        excName = col.get(0).get("agyName").toString();
                    } else {
                        excName = col.get(0).get("departmentName").toString();
                    }
                    String fullFilePaths ="";
                    if("all".equals(isAllAgy)){
                        fullFilePaths="C:"+File.separator+"Users"+File.separator+"Public" + File.separator+"Downloads" +File.separator +"预算执行计划表(所有单位)"+File.separator+ bip.getAgyName() + File.separator + excName +finalFundTypeName+ saveFileName;
                    }else {
                        fullFilePaths="C:"+File.separator+"Users"+File.separator+"Public" + File.separator+"Downloads" +File.separator +"tyzj"+File.separator+ excName +finalFundTypeName+ saveFileName;
                    }
                    fileNames.add(fullFilePaths);
                    os = new FileOutputStream(fullFilePaths);
                    // 写文件
                    wb.write(os);
                    //清空流缓冲区数据
                    os.flush();
                    //关闭流
                    os.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            });
            return fileNames;
        }
       else {
           return fileNames;
        }
    }
    /**
     * 创建文件夹
     */
    private void createFile(String path){
        File fileDir = new File(path);
        System.out.println(path);
        //创建文件夹
        if (!fileDir.exists() && !fileDir.isDirectory()) {
            fileDir.mkdirs();
        }
    }
        //压缩文件
    public void ZipFiles(File[] srcfile, File zipfile) {
        byte[] buf = new byte[1024];
        try {
            ZipOutputStream out = new ZipOutputStream(new FileOutputStream(
                    zipfile));
            for (int i = 0; i < srcfile.length; i++) {
                FileInputStream in = new FileInputStream(srcfile[i]);
                out.putNextEntry(new ZipEntry(srcfile[i].getName()));
                int len;
                while ((len = in.read(buf)) > 0) {
                    out.write(buf, 0, len);
                }
                out.closeEntry();
                in.close();
            }
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /***
     * 删除指定文件夹下所有文件
     *
     * @param path 文件夹完整绝对路径
     * @return
     */
    public static boolean delAllFile(String path) {
        boolean flag = false;
        File file = new File(path);
        if (!file.exists()) {
            return flag;
        }
        if (!file.isDirectory()) {
            return flag;
        }
        String[] tempList = file.list();
        File temp = null;
        for (int i = 0; i < tempList.length; i++) {
            if (path.endsWith(File.separator)) {
                temp = new File(path + tempList[i]);
            } else {
                temp = new File(path + File.separator + tempList[i]);
            }
            if (temp.isFile()) {
                temp.delete();
            }
            if (temp.isDirectory()) {
                delAllFile(path + "/" + tempList[i]);// 先删除文件夹里面的文件
                delFolder(path + "/" + tempList[i]);// 再删除空文件夹
                flag = true;
            }
        }
        return flag;
    }

    /***
     * 删除文件夹
     *
     * @param folderPath 文件夹完整绝对路径
     */
    public  static void delFolder(String folderPath) {
        try {
            delAllFile(folderPath); // 删除完里面所有内容
            String filePath = folderPath;
            filePath = filePath.toString();
            java.io.File myFilePath = new java.io.File(filePath);
            myFilePath.delete(); // 删除空文件夹
        } catch (Exception e) {
            e.printStackTrace();
        }
    }