package cn.estsh.i3plus.ext.mes.apiservice.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.main.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTConnector;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTShape;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Component
@Slf4j
public class SxExcelExtUtil {
    
    /**
     * 根据文件格式获取对应的Workbook对象
     * @param inputStream
     * @param fileName
     * @return
     */
    public static Workbook getWorkbook(InputStream inputStream, String fileName){
        Workbook workbook = null;
        try {
            String fileType = fileName.substring(fileName.lastIndexOf("."));
            if(".xls".equals(fileType)){
                workbook = new HSSFWorkbook(inputStream);
            }else if(".xlsx".equals(fileType)){
                workbook = new XSSFWorkbook(inputStream);
            }else {
                throw new RuntimeException("解析的文件格式有误！");
            }
        } catch (IOException e) {
            throw new RuntimeException("文件不存在！");
        }
        return workbook;
    }

    public static Workbook getWorkbook(File file){
        FileInputStream inputStream = null;
        Workbook workbook = null;

        try {
            String fileName = file.getName();
            String fileType = fileName.substring(fileName.lastIndexOf("."));

            if (file == null || file.length() == 0L){
                if(".xls".equals(fileType)){
                    return new HSSFWorkbook();
                }else{
                    return new XSSFWorkbook();
                }
            }

            inputStream = new FileInputStream(file);

            if(".xls".equals(fileType)){
                workbook = new HSSFWorkbook(inputStream);
            }else if(".xlsx".equals(fileType)){
                workbook = new XSSFWorkbook(inputStream);
            }else {
                throw new RuntimeException("解析的文件格式有误！");
            }
        } catch (IOException e) {
            throw new RuntimeException("文件不存在！");
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return workbook;
    }

    /**
     * 行复制(多行)
     *
     * @param workbook     文档对象
     * @param sheet        sheet对象
     * @param fromRowIndex 来源下标
     * @param toRowIndex   目标下标
     * @param copyRowNum   复制行数
     */
    public static void copyRow(Workbook workbook, Sheet sheet, int fromRowIndex, int toRowIndex, int copyRowNum, boolean insertFlag
            , Integer colNum) {
        try {
            for (int i = 0; i < copyRowNum; i++) {
                Row fromRow = sheet.getRow(fromRowIndex + i);
                Row toRow = sheet.getRow(toRowIndex + i);
                if (insertFlag) {
                    //复制行超出原有sheet页的最大行数时，不需要移动直接插入
                    if (toRowIndex + i <= sheet.getLastRowNum()) {
                        //先移动要插入的行号所在行及之后的行
                        sheet.shiftRows(toRowIndex + i, sheet.getLastRowNum(), 1, true, false);
                    }
                    //然后再插入行
                    toRow = sheet.createRow(toRowIndex + i);
                    //设置行高
                    toRow.setHeight(fromRow.getHeight());
                }
                for (int colIndex = 0; colIndex < (colNum != null ? colNum : fromRow.getLastCellNum()); colIndex++) {
                    Cell tmpCell = fromRow.getCell(colIndex);
                    if (tmpCell == null) {
                        tmpCell = fromRow.createCell(colIndex);
                        if (colIndex != 0) {
                            copyCell(workbook, fromRow.createCell(colIndex - 1), tmpCell);
                        }
                    }
                    Cell newCell = toRow.createCell(colIndex);
                    copyCell(workbook, tmpCell, newCell);
                }

                //复制合并单元格
                copyMergedRegion(sheet,fromRowIndex+i,toRow);
            }

            //获取形状（线条）
            XSSFDrawing drawing = (XSSFDrawing) sheet.getDrawingPatriarch();
            List<XSSFShape> shapeList = new ArrayList<>();
            Map<Integer, List<XSSFShape>> rowShapeMap = new HashMap<>();
            if (drawing != null) {
                shapeList = drawing.getShapes();
                rowShapeMap = shapeList.stream().filter(x -> x.getAnchor() != null)
                        .collect(Collectors.groupingBy(x -> ((XSSFClientAnchor) x.getAnchor()).getRow1()));
            }
            List<XSSFShape> insertShapeList = new ArrayList<>();
            for (int i = 0; i < copyRowNum; i++) {
                Row toRow = sheet.getRow(toRowIndex + i);

                //复制形状（线条）
                List<XSSFShape> rowShapeList = rowShapeMap.get(fromRowIndex + i);
                if (CollectionUtils.isNotEmpty(rowShapeList)) {
                    for (XSSFShape shape : rowShapeList) {
                        //复制描点
                        XSSFClientAnchor fromAnchor = (XSSFClientAnchor) shape.getAnchor();
                        XSSFClientAnchor toAnchor = new XSSFClientAnchor();
                        toAnchor.setDx1(fromAnchor.getDx1());
                        toAnchor.setDx2(fromAnchor.getDx2());
                        toAnchor.setDy1(fromAnchor.getDy1());
                        toAnchor.setDy2(fromAnchor.getDy2());
                        toAnchor.setRow1(toRow.getRowNum());
                        toAnchor.setRow2(toRow.getRowNum() + fromAnchor.getRow2() - fromAnchor.getRow1());
                        toAnchor.setCol1(fromAnchor.getCol1());
                        toAnchor.setCol2(fromAnchor.getCol2());
                        //复制形状
                        if (shape instanceof XSSFConnector) {
                            copyXSSFConnector((XSSFConnector) shape, drawing, toAnchor);
                        } else if (shape instanceof XSSFSimpleShape) {
                            copyXSSFSimpleShape((XSSFSimpleShape) shape, drawing, toAnchor);
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 复制合并格式
     * @param sheet 工作簿
     * @param fromRowIndex 来源下标
     * @param toRow 目标行
     */
    public static void copyMergedRegion(Sheet sheet,Integer fromRowIndex,Row toRow){
        //获取合并单元格
        List<CellRangeAddress> cellRangeAddressList = sheet.getMergedRegions();
        Map<Integer, List<CellRangeAddress>> rowCellRangeAddressMap = CollectionUtils.isNotEmpty(cellRangeAddressList) ? cellRangeAddressList.stream().collect(Collectors.groupingBy(x ->
                x.getFirstRow())) : new HashMap<>();

        //如果复制来源存在合并单元格
        if (rowCellRangeAddressMap.get(fromRowIndex) != null){
            //复制合并单元格
            List<CellRangeAddress> rowCellRangeAddressList = rowCellRangeAddressMap.get(fromRowIndex);
            if (CollectionUtils.isNotEmpty(rowCellRangeAddressList)) {
                for (CellRangeAddress cellRangeAddress : rowCellRangeAddressList) {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(), (toRow.getRowNum() +
                            (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress
                            .getFirstColumn(), cellRangeAddress.getLastColumn());
                    sheet.addMergedRegionUnsafe(newCellRangeAddress);
                }
            }
        }
    }

    public static void addMergedRegionUnsafe(Sheet sheet, CellRangeAddress ...addresses){
        for (int i = 0; i < addresses.length; i++) {
            sheet.addMergedRegionUnsafe(addresses[i]);
        }
    }

    public static void addMergedRegionUnsafe(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol){
        if (firstRow == lastRow && firstCol == lastCol) return;
        CellRangeAddress cellAddresses = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        addMergedRegionUnsafe(sheet, cellAddresses);
    }

    /**
     * 复制XSSFSimpleShape类
     *
     * @param fromShape
     * @param drawing
     * @param anchor
     * @return
     */
    public static XSSFSimpleShape copyXSSFSimpleShape(XSSFSimpleShape fromShape, XSSFDrawing drawing, XSSFClientAnchor anchor) {
        XSSFSimpleShape toShape = drawing.createSimpleShape(anchor);
        CTShape ctShape = fromShape.getCTShape();
        CTShapeProperties ctShapeProperties = ctShape.getSpPr();
        CTLineProperties lineProperties = ctShapeProperties.isSetLn() ? ctShapeProperties.getLn() : ctShapeProperties.addNewLn();
        CTPresetLineDashProperties dashStyle = lineProperties.isSetPrstDash() ? lineProperties.getPrstDash() : CTPresetLineDashProperties.Factory.newInstance();
        STPresetLineDashVal.Enum dashStyleEnum = dashStyle.isSetVal() ? dashStyle.getVal() : STPresetLineDashVal.Enum.forInt(1);
        CTSolidColorFillProperties fill = lineProperties.isSetSolidFill() ? lineProperties.getSolidFill() : lineProperties.addNewSolidFill();
        CTSRgbColor rgb = fill.isSetSrgbClr() ? fill.getSrgbClr() : CTSRgbColor.Factory.newInstance();
        // 设置形状类型
        toShape.setShapeType(fromShape.getShapeType());
        // 设置线宽
        toShape.setLineWidth(lineProperties.getW() * 1.0 / Units.EMU_PER_POINT);
        // 设置线的风格
        toShape.setLineStyle(dashStyleEnum.intValue() - 1);
        // 设置线的颜色
        byte[] rgbBytes = rgb.getVal();
        if (rgbBytes == null) {
            toShape.setLineStyleColor(0, 0, 0);
        } else {
            toShape.setLineStyleColor(rgbBytes[0], rgbBytes[1], rgbBytes[2]);
        }
        return toShape;
    }

    /**
     * 复制XSSFConnector类
     *
     * @param fromShape
     * @param drawing
     * @param anchor
     * @return
     */
    public static XSSFConnector copyXSSFConnector(XSSFConnector fromShape, XSSFDrawing drawing, XSSFClientAnchor anchor) {
        XSSFConnector toShape = drawing.createConnector(anchor);
        CTConnector ctConnector = fromShape.getCTConnector();
        CTShapeProperties ctShapeProperties = ctConnector.getSpPr();
        CTLineProperties lineProperties = ctShapeProperties.isSetLn() ? ctShapeProperties.getLn() : ctShapeProperties.addNewLn();
        CTPresetLineDashProperties dashStyle = lineProperties.isSetPrstDash() ? lineProperties.getPrstDash() : CTPresetLineDashProperties.Factory.newInstance();
        STPresetLineDashVal.Enum dashStyleEnum = dashStyle.isSetVal() ? dashStyle.getVal() : STPresetLineDashVal.Enum.forInt(1);
        CTSolidColorFillProperties fill = lineProperties.isSetSolidFill() ? lineProperties.getSolidFill() : lineProperties.addNewSolidFill();
        CTSRgbColor rgb = fill.isSetSrgbClr() ? fill.getSrgbClr() : CTSRgbColor.Factory.newInstance();
        // 设置形状类型
        toShape.setShapeType(fromShape.getShapeType());
        // 设置线宽
        toShape.setLineWidth(lineProperties.getW() * 1.0 / Units.EMU_PER_POINT);
        // 设置线的风格
        toShape.setLineStyle(dashStyleEnum.intValue() - 1);
        // 设置线的颜色
        byte[] rgbBytes = rgb.getVal();
        if (rgbBytes == null) {
            toShape.setLineStyleColor(0, 0, 0);
        } else {
            toShape.setLineStyleColor(rgbBytes[0], rgbBytes[1], rgbBytes[2]);
        }
        return toShape;
    }

    /**
     * 复制单元格
     *
     * @param srcCell
     * @param distCell
     */
    public static void copyCell(Workbook workbook, Cell srcCell, Cell distCell) {
        CellStyle newStyle = workbook.createCellStyle();
        copyCellStyle(srcCell.getCellStyle(), newStyle, workbook);
        //样式
        distCell.setCellStyle(newStyle);
        //设置内容
        CellType srcCellType = srcCell.getCellTypeEnum();
        distCell.setCellType(srcCellType);
        if (srcCellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(srcCell)) {
                distCell.setCellValue(srcCell.getDateCellValue());
            } else {
                distCell.setCellValue(srcCell.getNumericCellValue());
            }
        } else if (srcCellType == CellType.STRING) {
            distCell.setCellValue(srcCell.getRichStringCellValue());
        } else if (srcCellType == CellType.BOOLEAN) {
            distCell.setCellValue(srcCell.getBooleanCellValue());
        } else if (srcCellType == CellType.ERROR) {
            distCell.setCellErrorValue(srcCell.getErrorCellValue());
        } else if (srcCellType == CellType.FORMULA) {
            distCell.setCellFormula(srcCell.getCellFormula());
        } else {
        }
    }

    /**
     * 复制一个单元格样式到目的单元格样式
     *
     * @param fromStyle
     * @param toStyle
     */
    public static void copyCellStyle(CellStyle fromStyle, CellStyle toStyle, Workbook workbook) {
        //水平垂直对齐方式
        toStyle.setAlignment(fromStyle.getAlignmentEnum());
        toStyle.setVerticalAlignment(fromStyle.getVerticalAlignmentEnum());
        //边框和边框颜色
        toStyle.setBorderBottom(fromStyle.getBorderBottomEnum());
        toStyle.setBorderLeft(fromStyle.getBorderLeftEnum());
        toStyle.setBorderRight(fromStyle.getBorderRightEnum());
        toStyle.setBorderTop(fromStyle.getBorderTopEnum());
        toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
        toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
        toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
        toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
        //背景和前景
        if (fromStyle instanceof XSSFCellStyle) {
            XSSFCellStyle xssfToStyle = (XSSFCellStyle) toStyle;
            xssfToStyle.setFillBackgroundColor(((XSSFCellStyle) fromStyle).getFillBackgroundColorColor());
            xssfToStyle.setFillForegroundColor(((XSSFCellStyle) fromStyle).getFillForegroundColorColor());
        } else {
            toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
            toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
        }
        toStyle.setDataFormat(fromStyle.getDataFormat());
        toStyle.setFillPattern(fromStyle.getFillPatternEnum());
        if (fromStyle instanceof XSSFCellStyle) {
            toStyle.setFont(((XSSFCellStyle) fromStyle).getFont());
        } else if (fromStyle instanceof HSSFCellStyle) {
            toStyle.setFont(((HSSFCellStyle) fromStyle).getFont(workbook));
        }
        toStyle.setHidden(fromStyle.getHidden());
        //首行缩进
        toStyle.setIndention(fromStyle.getIndention());
        toStyle.setLocked(fromStyle.getLocked());
        //旋转
        toStyle.setRotation(fromStyle.getRotation());
        toStyle.setWrapText(fromStyle.getWrapText());
    }

    /**
     * 设置单元格值
     * @param row 行
     * @param cellIndexs 设置的列下标 (为null时下标按列值索引获取)
     * @param cellValues 设置的列值
     */
    public static void setCellValue(Row row, Integer[] cellIndexs, String[] cellValues) {
        for (int i = 0; i < (cellIndexs == null ? cellValues.length : cellIndexs.length) ; i++) {
            if (null == cellValues || i == cellValues.length) break;
            Cell cell = row.getCell( (cellIndexs==null? i:cellIndexs[i]) );
            if (cell == null){
                cell = row.createCell( (cellIndexs==null? i:cellIndexs[i]) ,CellType.STRING);
            }
            cell.setCellValue(cellValues[i]);
        }
    }

    /**
     * 设置行样式
     * @param style 样式
     * @param font 字体
     * @param rows 行
     */
    public static void setCellStyle(CellStyle style,Font font,Row ...rows) {
        for (int i = 0; i < rows.length; i++) {
            if (rows[i] == null){
                continue;
            }
            for (int j = 0; j < rows[i].getLastCellNum(); j++) {
                setCellStyle(style,font,rows[i].getCell(j));
            }
        }
    }

    /**
     * 设置单元格样式
     * @param style 样式
     * @param font 字体
     * @param cells 单元格
     */
    public static void setCellStyle(CellStyle style,Font font,Cell ...cells) {
        for (int i = 0; i < cells.length; i++) {
            if (font!=null){
                style.setFont(font);
            }
            cells[i].setCellStyle(style);
        }
    }

    public static class SetCellStyle{

        private Row[] rows = null;
        private Cell[] cells = null;
        private List<Method> executeMethods = new ArrayList<>();

        private Workbook workbook;
        private Font font;

        public SetCellStyle() {
            this.font = workbook.createFont();
        }

        public SetCellStyle(Row[] rows, Cell[] cells) {
            this.rows = rows;
            this.cells = cells;
        }
        public SetCellStyle(Font font) {
            this.font = font;
        }
        public SetCellStyle(Workbook workbook) {
            this.workbook = workbook;
            this.font = workbook.createFont();
        }
        public SetCellStyle(Workbook workbook, Row[] rows, Cell[] cells) {
            this.workbook = workbook;
            this.font = workbook.createFont();
            this.rows = rows;
            this.cells = cells;
        }
        public SetCellStyle(Font font, Row[] rows, Cell[] cells) {
            this.font = font;
            this.rows = rows;
            this.cells = cells;
        }

        public Row[] getRows() {
            return rows;
        }
        public SetCellStyle setRows(Row ...rows) {
            this.rows = rows;
            return this;
        }
        public Cell[] getCells() {
            return cells;
        }
        public SetCellStyle setCells(Cell ...cells) {
            this.cells = cells;
            return this;
        }
        public Workbook getWorkbook() {
            return workbook;
        }
        public void setWorkbook(Workbook workbook) {
            this.workbook = workbook;
        }
        public Font getFont() {
            return font;
        }
        public SetCellStyle setFont(Font font) {
            this.font = font;
            return this;
        }

        //内部链式方式

        //边框中等实线
        public SetCellStyle borderThin(){
            try {
                executeMethods.add(this.getClass().getDeclaredMethod("setBorderThin", CellStyle.class));
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            }
            return this;
        }

        //边框中等实线
        public SetCellStyle borderMedium(){
            try {
                executeMethods.add(this.getClass().getDeclaredMethod("setBorderMedium", CellStyle.class));
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            }
            return this;
        }

        //左上对齐
        public SetCellStyle alignmentLeftTop(){
            try {
                executeMethods.add(this.getClass().getDeclaredMethod("setAlignmentLeftTop", CellStyle.class));
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            }
            return this;
        }

        //垂直水平居中
        public SetCellStyle alignmentCenter(){
            try {
                executeMethods.add(this.getClass().getDeclaredMethod("setAlignmentCenter", CellStyle.class));
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            }
            return this;
        }

        //加粗
        public SetCellStyle bold(boolean bold){
            this.font.setBold(bold);
            return this;
        }

        //加粗
        public SetCellStyle bold(){
            this.font.setBold(true);
            return this;
        }

        //颜色
        public SetCellStyle color(short color){
            this.font.setColor(color);
            return this;
        }

        //字体大小
        public SetCellStyle fontHeightInPoints(int fontSize){
            this.font.setFontHeightInPoints((short)fontSize);
            return this;
        }

        //外部方法

        //边框中等实线
        public void setBorderThin(CellStyle style){
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
        }

        public void setBorderMedium(CellStyle style){
            style.setBorderTop(BorderStyle.MEDIUM);
            style.setBorderBottom(BorderStyle.MEDIUM);
            style.setBorderLeft(BorderStyle.MEDIUM);
            style.setBorderRight(BorderStyle.MEDIUM);
        }

        //左上对齐
        public void setAlignmentLeftTop(CellStyle style){
            style.setAlignment(HorizontalAlignment.LEFT);
            style.setVerticalAlignment(VerticalAlignment.TOP);
        }

        //垂直水平居中
        public void setAlignmentCenter(CellStyle style){
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
        }

        //执行
        public SetCellStyle execute(){
            if (this.rows !=null && this.rows.length >0) {
                for (int i = 0; i < this.rows.length; i++) {
                    if (rows[i] == null)
                        continue;
                    for (int j = 0; j < rows[i].getLastCellNum(); j++) {
                        execute(rows[i].getCell(j));
                    }
                }
            }
            if (this.cells !=null && this.cells.length >0){
                execute(cells);
            }
            return this;
        }

        private void execute(Cell ...cells){
            if (cells==null) return;

            CellStyle style = null;
            for (int i = 0; i < cells.length; i++) {
                if (cells[i] == null)
                    continue;

                style = cells[i].getCellStyle();
                for (int k = 0; k < this.executeMethods.size(); k++) {
                    Method method = this.executeMethods.get(k);
                    try {
                        method.invoke(this, style);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                    }
                }
                if (this.font != null){
                    style.setFont(this.font);
                }
                cells[i].setCellStyle(style);
            }
        }

        public SetCellStyle clear(){
            this.rows = null;
            this.cells = null;
            this.executeMethods = new ArrayList<>();
            this.font = workbook.createFont();
            return this;
        }
    }

}
