package com.ssm.core.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.ssm.core.utils.FtpUtils;
import com.ssm.manage.entity.SmsException;
import com.ssm.manage.response.ResponseCode;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
public class EasyExcelUtil {

    private static final String SUFFIX_XLS = ".xls";
    private static final String SUFFIX_XLSX = ".xlsx";
    private static final String REGEX_EXCEL = ".+(.xls|.xlsx|.XLS|.XLSX)$";


    public static String templateResponseExports(String templateFileName, Object obj, List list, HttpServletRequest request, HttpServletResponse response) throws SmsException {
        String fileName = System.currentTimeMillis() + SUFFIX_XLSX;
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + FtpUtils.setFileDownloadHeader(request, fileName));
        ExcelWriter excelWriter = null;
        InputStream stream = null;
        try {
            stream = EasyExcelUtil.class.getClassLoader().getResourceAsStream(templateFileName);
            excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(stream).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            excelWriter.fill(obj, writeSheet);
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(list, fillConfig, writeSheet);
        } catch (Exception e) {
            log.error("EasyExcelUtil.templateResponseExports:",e);
            throw new SmsException(ResponseCode.FILE_DOWNLOAD_FAILED);
        } finally {
            // 千万别忘记关闭流
            if (stream != null) {
                try {
                    stream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        return fileName;
    }

    public static String templateResponseExport(String templateFileName, Class<?> clas, List list, HttpServletRequest request, HttpServletResponse response) throws SmsException {
        OutputStream out = null;
        try {
            String fileName = System.currentTimeMillis() + SUFFIX_XLSX;
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + FtpUtils.setFileDownloadHeader(request, fileName));
            out = response.getOutputStream();
            simpleTemplateFill(templateFileName, clas, list, out);
            return fileName;
        } catch (IOException e) {
            log.error("EasyExcelUtil.templateResponseExport:",e);
            throw new SmsException(ResponseCode.FILE_DOWNLOAD_FAILED);
        }
    }

    public static String simpleResponseExport(String sheetName, Class<?> clas, List list, HttpServletRequest request, HttpServletResponse response) throws SmsException {
        OutputStream out = null;
        try {
            String fileName = sheetName + System.currentTimeMillis() + SUFFIX_XLSX;
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + FtpUtils.setFileDownloadHeader(request, fileName));
            out = response.getOutputStream();
            simpleFill(sheetName, clas, list, out);
            return fileName;
        } catch (IOException e) {
            log.error("EasyExcelUtil.simpleResponseExport:",e);
            throw new SmsException(ResponseCode.FILE_DOWNLOAD_FAILED);
        }
    }

    public static void simpleFill(String sheetName, Class<?> clas, List list, OutputStream out) {
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(out, clas)
                    // 去除默认样式
                    .useDefaultStyle(false)
//                    .registerWriteHandler(getStyleStrategy())
                    .build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(list, writeSheet);
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    public static void simpleTemplateFill(String templateFileName, Class<?> clas, List list, OutputStream out) throws SmsException {
        // 方案2 分多次 填充 会使用文件缓存（省内存）
        ExcelWriter excelWriter = null;
        InputStream stream = null;
        try {
            stream = EasyExcelUtil.class.getClassLoader().getResourceAsStream(templateFileName);
            excelWriter = EasyExcel.write(out, clas).withTemplate(stream).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(list, fillConfig, writeSheet);
        } catch (Exception e) {
            log.error("EasyExcelUtil.simpleTemplateFill:",e);
            throw new SmsException(ResponseCode.FILE_DOWNLOAD_FAILED);
        } finally {
            // 千万别忘记关闭流
            if (stream != null) {
                try {
                    stream.close();
                } catch (IOException e) {
                    log.error("EasyExcelUtil.simpleTemplateFill:",e);
                }
            }
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    public static List simpleWrite(MultipartFile multipartFile, Class<?> clas) throws SmsException {
        if (multipartFile == null || !verifyFilename(multipartFile.getOriginalFilename())) {
            throw new SmsException(ResponseCode.FILE_UPLOAD_FAILED);
        }
        try {
            // 解析每行结果在listener中处理
            ExcelListener listener = new ExcelListener();
            InputStream inputStream = multipartFile.getInputStream();
            ExcelReader excelReader = EasyExcel.read(inputStream, clas, listener).build();
            ReadSheet readSheet = EasyExcel.readSheet(0).build();
            excelReader.read(readSheet);
            return listener.getDatas();
        } catch (IOException e) {
            log.error("EasyExcelUtil.simpleWrite:",e);
        }
        throw new SmsException(ResponseCode.FILE_UPLOAD_FAILED);
    }

    public static List simpleWrite(MultipartFile multipartFile, Class<?> clas, int headRowNumber) throws SmsException {
        if (multipartFile == null || !verifyFilename(multipartFile.getOriginalFilename())) {
            throw new SmsException(ResponseCode.FILE_UPLOAD_FAILED);
        }
        try {
            // 解析每行结果在listener中处理
            ExcelListener listener = new ExcelListener();
            InputStream inputStream = multipartFile.getInputStream();
            ExcelReader excelReader = EasyExcel.read(inputStream, clas, listener).build();
            ReadSheet readSheet = EasyExcel.readSheet(0).build();
            readSheet.setHeadRowNumber(headRowNumber);
            excelReader.read(readSheet);
            return listener.getDatas();
        } catch (IOException e) {
            log.error("EasyExcelUtil.simpleWrite:",e);
        }
        throw new SmsException(ResponseCode.FILE_UPLOAD_FAILED);
    }

    public static boolean verifyFilename(String fileName) {
        Matcher matcher = Pattern.compile(REGEX_EXCEL).matcher(fileName);
        return matcher.matches();
    }

    /**
     * 设置样式 去除默认表头样式及设置内容居中
     */
    public static HorizontalCellStyleStrategy getStyleStrategy() {
        //内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //垂直居中,水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //头策略使用默认
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setShrinkToFit(false);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    public static CellStyle cellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        //居中
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        //设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        return cellStyle;
    }
}
