package cn.com.capcloud.core.execl.xlsx;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Author LiaoJiaHui
 * @Create 2024/7/4 16:42
 */
public class ExcelOneUtil {

    public static void exportExcel(String templatePath, String outputPath, Map<String, Object>  dataMap){
        try {
            // 读取Excel模板
            FileInputStream fis = new FileInputStream(templatePath);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet sheet = workbook.getSheetAt(0);

            // 替换表头和表尾
            replaceTemplateList(sheet, dataMap);
            replacePlaceholdersStr(sheet, dataMap);

            // 保存输出Excel
            FileOutputStream fos = new FileOutputStream(outputPath);
            workbook.write(fos);
            fos.close();
            workbook.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static void replacePlaceholdersStr(Sheet sheet, Map<String, Object> dataMap) {
        // 遍历模板中的每一行和每一个单元格
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == CellType.STRING) {
                    String cellValue = cell.getStringCellValue();
                    // 替换字符串格式的占位符
                    for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
                        String placeholder = "{{" + entry.getKey() + "}}";
                        if (cellValue.contains(placeholder)) {
                            // 替换占位符
                            cellValue = cellValue.replace(placeholder, String.valueOf(entry.getValue()));
                            if (isNumeric(cellValue)) {
                                if (cellValue.contains(".")) {
                                    //System.out.println(cellValue + " 是一个浮点数。");
                                    cell.setCellValue(Double.valueOf(cellValue));
                                } else if (cellValue.length()<=8){//长度大于8为字符串
                                    //System.out.println(cellValue + " 是一个整数。");
                                    cell.setCellValue(Long.valueOf(cellValue));
                                }else{
                                    cell.setCellValue(cellValue);
                                }
                            } else {
                                //System.out.println(cellValue + " 不是一个有效的数字。");
                                cell.setCellValue(cellValue);
                            }
                        }
                    }
                }
            }
        }
    }
    private static void replaceTemplateList(Sheet sheet, Map<String, Object> dataMap) {
        for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
            int startRow = findRowWithPlaceholder(sheet, "{{?"+entry.getKey()+"}}");
            int endRow = findRowWithPlaceholder(sheet, "{{/"+entry.getKey()+"}}");
            List<Map<String, Object>> dataList = null;
            try {
                dataList = (List<Map<String, Object>>) entry.getValue();
            } catch (Exception e) {
                continue;
            }
            if (startRow != -1 && endRow != -1) {
                List<String> rowKeyTemplate=new ArrayList<>();
                Row rowTemplate = sheet.getRow(startRow+1);
                for (Cell cell : rowTemplate) {
                    String cellValue = cell.getStringCellValue();
                    rowKeyTemplate.add(cellValue);
                }
                // 插入数据行
                int currentRow = startRow;
                sheet.shiftRows(currentRow, sheet.getLastRowNum(), dataList.size());//插入渲染的空行：dataList.size()
                for (Map<String, Object> data : dataList) {
                    int rowIndex=currentRow;
                    Row row = sheet.createRow(rowIndex);
                    int cellIndex = 0;
                    for (int i = 0; i < rowKeyTemplate.size(); i++) {
                        String templateKey=String.valueOf(rowKeyTemplate.get(i));
                        String key=templateKey.replace("{{","").replace("}}","");
                        Cell cell = row.createCell(cellIndex++);
                        String str=String.valueOf(data.get(key));
                        if(str.equals("null")){
                            str="";
                        }
                        // 替换占位符
                        if (isNumeric(str)) {
                            if (str.contains(".")) {
                                //System.out.println(cellValue + " 是一个浮点数。");
                                cell.setCellValue(Double.valueOf(str));
                            } else if (str.length()<=8){//长度大于8为字符串
                                //System.out.println(cellValue + " 是一个整数。");
                                cell.setCellValue(Long.valueOf(str));
                            }else{
                                cell.setCellValue(str);
                            }
                        } else {
                            //System.out.println(cellValue + " 不是一个有效的数字。");
                            cell.setCellValue(str);
                        }
                        copyCellStyle(rowTemplate.getCell(i), cell);
                    }
                    rowTemplate=sheet.getRow(startRow);
                    currentRow++;
                }
                startRow = findRowWithPlaceholder(sheet, "{{?"+entry.getKey()+"}}");
                endRow = findRowWithPlaceholder(sheet, "{{/"+entry.getKey()+"}}");
                for (int i = startRow; i <= endRow; endRow--) {//删掉模板行
                    sheet.removeRow(sheet.getRow(i));
                    try {
                        sheet.shiftRows(startRow + 1, sheet.getLastRowNum(), -1);
                    } catch (Exception e) {
                        System.out.println("删除行异常，tay catch 抓了");
                        //异常了，可能是底部没；留空行导致，暂时
                    }
                }
            }
        }
    }

    private static int findRowWithPlaceholder(Sheet sheet, String placeholder) {
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains(placeholder)) {
                    return row.getRowNum();
                }
            }
        }
        return -1;
    }

    private static void copyRow(Sheet sheet, Row srcRow, Row destRow) {
        for (int i = 0; i < srcRow.getLastCellNum(); i++) {
            Cell oldCell = srcRow.getCell(i);
            Cell newCell = destRow.createCell(i);
            if (oldCell != null) {
                newCell.setCellStyle(oldCell.getCellStyle());
                switch (oldCell.getCellType()) {
                    case STRING:
                        newCell.setCellValue(oldCell.getStringCellValue());
                        break;
                    case NUMERIC:
                        newCell.setCellValue(oldCell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        newCell.setCellValue(oldCell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        newCell.setCellFormula(oldCell.getCellFormula());
                        break;
                    default:
                        break;
                }
            }
        }
    }
    private static void copyCellStyle(Cell source, Cell target) {
        Workbook workbook = source.getSheet().getWorkbook();
        CellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(source.getCellStyle());
        target.setCellStyle(newCellStyle);
    }
    public static boolean isNumeric(String str) {
        return str.matches("-?\\d+(\\.\\d+)?");
    }
}
