FileExcelUtil.java 14.7 KB
Newer Older
kevin's avatar
#  
kevin committed
1 2 3 4 5 6 7 8 9
package pwc.taxtech.atms.common.util;

/**
 * @ClassName FileExcelUtil
 * Description TODO
 * @Author pwc kevin
 * @Date 3/31/2019 12:35 PM
 * Version 1.0
 **/
kevin's avatar
#  
kevin committed
10 11

import com.google.common.collect.Lists;
Memorydoc's avatar
#  
Memorydoc committed
12 13
import org.activiti.engine.ProcessEngine;
import org.activiti.engine.ProcessEngineConfiguration;
Memorydoc's avatar
#  
Memorydoc committed
14
import org.apache.poi.hssf.usermodel.HSSFCell;
Memorydoc's avatar
#  
Memorydoc committed
15 16 17
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.nutz.http.Http;
kevin's avatar
#  
kevin committed
18 19 20 21
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import sun.misc.BASE64Encoder;

Memorydoc's avatar
#  
Memorydoc committed
22
import javax.servlet.ServletContext;
kevin's avatar
#  
kevin committed
23
import javax.servlet.ServletOutputStream;
kevin's avatar
#  
kevin committed
24 25 26
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
Memorydoc's avatar
#  
Memorydoc committed
27
import java.math.BigDecimal;
kevin's avatar
#  
kevin committed
28
import java.net.URLEncoder;
kevin's avatar
#  
kevin committed
29
import java.util.List;
kevin's avatar
#  
kevin committed
30 31 32 33 34 35 36 37 38
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

public class FileExcelUtil {

    private static final Logger logger = LoggerFactory.getLogger(FileExcelUtil.class);

    /**
     * 编译下载的文件名
kevin's avatar
#  
kevin committed
39
     *
kevin's avatar
#  
kevin committed
40 41 42 43 44
     * @param filename
     * @param agent
     * @return
     * @throws IOException
     */
kevin's avatar
#  
kevin committed
45
    public static String encodeDownloadFilename(String filename, String agent) throws IOException {
Memorydoc's avatar
#  
Memorydoc committed
46
        if (agent != null && agent.contains("Firefox")) { // 火狐浏览器
kevin's avatar
#  
kevin committed
47 48 49 50 51 52
            filename = "=?UTF-8?B?"
                    + new BASE64Encoder().encode(filename.getBytes("utf-8"))
                    + "?=";
            filename = filename.replaceAll("\r\n", "");
        } else { // IE及其他浏览器
            filename = URLEncoder.encode(filename, "utf-8");
kevin's avatar
#  
kevin committed
53
            filename = filename.replace("+", " ");
kevin's avatar
#  
kevin committed
54 55 56 57 58 59
        }
        return filename;
    }

    /**
     * 创建文件夹;
kevin's avatar
#  
kevin committed
60
     *
kevin's avatar
#  
kevin committed
61 62
     * @param path
     */
kevin's avatar
#  
kevin committed
63
    public static File createFile(String path) {
kevin's avatar
#  
kevin committed
64 65 66 67 68 69
        File file = new File(path);
        //判断文件是否存在;
        if (!file.exists()) {
            //创建文件;
            file.mkdirs();
        }
kevin's avatar
#  
kevin committed
70
        return file;
kevin's avatar
#  
kevin committed
71 72 73 74
    }

    /**
     * 生成.zip文件;
kevin's avatar
#  
kevin committed
75
     *
kevin's avatar
#  
kevin committed
76 77 78
     * @param path
     * @throws IOException
     */
Memorydoc's avatar
#  
Memorydoc committed
79
    public static File createZip(String path) throws IOException {
kevin's avatar
#  
kevin committed
80
        ZipOutputStream zipOutputStream = null;
kevin's avatar
#  
kevin committed
81
        File file = new File(path + DateUtils.nowDateFormat() + ".zip");
kevin's avatar
#  
kevin committed
82 83 84 85 86
        zipOutputStream = new ZipOutputStream(new BufferedOutputStream(new FileOutputStream(file)));
        File[] files = new File(path).listFiles();
        FileInputStream fileInputStream = null;
        byte[] buf = new byte[1024];
        int len = 0;
kevin's avatar
#  
kevin committed
87 88
        if (files != null && files.length > 0) {
            for (File excelFile : files) {
kevin's avatar
#  
kevin committed
89 90 91
                String fileName = excelFile.getName();
                fileInputStream = new FileInputStream(excelFile);
                //放入压缩zip包中;
kevin's avatar
#  
kevin committed
92
                zipOutputStream.putNextEntry(new ZipEntry(path + "/" + fileName));
kevin's avatar
#  
kevin committed
93
                //读取文件;
kevin's avatar
#  
kevin committed
94
                while ((len = fileInputStream.read(buf)) > 0) {
kevin's avatar
#  
kevin committed
95 96 97 98
                    zipOutputStream.write(buf, 0, len);
                }
                //关闭;
                zipOutputStream.closeEntry();
kevin's avatar
#  
kevin committed
99
                if (fileInputStream != null) {
kevin's avatar
#  
kevin committed
100 101 102 103 104 105 106 107
                    fileInputStream.close();
                }
            }
        }

        /*if(zipOutputStream !=null){
            zipOutputStream.close();
        }*/
kevin's avatar
#  
kevin committed
108
        return file;
kevin's avatar
#  
kevin committed
109 110 111 112 113
    }


    /**
     * 设置下载excel的响应头信息
kevin's avatar
#  
kevin committed
114
     *
kevin's avatar
#  
kevin committed
115 116 117 118 119
     * @param response
     * @param request
     * @param fileName
     * @throws IOException
     */
kevin's avatar
#  
kevin committed
120
    public static void setExcelHeadInfo(HttpServletResponse response, HttpServletRequest request, String fileName) {
kevin's avatar
#  
kevin committed
121 122 123 124 125 126 127 128
        try {
            // 获取客户端浏览器的类型
            String agent = request.getHeader("User-Agent");
            // 对文件名重新编码
            String encodingFileName = FileExcelUtil.encodeDownloadFilename(fileName, agent);
            // 告诉客户端允许断点续传多线程连接下载
            response.setHeader("Accept-Ranges", "bytes");
            //文件后缀
kevin's avatar
#  
kevin committed
129
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
kevin's avatar
#  
kevin committed
130 131
            response.setHeader("Content-Disposition", "attachment; filename=" + encodingFileName);
        } catch (IOException e) {
kevin's avatar
#  
kevin committed
132
            logger.error(Thread.currentThread().getStackTrace()[1].getMethodName() + "发生的异常是: ", e);
kevin's avatar
#  
kevin committed
133 134 135 136 137 138
            throw new RuntimeException(e);
        }
    }

    /**
     * 设置下载zip的响应头信息
kevin's avatar
#  
kevin committed
139
     *
kevin's avatar
#  
kevin committed
140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157
     * @param response
     * @param fileName 文件名
     * @param request
     * @throws IOException
     */
    public static void setZipDownLoadHeadInfo(HttpServletResponse response, HttpServletRequest request, String fileName) throws IOException {
        // 获取客户端浏览器的类型
        String agent = request.getHeader("User-Agent");
        response.setContentType("application/octet-stream ");
        // 表示不能用浏览器直接打开
        response.setHeader("Connection", "close");
        // 告诉客户端允许断点续传多线程连接下载
        response.setHeader("Accept-Ranges", "bytes");
        // 对文件名重新编码
        String encodingFileName = FileExcelUtil.encodeDownloadFilename(fileName, agent);
        response.setHeader("Content-Disposition", "attachment; filename=" + encodingFileName);
    }

kevin's avatar
#  
kevin committed
158 159 160 161 162 163 164 165 166
    /**
     * 下载excel
     *
     * @param request
     * @param response
     * @param fileName
     * @param workbook
     * @time 2018年6月25日11:47:07
     */
Memorydoc's avatar
#  
Memorydoc committed
167
    public static void downloadExcel(HttpServletRequest request, HttpServletResponse response, String fileName, Workbook workbook) {
kevin's avatar
#  
kevin committed
168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189
        //一个流两个头
        //设置下载excel的头信息
        FileExcelUtil.setExcelHeadInfo(response, request, fileName);

        // 写出文件
        ServletOutputStream os = null;
        try {
            os = response.getOutputStream();
            workbook.write(os);
        } catch (IOException e) {
            logger.error(Thread.currentThread().getStackTrace()[1].getMethodName() + "发生的异常是: ", e);
            throw new RuntimeException(e);
        } finally {
            try {
                if (os != null) {
                    os.flush();
                    os.close();
                }
                if (workbook != null) {
                    workbook.close();
                }
            } catch (Exception e1) {
Memorydoc's avatar
#  
Memorydoc committed
190
                e1.printStackTrace();
kevin's avatar
#  
kevin committed
191 192 193 194 195 196 197 198 199 200 201
                logger.error(Thread.currentThread().getStackTrace()[1].getMethodName() + "发生的异常是: ", e1);
                throw new RuntimeException(e1);
            }
        }
    }

    /**
     * 生成excel到指定路径
     *
     * @param wb
     * @throws Exception
Memorydoc's avatar
#  
Memorydoc committed
202
     * @Param path 文件路径,包括zip文件夹路径
kevin's avatar
#  
kevin committed
203
     */
Memorydoc's avatar
#  
Memorydoc committed
204 205
    public static File generateExcelToPath(Workbook wb, String fileNamet) throws Exception {
        File file = new File(fileNamet);
kevin's avatar
#  
kevin committed
206 207
        FileOutputStream fos = null;
        try {
kevin's avatar
#  
kevin committed
208
            fos = new FileOutputStream(file);
kevin's avatar
#  
kevin committed
209
            wb.write(fos);
Memorydoc's avatar
#  
Memorydoc committed
210 211
            return file;
        } catch (Exception e) {
kevin's avatar
#  
kevin committed
212
            e.printStackTrace();
Memorydoc's avatar
#  
Memorydoc committed
213 214
            return null;
        } finally {
kevin's avatar
#  
kevin committed
215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231
            if (fos != null) {
                fos.flush();
                fos.close();
            }
            if (wb != null) {
                wb.close();
            }
        }
    }

    /**
     * 将批量文件打包下载成zip
     *
     * @param request
     * @param response
     * @param zipName  下载的zip名
     * @param files    要打包的批量文件
Memorydoc's avatar
#  
Memorydoc committed
232
     * @param zipDir   存放zip文件的文件夹路径
kevin's avatar
#  
kevin committed
233 234
     * @throws Exception
     */
Memorydoc's avatar
#  
Memorydoc committed
235 236 237 238
    public static synchronized void downloadZip(HttpServletRequest request, HttpServletResponse response, String zipName, List<File> files, String zipDir) throws Exception {
        //ZIPPATH = this.getClass().getResource("/").getPath().substring(1) + "zipDir";
        FileExcelUtil.createFile(zipDir);// 先生成存放zip文件的文件夹
        String zipPath = zipDir + "/" + Math.random() + ".zip";
kevin's avatar
#  
kevin committed
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
        File srcfile[] = new File[files.size()];
        File zip = new File(zipPath);
        for (int i = 0; i < files.size(); i++) {
            srcfile[i] = files.get(i);
        }
        FileInputStream inStream = null;
        ServletOutputStream os = null;
        try {
            //设置下载zip的头信息
            FileExcelUtil.setZipDownLoadHeadInfo(response, request, zipName);
            os = response.getOutputStream();
            FileExcelUtil.ZipFiles(srcfile, zip);
            inStream = new FileInputStream(zip);
            byte[] buf = new byte[4096];
            int readLength;
            while (((readLength = inStream.read(buf)) != -1)) {
                os.write(buf, 0, readLength);
            }
        } finally {
            if (inStream != null) {
                inStream.close();
            }
            if (os != null) {
                os.flush();
                os.close();
            }
            deleteDir(zip);
        }
    }

    /**
     * //压缩文件
     *
     * @param srcfile 要压缩的文件数组
     * @param zipfile 生成的zip文件对象
     */
    public static void ZipFiles(java.io.File[] srcfile, File zipfile) throws Exception {
        byte[] buf = new byte[1024];
        FileOutputStream fos = new FileOutputStream(zipfile);
        ZipOutputStream out = new ZipOutputStream(fos);
        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();
        fos.flush();
        fos.close();
    }

    /**
     * 删除文件夹及文件夹下所有文件
     *
     * @param dir
     * @return
     */
    public static boolean deleteDir(File dir) {
        if (dir == null || !dir.exists()) {
            return true;
        }
        if (dir.isDirectory()) {
            String[] children = dir.list();
            //递归删除目录中的子目录下
            for (int i = 0; i < children.length; i++) {
                boolean success = deleteDir(new File(dir, children[i]));
                if (!success) {
                    return false;
                }
            }
        }
        // 目录此时为空,可以删除
        return dir.delete();
    }
Memorydoc's avatar
#  
Memorydoc committed
317 318 319 320


    /**
     * 删除列
Memorydoc's avatar
#  
Memorydoc committed
321
     *
Memorydoc's avatar
#  
Memorydoc committed
322 323
     * @param sheet
     * @param columnToDelete
Memorydoc's avatar
#  
Memorydoc committed
324
     * @param cols           制定哪些行不进行列偏移
Memorydoc's avatar
#  
Memorydoc committed
325
     */
Memorydoc's avatar
#  
Memorydoc committed
326
    public static void deleteColumn(Sheet sheet, int columnToDelete, List<Integer> cols) {
Memorydoc's avatar
#  
Memorydoc committed
327 328
        for (int rId = 0; rId <= sheet.getLastRowNum(); rId++) {
            Row row = sheet.getRow(rId);
Memorydoc's avatar
#  
Memorydoc committed
329 330
            if(row == null)
                continue;
Memorydoc's avatar
#  
Memorydoc committed
331 332 333 334 335 336 337 338
            for (int cID = columnToDelete; cID <= row.getLastCellNum(); cID++) {
                Cell cOld = row.getCell(cID);
                if (cOld != null) {
                    row.removeCell(cOld);
                }
                Cell cNext = row.getCell(cID + 1);
                if (cNext != null) {
                    Cell cNew = row.createCell(cID, cNext.getCellTypeEnum());
Memorydoc's avatar
#  
Memorydoc committed
339
                    if (cols.contains(cID))
Memorydoc's avatar
#  
Memorydoc committed
340 341 342 343 344 345 346 347 348 349 350 351 352 353 354
                        continue;
                    cloneCell(cNew, cNext);
                    //Set the column width only on the first row.
                    //Other wise the second row will overwrite the original column width set previously.
                    if (rId == 0) {
                        sheet.setColumnWidth(cID, sheet.getColumnWidth(cID + 1));
                    }
                }
            }
        }
    }


    /**
     * 右边列左移
Memorydoc's avatar
#  
Memorydoc committed
355
     *
Memorydoc's avatar
#  
Memorydoc committed
356 357 358
     * @param cNew
     * @param cOld
     */
Memorydoc's avatar
#  
Memorydoc committed
359 360
    public static void cloneCell(Cell cNew, Cell cOld) {
        try {
Memorydoc's avatar
#  
Memorydoc committed
361 362
            if(cOld == null || cNew == null)
                return;;
Memorydoc's avatar
#  
Memorydoc committed
363 364 365 366 367 368 369
            cNew.setCellComment(cOld.getCellComment());
            cNew.setCellStyle(cOld.getCellStyle());
            try {
                String stringCellValue = cOld.getStringCellValue();
                if ("".equals(stringCellValue))
                    return;
            } catch (Exception e) {
Memorydoc's avatar
#  
Memorydoc committed
370
                //do nothing
Memorydoc's avatar
#  
Memorydoc committed
371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386
            }
            if (CellType.BOOLEAN == cNew.getCellTypeEnum() || CellType.BOOLEAN == cOld.getCellTypeEnum()) {
                cNew.setCellValue(cOld.getBooleanCellValue());
            } else if (CellType.NUMERIC == cNew.getCellTypeEnum() || CellType.NUMERIC == cOld.getCellTypeEnum()) {
                cNew.setCellValue(cOld.getNumericCellValue());
            } else if (CellType.STRING == cNew.getCellTypeEnum() || CellType.STRING == cOld.getCellTypeEnum()) {
                cNew.setCellValue(cOld.getStringCellValue());
            } else if (CellType.ERROR == cNew.getCellTypeEnum() || CellType.ERROR == cOld.getCellTypeEnum()) {
                cNew.setCellValue(cOld.getErrorCellValue());
            } else if (CellType.FORMULA == cNew.getCellTypeEnum() || CellType.FORMULA == cOld.getCellTypeEnum()) {
                cNew.setCellValue(cOld.getCellFormula());
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.warn("数据转换异常", e.getMessage());
        }
Memorydoc's avatar
#  
Memorydoc committed
387 388
    }

Memorydoc's avatar
#  
Memorydoc committed
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
    /**
     * @param cell 获取值的单元格
     * @param zero 是否将空 或者null转换成  zero
     * @return
     */
    public static Object getCellValue(Cell cell, Boolean zero) {
        String cellValue = null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING://字符串类型
                cellValue = cell.getStringCellValue();
                if (cellValue.trim().equals("") || cellValue.trim().length() <= 0)
                    cellValue = "";
                break;
            case Cell.CELL_TYPE_NUMERIC: //数值类型
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                cellValue = "";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                break;
            case Cell.CELL_TYPE_ERROR:
                break;
            default:
                break;
        }
Memorydoc's avatar
#  
Memorydoc committed
419
        if ("".equals(cellValue) && zero)
Memorydoc's avatar
#  
Memorydoc committed
420
            return BigDecimal.ZERO;
Memorydoc's avatar
#  
Memorydoc committed
421 422 423 424
        if (zero) {
            try {
                return new BigDecimal(cellValue);
            } catch (Exception e) {
Memorydoc's avatar
#  
Memorydoc committed
425
                logger.warn("获取Cell,在值转换成数字的地方出错");
Memorydoc's avatar
#  
Memorydoc committed
426
                return BigDecimal.ZERO;
Memorydoc's avatar
#  
Memorydoc committed
427 428 429 430 431 432
            }
        }
        return cellValue;
    }


kevin's avatar
#  
kevin committed
433
}