yl-statistics/src/main/java/com/poteviohealth/cgp/statistics/utils/ExcelUtils.java

package com.poteviohealth.cgp.statistics.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.SyncReadListener;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.google.common.collect.Lists;
import lombok.SneakyThrows;
import org.apache.commons.io.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.ResourceLoaderAware;
import org.springframework.context.annotation.Lazy;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.Collections;
import java.util.List;
import java.util.Map;


@Lazy(false)
@Component
public class ExcelUtils implements ResourceLoaderAware {
    private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * excel文件的导出下载
     *
     * @param templateLoacation excel模板文件位置，格式参见org.springframework.core.io.ResourceLoader#getResource方法注释
     * @param data              excel模板需要的单项数据
     * @param list              excel模板需要的列表数据
     * @param filename          给浏览器端下载的文件名
     * @param resp 响应类
     * @see ResourceLoader#getResource(String)
     */
    public static void easyDownload(String templateLoacation, Map<String, Object> data, List<?> list, String filename,
                                    HttpServletResponse resp) {

        Resource template = resourceLoader.getResource(templateLoacation);
        resp.setHeader("Content-Type", "application/vnd.ms-excel; charset=utf-8");
        try {
            //resp.setHeader("Transfer-Encoding", "chunked");
            resp.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
            //resp.setContentLength(getExcelFileSize(list));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        long begin = System.currentTimeMillis();
        try {
            InputStream is = template.getInputStream();
            OutputStream outputStream = resp.getOutputStream();
            ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(is).build();
            is.close();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 直接写入数据
            long t4 = System.currentTimeMillis();
            List<? extends List<?>> newList = Lists.partition(list, 5000);
            for (List<?> objects : newList) {
                excelWriter.fill(objects, writeSheet);
            }
            if (!data.isEmpty()) {
                excelWriter.fill(data, writeSheet);
            }
            long t5 = System.currentTimeMillis();
            excelWriter.finish();
            outputStream.flush();
            logger.info("excelWriter time:" + (t5- t4));
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            try {
                resp.getOutputStream().close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        logger.info("export time:" + (System.currentTimeMillis() - begin));
    }

    /**
     * excel文件的导出下载
     *
     * @param templateLoacation excel模板文件位置，格式参见org.springframework.core.io.ResourceLoader#getResource方法注释
     * @param data              excel模板需要的单项数据
     * @param list              excel模板需要的列表数据
     * @param filename          给浏览器端下载的文件名
     * @param resp 响应类
     * @see ResourceLoader#getResource(String)
     */
    public static void easyDownloadOrder(String templateLoacation, Map<String, Object> data, List<?> list, String filename,
                                    HttpServletResponse resp) {
        Resource template = resourceLoader.getResource(templateLoacation);
        resp.setContentType("application/vnd.ms-excel");
        try {
            resp.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
            resp.setCharacterEncoding("utf8");
            resp.setHeader("Pragma", "public");
            resp.setHeader("Cache-Control", "no-store");
            resp.addHeader("Cache-Control", "max-age=0");
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
        long begin = System.currentTimeMillis();
        try {
            InputStream is = template.getInputStream();

            OutputStream outputStream = resp.getOutputStream();
            ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(is).build();
            is.close();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 直接写入数据
            excelWriter.fill(list, writeSheet);
            if (!data.isEmpty()) {
                excelWriter.fill(data, writeSheet);
            }
            excelWriter.finish();
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            try {
                resp.getOutputStream().close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        logger.info("export time:" + (System.currentTimeMillis() - begin));
    }

    /**
     * 实体模型导出
     * @param response 响应类
     * @param modelClass 实体类型
     * @param fileName 文件名
     * @param sheetName sheet名
     * @param dataList 数据
     */
    @SneakyThrows
    public static void easyDownload(HttpServletResponse response, Class<?> modelClass, String fileName, String sheetName, List<?> dataList) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        EasyExcel.write(response.getOutputStream(), modelClass).sheet(sheetName).doWrite(dataList);
    }

    public static <T>  List<T> easyImport(MultipartFile file, Integer head, Class<T> modelClass){
        InputStream io = null;
        try {
            io = file.getInputStream();
            SyncReadListener listener = new SyncReadListener();
            EasyExcel.read(io, modelClass, listener).sheet().headRowNumber(head).doRead();
            return (List<T>)listener.getList();
        }catch (IOException e){
            return Collections.emptyList();
        }
        finally {
          IOUtils.closeQuietly(io);
        }
    }

    private static ResourceLoader resourceLoader;

    @Override
    public void setResourceLoader(ResourceLoader resourceLoader) {
        setResourceLoader0(resourceLoader);
    }

    private static void setResourceLoader0(ResourceLoader resourceLoader) {
        ExcelUtils.resourceLoader = resourceLoader;
    }

    ExcelUtils() {
    }
}