package com.zt.life.modules.mainPart.utils;


import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.spire.doc.*;
import com.spire.doc.documents.ParagraphStyle;
import com.zt.core.oss.service.ISysOssService;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.io.*;
import java.util.HashMap;
import java.util.Map;

public class LuckyWordUtils {

    private static Document doc;

    public static void Testwords() {
        doc = new Document();
    }

    public static void excelDatatoWordTab(HttpServletResponse title, String newFileDir, String newFileName, String excelData) {
        System.out.println(excelData);
        excelData = excelData.replace("
", "\\r\\n");//去除luckysheet中 &#xA 的换行
        JSONArray jsonArray = (JSONArray) JSONObject.parse(excelData);
        for (int sheetIndex = 0; sheetIndex < jsonArray.size(); sheetIndex++) {
            JSONObject jsonObject = (JSONObject) jsonArray.get(sheetIndex);
            JSONArray celldataObjectList = jsonObject.getJSONArray("celldata");
            JSONArray rowObjectList = jsonObject.getJSONArray("visibledatarow");
            JSONArray colObjectList = jsonObject.getJSONArray("visibledatacolumn");
            JSONArray dataObjectList = jsonObject.getJSONArray("data");
            JSONObject mergeObject = jsonObject.getJSONObject("config").getJSONObject("merge");//合并单元格
            JSONObject columnlenObject = jsonObject.getJSONObject("config").getJSONObject("columnlen");//表格列宽
            JSONObject rowlenObject = jsonObject.getJSONObject("config").getJSONObject("rowlen");//表格行高
            JSONArray borderInfoObjectList = jsonObject.getJSONObject("config").getJSONArray("borderInfo");//边框样式
            Testwords();
            Section sec = doc.addSection();
            Table tb = sec.addTable(true);

            int rowsize =  colObjectList.size();
            if (rowsize>60)
                rowsize = 20;
            rowsize = 6;
            for (int i = 0; i < rowsize; i++) {
                TableRow row = tb.addRow(i);
                try {
                    row.setHeight(Float.parseFloat(rowlenObject.get(i) + ""));
                } catch (Exception e) {
                    row.setHeight(20f);
                }

                int colsize =  colObjectList.size();
                if (colsize>60)
                    colsize = 60;
                for (int j = 0; j < colsize; j++) {
//                    if (columnlenObject != null && columnlenObject.getInteger(j + "") != null) {
//                        sec.makeColumnsSameWidth();
//                    }
                    row.addCell(true);
                }
            }

            setCellValue(celldataObjectList, borderInfoObjectList, sec, doc, tb);

            // 判断路径是否存在
            File dir = new File(newFileDir);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            OutputStream out = null;
            try {
//                out = new FileOutputStream(newFileDir + newFileName);
//                System.out.println(out);
//                out.close();
//
                doc.saveToFile(newFileDir + newFileName);

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

    private static void setMergeAndColorByObject(JSONObject jsonObjectValue, XSSFSheet sheet, Table tb, XSSFCellStyle style) {
        JSONObject mergeObject = (JSONObject) jsonObjectValue.get("mc");
        if (mergeObject != null) {
            int r = (int) (mergeObject.get("r"));
            int c = (int) (mergeObject.get("c"));
            if ((mergeObject.get("rs") != null && (mergeObject.get("cs") != null))) {
                int rs = (int) (mergeObject.get("rs"));
                int cs = (int) (mergeObject.get("cs"));
                CellRangeAddress region = new CellRangeAddress(r, r + rs - 1, (short) (c), (short) (c + cs - 1));
                sheet.addMergedRegion(region);
            }
        }

        if (jsonObjectValue.getString("bg") != null) {
            int bg = Integer.parseInt(jsonObjectValue.getString("bg").replace("#", ""), 16);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);    //设置填充方案
            style.setFillForegroundColor(new XSSFColor(new Color(bg)));  //设置填充颜色
        }

    }

    private static void setBorder(JSONArray borderInfoObjectList, Document doc, Table tb) {
        //设置边框样式map
        Map<Integer, BorderStyle> bordMap = new HashMap<>();
        bordMap.put(1, BorderStyle.THIN);
        bordMap.put(2, BorderStyle.HAIR);
        bordMap.put(3, BorderStyle.DOTTED);
        bordMap.put(4, BorderStyle.DASHED);
        bordMap.put(5, BorderStyle.DASH_DOT);
        bordMap.put(6, BorderStyle.DASH_DOT_DOT);
        bordMap.put(7, BorderStyle.DOUBLE);
        bordMap.put(8, BorderStyle.MEDIUM);
        bordMap.put(9, BorderStyle.MEDIUM_DASHED);
        bordMap.put(10, BorderStyle.MEDIUM_DASH_DOT);
        bordMap.put(11, BorderStyle.MEDIUM_DASH_DOT_DOT); //.MEDIUM_DASH_DOT_DOTC
        bordMap.put(12, BorderStyle.SLANTED_DASH_DOT);
        bordMap.put(13, BorderStyle.THICK);

        //一定要通过 cell.getCellStyle()  不然的话之前设置的样式会丢失
        //设置边框
        if (borderInfoObjectList != null) {
            for (int i = 0; i < borderInfoObjectList.size(); i++) {
                JSONObject borderInfoObject = (JSONObject) borderInfoObjectList.get(i);
                if (borderInfoObject.get("rangeType").equals("cell")) {//单个单元格
                    JSONObject borderValueObject = borderInfoObject.getJSONObject("value");

                    JSONObject l = borderValueObject.getJSONObject("l");
                    JSONObject r = borderValueObject.getJSONObject("r");
                    JSONObject t = borderValueObject.getJSONObject("t");
                    JSONObject b = borderValueObject.getJSONObject("b");


                    int row = borderValueObject.getInteger("row_index");
                    int col = borderValueObject.getInteger("col_index");

                    TableCell cell = tb.getRows().get(row).getCells().get(col);

                    if (l != null) {

                        //设置表格的左边框
                        tb.getTableFormat().getBorders().getLeft().setBorderType(com.spire.doc.documents.BorderStyle.None);
                        /*cell.getCellStyle().setBorderLeft(bordMap.get((int) l.get("style"))); //左边框*/
                        int bg = Integer.parseInt(l.getString("color").replace("#", ""), 16);
                        /*cell.getCellStyle().setLeftBorderColor(new XSSFColor(new Color(bg)));//左边框颜色*/
                        tb.getTableFormat().getBorders().getLeft().setColor(new Color(bg));
                    }
                    if (r != null) {
                        //设置表格的右边框
                        tb.getTableFormat().getBorders().getRight().setBorderType(com.spire.doc.documents.BorderStyle.None);
                        /*cell.getCellStyle().setBorderRight(bordMap.get((int) r.get("style"))); //右边框*/
                        int bg = Integer.parseInt(r.getString("color").replace("#", ""), 16);
                        tb.getTableFormat().getBorders().getRight().setColor(new Color(bg));
                        /*cell.getCellStyle().setRightBorderColor(new XSSFColor(new Color(bg)));//右边框颜色*/
                    }
                    if (t != null) {
                        //设置表格的顶部边框
                        tb.getTableFormat().getBorders().getTop().setBorderType(com.spire.doc.documents.BorderStyle.None);
                        /*cell.getCellStyle().setBorderTop(bordMap.get((int) t.get("style"))); //顶部边框*/
                        int bg = Integer.parseInt(t.getString("color").replace("#", ""), 16);
                        /*cell.getCellStyle().setTopBorderColor(new XSSFColor(new Color(bg)));//顶部边框颜色*/
                        tb.getTableFormat().getBorders().getTop().setColor(new Color(bg));
                    }
                    if (b != null) {
                        //设置表格的底部边框
                        tb.getTableFormat().getBorders().getBottom().setBorderType(com.spire.doc.documents.BorderStyle.None);
                        /*cell.getCellStyle().setBorderBottom(bordMap.get((int) b.get("style"))); //底部边框*/
                        int bg = Integer.parseInt(b.getString("color").replace("#", ""), 16);
                        /*cell.getCellStyle().setBottomBorderColor(new XSSFColor(new Color(bg)));//底部边框颜色*/
                        tb.getTableFormat().getBorders().getBottom().setColor(new Color(bg));
                    }
                } else if (borderInfoObject.get("rangeType").equals("range")) {//选区
                    int bg_ = Integer.parseInt(borderInfoObject.getString("color").replace("#", ""), 16);
                    int style_ = borderInfoObject.getInteger("style");

                    JSONObject rangObject = (JSONObject) ((JSONArray) (borderInfoObject.get("range"))).get(0);

                    JSONArray rowList = rangObject.getJSONArray("row");
                    JSONArray columnList = rangObject.getJSONArray("column");


                    for (int row_ = rowList.getInteger(0); row_ < rowList.getInteger(rowList.size() - 1) + 1; row_++) {
                        for (int col_ = columnList.getInteger(0); col_ < columnList.getInteger(columnList.size() - 1) + 1; col_++) {
                            TableCell cell = tb.getRows().get(row_).getCells().get(col_);
                            tb.getTableFormat().getBorders().getLeft().setBorderType(com.spire.doc.documents.BorderStyle.None);
                            /*cell.getCellStyle().setBorderLeft(bordMap.get(style_));*/ //左边框
                            tb.getTableFormat().getBorders().getLeft().setColor(new Color(bg_));
                            /*cell.getCellStyle().setLeftBorderColor(new XSSFColor(new Color(bg_)));*///左边框颜色
                            tb.getTableFormat().getBorders().getRight().setBorderType(com.spire.doc.documents.BorderStyle.None);
                            /*cell.getCellStyle().setBorderRight(bordMap.get(style_));*/ //右边框
                            tb.getTableFormat().getBorders().getRight().setColor(new Color(bg_));
                            /*cell.getCellStyle().setRightBorderColor(new XSSFColor(new Color(bg_)));*///右边框颜色
                            tb.getTableFormat().getBorders().getTop().setBorderType(com.spire.doc.documents.BorderStyle.None);
                            /* cell.getCellStyle().setBorderTop(bordMap.get(style_));*/ //顶部边框
                            tb.getTableFormat().getBorders().getTop().setColor(new Color(bg_));
                            /*cell.getCellStyle().setTopBorderColor(new XSSFColor(new Color(bg_)));*///顶部边框颜色
                            tb.getTableFormat().getBorders().getBottom().setBorderType(com.spire.doc.documents.BorderStyle.None);
                            /*cell.getCellStyle().setBorderBottom(bordMap.get(style_)); *///底部边框
                            tb.getTableFormat().getBorders().getBottom().setColor(new Color(bg_));
                            /*cell.getCellStyle().setBottomBorderColor(new XSSFColor(new Color(bg_)));*///底部边框颜色

                        }
                    }
                }
            }
        }
    }

    private static void setCellValue(JSONArray jsonObjectList, JSONArray borderInfoObjectList, Section sec, Document doc, Table tb) {
        //设置字体大小和颜色
        Map<Integer, String> fontMap = new HashMap<>();
        fontMap.put(-1, "Arial");
        fontMap.put(0, "Times New Roman");
        fontMap.put(1, "Arial");
        fontMap.put(2, "Tahoma");
        fontMap.put(3, "Verdana");
        fontMap.put(4, "微软雅黑");
        fontMap.put(5, "宋体");
        fontMap.put(6, "黑体");
        fontMap.put(7, "楷体");
        fontMap.put(8, "仿宋");
        fontMap.put(9, "新宋体");
        fontMap.put(10, "华文新魏");
        fontMap.put(11, "华文行楷");
        fontMap.put(12, "华文隶书");

        for (int index = 0; index < jsonObjectList.size(); index++) {
            //Section section = doc.getSections().get(0);
            //Table table = section.getTables().get(0);
            /*XSSFCellStyle style = doc.addParagraphStyle();
             *//*XSSFFont font = workbook.createFont();//字体样式*/
            ParagraphStyle font = new ParagraphStyle(doc);
            //tb = sec.addTable(true);


            JSONObject object = jsonObjectList.getJSONObject(index);
            if ((int) object.get("r")>5)
                continue;
            if ((int)object.get("c")>59)
                continue;
            String str_ = (int) object.get("r") + "_" + object.get("c") + "=" + ((JSONObject) object.get("v")).get("v") + "\n";
            JSONObject jsonObjectValue = ((JSONObject) object.get("v"));

            String value = "";
            if (jsonObjectValue != null && jsonObjectValue.get("v") != null) {
                value = jsonObjectValue.getString("v");
            }

            if (tb.getRows().get((int) object.get("r")) != null && tb.getRows().get((int) object.get("r")).getCells().get((int) object.get("c")) != null) {
                TableCell cell = tb.getRows().get((int) object.get("r")).getCells().get((int) object.get("c"));
//                if (jsonObjectValue != null && jsonObjectValue.get("f") != null) {//如果有公式，设置公式
//                    value = jsonObjectValue.getString("f");
//                    cell.setCellWidthType(CellWidthType.valueOf(value.substring(1, value.length())));//不需要=符号
//                }
                //合并单元格与填充单元格颜色
//                setMergeAndColorByObject(jsonObjectValue, tb, style);

                //填充值
                cell.addParagraph().appendText(value);
//                cell.setCellWidthType(CellWidthType.valueOf(value));
//                TableRow row = tb.getRows().get((int) object.get("r"));

                /*//设置垂直水平对齐方式
                int vt = jsonObjectValue.getInteger("vt") == null ? 1 : jsonObjectValue.getInteger("vt");//垂直对齐	 0 中间、1 上、2下
                int ht = jsonObjectValue.getInteger("ht") == null ? 1 : jsonObjectValue.getInteger("ht");//0 居中、1 左、2右
                switch (vt) {
                    case 0:
                        style.setVerticalAlignment(VerticalAlignment.CENTER); //XSSFCellStyle.VERTICAL_CENTER
                        break;
                    case 1:
                        style.setVerticalAlignment(VerticalAlignment.TOP); //XSSFCellStyle.VERTICAL_TOP
                        break;
                    case 2:
                        style.setVerticalAlignment(VerticalAlignment.BOTTOM); //XSSFCellStyle.VERTICAL_BOTTOM
                        break;
                }
                switch (ht) {
                    case 0:
                        style.setAlignment(HorizontalAlignment.CENTER); //XSSFCellStyle.ALIGN_CENTER
                        break;
                    case 1:
                        style.setAlignment(HorizontalAlignment.LEFT); //XSSFCellStyle.ALIGN_LEFT
                        break;
                    case 2:
                        style.setAlignment(HorizontalAlignment.RIGHT); //XSSFCellStyle.ALIGN_RIGHT
                        break;
                }*/


                //设置合并单元格的样式有问题
//                String ff = jsonObjectValue.getString("ff");//0 Times New Roman、 1 Arial、2 Tahoma 、3 Verdana、4 微软雅黑、5 宋体（Song）、6 黑体（ST Heiti）、7 楷体（ST Kaiti）、 8 仿宋（ST FangSong）、9 新宋体（ST Song）、10 华文新魏、11 华文行楷、12 华文隶书
//                int fs = jsonObjectValue.getInteger("fs") == null ? 14 : jsonObjectValue.getInteger("fs");//字体大小
//                int bl = jsonObjectValue.getInteger("bl") == null ? 0 : jsonObjectValue.getInteger("bl");//粗体	0 常规 、 1加粗
//                int it = jsonObjectValue.getInteger("it") == null ? 0 : jsonObjectValue.getInteger("it");//斜体	0 常规 、 1 斜体
//                String fc = jsonObjectValue.getString("fc") == null ? "" : jsonObjectValue.getString("fc");//字体颜色
//                font.setName(fontMap.get(ff));//字体名字


                /*if (fc.length() > 0) {
                    font.setColor(new XSSFColor(new Color(Integer.parseInt(fc.replace("#", ""), 16))));
                }
                *//*font.setFontName(ff);//字体名字*//*
                font.setName(ff);
                *//*font.((short) fs);setFontHeightInPoints((short) fs);//字体大小*//*
                if (bl == 1) {
                    //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
                }
                font.setItalic(it == 1 ? true : false);//斜体*/


                /*style.setFont((org.apache.poi.ss.usermodel.Font) font);
                style.setWrapText(true);//设置自动换行
                cell.setCellStyle(style);*/

            } else {
                System.out.println("错误的=" + index + ">>>" + str_);
            }


        }
        //设置边框
        //setBorder(borderInfoObjectList, doc, tb);

    }

    public static void downLuckySheetXlsxByPOI(HttpServletResponse response, String title, String newFileDir, String newFileName) {
        try {
            // path是指欲下载的文件的路径。

            String path = newFileDir + newFileName;

            File file = new File(path);
            // 取得文件名。
            String filename = newFileName;
            if (StringUtils.isEmpty(newFileName)) {
                filename = file.getName();
            }

            // 取得文件的后缀名。
            String ext = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();

            // 以流的形式下载文件。
            InputStream fis = new BufferedInputStream(new FileInputStream(path));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            // 清空response
            response.reset();
            // 设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes()));
            response.addHeader("Content-Length", "" + file.length());
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/octet-stream");
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }
}