package com.ywt.mg.web.common;

import com.ywt.mg.core.exceptions.AppMessageException;
import com.ywt.mg.core.utils.Checker;
import com.ywt.mg.core.utils.ExcelHelper;
import com.ywt.mg.domain.models.ExcelDataMap;
import com.ywt.mg.domain.models.ExcelStyleMap;
import com.ywt.mg.domain.models.pojo.CustomExcelItem;
import com.ywt.mg.domain.models.pojo.ExcelCollectPojo;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.format.*;
import jxl.write.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

/**
 * @author daiyihua
 * @create 2022-08-23 下午3:52
 * @desc excel 下载里面抽出来的一些代码
 **/

@Service
public class ExcelDownloadSrv {
    private final Logger logger = LoggerFactory.getLogger(getClass().getName());

    // 每个单元格最多存放多少行数据
    private final int SHEET_MAX_SIZE = 60000;

    /**
     * 下载excel 时 设置 httpServletResponse
     */
    public static HttpServletResponse setHttpServletResponse(HttpServletResponse httpServletResponse, String filename) throws IOException {
        httpServletResponse.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "iso-8859-1"));
        httpServletResponse.setContentType("application/vnd.ms-excel;charset=UTF-8");
        httpServletResponse.setHeader("Pragma", "no-cache");
        httpServletResponse.setHeader("Cache-Control", "no-cache");
        httpServletResponse.setDateHeader("Expires", 0);
        return httpServletResponse;
    }

    /**
     * 得到已经设置好的excel模版
     *
     * @return
     */
    public static WritableCellFormat getWritableCellFormat() throws WriteException {
        //设置字体;
        WritableFont font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);

        WritableCellFormat cellFormat = new WritableCellFormat(font);
        //设置背景颜色;
        cellFormat.setBackground(Colour.WHITE);
        //设置边框;
        cellFormat.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
        //设置自动换行;
        cellFormat.setWrap(true);
        //设置文字居中对齐方式;
        cellFormat.setAlignment(Alignment.CENTRE);
        //设置垂直居中;
        cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        return cellFormat;
    }

    /**
     * 通用的生成 Excel 文件并以流返回
     *
     * @param fileName            excel 文件名
     * @param data                需要填充的数据，key 是列名，value 是每一列的数据组成的 list, 类型必须是 {@link String}
     * @param httpServletResponse resp
     * @throws AppMessageException 业务层异常抛出，如传入数据为空
     * @author Walker, added on 06/27/2019.
     */
    public void generateAndReturnExcelFile(String fileName, ExcelDataMap data, ExcelStyleMap excelStyleMap,
                                           HttpServletResponse httpServletResponse) throws AppMessageException {
        if (data == null || data.isEmpty()) {
            throw new AppMessageException("数据为空");
        }
        try (OutputStream out = ExcelDownloadSrvOld.setHttpServletResponse(httpServletResponse, fileName + ".xls").getOutputStream()) {
            // 创建写工作簿对象
            WritableWorkbook workbook = Workbook.createWorkbook(out);
            // 工作表
            WritableSheet sheet = workbook.createSheet(fileName, 0);
            // 获取带样式的cell
            WritableCellFormat cellFormat = getWritableCellFormat();
            //给sheet电子版中所有的列设置默认的列的宽度;
            sheet.getSettings().setDefaultColumnWidth(20);
            sheet.getSettings().setDefaultRowHeight(30 * 20);

            //单独应用样式
            if (excelStyleMap != null) {
                for (String column : excelStyleMap.keySet()) {
                    // 指定列宽
                    int styleColumnWidth = excelStyleMap.getIntValueSafely(column, ExcelStyleMap.STYLE_COLUMN_WIDTH, 0);
                    List<String> columns = new ArrayList<>(data.keySet());
                    for (int i = 0; i < columns.size(); i++) {
                        if (columns.get(i).equals(column)) {
                            sheet.setColumnView(i, styleColumnWidth);
                        }
                    }
                }
            }

            //行号
            int firstRow = 0;
            //填充表头
            int headerColumn = 0;
            for (String key : data.keySet()) {
                Label label = new Label(headerColumn, firstRow, key, cellFormat);
                sheet.addCell(label);
                List<String> colData = data.get(key);
                for (int i = 0; i < colData.size(); i++) {
                    Label lb = new Label(headerColumn, i + 1, Checker.getStringValue(colData.get(i)), cellFormat);
                    sheet.addCell(lb);
                }
                headerColumn++;
            }
            //开始执行写入操作
            workbook.write();
            //关闭流
            workbook.close();
        } catch (Exception e) {
            logger.error("ExcelDownloadSrv#generateAndReturnExcelFile(fileName={}):\n {}",
                    fileName, e.getMessage(), e);
        }
    }

    /**
     * 通用的生成 Excel 文件并以流返回
     *
     * @param fileName            excel 文件名
     * @param data                需要填充的数据，key 是列名，value 是每一列的数据组成的 list, 类型必须是 {@link String}
     * @param httpServletResponse resp
     * @throws AppMessageException 业务层异常抛出，如传入数据为空
     * @author Walker, added on 06/27/2019.
     */
    public void generateAndReturnExcelFileWithNoStyle(String fileName, ExcelDataMap data, ExcelStyleMap excelStyleMap,
                                                      HttpServletResponse httpServletResponse) throws AppMessageException {
        if (data == null || data.isEmpty()) {
            throw new AppMessageException("数据为空");
        }
        try (OutputStream out = ExcelDownloadSrvOld.setHttpServletResponse(httpServletResponse, fileName + ".xls").getOutputStream()) {
            // 创建写工作簿对象
            WritableWorkbook workbook = Workbook.createWorkbook(out);
            // 工作表
            WritableSheet sheet = workbook.createSheet(fileName, 0);

            //单独应用样式
            if (excelStyleMap != null) {
                for (String column : excelStyleMap.keySet()) {
                    // 指定列宽
                    int styleColumnWidth = excelStyleMap.getIntValueSafely(column, ExcelStyleMap.STYLE_COLUMN_WIDTH, 0);
                    List<String> columns = new ArrayList<>(data.keySet());
                    for (int i = 0; i < columns.size(); i++) {
                        if (columns.get(i).equals(column)) {
                            sheet.setColumnView(i, styleColumnWidth);
                        }
                    }
                }
            }

            //行号
            int firstRow = 0;
            //填充表头
            int headerColumn = 0;
            for (String key : data.keySet()) {
                Label label = new Label(headerColumn, firstRow, key);
                sheet.addCell(label);
                List<String> colData = data.get(key);
                for (int i = 0; i < colData.size(); i++) {
                    Label lb = new Label(headerColumn, i + 1, Checker.getStringValue(colData.get(i)));
                    sheet.addCell(lb);
                }
                headerColumn++;
            }
            //开始执行写入操作
            workbook.write();
            //关闭流
            workbook.close();
        } catch (Exception e) {
            logger.error("ExcelDownloadSrv#generateAndReturnExcelFile(fileName={} ):\n {}",
                    fileName, e.getMessage(), e);
        }
    }

    /**
     * 通用的生成 Excel 文件(包含汇总)并以流返回
     *
     * @param fileName            excel 文件名
     * @param data                需要填充的数据，key 是列名，value 是每一列的数据组成的 list, 类型必须是 {@link String}
     * @param httpServletResponse resp
     * @throws AppMessageException 业务层异常抛出，如传入数据为空
     * @author Walker, added on 06/27/2019.
     */
    public void generateAndReturnExcelFileWithNoStyleAndCollect(String fileName, ExcelDataMap data, ExcelStyleMap excelStyleMap,
                                                                List<ExcelCollectPojo> collectList, HttpServletResponse httpServletResponse) throws AppMessageException {
        if (data == null || data.isEmpty()) {
            throw new AppMessageException("数据为空");
        }
        try (OutputStream out = ExcelDownloadSrvOld.setHttpServletResponse(httpServletResponse, fileName + ".xls").getOutputStream()) {
            // 创建写工作簿对象
            WritableWorkbook workbook = Workbook.createWorkbook(out);
            // 工作表
            WritableSheet sheet = workbook.createSheet(fileName, 0);
            //单独应用样式
            if (excelStyleMap != null) {
                for (String column : excelStyleMap.keySet()) {
                    // 指定列宽
                    int styleColumnWidth = excelStyleMap.getIntValueSafely(column, ExcelStyleMap.STYLE_COLUMN_WIDTH, 0);
                    List<String> columns = new ArrayList<>(data.keySet());
                    for (int i = 0; i < columns.size(); i++) {
                        if (columns.get(i).equals(column)) {
                            sheet.setColumnView(i, styleColumnWidth);
                        }
                    }
                }
            }
            // 设置格式(实线)
            WritableCellFormat cellFormat = ExcelHelper.getThinWritableCellFormat();
            //给sheet电子版中所有的列设置默认的列的宽度;
            sheet.getSettings().setDefaultColumnWidth(20);
            sheet.getSettings().setDefaultRowHeight(35 * 20);
            //行号
            int firstRow = 0;
            //填充表头
            int headerColumn = 0;
            for (String key : data.keySet()) {
                Label label = new Label(headerColumn, firstRow, key, cellFormat);
                sheet.addCell(label);
                List<String> colData = data.get(key);
                for (int i = 0; i < colData.size(); i++) {
                    Label lb = new Label(headerColumn, i + 1, Checker.getStringValue(colData.get(i)), cellFormat);
                    sheet.addCell(lb);
                }
                headerColumn++;
            }
            // 添加汇总
            if (!Checker.isNone(collectList)) {
                for (ExcelCollectPojo pojo : collectList) {
                    String[] dataArr = pojo.getDataArr();
                    for (int i = 0, j = dataArr.length; i < j; i++) {
                        Label lb = new Label(pojo.getCol() + i, pojo.getRow(), dataArr[i], cellFormat);
                        sheet.addCell(lb);
                    }
                }
            }

            //开始执行写入操作
            workbook.write();
            //关闭流
            workbook.close();
        } catch (Exception e) {
            logger.error("ExcelDownloadSrv#generateAndReturnExcelFile(fileName={} ):\n {}",
                    fileName, e.getMessage(), e);
        }
    }

    /**
     * 通用的生成 Excel 文件(包含汇总)并以流返回(一点样式也没有)
     *
     * @param fileName excel 文件名
     * @param data     需要填充的数据，key 是列名，value 是每一列的数据组成的 list, 类型必须是 {@link String}
     * @throws AppMessageException 业务层异常抛出，如传入数据为空
     * @author Walker, added on 06/27/2019.
     */
    public OutputStream generateAndReturnExcelByteArrayOutputStreamWithStyleAndCollect(String fileName, String path, ExcelDataMap data, ExcelStyleMap excelStyleMap,
                                                                                       List<ExcelCollectPojo> collectList, int size, int bill) throws AppMessageException {

        OutputStream ba = null;
        ba = new ByteArrayOutputStream();
        WritableWorkbook workbook = null;
        if (data == null || data.isEmpty()) {
            throw new AppMessageException("数据为空");
        }
        try {
            // 创建工作表格
            workbook = Workbook.createWorkbook(ba);
            int listSize = 0;
            if (data.size() > 0) {
                Set<String> s1 = data.keySet();
                //开始根据键找值
                for (String key : s1) {
                    List<String> value = data.get(key);
                    if (!Checker.isNone(value)) {
                        int dataItemSize = value.size();
                        if (listSize < dataItemSize) {
                            listSize = dataItemSize;
                        }
                    }
                }
            }

            // 单元数
            int sheetAmount = 0;
            // 每个单元表格存放最大的行数，当list总数超过每个sheet所设置的最大值时，要注意分成多个sheet来存储
            int sheetMaxSize = SHEET_MAX_SIZE;
            if (listSize < sheetMaxSize) {
                sheetAmount = 1;
            } else {
                if (listSize % sheetMaxSize == 0) {
                    sheetAmount = listSize / sheetMaxSize;
                } else {
                    sheetAmount = listSize / sheetMaxSize + 1;
                }
            }

            // 工作表
            for (int i = 0; i < sheetAmount; i++) {
                // 当前页数(从0开始)
                int currentSheetIndex = i;
                // 当前页数(从1开始)
                int sheetIndex = currentSheetIndex + 1;
                WritableSheet sheet = workbook.createSheet(fileName + sheetIndex, currentSheetIndex);
                // 获取带样式的cell
                WritableCellFormat cellFormat = getWritableCellFormat();
                //给sheet电子版中所有的列设置默认的列的宽度;
                sheet.getSettings().setDefaultColumnWidth(20);
                sheet.getSettings().setDefaultRowHeight(30 * 20);

                // 当前页数所对应的行数
                int currentSheetRowAmount = 0;
                if (listSize < sheetMaxSize * sheetIndex) {
                    currentSheetRowAmount = listSize - sheetMaxSize * currentSheetIndex;
                } else {
                    currentSheetRowAmount = sheetMaxSize;
                }
                // 单独应用样式
                if (excelStyleMap != null) {
                    for (String column : excelStyleMap.keySet()) {
                        // 指定列宽
                        int styleColumnWidth = excelStyleMap.getIntValueSafely(column, ExcelStyleMap.STYLE_COLUMN_WIDTH, 0);
                        List<String> columns = new ArrayList<>(data.keySet());
                        for (int j = 0, columnsSize = columns.size(); j < columnsSize; j++) {
                            if (columns.get(j).equals(column)) {
                                sheet.setColumnView(j, styleColumnWidth);
                            }
                        }
                    }
                }
                // 第一行
                int firstRow = 0;
                //填充表头
                int headerColumn = 0;
                for (String key : data.keySet()) {
                    Label label = new Label(headerColumn, firstRow, key, cellFormat);
                    sheet.addCell(label);
                    List<String> colData = data.get(key);
                    if(!Checker.isNone(colData)) {
                        for (int q = 0; q < currentSheetRowAmount; q++) {
                            int e = sheetMaxSize * currentSheetIndex;
                            Label lb = new Label(headerColumn, q + 1, Checker.getStringValue(colData.get(e + q)), cellFormat);
                            sheet.addCell(lb);
                        }
                    }
                    headerColumn++;
                }
                // 最后一页，添加汇总，同时关闭流
                if (currentSheetIndex == sheetAmount - 1) {
                    int n = 0;
                    if (!Checker.isNone(collectList)) {
                        for (ExcelCollectPojo pojo : collectList) {
                            String[] dataArr = pojo.getDataArr();
                            for (int r = 0, j = dataArr.length; r <= j; r++) {
                                if (r < j){
                                Label lb2 = new Label(pojo.getCol() + r, pojo.getRow() - sheetMaxSize * currentSheetIndex, dataArr[r], cellFormat);
                                sheet.addCell(lb2);
                                if(j-r == 1){
                                    n = pojo.getRow() - sheetMaxSize * currentSheetIndex;
                                }
                                }
                            }
                        }

                        if(bill == 1) {
                             n = n + 1;
                            Label labelRemark = new Label(1, n, "注意：如果需把excel表导入院内系统， 可按需把上面的“数据汇总表格”和“支付渠道列”删除并保存后，再导入", cellFormat);
                            sheet.mergeCells(1, n, 6, n);
                            sheet.addCell(labelRemark);
                        }

                    }
                    // 写入流
                    workbook.write();
                    //关闭流
                    workbook.close();
                }
            }
            if (ba != null) {
                try {
                    ba.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
            logger.error("ExcelDownloadSrv#generateAndReturnExcelByteArrayOutputStreamWithStyleAndCollect(fileName={}, path={} ):\n {}",
                    fileName, path, e.getMessage(), e);
        }
        return ba;

    }

    /**
     * 订单统计中备注的样式(没有边框样式)
     */
    public static WritableCellFormat getCellFormat4StatRemarkWithNoSetBorder() {
        //设置字体;
        WritableFont font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.DEFAULT_BACKGROUND);

        WritableCellFormat cellFormat = new WritableCellFormat(font);
        try {
            //设置背景颜色;
            cellFormat.setBackground(Colour.WHITE);
            //设置边框;
            // cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
            //设置自动换行;
//            cellFormat.setWrap(true);
            //设置文字居中对齐方式;
            cellFormat.setAlignment(Alignment.LEFT);
            //设置垂直居中;
            cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
            return cellFormat;
        } catch (WriteException e) {
            return null;
        }
    }


    /**
     * 通用的生成 Excel 字节流(包含汇总)并以流返回(一点样式也没有)
     *
     * @param fileName excel 文件名
     * @param data     需要填充的数据，key 是列名，value 是每一列的数据组成的 list, 类型必须是 {@link String}
     * @throws AppMessageException 业务层异常抛出，如传入数据为空
     * @author Walker, added on 06/27/2019.
     */
    public OutputStream generateAndReturnExcelByteArrayOutputStreamWithStyle(String fileName, String path, ExcelDataMap data, ExcelStyleMap excelStyleMap,
                                                                             List<CustomExcelItem> collectList, int bill) throws AppMessageException {
        if (data == null || data.isEmpty()) {
            throw new AppMessageException("数据为空");
        }
        OutputStream ba = null;
        ba = new ByteArrayOutputStream();
        WritableWorkbook workbook = null;
        try {
            // 创建工作表格
            workbook = Workbook.createWorkbook(ba);

            int listSize = 0;
            if (data.size() > 0) {
                Set<String> s1 = data.keySet();
                //开始根据键找值
                for (String key : s1) {
                    List<String> value = data.get(key);
                    if (!Checker.isNone(value)) {
                        int dataItemSize = value.size();
                        if (listSize < dataItemSize) {
                            listSize = dataItemSize;
                        }
                    }
                }
            }
            // 单元数
            int sheetAmount = 0;
            // 每个单元表格存放最大的行数，当list总数超过每个sheet所设置的最大值时，要注意分成多个sheet来存储
            int sheetMaxSize = SHEET_MAX_SIZE;
            if (listSize < sheetMaxSize) {
                sheetAmount = 1;
            } else {
                if (listSize % sheetMaxSize == 0) {
                    sheetAmount = listSize / sheetMaxSize;
                } else {
                    sheetAmount = listSize / sheetMaxSize + 1;
                }
            }
            // 工作表
            for (int i = 0; i < sheetAmount; i++) {
                // 当前页数(从0开始)
                int currentSheetIndex = i;
                // 当前页数(从1开始)
                int sheetIndex = currentSheetIndex + 1;
                WritableSheet sheet = workbook.createSheet(fileName + sheetIndex, currentSheetIndex);
                // 获取带样式的cell
                WritableCellFormat cellFormat = getWritableCellFormat();
                //给sheet电子版中所有的列设置默认的列的宽度;
                sheet.getSettings().setDefaultColumnWidth(20);
                sheet.getSettings().setDefaultRowHeight(30 * 20);

                // 当前页数所对应的行数
                int currentSheetRowAmount = 0;
                if (listSize < sheetMaxSize * sheetIndex) {
                    currentSheetRowAmount = listSize - sheetMaxSize * currentSheetIndex;
                } else {
                    currentSheetRowAmount = sheetMaxSize;
                }
                // 单独应用样式
                if (excelStyleMap != null) {
                    for (String column : excelStyleMap.keySet()) {
                        // 指定列宽
                        int styleColumnWidth = excelStyleMap.getIntValueSafely(column, ExcelStyleMap.STYLE_COLUMN_WIDTH, 0);
                        List<String> columns = new ArrayList<>(data.keySet());
                        for (int j = 0, columnsSize = columns.size(); j < columnsSize; j++) {
                            if (columns.get(j).equals(column)) {
                                sheet.setColumnView(j, styleColumnWidth);
                            }
                        }
                    }
                }
                // 第一行
                int firstRow = 0;
                // 填充表头
                int headerColumn = 0;
                for (String key : data.keySet()) {
                    Label label = new Label(headerColumn, firstRow, key, cellFormat);
                    sheet.addCell(label);
                    List<String> colData = data.get(key);
                    if(!Checker.isNone(colData)) {
                        for (int q = 0; q < currentSheetRowAmount; q++) {
                            // 之前所有页的数据汇总数量
                            int beforePageAllAmount = sheetMaxSize * currentSheetIndex;
                            Label lb = new Label(headerColumn, q + 1, Checker.getStringValue(colData.get(beforePageAllAmount + q)), cellFormat);
                            sheet.addCell(lb);
                        }
                    }
                    headerColumn++;
                }
                // 最后一页，添加汇总，同时关闭流
                if (currentSheetIndex == sheetAmount - 1) {
                    int n = 0;
                    if (!Checker.isNone(collectList)) {
                        for (CustomExcelItem item : collectList) {
                            sheet.addCell(new Label(item.getColumn(), item.getRow() - sheetMaxSize * currentSheetIndex, item.getContent(), item.getCellFormat() == null ? cellFormat : item.getCellFormat()));
                            n = item.getRow() - sheetMaxSize * currentSheetIndex;
                        }
                        if (bill == 1) {

                            n = n + 1;
                            Label labelRemark = new Label(4, n, "注意：如果需把excel表导入院内系统， 可按需把上面的“数据汇总表格”和“支付渠道列”删除并保存后，再导入", cellFormat);
                            sheet.mergeCells(4, n, 9, n);
                            sheet.addCell(labelRemark);
                        }

                    }
                    // 写入流
                    workbook.write();
                    // 关闭流
                    workbook.close();
                }
            }
            if (ba != null) {
                try {
                    ba.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
            logger.error("ExcelDownloadSrv#generateAndReturnExcelByteArrayOutputStreamWithStyleAndCollect(fileName={}, path={} ):\n {}",
                    fileName, path, e.getMessage(), e);
        }
        return ba;
    }

    /**
     * OutputStream 转化成 File
     * @param localPath 对应本地文件路径
     * @param ba 文件流 {@link OutputStream}
     */
    public void outputStreamToFile(String localPath, OutputStream ba) {

        File file = new File(localPath);
        //选择流
        FileOutputStream fos = null;
        ByteArrayInputStream bais = null;
        try {
            bais = new ByteArrayInputStream(((ByteArrayOutputStream) ba).toByteArray());
            fos = new FileOutputStream(file);
            int temp;
            byte[] bt = new byte[1024 * 10];
            while ((temp = bais.read(bt)) != -1) {
                fos.write(bt, 0, temp);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            //关流
            try {
                if (null != fos) {
                    fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}
