package com.yh.saas.plugin.base.service.impl;

import com.yh.saas.plugin.base.entity.view.ExportCellStyleView;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Component;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLDecoder;
import java.nio.charset.StandardCharsets;

@Component
@Slf4j
public class ExportCommonStylesImpl {

    /**
     * 设置打印参数
     *
     * @param headerTitle        头部文字
     * @param wb
     * @param topMargin          上边距
     * @param bottomMargin       下边距
     * @param leftMargin         左边距
     * @param rightMargin        右边距
     * @param horizontallyCenter 水平居中
     * @param printGridlines     是否显示网络线
     * @param autobreaks         调整位置
     * @param displayGridlines   是否打印网络线
     * @return
     */
    public HSSFSheet setSheet(String headerTitle, HSSFWorkbook wb, short topMargin, short bottomMargin,
                              short leftMargin, short rightMargin, boolean horizontallyCenter, boolean printGridlines, boolean autobreaks,
                              boolean displayGridlines) {

        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(headerTitle);
        //
        // 设置打印参数
        sheet.setMargin(HSSFSheet.TopMargin, topMargin);// 页边距（上）
        sheet.setMargin(HSSFSheet.BottomMargin, bottomMargin);// 页边距（下）
        sheet.setMargin(HSSFSheet.LeftMargin, leftMargin);// 页边距（左）
        sheet.setMargin(HSSFSheet.RightMargin, rightMargin);// 页边距（右）

        // 设置水平居中
        sheet.setHorizontallyCenter(horizontallyCenter);
        // 设置是否显示网络线
        sheet.setDisplayGridlines(displayGridlines);
        // 设置是否打印网络线
        sheet.setPrintGridlines(printGridlines);
        sheet.setAutobreaks(autobreaks);
        return sheet;
    }

    /**
     * 设置打印对象
     *
     * @param sheet
     * @param landscape 打印方向，true：横向，false：纵向(默认)
     * @param scale     设置缩放比例
     * @param paperSize 纸张类型
     * @return
     */
    public HSSFPrintSetup setPrintSetup(HSSFSheet sheet, Boolean landscape, short scale, short paperSize) {
        // 创建打印设置对象
        HSSFPrintSetup ps = sheet.getPrintSetup();
        // 打印方向，true：横向，false：纵向(默认)
        ps.setLandscape(landscape);
        // 设置缩放比例
        ps.setScale(scale);
        // 纸张类型
        ps.setPaperSize(paperSize);
        return ps;
    }

    /**
     * 设置字体
     *
     * @param fontName   字体
     * @param fontPoints 字号
     * @param wb
     * @return
     */
    @SuppressWarnings("deprecation")
    public HSSFFont setFont(String fontName, short boldweight, int fontPoints, HSSFWorkbook wb) {
        HSSFFont font = wb.createFont();
        font.setFontName(fontName);
        if (boldweight != 0) {
            font.setBoldweight(boldweight);
        }
        font.setFontHeightInPoints((short) fontPoints);
        return font;
    }

    /**
     * 设置单元格样式
     *
     * @param view
     * @param wb
     * @return
     */
    @SuppressWarnings("deprecation")
    public HSSFCellStyle setCellStyle(ExportCellStyleView view, HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 指定单元格靠水平对齐
        if (view.getAlignment() != 0) {
            cellStyle.setAlignment(view.getAlignment());
        }
        // 指定单元格垂直对齐
        if (view.getVerticalAlignment() != 0) {
            cellStyle.setVerticalAlignment(view.getVerticalAlignment());
        }
        // 下边框
        if (view.getBorderBottom() != 0) {
            cellStyle.setBorderBottom(view.getBorderBottom());
        }
        // 左边框
        if (view.getBorderLeft() != 0) {
            cellStyle.setBorderLeft(view.getBorderLeft());
        }
        // 上边框
        if (view.getBorderTop() != 0) {
            cellStyle.setBorderTop(view.getBorderTop());
        }
        // 右边框
        if (view.getBorderRight() != 0) {
            cellStyle.setBorderRight(view.getBorderRight());
        }
        // 自动换行
        cellStyle.setWrapText(view.getWrapText());
        // 设置字体
        cellStyle.setFont(view.getFont());
        // 设置单元格样式
        if (view.getDataFormat() != 0) {
            cellStyle.setDataFormat(view.getDataFormat());
        }
        // 设置背景色
        if (view.getBackGroundColor() != 0) {
            cellStyle.setFillForegroundColor(view.getBackGroundColor());
        }
        if (view.getFillPattern() != 0) {
            cellStyle.setFillPattern(view.getFillPattern());
        }
        return cellStyle;
    }

    /**
     * 创建通用EXCEL头部
     *
     * @param headString 头部显示的字符
     */
    @SuppressWarnings("deprecation")
    public void createNormalHead(String compName, String headString, HSSFWorkbook wb, HSSFSheet sheet, int count) {

        HSSFRow row0 = sheet.createRow(0);

        CellRangeAddress cra;
        // 设置第一行(设置图片用)
        @SuppressWarnings("unused")
        HSSFCell cell0 = row0.createCell(1);
        row0.setHeightInPoints(5);

        // 第一行
        HSSFRow row1 = sheet.createRow(1);
        // 数据行样式
        HSSFCellStyle styleCell1 = wb.createCellStyle();
        // 指定单元格居中对齐
        styleCell1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        styleCell1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        styleCell1.setWrapText(true);
        // 设置单元格字体
        HSSFFont font2 = wb.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 16);
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        styleCell1.setFont(font2);

        // 设置第一行
        HSSFCell cell1 = row1.createCell(1);
        cell1.setCellStyle(styleCell1);
        if (StringUtils.isNotEmpty(compName)) {
            cell1.setCellValue(compName);
        }
        row1.setHeightInPoints(25);

        // 指定合并区域
        cra = new CellRangeAddress(1, 1, (short) 1, (short) count);
        sheet.addMergedRegion(cra);

        // 第二行
        HSSFRow row2 = sheet.createRow(2);

        // 设置第一行
        HSSFCell cell2 = row2.createCell(1);
        row2.setHeightInPoints(25);
        // 定义单元格为字符串类型
        cell2.setCellType(HSSFCell.ENCODING_UTF_16);
        if (StringUtils.isNotEmpty(headString)) {
            cell2.setCellValue(new HSSFRichTextString(headString));
        }
        // 指定合并区域
        cra = new CellRangeAddress(2, 2, (short) 1, (short) count);
        sheet.addMergedRegion(cra);

        HSSFCellStyle styleTitle = wb.createCellStyle();
        // 指定单元格居中对齐
        styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        styleTitle.setWrapText(true);
        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font2.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 14);
        styleTitle.setFont(font);

        cell2.setCellStyle(styleTitle);
    }

    /**
     * 创建通用EXCEL头部(中运)
     * count表格列数要大于4列
     *
     * @param headString 头部显示的字符
     */
    @SuppressWarnings("deprecation")
    public void createNormalHead1(String title, String titleCode, String headString, HSSFWorkbook wb, HSSFSheet sheet, int count) {

        HSSFRow row0 = sheet.createRow(0);

        CellRangeAddress cra;
        // 设置第一行(设置图片用)
        @SuppressWarnings("unused")
        HSSFCell cell0 = row0.createCell(1);
        row0.setHeightInPoints(5);

        // 第一行
        HSSFRow row1 = sheet.createRow(1);
        // 数据行样式
        HSSFCellStyle styleCell1 = wb.createCellStyle();
        // 指定单元格居中对齐
        styleCell1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        styleCell1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        styleCell1.setWrapText(true);
        // 设置单元格字体
        HSSFFont font2 = wb.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 16);
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        styleCell1.setFont(font2);

        // 数据行样式2
        HSSFCellStyle styleCell2 = wb.createCellStyle();
        // 指定单元格居左对齐
        styleCell2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        // 指定单元格垂直居中对齐
        styleCell2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        styleCell2.setWrapText(true);
        // 设置单元格字体
        HSSFFont font3 = wb.createFont();
        font3.setFontName("宋体");
        font3.setFontHeightInPoints((short) 10);
        styleCell2.setFont(font3);

        // 数据行样式3
        HSSFCellStyle styleCell3 = wb.createCellStyle();
        // 指定单元格居中对齐
        styleCell3.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        // 指定单元格垂直居中对齐
        styleCell3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        styleCell3.setWrapText(true);
        styleCell3.setFont(font3);

        // 標題列
        HSSFCell cell1 = row1.createCell(1);
        if (StringUtils.isNotEmpty(title)) {
            cell1.setCellValue(title);
        }
        cell1.setCellStyle(styleCell2);
        row1.setHeightInPoints(15);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, (short) 1, (short) (count - 5)));
        cell1 = row1.createCell((short) (count - 4));
        if (StringUtils.isNotEmpty(titleCode)) {
            cell1.setCellValue(titleCode);
        }
        cell1.setCellStyle(styleCell3);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, (short) (count - 4), (short) (count - 1)));

        // 表名列
        HSSFRow row2 = sheet.createRow(2);
        // 设置第一行
        HSSFCell cell2 = row2.createCell(1);
        row2.setHeightInPoints(25);
        // 定义单元格为字符串类型
        cell2.setCellType(HSSFCell.ENCODING_UTF_16);
        if (StringUtils.isNotEmpty(headString)) {
            cell2.setCellValue(new HSSFRichTextString(headString));
        }
        // 指定合并区域
        cra = new CellRangeAddress(2, 2, (short) 1, (short) count);
        sheet.addMergedRegion(cra);

        HSSFCellStyle styleTitle = wb.createCellStyle();
        // 指定单元格居中对齐
        styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        styleTitle.setWrapText(true);
        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font2.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 14);
        styleTitle.setFont(font);

        cell2.setCellStyle(styleTitle);
    }

    /**
     * 下载
     *
     * @param path
     * @param response
     */
    public void download(String path, HttpServletResponse response, HSSFWorkbook wb) {

        try {

            // path是指欲下载的文件的路径。
            File file = new File(path);
            // 取得文件名。
            String fileName = file.getName();
            // 给文件名编码
            fileName = new String(fileName.getBytes("GBK"), "ISO-8859-1");

            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);

            // 定义byte输出流
            ByteArrayOutputStream out = new ByteArrayOutputStream();

            // 写导出文件
            wb.write(out);

            // 取得模板文件中的数据
            byte[] result = out.toByteArray();

            // 设置输出数据类型
            response.setContentType("application/vnd.ms-excel");
            // 设置输出数据长度
            response.setContentLength(result.length);

            // 设置文件名称
            response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
            // 将文件流输出到画面
            response.getOutputStream().write(result);

            out.flush();
            out.close();

        } catch (IOException ex) {
            log.debug(ex.getMessage());
        }

    }

    /**
     * 绘制公司logo图片
     *
     * @param workBook
     * @param patriarch
     * @param logoPath
     * @param a         起始列
     * @param b         起始行
     * @param c         终止列
     * @param d         终止行
     * @throws Exception
     */
    @SuppressWarnings("static-access")
    public void createPictureLogo(HSSFWorkbook workBook, HSSFPatriarch patriarch, String logoPath, int a, int b, int c,
                                  int d) throws Exception {
        try {

            if (logoPath.startsWith("http")) {
                URL url = new URL(logoPath);
                InputStream input;
                String imgType = logoPath.substring(logoPath.lastIndexOf(".") + 1, logoPath.length());
                // 打开链接
                HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                conn.setRequestMethod("GET");
                conn.setRequestProperty("User-Agent", "Mozilla/4.0 compatible; MSIE 5.0;Windows NT; DigExt)");
                conn.setConnectTimeout(5 * 1000);
                input = conn.getInputStream();

                BufferedImage bi = ImageIO.read(input);
                File file = new File(logoPath);
                input.close();

                // 定义签名照片在sheet中的位置 参数说明见本方法最后
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) a, b, (short) c, d);

                // 定义字节输出流
                ByteArrayOutputStream bos = new ByteArrayOutputStream();

                // 将照片写入输出字节流中
                ImageIO.write(bi, imgType.toUpperCase(), bos);

                if ("jpg".equals(imgType) || "jpeg".equals(imgType)) {
                    // 创建照片
                    patriarch.createPicture(anchor, workBook.addPicture(bos.toByteArray(), workBook.PICTURE_TYPE_JPEG));
                } else if ("png".equals(imgType)) {
                    // 创建照片
                    patriarch.createPicture(anchor, workBook.addPicture(bos.toByteArray(), workBook.PICTURE_TYPE_PNG));
                }
            } else {

                // 取得照片路径可为png、jpg格式
                String picture = java.net.URLDecoder.decode(logoPath, "UTF-8");
                // 取得图片类型
                String imgType = picture.substring(picture.lastIndexOf(".") + 1, picture.length());
                File f = new File(picture);
                if (f.exists()) {
                    // 读取照片
                    BufferedImage BufferImg = ImageIO.read(f);

                    // 定义签名照片在sheet中的位置 参数说明见本方法最后
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) a, b, (short) c, d);

                    // 定义字节输出流
                    ByteArrayOutputStream bos = new ByteArrayOutputStream();

                    // 将照片写入输出字节流中
                    ImageIO.write(BufferImg, imgType.toUpperCase(), bos);

                    if ("jpg".equals(imgType) || "jpeg".equals(imgType)) {
                        // 创建照片
                        patriarch.createPicture(anchor, workBook.addPicture(bos.toByteArray(), workBook.PICTURE_TYPE_JPEG));
                    } else if ("png".equals(imgType)) {
                        // 创建照片
                        patriarch.createPicture(anchor, workBook.addPicture(bos.toByteArray(), workBook.PICTURE_TYPE_PNG));
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    /**
     * 放入图片(泽瑞)
     *
     * @param wb
     * @param sheet
     * @param picPath
     * @param startRow
     * @param startCol
     * @param endRow
     * @param endCol
     */
    @SuppressWarnings("null")
    public void setOnPicture(HSSFWorkbook wb, HSSFSheet sheet, String picPath, int startRow, short startCol,
                             int endRow, short endCol, String localPath) {
        FileOutputStream fileOut = null;
        BufferedImage bufferImg = null;
        // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        // 将图片读到BufferedImage
        if (StringUtils.isNotBlank(picPath)) {
            try {
                picPath = localPath + URLDecoder.decode(picPath, String.valueOf(StandardCharsets.UTF_8));

                bufferImg = ImageIO.read(new File(picPath));
                // 取得图片类型
                String imgType = picPath.substring(picPath.lastIndexOf(".") + 1, picPath.length());
                ImageIO.write(bufferImg, imgType, byteArrayOut);

                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                /**
                 * 该构造函数有8个参数 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
                 * 后四个参数，前两个表示图片左上角所在的cellNum和 rowNum，后两个参数对应的表示图片右下角所在的cellNum和 rowNum，
                 * excel中的cellNum和rowNum的index都是从0开始的
                 *
                 */
                // 图片一导出到单元格
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, startCol, startRow, endCol, endRow);
                // 插入图片
                patriarch.createPicture(anchor,
                        wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

                fileOut.close();
                byteArrayOut.close();

            } catch (IOException e1) {

                e1.printStackTrace();
            } catch (Exception e) {

                e.printStackTrace();
            }
        }
    }
}
