package com.chinaztt.mes.common.util.easyexcel;


import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Font;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.metadata.TableStyle;
import com.alibaba.excel.metadata.*;
import com.alibaba.excel.support.ExcelTypeEnum;
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.chinaztt.mes.common.util.easyexcel.exception.ExcelException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

/**
*@author: Zou, Yu
*@description:
*@date: 2023/9/24 0024 13:18
**/
public class EasyExcelUtils {

    public static HorizontalCellStyleStrategy getStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为灰色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        // 字体样式
        headWriteFont.setFontName("宋体");
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 自动换行
        headWriteCellStyle.setWrapped(false);
        // 水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
        // 背景白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 11);
        // 字体样式
        contentWriteFont.setFontName("宋体");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 内容居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //边框设置
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    public static HorizontalCellStyleStrategy getStyleFixedStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为灰色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        // 字体样式
        headWriteFont.setFontName("Calibri");
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 自动换行
        headWriteCellStyle.setWrapped(false);
        // 水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
        // 背景白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        // 字体样式
        contentWriteFont.setFontName("Calibri");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 内容左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    public static HorizontalCellStyleStrategy getArveStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为
        headWriteCellStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        // 字体样式
        headWriteFont.setFontName("Calibri");
        headWriteFont.setColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setWriteFont(headWriteFont);

        headWriteCellStyle.setBorderBottom(BorderStyle.MEDIUM);
        headWriteCellStyle.setLeftBorderColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setRightBorderColor(IndexedColors.WHITE.getIndex());
        // 自动换行
        headWriteCellStyle.setWrapped(true);
        // 水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
        // 背景白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        // 字体样式
        contentWriteFont.setFontName("Calibri");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 设置response
     * @param response
     * @param targetName
     * @return
     */
    public static HttpServletResponse getResponse(HttpServletResponse response, String targetName){
        String fileName;
        try {
            fileName = String.valueOf(new StringBuilder()
                    .append(targetName)
                    .append("_")
                    .append(DateUtil.today())
                    .append(ExcelTypeEnum.XLS.getValue()));
            fileName =  URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            return response;
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * excel文件读取
     *
     * @param clazz model类型Class对象
     * @param excel excel文件对象
     * @param <T>   model类型泛型
     * @return model类型数据集合
     * @throws IOException IO异常
     */
    public static <T extends BaseRowModel> List<T> readModelExcel(Class<T> clazz, MultipartFile excel) throws IOException {
        ExcelListener<T> excelListener = new ExcelListener<>();
        ExcelReader reader = getReader(excel, excelListener);
        try {
            reader.getSheets().forEach(sheet -> {
                sheet.setClazz(clazz);
                reader.read(sheet);
            });
        } catch (Exception e) {
            e.printStackTrace();
            throw new IOException("请检查导入模板！");
        }
        return excelListener.getDataList();
    }

    /**
     *
     * @param clazz model类型Class对象
     * @param excel excel文件对象
     * @param sheetNumber 指定sheet页
     * @param <T> model类型泛型
     * @return model类型数据集合
     * @throws IOException
     */
    public static <T extends BaseRowModel> List<T> readModelSheetExcel(Class<T> clazz, MultipartFile excel, int sheetNumber) throws IOException {
        ExcelListener<T> excelListener = new ExcelListener<>();
        ExcelReader reader = getReader(excel, excelListener);
        try {
            Sheet sheet = reader.getSheets().get(sheetNumber);
            sheet.setClazz(clazz);
            reader.read(sheet);
        } catch (Exception e) {
            throw new IOException("请检查导入模板！");
        }
        return excelListener.getDataList();
    }

    /**
     * 获取excel文件读取对象
     *
     * @param excel         excel文件对象
     * @param excelListener excel事件处理
     * @return excel 读取对象
     * @throws IOException IO异常
     */
    private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws IOException {
        String filename = excel.getOriginalFilename();
        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx") && !filename.toLowerCase().endsWith(".xltm") && !filename.toLowerCase().endsWith(".xlsm"))) {
            throw new ExcelException(ResponseResultEnum.EXCEL_FILE_EXT_ERROR);
        }
        InputStream inputStream = new BufferedInputStream(excel.getInputStream());
        return new ExcelReader(inputStream, null, excelListener, false);
    }

    /**
     * 写入数据到导出文件
     *
     * @param clazz       model类型Class对象
     * @param sheetNo     sheet序号
     * @param headLineMun head行数
     * @param startRow    开始写入行数
     * @param tableNo     table序号
     * @param modelList   model数据集合
     * @param temp        临时文件对象
     * @param export      导出文件对象
     * @param <T>         model泛型
     */
    public static <T extends BaseRowModel> void writeModelExport(Class<T> clazz, Integer sheetNo, Integer headLineMun, Integer startRow, Integer tableNo, List<T> modelList, File temp, File export) {
        try {
            InputStream inputStream = new FileInputStream(temp);
            FileOutputStream OutputStream = new FileOutputStream(export);
            ExcelWriter writer = EasyExcelFactory.getWriterWithTemp(inputStream, OutputStream, ExcelTypeEnum.XLSX, false);
            writer.write(modelList, initSheet(sheetNo, headLineMun, startRow), initTable(tableNo, clazz));
            writer.finish();
            OutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Sheet初始化
     *
     * @param sheetNo     sheet序号
     * @param headLineMun head行数
     * @param startRow    开始写入行数
     * @return Sheet对象
     */
    public static Sheet initSheet(Integer sheetNo, Integer headLineMun, Integer startRow) {
        Sheet sheet = new Sheet(sheetNo, headLineMun);
        sheet.setStartRow(startRow);
        return sheet;
    }

    /**
     * Table初始化
     *
     * @param tableNo table序号
     * @param clazz   model类型Class对象
     * @param <T>     model泛型
     * @return Table对象
     */
    public static <T extends BaseRowModel> Table initTable(Integer tableNo, Class<T> clazz) {
        Table table = new Table(tableNo);
        table.setClazz(clazz);
        TableStyle tableStyle = new TableStyle();
        tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
        tableStyle.setTableHeadBackGroundColor(IndexedColors.WHITE);
        Font font = new Font();
        font.setBold(false);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        tableStyle.setTableContentFont(font);
        table.setTableStyle(tableStyle);
        return table;
    }

    /**
     * 替换模板标记
     *
     * @param export     导出文件对象
     * @param index      sheet序号
     * @param rows       单元格行序
     * @param columns    单元格列序
     * @param replaceMap 多个标记集合
     */
    public static void editModelWorkBook(File export, Integer index, Integer rows, Integer columns, Map<String, String> replaceMap) {
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(export);
//            editModelWorkBook(workBook,index,0,0,replaceMap);
            editModelWorkBook(workBook, index, rows, columns, replaceMap);
            workBook.close();
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    /**
     * 替换模板标记
     *
     * @param workBook   excel工作册对象
     * @param index      sheet序号
     * @param rows       单元格行序
     * @param columns    单元格列序
     * @param replaceMap 多个标记集合
     */
    public static void editModelWorkBook(XSSFWorkbook workBook, Integer index, Integer rows, Integer columns, Map<String, String> replaceMap) {
        XSSFSheet sheet = workBook.getSheetAt(index);
        Row row = sheet.getRow(rows);
        Cell cell = row.getCell(columns);
        String replaceString = cell.toString();
        for (Map.Entry<String, String> entry : replaceMap.entrySet()) {
            replaceString = replaceString.replace(entry.getKey(), entry.getValue());
        }
        cell.setCellValue(replaceString);
        workBook.setSheetName(index, replaceString.replace(":", ""));
    }

    /**
     * 模板sheet克隆及命名
     *
     * @param temp           临时文件对象
     * @param export         导出文件对象
     * @param cloneSheetNo   克隆sheet序列
     * @param rows           单元格行序
     * @param columns        单元格列序
     * @param replaceMapList 多个标记集合
     */
    public static void writeModelCloneSheet(File temp, File export, Integer cloneSheetNo, Integer rows, Integer columns, List<Map<String, String>> replaceMapList) {
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(temp);
            OutputStream outputStream = new FileOutputStream(export);
            /** 如果你需要6份相同模板的sheet 那么你只需要克隆5份即可*/
            for (int index = 0; index < replaceMapList.size() - 1; index++) {
                /** 克隆模板文件 */
                XSSFSheet sheet = workBook.cloneSheet(cloneSheetNo);
            }
            for (int index = 0; index < replaceMapList.size(); index++) {
                editModelWorkBook(workBook, index, rows, columns, replaceMapList.get(index));
            }
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            workBook.close();
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    /**
     * 临时文件写入导出文件
     *
     * @param temp    临时文件对象
     * @param export  导出文件对象
     * @param index   sheet序号
     * @param rows    单元格行序
     * @param columns 单元格列序
     * @param isTime  是否有时间标记
     */
    public static void writeModelWorkBook(File temp, File export, Integer index, Integer rows, Integer columns, Boolean isTime) {
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(temp);
            OutputStream outputStream = new FileOutputStream(export);
            if (isTime) {
                writeModelWorkBook(workBook, index, rows, columns);
            }
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            workBook.close();
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    /**
     * 替换时间标记（year,month,day）
     *
     * @param workBook excel工作册对象
     * @param index    sheet序号
     * @param rows     单元格行序
     * @param columns  单元格列序
     */
    public static void writeModelWorkBook(XSSFWorkbook workBook, Integer index, Integer rows, Integer columns) {
        XSSFSheet xssfSheet = workBook.getSheetAt(index);
        Row row = xssfSheet.getRow(rows);
        Cell cell = row.getCell(columns);
        String cellString = cell.toString();
        Calendar calendar = Calendar.getInstance();
        int year = calendar.get(Calendar.YEAR);
        int month = calendar.get(Calendar.MONTH) + 1;
        int day = calendar.get(Calendar.DAY_OF_MONTH);
        String yearString = cellString.replace("year", String.valueOf(year));
        String monthString = yearString.replace("month", String.valueOf(month));
        String dayString = monthString.replace("day", String.valueOf(day));
        cell.setCellValue(dayString);
    }

    /**
     * 针对UserModel导出方法
     *
     * @param temp
     * @param export
     * @param isTime
     */
    public static void writeModelWorkBook(File temp, File export, Boolean isTime) {
        writeModelWorkBook(temp, export, 0, 1, 9, isTime);
    }


    public static void writeModelCloneSheet(File temp, File export, List<Map<String, String>> replaceMapList) {
        writeModelCloneSheet(temp, export, 0, 0, 0, replaceMapList);
    }

}