package cn.com.victorysoft.business.util;

import cn.com.victorysoft.business.sys.entity.ExportResult;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils {

    /**
     * 要求excel版本在2007以上
     *
     * @param file 上传的文件信息
     * @param sheetIndex 要读取的sheet索引
     * @return
     * @throws Exception
     */
    public static List<List<Object>> readExcel(MultipartFile file, int sheetIndex) throws Exception {
        File temp = File.createTempFile("temp", null);
        file.transferTo(temp);
        return ExcelUtils.readExcel(temp, sheetIndex);
    }

    /**
     * 要求excel版本在2007以上
     *
     * @param file 文件信息
     * @param sheetIndex 要读取的sheet索引
     * @return
     * @throws Exception
     */
    public static List<List<Object>> readExcel(File file, int sheetIndex) throws Exception {
        if(!file.exists()){
            throw new Exception("找不到文件");
        }
        return readExcel(new FileInputStream(file), sheetIndex);
    }
    /**
     * 要求excel版本在2007以上
     *
     * @param fileInputStream 文件信息
     * @param sheetIndex 要读取的sheet索引
     * @return
     * @throws Exception
     */
    public static List<List<Object>> readExcel(FileInputStream fileInputStream, int sheetIndex) throws Exception {
        List<List<Object>> list = new LinkedList<List<Object>>();
        XSSFWorkbook xwb = new XSSFWorkbook(fileInputStream);
        // 读取第sheetIndex+1张表格内容
        XSSFSheet sheet = xwb.getSheetAt(sheetIndex);
        XSSFRow row;
        XSSFCell cell;
        for (int i = sheet.getFirstRowNum(); i <= (sheet.getPhysicalNumberOfRows() - 1); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
//            int j = row.getFirstCellNum();
            for (int j = 0; j <= row.getLastCellNum(); j++) {
                Object value = null;
                cell = row.getCell(j);
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case STRING:
                            value = cell.getStringCellValue();
                            break;
                        case NUMERIC:
                            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                                //System.out.println(cell.getNumericCellValue()+":日期格式："+cell.getCellStyle().getDataFormatString());
                                value = DateUtil.getJavaDate(cell.getNumericCellValue());
                            } else {
                                //System.out.println(cell.getNumericCellValue()+":格式："+cell.getCellStyle().getDataFormatString());
                                value = cell.getNumericCellValue();
                            }
                            break;
                        case BOOLEAN:
                            value = cell.getBooleanCellValue();
                            break;
                        case FORMULA:
                            try {
                                value = cell.getNumericCellValue();
                            } catch (IllegalStateException e) {
                                value = String.valueOf(cell.getRichStringCellValue());
                            }
                            break;
                        case BLANK:
                            break;
                        default:
                            value = cell.toString();
                    }
                }
                linked.add(value);
            }
            if (linked.size() != 0) {
                for (Object o : linked) {
                    if (o != null && !o.equals("")) {
                        list.add(linked);
                        break;
                    }
                }
            }
        }
        return list;
    }

    /**
     * 导出excel
     * @param excel_name 导出的excel路径（需要带.xlsx)
     * @param headList  excel的标题备注名称
     * @param fieldList excel的标题字段（与数据中map中键值对应）
     * @param dataList  excel数据
     * @throws Exception
     */
    public static void createExcel(String excel_name, String[] headList,
                                   String[] fieldList, List<Map<String, Object>> dataList)
            throws Exception {
        // 创建新的Excel 工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 在Excel工作簿中建一工作表，其名为缺省值
        XSSFSheet sheet = workbook.createSheet();
        // 在索引0的位置创建行（最顶端的行）
        XSSFRow row = sheet.createRow(0);
        // 设置excel头（第一行）的头名称
        for (int i = 0; i < headList.length; i++) {

            // 在索引0的位置创建单元格（左上端）
            XSSFCell cell = row.createCell(i);
            // 定义单元格为字符串类型
            cell.setCellType(CellType.STRING);
            // 在单元格中输入一些内容
            cell.setCellValue(headList[i]);
        }
        // ===============================================================
        //添加数据
        for (int n = 0; n < dataList.size(); n++) {
            // 在索引1的位置创建行（最顶端的行）
            XSSFRow row_value = sheet.createRow(n + 1);
            Map<String, Object> dataMap = dataList.get(n);
            // ===============================================================
            for (int i = 0; i < fieldList.length; i++) {

                // 在索引0的位置创建单元格（左上端）
                XSSFCell cell = row_value.createCell(i);
                // 定义单元格为字符串类型
                cell.setCellType(CellType.STRING);
                // 在单元格中输入一些内容
                cell.setCellValue((dataMap.get(fieldList[i])).toString());
            }
            // ===============================================================
        }
        // 新建一输出文件流
        FileOutputStream fos = new FileOutputStream(excel_name);
        // 把相应的Excel 工作簿存盘
        workbook.write(fos);
        fos.flush();
        // 操作结束，关闭文件
        fos.close();
    }

    public static ExportResult export(JSONArray columnConfig, List<?> datalist, String fileName) {
        //声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        //生成一个表格
        XSSFSheet sheet = workbook.createSheet();

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直

        //设置表格默认列宽度为15个字节
//        sheet.setDefaultColumnWidth((short) 18);
        // 解析表头数据，设置合并单元格，只支持3级表头
        // 列索引
        int colIndex1 = 0;
        // 数据列
        List<String> propertyList = new ArrayList<>();
        // 第一行表头
        List<JSONObject> row1 = new ArrayList<>();
        // 第二行表头
        List<JSONObject> row2 = new ArrayList<>();
        // 第三行表头
        List<JSONObject> row3 = new ArrayList<>();
        // 遍历列
        for (int i = 0; i < columnConfig.size(); i++) {
            JSONObject column = columnConfig.getJSONObject(i);
            // 前端传过来的列可能包含null
            if (column == null || "null".equals(column.toString()) || !column.has("label")) continue;
            column.put("colIndex", colIndex1);
            int rowSpan1 = column.getInt("rowSpan");
            int colSpan1 = column.getInt("colSpan");
            if (rowSpan1 > 3) {
                throw new RuntimeException("只支持3级表头");
            }
            if (rowSpan1 > 1 || colSpan1 > 1) {
                sheet.addMergedRegion(new CellRangeAddress(0, rowSpan1 - 1, colIndex1, colIndex1 + colSpan1 - 1));
            }
            if (column.has("property")) {
                propertyList.add(column.getString("property"));
            }
            row1.add(column);
            // 查找二级表头
            if (column.has("children")) {
                JSONArray children2 = column.getJSONArray("children");
                int colIndex2 = colIndex1;
                for (int j = 0; j < children2.size(); j++) {
                    JSONObject child2 = children2.getJSONObject(j);
                    child2.put("colIndex", colIndex2);
                    int rowSpan2 = child2.getInt("rowSpan");
                    int colSpan2 = child2.getInt("colSpan");
                    if (rowSpan2 > 1 || colSpan2 > 1) {
                        sheet.addMergedRegion(new CellRangeAddress(1, 1 + rowSpan2 - 1, colIndex2, colIndex2 + colSpan2 - 1));
                    }
                    if (child2.has("property")) {
                        propertyList.add(child2.getString("property"));
                    }
                    row2.add(child2);
                    // 查找三级表头
                    if (child2.has("children")) {
                        JSONArray children3 = child2.getJSONArray("children");
                        for (int k = 0; k < children3.size(); k++) {
                            JSONObject child3 = children3.getJSONObject(k);
                            child3.put("colIndex", colIndex2 + k);
                            propertyList.add(child3.getString("property"));
                            row3.add(child3);
                        }
                    }
                    colIndex2 += colSpan2;
                }
            }
            colIndex1 += colSpan1;
        }

        //循环字段名数组，创建标题行
        createTitleCell(sheet, row1, 0, cellStyle);
        if (row2.size() > 0) {
            createTitleCell(sheet, row2, 1, cellStyle);
        }
        if (row3.size() > 0) {
            createTitleCell(sheet, row3, 2, cellStyle);
        }

        int dataRowIndex = row2.size() > 0 ? (row3.size() > 0 ? 3 : 2) : 1;
        // Field缓存
        Map<String, Field> fieldMap = new HashMap<>();
        Map<String, Field> parentFieldMap = new HashMap<>();
        Map<String, Field> childFieldMap = new HashMap<>();
        //创建普通行
        for (int i = 0; i < datalist.size(); i++){
            //因为第一行已经用于创建标题行，故从第二行开始创建
            XSSFRow row = sheet.createRow(i + dataRowIndex);
            //如果是第一行就让其为标题行
            Object targetObj = datalist.get(i);
            for (int j = 0; j < propertyList.size(); j++){
                String property = propertyList.get(j);
                //创建列
                XSSFCell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                try {
                    // 子对象的字段
                    if (property.contains(".")) {
                        String[] array = property.split("\\.");
                        if (array.length > 2) {
                            throw new RuntimeException("只支持一级子对象");
                        }
                        String property1 = array[0];
                        String property2 = array[1];
                        if (parentFieldMap.get(property) == null) {
                            Field parentField = getObjectField(targetObj, property1);
                            parentField.setAccessible(true);
                            parentFieldMap.put(property, parentField);
                        }
                        Object childObject = parentFieldMap.get(property).get(targetObj);
                        if (childObject != null) {
                            if (childFieldMap.get(property) == null) {
                                Field childField = getObjectField(childObject, property2);
                                childField.setAccessible(true);
                                childFieldMap.put(property, childField);
                            }
                            setCellValue(cell, childFieldMap.get(property), childFieldMap.get(property).get(childObject));
                        }
                    }
                    // 基础字段
                    else {
                        if (fieldMap.get(property) == null) {
                            Field field = getObjectField(targetObj, property);
                            field.setAccessible(true);
                            fieldMap.put(property, field);
                        }
                        setCellValue(cell, fieldMap.get(property), fieldMap.get(property).get(targetObj));
                    }
                } catch (IllegalAccessException | NoSuchFieldException e) {
                    e.printStackTrace();
                }
            }
        }

        return export(workbook, fileName);
    }

    public static ExportResult exportMap(JSONArray columnConfig, List<Map<String, Object>> datalist, String fileName) {
        //声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        //生成一个表格
        XSSFSheet sheet = workbook.createSheet();

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直

        //设置表格默认列宽度为15个字节
//        sheet.setDefaultColumnWidth((short) 18);
        // 解析表头数据，设置合并单元格，只支持3级表头
        // 列索引
        int colIndex1 = 0;
        // 数据列
        List<String> propertyList = new ArrayList<>();
        // 第一行表头
        List<JSONObject> row1 = new ArrayList<>();
        // 第二行表头
        List<JSONObject> row2 = new ArrayList<>();
        // 第三行表头
        List<JSONObject> row3 = new ArrayList<>();
        // 遍历列
        for (int i = 0; i < columnConfig.size(); i++) {
            JSONObject column = columnConfig.getJSONObject(i);
            // 前端传过来的列可能包含null
            if (column == null || "null".equals(column.toString()) || !column.has("label")) continue;
            column.put("colIndex", colIndex1);
            int rowSpan1 = column.getInt("rowSpan");
            int colSpan1 = column.getInt("colSpan");
            if (rowSpan1 > 3) {
                throw new RuntimeException("只支持3级表头");
            }
            if (rowSpan1 > 1 || colSpan1 > 1) {
                sheet.addMergedRegion(new CellRangeAddress(0, rowSpan1 - 1, colIndex1, colIndex1 + colSpan1 - 1));
            }
            if (column.has("property")) {
                propertyList.add(column.getString("property"));
            }
            row1.add(column);
            // 查找二级表头
            if (column.has("children")) {
                JSONArray children2 = column.getJSONArray("children");
                int colIndex2 = colIndex1;
                for (int j = 0; j < children2.size(); j++) {
                    JSONObject child2 = children2.getJSONObject(j);
                    child2.put("colIndex", colIndex2);
                    int rowSpan2 = child2.getInt("rowSpan");
                    int colSpan2 = child2.getInt("colSpan");
                    if (rowSpan2 > 1 || colSpan2 > 1) {
                        sheet.addMergedRegion(new CellRangeAddress(1, 1 + rowSpan2 - 1, colIndex2, colIndex2 + colSpan2 - 1));
                    }
                    if (child2.has("property")) {
                        propertyList.add(child2.getString("property"));
                    }
                    row2.add(child2);
                    // 查找三级表头
                    if (child2.has("children")) {
                        JSONArray children3 = child2.getJSONArray("children");
                        for (int k = 0; k < children3.size(); k++) {
                            JSONObject child3 = children3.getJSONObject(k);
                            child3.put("colIndex", colIndex2 + k);
                            propertyList.add(child3.getString("property"));
                            row3.add(child3);
                        }
                    }
                    colIndex2 += colSpan2;
                }
            }
            colIndex1 += colSpan1;
        }

        //循环字段名数组，创建标题行
        createTitleCell(sheet, row1, 0, cellStyle);
        if (row2.size() > 0) {
            createTitleCell(sheet, row2, 1, cellStyle);
        }
        if (row3.size() > 0) {
            createTitleCell(sheet, row3, 2, cellStyle);
        }

        int dataRowIndex = row2.size() > 0 ? (row3.size() > 0 ? 3 : 2) : 1;
        // Field缓存
        //创建普通行
        for (int i = 0; i < datalist.size(); i++){
            //因为第一行已经用于创建标题行，故从第二行开始创建
            XSSFRow row = sheet.createRow(i + dataRowIndex);
            //如果是第一行就让其为标题行
            Map<String, Object> targetObj = datalist.get(i);
            for (int j = 0; j < propertyList.size(); j++){
                String property = propertyList.get(j);
                //创建列
                XSSFCell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                Object value = targetObj.get(property);
                if (value == null) {
                    cell.setCellType(CellType.BLANK);
                }else {
                    String str = String.valueOf(value);
                    if (value instanceof Integer || value instanceof Double || value instanceof Float || value instanceof BigDecimal) {
                        double d = Double.parseDouble(str);
                        cell.setCellType(CellType.NUMERIC);
                        cell.setCellValue(d);
                    }else if (value instanceof Date) {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        str = sdf.format(date);
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue(str);
                    }else {
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue(str);
                    }
                }
            }
        }

        return export(workbook, fileName);
    }

    private static Field getObjectField(Object targetObj, String property) throws NoSuchFieldException {
        Field field = null;
        // 取不到字段时尝试从父级获取
        try {
            field = targetObj.getClass().getDeclaredField(property);
        }catch (NoSuchFieldException e) {
            field = targetObj.getClass().getSuperclass().getDeclaredField(property);
        }
        return field;
    }

    /**
     * 保存到磁盘
     */
    public static ExportResult export(XSSFWorkbook workbook, String fileName) {
        String dirName = UUID.randomUUID().toString();
        String dirPath = PathUtils.getExportPath() + "/" + dirName;
        File dir = new File(dirPath);
        boolean mkdir = dir.mkdir();
        if (mkdir) {
            FileOutputStream fos;
            try {
                String filePath = dirPath + "/" + fileName + ".xlsx";
                fos = new FileOutputStream(filePath);
                workbook.write(fos);
                fos.flush();
                fos.close();
                return new ExportResult(dirName + "/" + fileName + ".xlsx");
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 写入字符串
     */
    public static void writeString(XSSFSheet sheet, String value, int rowIndex, int columnIndex) {
        XSSFRow row = sheet.getRow(rowIndex);
        XSSFCell cell = row.getCell(columnIndex);
        cell.setCellType(CellType.STRING);
        cell.setCellValue(value);
    }

    /**
     * 写入BigDecimal
     */
    public static void writeBigDecimal(XSSFSheet sheet, BigDecimal value, int rowIndex, int columnIndex) {
        XSSFRow row = sheet.getRow(rowIndex);
        XSSFCell cell = row.getCell(columnIndex);
        cell.setCellType(CellType.NUMERIC);
        if (value != null) {
            cell.setCellValue(value.doubleValue());
        }
    }

    /**
     * 写入Double
     */
    public static void writeDouble(XSSFSheet sheet, Double value, int rowIndex, int columnIndex) {
        XSSFRow row = sheet.getRow(rowIndex);
        XSSFCell cell = row.getCell(columnIndex);
        cell.setCellType(CellType.NUMERIC);
        if (value != null) {
            cell.setCellValue(value);
        }
    }

    /**
     * 写入Integer
     */
    public static void writeInteger(XSSFSheet sheet, Integer value, int rowIndex, int columnIndex) {
        XSSFRow row = sheet.getRow(rowIndex);
        XSSFCell cell = row.getCell(columnIndex);
        cell.setCellType(CellType.NUMERIC);
        if (value != null) {
            cell.setCellValue(value);
        }
    }

    /**
     * 写入Short
     */
    public static void writeShort(XSSFSheet sheet, Short value, int rowIndex, int columnIndex) {
        XSSFRow row = sheet.getRow(rowIndex);
        XSSFCell cell = row.getCell(columnIndex);
        cell.setCellType(CellType.NUMERIC);
        if (value != null) {
            cell.setCellValue(value);
        }
    }

    /**
     * 根据字段类型设置单元格格式和内容
     */
    private static void setCellValue(XSSFCell cell, Field field, Object value) {
        if (value == null) {
            cell.setCellType(CellType.BLANK);
            return;
        }
        String str = String.valueOf(value);
        if (BeanUtils.isNumber(field)) {
            double d = Double.parseDouble(str);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(d);
        }else if (BeanUtils.isDate(field)) {
            Date date = (Date) value;
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            str = sdf.format(date);
            cell.setCellType(CellType.STRING);
            cell.setCellValue(str);
        }else {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(str);
        }
    }

    /**
     * 创建表头行
     */
    private static void createTitleCell(XSSFSheet sheet, List<JSONObject> titleCols, int rowIndex, XSSFCellStyle cellStyle) {
        XSSFRow row = sheet.createRow(rowIndex);
        for (JSONObject col : titleCols) {
            //创建列
            int colIndex = col.getInt("colIndex");
            XSSFCell cell = row.createCell(colIndex);
            cell.setCellStyle(cellStyle);
            //设置单元类型为String
            cell.setCellType(CellType.STRING);
            if(!col.has("label")){
                continue;
            }
            cell.setCellValue(col.getString("label"));
            // 设置列宽
            if (col.getInt("colSpan") == 1) {
                if (col.get("width") != null) {
                    sheet.setColumnWidth(colIndex, col.getInt("width") * 35);
                }else if (col.get("realWidth") != null) {
                    sheet.setColumnWidth(colIndex, col.getInt("realWidth") * 35);
                }
            }
        }
    }

}
