Java常用excel导出实现

最近在做很多报表相关的开发，包括使用shell和hive将大数据汇总，然后页面做展示，还有将结果导出excel发送给相关负责人，由于导出表格的时候，有很多场景，现在针对几种常用场景，进行一些整理，方便以后使用。

1.少量数据，使用alibaba.excel.EasyExcel

ExcelUtils.java：

@Slf4j
public class ExcelUtils {

    /**
     * Excel模板导出
     *
     * @param templateInputStream
     * @param outer
     * @param sheetName
     * @param clazz
     * @param datas
     * @param writeHandler
     */
    public static void templateWrite(InputStream templateInputStream,
                                     File outer,
                                     String sheetName,
                                     Class clazz,
                                     List datas,
                                     WriteHandler writeHandler) {
        EasyExcel.write(outer, clazz)
                .needHead(false)
                .inMemory(true)
                .registerWriteHandler(writeHandler)
                .withTemplate(templateInputStream)
                .sheet(sheetName)
                .doWrite(datas);
    }

    /**
     * 导出
     *
     * @param response           response
     * @param data               数据
     * @param includeColumnNames 需要导出的列名
     * @param clazz
     * @param fileName
     * @param writeHandler
     * @throws IOException
     */
    public static void export(HttpServletResponse response,
                              List data,
                              List<String> includeColumnNames,
                              Class clazz,
                              String fileName,
                              WriteHandler... writeHandler) throws IOException {

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.write(response.getOutputStream(), clazz)
                .inMemory(true)
                .includeColumnFiledNames(includeColumnNames)
                .sheet("模板");
        for (WriteHandler handler : writeHandler) {
            excelWriterSheetBuilder.registerWriteHandler(handler);
        }
        excelWriterSheetBuilder.doWrite(data);
    }

使用样例：

    public void export(XXXXReq req, HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException, IOException {
        //定义导出的字段
        List<String> includeColumnNames = Arrays.asList
                ("aaa","bbb","ccc","ddd","eee","......");
        //定义导出文件名
        String fileName = URLEncoder.encode("XXX分析明细报表导出" +  LocalDateTime.now().toString().substring(0,10)).replaceAll("\\+", "%20");
        //List类型的数据
        List<XXXXXResp> data = XXXXDetail.getData();
        ExcelUtils.export(response,data.getList(),includeColumnNames,XXXXXResp.class,fileName);
    }

2.大数据量导出使用CSV

public void export(HttpServletRequest request, HttpServletResponse response) throws IOException, InterruptedException {
        String startTime = request.getParameter("beginDate");
        String endTime = request.getParameter("endDate");
        long start = System.currentTimeMillis();
        //定义导出的字段
        List<String> titles = Arrays.asList
                ("aaa","bbb","ccc","ddd","eee","fff","....");
        List<String> titleStrs = Arrays.asList
                ("列名1","列名2","列名3","列名4","列名5","列名6","......");
        List<XXXXXVO> vo= XXXXXMapper.getAllDetail(startTime, endTime);
        String fileName = "XXXX明细导出-";
        try {
            OutputStream os = response.getOutputStream();
            this.responseSetProperties(fileName, response);
            this.doExport(vo, titleStrs,os,titles);
            os.close();
            long millis = System.currentTimeMillis() - start;
            long second = millis / 1000;
            log.info("导出数据成功:数量为--->" + vo.size()+",花费时长："+second + "s");
        }catch (Exception e){
            log.error("导出数据失败 ,error:{}",e.getMessage());
        }

    }



/**
     * 设置Header
     *
     * @param fileName
     * @param response
     * @throws UnsupportedEncodingException
     */
    public void responseSetProperties(String fileName, HttpServletResponse response)
            throws UnsupportedEncodingException {
        // 设置文件后缀
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String fn = fileName + sdf.format(new Date()) + ".csv";
        // 读取字符编码
        String utf = "utf-8";

        // 设置响应
        response.setContentType("application/ms-txt.numberformat:@");
        response.setCharacterEncoding(utf);
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "max-age=30");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fn, utf));
    }


/**
     * CSV文件列分隔符
     */
    private static final String CSV_COLUMN_SEPARATOR = ",";

    /**
     * CSV文件行分隔符
     */
    private static final String CSV_ROW_SEPARATOR = "\r\n";

    /**
     * @param dataList
     *            集合数据
     * @param titles
     *            表头部数据
     *
     * @param os
     *            输出流
     */
    public void doExport(List<XXXXXVO> dataList, List<String> titleStr, OutputStream os,List<String> titles)
            throws Exception {

        // 保证线程安全
        StringBuffer buf = new StringBuffer();

        // 组装表头
        for (String title : titleStr) {
            buf.append(title).append(CSV_COLUMN_SEPARATOR);
        }
        buf.append(CSV_ROW_SEPARATOR);

        // 组装数据
        if (CollectionUtils.isNotEmpty(dataList)) {
            for (XXXXXVO data : dataList) {
                //转成jsonString
                String jsonList ="{"xxxx":"xxxx"......}";
                //需要注意，导出实体类需要每个属性都是String类型，否则需要转换
                Map<String,String> map = JSONObject.parseObject(jsonList,Map.class);
                for (int i = 0; i < titles.size(); i++) {
                    if (map.containsKey(titles.get(i))){
                        buf.append("null".equals(map.get(titles.get(i))) ? "-" : map.get(titles.get(i))+"").append(CSV_COLUMN_SEPARATOR);
                    }
                }
                buf.append(CSV_ROW_SEPARATOR);
            }
        }

        // 写出响应
        os.write(buf.toString().getBytes("GBK"));
        os.flush();
    }

3.大数据量，使用EasyExcel，多线程分多个文件，合并压缩包导出

    //文件定义相关
    private static  final String SRC_DIR = "app/tempexcel/src/";
    private static  final String DELETE_DIR = "app/tempexcel/src/";
    private static  final String XLSX_SUFFIX = ".xlsx";
    private static  final String FILE_PREFIX = "XXX明细导出文件_";
    private static  final String ZIP_SUFFIX = ".zip";

 public void doExportDetail(HttpServletResponse response ,String startTime, String endTime,List<String> includeColumnNames) throws InterruptedException, FileNotFoundException {
        long start = System.currentTimeMillis();
        String fileDir = SRC_DIR;
        FileUtils.creatFileDirs(fileDir);
        //统计条数,每个表格5w条
        int count = XXXXXMapper.countDetail(startTime,endTime)/50000;
        CountDownLatch cdl = new CountDownLatch(count);
        //xlsx文件时间格式化
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddmmssSSS");
        //zip文件时间格式化
        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMdd");
        for (int i = 0; i <= count; i++) {
            int j = i+1;
            //多线程导出文件
            String fileName =  fileDir + FILE_PREFIX + sdf.format(new Date()) + Math.random() +"-" + j  + XLSX_SUFFIX;
            executorService.execute(() -> {
                        List<XXXXXVO> vo = XXXXXMapper.findDetail(startTime,endTime);
                        log.info("开始导出文件：第" + j + "个文件----" + fileName + "size :" + dataList.size());
                        try {
                            ExcelUtils.export(vo, includeColumnNames, fileName);
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                        cdl.countDown();
                    }
            );

        }
        cdl.await();
        //将文件夹压缩
        FileUtils.toZip(SRC_DIR,new FileOutputStream(new File(SRC_DIR + FILE_PREFIX + sdf1.format(new Date()) + ZIP_SUFFIX)),false);
        //将压缩文件响应给浏览器
        FileUtils.sendZip(response,new File(SRC_DIR + FILE_PREFIX + sdf1.format(new Date()) + ZIP_SUFFIX));
        //删除文件夹
        boolean isDelete = FileUtils.deleteDir(DELETE_DIR);
        System.out.println(isDelete);
        long millis = System.currentTimeMillis() - start;
        long second = millis / 1000;
        log.info("Page Thread 导出数据，花费：" + second + "s/ " + millis + "ms");
    }

FileUtils.java:

public class FileUtils {

    private static final int BUFFER_SIZE = 2 * 1024;

    /**
     * 压缩成ZIP 方法     * @param srcDir 压缩文件夹路径
     *
     * @param out              压缩文件输出流
     * @param KeepDirStructure 是否保留原来的目录结构,true:保留目录结构;
     *                         false:所有文件跑到压缩包根目录下(注意：不保留目录结构可能会出现同名文件,会压缩失败)
     * @throws RuntimeException 压缩失败会抛出运行时异常
     */
    public static void toZip(String srcDir, OutputStream out, boolean KeepDirStructure)
            throws RuntimeException {
        log.info("正在压缩文件。。。");
        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();
            log.info("压缩完成，耗时：" + (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 方法     * @param srcFiles 需要压缩的文件列表
     *
     * @param out 压缩文件输出流
     * @throws RuntimeException 压缩失败会抛出运行时异常
     */
    public static void toZip(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);
                    }

                }
            }
        }
    }

    /**
     * 向浏览器发送zip包
     *
     * @param response
     */
    public static void sendZip(HttpServletResponse response, File zipFile) {
        log.info("正在发送zip包");
        OutputStream outputStream = null;
        BufferedInputStream fis = null;
        try {
            // 以流的形式下载文件。
            fis = new BufferedInputStream(new FileInputStream(zipFile.getPath()));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            // 清空response
            response.reset();
            outputStream = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(zipFile.getName().getBytes("UTF-8"), "ISO-8859-1"));
            outputStream.write(buffer);
            outputStream.flush();
            log.info("发送成功。");
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (fis != null) { fis.close(); }
                if (outputStream != null) { outputStream.close(); }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * @Description //TODO
     * @Date 2021/3/26 20:24
     * @param dirFile
     * @return boolean
     */
    public static boolean deleteFile(File dirFile) {
        // 如果dir对应的文件不存在，则退出
        if (!dirFile.exists()) {
            return false;
        }

        if (dirFile.isFile()) {
            return dirFile.delete();
        } else {

            for (File file : dirFile.listFiles()) {
                deleteFile(file);
            }
        }

        return dirFile.delete();
    }


    /**
     * -创建多个目录(子目录)
     *
     * @param path 路径
     */
    public static void creatFileDirs(String path) {
        //首先要创建目标路径
        File files = new File(path);
        if (!files.exists()) {
            if (files.mkdirs()) {
                log.info("创建多个临时目录成功");
            } else {
                log.info("创建多个临时目录失败.....");
            }
        }
    }


    /**
     * 递归删除临时目录下所有文件以及文件夹
     * @param dir
     * @return
     */
    public static  boolean deleteDir(String dir) {
        File file = new File(dir);
        boolean delete ;
        if (file.isDirectory()) {
            String[] children = file.list();
            if(children.length>0){
                /**递归删除目录中的子目录下*/
                for (int i=0; i<children.length; i++) {
                    boolean success = deleteDir(file.getPath()+"/"+children[i]);
                    if (!success) {
                        return false;
                    }
                }
            }
            delete = file.delete();
        }else {
            delete = file.delete();
        }
        return delete;
    }

}