利用EasyExcel简单搭建一套压缩的表格

.1.需求是对整个页面的表格输出并且压缩成zip的压缩包。

首先是service层的业务代码

 //下载至本地
        String filePath = path + "/" + hhitExportTemplate.getFileName() + id + ".xlsx";
        String zipPath = path + "/" + hhitExportTemplate.getFileName() + id + ".zip";  

CustomizeColumnWidth widthDealHandler = new CustomizeColumnWidth(viewDataList);
        try {
            // 这里需要设置不关闭流
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            //设置背景颜色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            //内容策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            ExcelWriter excelWriter = EasyExcel.write(filePath).autoCloseStream(Boolean.FALSE)
                    .head(widthDealHandler.myriadPeopleHead()).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(hhitExportTemplate.getFileName())
                    .registerWriteHandler(widthDealHandler).registerWriteHandler(horizontalCellStyleStrategy).build();
            //获取数据填充
            for (Integer pageNo = 1; pageNo <= totalPage; pageNo++) {
                exportResultParam.getPageParamNew().setCurrPage(String.valueOf(pageNo));
                //获取返回的数据
                ResultVO<List<Map<String, Object>>> body = this.threeInterface(hhitExportDataSource.getRequestUrl(), exportResultParam, headers);
                if (!body.getCode().equals(CodeMsg.NORMAL_CODE.getCode())) {
                    log.info("获取第三方接口{}，第{}页数据失败", hhitExportDataSource.getRequestUrl(), pageNo);
                }
                //根据导出数据源表的数据路径获取到具体的数据
                List<Map<String, Object>> pageDataList = this.getPageData(body, hhitExportDataSource);
                //对获取到的数据列表进行封装
                List<List<Object>> data = changeData(pageDataList, viewDataList);
                //导入到excel表里面
                excelWriter.write(data, writeSheet);
                if (ExportTypeEnum.getExportTypeEnumByStr(exportType).getDesc().equals(ExportTypeEnum.LOCAL.getDesc())) {
                    //获取进度
                    int progress = pageNo * 100 / totalPage;
                    HhitExportRecord hhitExportRecord = new HhitExportRecord(id, progress);
                    hhitExportRecordMapper.updateOne(hhitExportRecord);
                }
            }
            excelWriter.finish();
            //压缩文件
            Map zipMap = new HashMap();
            zipMap.put(hhitExportTemplate.getFileName(), zipPath);
            String zipFile = FileUtils.zipFile(filePath, exportResultParam.getPassword(), hhitExportTemplate.getFileName() + id, zipMap);
            //判断数据量的大小，选择直接导出，还是存储在服务器
            if (ExportTypeEnum.getExportTypeEnumByStr(exportType).getDesc().equals(ExportTypeEnum.BROWSER.getDesc())) {
                // 前端浏览器下载
                OutputStream out = null;
                out = ServletUtil.getOutPutStream(HttpServletUtil.getResponse(), FileUtils.ZIP, "Export");
                FileUtils.write(out, zipFile);
                // 关闭文件流,删除临时文件
                FileUtils.closeAllAndDeleteTemplate(out, null, zipPath, filePath);
                //导出后对数据进行修改
                hhitExportRecordMapper.deleteById(id);
            } else {
                HhitExportRecord hhitExportRecord = new HhitExportRecord(id, ProgressStatusEnum.BUILDTURE.getValue(), DownloadStatusEnum.UNDOWNLOAD.getValue(), 100, new Date(), zipPath, new Date(), headers.get("user_id").get(0));
                hhitExportRecordMapper.updateOne(hhitExportRecord);
            }
        } catch (Exception e) {
            log.error("导出报错", e);
        } finally {
            // 关闭文件流,删除临时文件
            FileUtils.deleteFile(filePath);
        }

这里面是先生成临时的excel文件，在通过压缩文件，压缩成zip格式，下面是两个excel的工具类，和zip的工具类

1.excel工具类：需要注意的是这里实现了

一级和二级的分开，方法在 自定义表头里面，其次，这个方法并没有对导入的字段和表头字段要一一对应的要求，所以我们在导入之前必须对导入的数据进行排序好。

package com.gzhhit.export.util;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.google.common.collect.Lists;
import com.gzhhit.export.constants.CommonConstants;
import com.gzhhit.export.constants.SpecialNameEnum;
import com.gzhhit.export.pojo.param.base.ExcelOneSet;
import org.apache.poi.ss.usermodel.Cell;

import javax.swing.text.html.HTMLEditorKit;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 自定义头部的 列的宽度设置 策略. .
 */
public class CustomizeColumnWidth extends AbstractColumnWidthStyleStrategy {

    public List<Map<String, Object>> headFormatList;

    public CustomizeColumnWidth(List<Map<String, Object>> headFormatList) {
        this.headFormatList = headFormatList;
    }

    /**
     * 表格自定义格式
     *
     * @param writeSheetHolder
     * @param list
     * @param cell
     * @param head
     * @param integer
     * @param isHead
     */
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
        //根据数据库的字段宽度来定义表格宽度
        for (int i = 0; i < headFormatList.size(); i++) {
            Map<String, Object> headFormatMap = headFormatList.get(i);
            int columnWidth = headFormatMap.get("name").toString().length();
            if (headFormatMap.containsKey("width")) {
                columnWidth = Integer.valueOf(headFormatMap.get("width").toString());
            }
            writeSheetHolder.getSheet().setColumnWidth(i, columnWidth * 256);
        }
    }


    /**
     * 自定义表头
     *
     * @return
     */
    public List<List<String>> myriadPeopleHead() {
        List<List<String>> list = new ArrayList<List<String>>();
        for (int i = 0; i < headFormatList.size(); i++) {
            Map<String, Object> headStr = headFormatList.get(i);
            String headField = headStr.get("name").toString();
            List<String> heads = new ArrayList<String>();
            //风险整改状态分布
            if (headField.equals(SpecialNameEnum.RECTIFICATION_STATUS.getDesc())) {
                List<String> severityStatus = CommonConstants.VUL_STATE_STATUS;
                for (String status : severityStatus) {
                    List<String> headStatus = new ArrayList<String>();
                    headStatus.add(headField);
                    headStatus.add(status);
                    list.add(headStatus);
                    continue;
                }
                //风险等级分布
            } else if (headField.equals(SpecialNameEnum.RISK_DISTRIBUTION.getDesc())) {
                List<String> severityStatus = CommonConstants.SEVERITY_STATUS;
                for (String status : severityStatus) {
                    List<String> headStatus = new ArrayList<String>();
                    headStatus.add(headField);
                    headStatus.add(status);
                    list.add(headStatus);
                    continue;
                }
                //整改结果
            } else if (headField.equals(SpecialNameEnum.RECTIFICATION_STATUS_RESULT.getDesc())) {
                List<String> severityStatus = CommonConstants.VUL_STATE_RESULT;
                for (String status : severityStatus) {
                    List<String> headStatus = new ArrayList<String>();
                    headStatus.add(headField);
                    headStatus.add(status);
                    list.add(headStatus);
                    continue;
                }
                //其他字段
            } else {
                heads.add(headField);
                list.add(heads);
            }

        }
        return list;
    }
}

2.压缩文件类

package com.gzhhit.export.util;

import cn.hutool.core.util.StrUtil;
import lombok.SneakyThrows;
import net.lingala.zip4j.core.ZipFile;
import net.lingala.zip4j.exception.ZipException;
import net.lingala.zip4j.model.ZipParameters;
import net.lingala.zip4j.util.Zip4jConstants;
import org.apache.juli.logging.Log;
import org.apache.juli.logging.LogFactory;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.*;
import java.net.URLEncoder;
import java.util.Map;

public class FileUtils {

    private static final Log logger = LogFactory.getLog(FileUtils.class);

    public static final String CSV = "csv";
    public static final String XLSX = "xlsx";
    public static final String ZIP = "zip";

    public static String templatePath = "F:/export";

    public static String toolPath = System.getProperty("user.dir") + "/src/main/resources/toolPath/";

    /**
     * 压缩单个excel文件,多个工具文件并加密
     *
     * @param file     文件
     * @param passWord 密码
     * @param fileName 压缩文件名称
     * @param urlMap   文件urlMap
     * @return 压缩zip文件路径
     * @throws FileNotFoundException exception
     */
    @SneakyThrows
    public static String zipFile(String file, String passWord, String fileName, Map<String, String> urlMap) throws FileNotFoundException {
        String fileOut = templatePath + "/" + fileName + ".zip";
        FileInputStream fileInputStream = new FileInputStream(file);
        ZipParameters parameters = new ZipParameters();
        // 压缩方式
        parameters.setCompressionMethod(Zip4jConstants.COMP_DEFLATE);
        // 压缩级别
        parameters.setCompressionLevel(Zip4jConstants.DEFLATE_LEVEL_NORMAL);
        // 开启加密
        parameters.setSourceExternalStream(true);
        // 文件名称
        if (!"".equals(passWord)) {
            parameters.setEncryptFiles(true);
            // 加密方式
            parameters.setEncryptionMethod(Zip4jConstants.ENC_METHOD_STANDARD);
            // 设置加密密码
            parameters.setPassword(passWord.toCharArray());
        }
        try {
            // 压缩excel文件
            ZipFile zipFile = new ZipFile(fileOut);
            parameters.setFileNameInZip(fileName + "." + XLSX);
            zipFile.addFile(new File(file), parameters);
        } catch (ZipException e) {
            logger.error("zipFile 外建加密压缩异常", e);
        } finally {
            fileInputStream.close();
        }
        return fileOut;
    }

    /**
     * 文件上传
     *
     * @param in       in
     * @param fileName 文件名
     * @return string
     */
    public static String upload(InputStream in, String fileName) {
        logger.info("文件名" + fileName);
        if (fileName == null) {
            return "";
        }
        OutputStream out = null;
        byte[] readBytes = new byte[1024];
        String file = getFileName1(fileName);
        try {
            File f = new File(toolPath + file);
            if (!f.getParentFile().exists()) {
                boolean mkdirs = f.getParentFile().mkdirs();
                logger.info("upload 创建文件夹结果");
                logger.info(mkdirs);
            }
            out = new FileOutputStream(toolPath + file);
            while (true) {
                int read = in.read(readBytes);
                if (read > 0) {
                    out.write(readBytes);
                } else {
                    break;
                }
            }
        } catch (IOException e) {
            logger.error("FileUtils -> upload IOException", e);
        } finally {
            closeAll(out, in);
        }
        return toolPath + file;
    }

    /**
     * 下载
     *
     * @param url 地址
     */
    @SneakyThrows
    public static void download(String url) {
        String urlFileName=url;
        String reg = "[^\u4e00-\u9fa5]";
        String fileName = URLEncoder.encode( urlFileName.replaceAll(reg,""), "UTF-8").replaceAll("\\+", "%20")+".zip";
        OutputStream out = ServletUtil.getOutPutStream(fileName);
        File file = new File(url);
        if (file.exists()) {
            InputStream in = new FileInputStream(file);
            write(out, in);
        }
    }

    /**
     * 保存到本地临时文件
     *
     * @param workbook csv的sheet
     * @return 路径
     */
    @SneakyThrows
    public static String saveTemplateFile(Workbook workbook) {
        String tempFile = templatePath + DateUtils.getDateTimeStr() + "-template.csv";
        File file = new File(tempFile);
        logger.info("tempFile " + tempFile);
        if (!file.exists()) {
            if (!file.getParentFile().exists()) {
                boolean mkdirs = file.getParentFile().mkdirs();
                logger.info("文件夹结果");
                logger.info(mkdirs);
            }
            boolean newFile = file.createNewFile();
            logger.info("文件创建结果");
            logger.info(newFile);
        }

        OutputStream os = new FileOutputStream(file);
        workbook.write(os);
        os.close();
        return tempFile;
    }

    /**
     * 关闭文件流
     *
     * @param out out
     * @param in  in
     */
    @SneakyThrows
    public static void closeAll(OutputStream out, InputStream in) {
        if (out != null) {
            out.close();
        }
        if (in != null) {
            in.close();
        }
    }

    /**
     * 关闭文件流,删除临时文件
     *
     * @param out         out
     * @param in          in
     * @param zipFile     文件url
     * @param templateUrl templateUrl
     */
    public static void closeAllAndDeleteTemplate(OutputStream out, InputStream in, String zipFile, String templateUrl) {
        FileUtils.closeAll(out, in);
        if (StrUtil.isNotEmpty(zipFile)) {
            File file = new File(zipFile);
            if (file.exists()) {
                boolean delete = file.delete();
                logger.info("临时zip文件删除结果" + delete);
            }
        }
        if (StrUtil.isNotEmpty(templateUrl)) {
            File tempFile = new File(templateUrl);
            if (StrUtil.isNotEmpty(templateUrl) && tempFile.exists()) {
                boolean delete = tempFile.delete();
                logger.info("临时csv文件删除结果" + delete);
            }
        }
    }


    /***
     * 文件写出
     * @param out 输出流
     * @param in 输入流
     */
    public static void write(OutputStream out, InputStream in) {
        try {
            byte[] readBytes = new byte[1024];
            while (true) {
                int read = in.read(readBytes);
                if (read > 0) {
                    out.write(readBytes);
                } else {
                    break;
                }
            }
        } catch (IOException e) {
            logger.error("FielUtils->write IOException", e);
        } finally {
            closeAll(out, in);
        }
    }

    /***
     * 文件写出
     * @param out 输出流
     * @param zipFile 文件url
     */
    public static void write(OutputStream out, String zipFile) {
        InputStream in = null;
        try {
            in = new FileInputStream(zipFile);
        } catch (FileNotFoundException e) {
            logger.error("FileUtils->write IOException',e");
        }
        if (in == null) {
            return;
        }
        write(out, in);
    }

    /**
     * 根据uuid和当前时间拼接后缀得到文件名
     *
     * @param fileSuffix 后缀
     * @return String
     */
    public static String getFileName(String fileSuffix) {
        return DateUtils.getUUIDTimeFilePrefix() + "-" + fileSuffix;
    }

    /**
     * 根据uuid和当前时间拼接后缀得到文件名
     *
     * @param fileSuffix 后缀
     * @return String
     */
    public static String getFileName1(String fileSuffix) {
        return DateUtils.getDateTimeStr() + "-" + fileSuffix;
    }

    /**
     * 删除文件
     *
     * @param path 路径
     */
    public static void deleteFile(String path) {
        if (StrUtil.isEmpty(path)) {
            return;
        }
        File file = new File(path);
        if (file.exists()) {
            boolean delete = file.delete();
            logger.info("deleteFile result");
            logger.info(delete);
        }
    }

}