导出文件的新形势

问题描述

大概问题是这个样子，有一个bug是提示说导出的文件的字段顺序有问题，我一看这不是挺简单的么，而且用的是org.apache.poi包，这个我用过，没啥问题呀，而且我之前整个复杂的都可以，我觉得问题不大，然后就就调整了一下顺序，
然后发现一个问题，表头没变，我才意识到，我没有改表头呀，不过我没有看到对应的表头呀，然后我找了好多确实没找到

问题处理

我都去找底层，也没有发现哪里有表头，我还以为是在实体类上面呢，因为我做过类似的，然后发现，好家伙这边的是直接用手建表头文件，
File newFile = ExcelUtils.createNewPayFile(path + "softwareAdd.xlsx", path + "temp");
对应的工具类

import cn.chinaunicom.core.enums.ExcelEnum;
import cn.chinaunicom.core.enums.FileErrorInfoEnum;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.function.Consumer;
import java.util.logging.Level;

/**
 * @author wanggd
 * @Description:excel相关操作
 * @date 2019/2/22 10:08
 */
public class ExcelUtils extends AbstractExcelBase {

    private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);
    private static final int BUFFER_SIZE = 2 * 1024;

    public ExcelUtils(MultipartFile multipartFile) {
        super(multipartFile);
    }

    /**
     * 导出excel模板也可导出文件
     *
     * @param response
     * @param excelEnum 枚举模板路径
     */
    @SneakyThrows
    public static void exportExcelTemplate(HttpServletResponse response, ExcelEnum excelEnum) {
        ClassPathResource resource = new ClassPathResource(excelEnum.getValue());
        if (!resource.exists()) {
            log.error("exception's message:{}", FileErrorInfoEnum.NOT_EXISTS_FILE_ERROR.getValue());
        }
        // 输出流
        try (
             InputStream fis = resource.getInputStream();
             OutputStream fos = response.getOutputStream();
        ) {
            byte[] buffer = new byte[1024];
            int size;
            while ((size = fis.read(buffer)) != -1) {
                fos.write(buffer, 0, size);
            }
        } catch (IOException e) {
            log.error("exception's message:{}", "写出文件错误,具体请见:" + e.getMessage());
        }
    }

    /**
     * 明细导出用到的方法
     *
     * @param response
     * @param filePath
     * @param fileName
     */
    public static void exportDetailExcel(HttpServletResponse response, String filePath, String fileName) {
        OutputStream outputStream = null;
        File downFile = new File(filePath);
        if (!downFile.exists()) {
            return;
        }
        String downloadPath = filePath;
        File file = new File(downloadPath);
        try (
                InputStream fis = new BufferedInputStream(new FileInputStream(file));
                OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
        ) {
            byte[] buffer = new byte[fis.available()];
            if (fis.available() > 0) {
                fis.read(buffer);
            }
            response.reset();
            response.setCharacterEncoding("UTF-8");
            // String filename = file.getName().replaceAll("\\s", "");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName));
            response.addHeader("Content-Length", "" + file.length());

            response.setContentType("application/vnd.ms-word;charset=UTF-8");
            toClient.write(buffer);
            toClient.flush();
        } catch (IOException e) {
            log.error("写出文件错误,具体请见:" + e.getMessage());
        }
    }

    public static ExcelUtils getExcelUtils(MultipartFile multipartFile) {
        return new ExcelUtils(multipartFile);
    }

    /**
     * 读取excel模板，并复制到新文件中供写入和下载
     *
     * @return
     */
    public static File createNewPayFile(String tempPath, String rPath) throws IOException {
        // 读取模板，并赋值到新文件
        // 获取文件流
        ClassPathResource resource = new ClassPathResource(tempPath);
        // 保存文件的路径
        String realPath = rPath;
        // 新的文件名
        String newFileName = System.currentTimeMillis() + ".xlsx";
        // 判断路径是否存在
        File dir = new File(realPath);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        // 写入到新的excel
        File newFile = new File(realPath, newFileName);
        try (
                InputStream in = new BufferedInputStream(resource.getInputStream(), 10 * 1024 * 1024);
                OutputStream out = new BufferedOutputStream(new FileOutputStream(newFile), 10 * 1024 * 1024);
        ) {
            newFile.createNewFile();
            byte[] buffer = new byte[10 * 1024 * 1024];
            int len;
            while ((len = in.read(buffer)) != -1) {
                out.write(buffer, 0, len);
            }
        } catch (Exception e) {
            log.error("exception's message:{}",e.getStackTrace());
        }
        return newFile;
    }

    /**
     * 复制文件
     *
     * @param s 源文件
     * @param t 复制到的新文件
     */
    public static void fileChannelCopy(File s, File t) throws IOException {
        try (
                InputStream in = new BufferedInputStream(new FileInputStream(s), 100 * 1024 * 1024);
                OutputStream out = new BufferedOutputStream(new FileOutputStream(t), 100 * 1024 * 1024);
        ) {
            byte[] buffer = new byte[100 * 1024 * 1024];
            int len;
            while ((len = in.read(buffer)) != -1) {
                out.write(buffer, 0, len);
            }
        } catch (IOException e) {
            log.error("exception's message:{}",e.getStackTrace());
        }
    }

    /**
     * 打开Excel导出文件流
     *
     * @param request  HttpServletRequest
     * @param response HttpServletResponse
     * @param fileName 文件名
     * @param path     模板路径
     * @param newFile  临时Excel对象
     * @return Workbook
     * @throws Exception
     */
    public static Workbook openExportExcelStream(HttpServletRequest request, HttpServletResponse response, String fileName, String path, File newFile) throws Exception {
        String contentType = "application/vnd.ms-excel";
        response.setContentType(contentType);
        response.setHeader("Content-Disposition", "attachment;filename="
                + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
        if (!newFile.exists()) {
            throw new RuntimeException("文件不存在!");
        }
        return WorkbookFactory.create(new FileInputStream(newFile));
    }

    /**
     * 关闭Excel导出文件流
     *
     * @param response HttpServletResponse
     * @param wb       Workbook
     * @param fos      输出流
     * @param newFile  临时Excel对象
     * @throws Exception
     */
    public static void closeExportExcelStream(HttpServletResponse response, Workbook wb, FileOutputStream fos, File newFile) throws Exception {
        try (
                OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
                InputStream fis = new BufferedInputStream(new FileInputStream(newFile));
        ) {
            wb.write(fos);
            fos.flush();
            byte[] buffer = new byte[fis.available()];
            if (fis.available() > 0) {
                fis.read(buffer);
            }
            toClient.write(buffer);
            toClient.flush();
        } catch (Exception e) {
            log.error("exception's message:{}",e.getStackTrace());
        } finally {
            if (fos != null) {
                fos.close();
            }
        }
    }

    /**
     * 下载成功后删除
     *
     * @param files
     */
    public static void deleteFile(File... files) {
        for (File file : files) {
            if (file.exists()) {
                final boolean delete = file.delete();
            } else {
                log.error("exception's message:{}", "为找到该文件");
            }
        }
    }

    /**
     * 导出
     *
     * @param fileName     要导出的文件名称
     * @param templatePath 用到的模板名称
     * @param request
     * @param response
     * @param consumer     函数式接口 入参一个sheet
     */
    public static void exportUtil(String fileName, String templatePath, HttpServletRequest request, HttpServletResponse response, Consumer<Sheet> consumer) {
        try {
            String path = ExportExcelUtils.getImplementTemplate(request);
            File newFile = ExcelUtils.createNewPayFile(path + templatePath, path + "temp");
            //获取Workbook
            Workbook wb = ExcelUtils.openExportExcelStream(request, response, fileName, path, newFile);
            //获取第一个sheet
            Sheet sheet = wb.getSheetAt(0);
            if (sheet == null) {
                throw new RuntimeException("出错");
            }
            FileOutputStream fos = new FileOutputStream(newFile);
            consumer.accept(sheet);
            //关闭文件输出流
            ExcelUtils.closeExportExcelStream(response, wb, fos, newFile);
            //删除文件
            ExcelUtils.deleteFile(newFile);
        } catch (Exception e) {
            log.error("exception's message:{}",e.getStackTrace());
        }
    }

    /**
     * 导出
     *
     * @param fileName     要导出的文件名称
     * @param templatePath 用到的模板名称
     * @param request
     * @param response
     * @param consumer     函数式接口 入参一个sheet
     * @param index        第几个sheet页
     */
    public static void exportUtilIndex(String fileName, String templatePath, HttpServletRequest request, HttpServletResponse response, Consumer<Sheet> consumer, Integer index) {
        try {
            String path = ExportExcelUtils.getImplementTemplate(request);

            File newFile = ExcelUtils.createNewPayFile(path + templatePath, path + "temp");
            //获取Workbook
            Workbook wb = ExcelUtils.openExportExcelStream(request, response, fileName, path, newFile);
            //获取第index个sheet
            Sheet sheet = wb.getSheetAt(index);
            if (sheet == null) {
                throw new RuntimeException("出错");
            }
            FileOutputStream fos = new FileOutputStream(newFile);
            consumer.accept(sheet);
            //关闭文件输出流
            ExcelUtils.closeExportExcelStream(response, wb, fos, newFile);
            //删除文件
            ExcelUtils.deleteFile(newFile);
        } catch (Exception e) {
            log.error("exception's message:{}",e.getStackTrace());
        }
    }
}