package easy_excel

import (
	"errors"
	"fmt"
	"gitee.com/xlizy/common-go/base/common_const"
	"github.com/xuri/excelize/v2"
	"reflect"
	"strconv"
	"strings"
	"time"
)

//文档:https://xuri.me/excelize/zh-hans/base/installation.html

type TestModel struct {
	AreaCode int64     `excel:"label:areaCode;width:100"`
	Reason   time.Time `excel:"label:reason;"`
	Mobile   int64     `excel:"label:mobile;"`
}

// 根据tag获取对应Excel列名
func getExcelLabel(field reflect.StructField) string {
	tag := field.Tag.Get("excel")
	array := strings.Split(tag, ";")
	for i := 0; i < len(array); i++ {
		if strings.Index(array[i], "label:") == 0 {
			return array[i][6:]
		}
	}
	return tag
}

// 根据tag获取对应Excel列名
func getExcelIgnore(field reflect.StructField) bool {
	tag := field.Tag.Get("excel")
	array := strings.Split(tag, ";")
	for i := 0; i < len(array); i++ {
		if strings.Index(array[i], "ignore") == 0 {
			return true
		}
	}
	return false
}
func WriteStructToExcel(filePath string, data interface{}) error {
	// 创建一个新Excel文件
	file := excelize.NewFile()
	// 创建一个工作表
	sheetName := "Sheet1"
	_, _ = file.NewSheet(sheetName)

	// 反射获取传入的切片类型的元素类型（也就是结构体类型）
	v := reflect.ValueOf(data)
	if v.Kind() != reflect.Ptr || v.Elem().Kind() != reflect.Slice {
		return fmt.Errorf("result参数需要是结构体切片的指针类型")
	}
	sliceValue := v.Elem()
	sliceElementType := sliceValue.Type().Elem()

	headers := make([]string, 0)

	style, _ := file.NewStyle(&excelize.Style{
		Font: &excelize.Font{
			Bold: true,
			Size: 14,
		},
		Alignment: &excelize.Alignment{Horizontal: "right"},
	})
	for i := 0; i < sliceElementType.NumField(); i++ {
		fd := sliceElementType.Field(i)
		if !getExcelIgnore(fd) {
			headers = append(headers, fd.Name)
			ec, _ := excelize.CoordinatesToCellName(i+1, 1)
			_ = file.SetCellValue(sheetName, ec, getExcelLabel(fd))
			zb := fmt.Sprintf("%s%v", convertTo26(len(headers)-1), 1)
			l := convertTo26(len(headers) - 1)
			_ = file.SetCellStyle(sheetName, zb, zb, style)
			_ = file.SetColWidth(sheetName, l, l, 15)
		}
	}

	var val reflect.Value
	if reflect.TypeOf(data).Kind() == reflect.Slice {
		val = reflect.ValueOf(data)
	}
	if reflect.TypeOf(data).Kind() == reflect.Ptr {
		val = reflect.ValueOf(data).Elem()
	}
	fmt.Printf("arrayNum:%v\n", val.Len())
	for i := 0; i < val.Len(); i++ {
		ele := val.Index(i)
		for j := 0; j < len(headers); j++ {
			ec, _ := excelize.CoordinatesToCellName(j+1, i+2)
			_ = file.SetCellValue(sheetName, ec, ele.FieldByName(headers[j]).Interface())
		}
	}

	return file.SaveAs(filePath)

}

// ReadExcelToStruct 从Excel文件读取数据到结构体切片
func ReadExcelToStruct(filePath string, result interface{}) (int, error) {
	errorLen := 0
	f, err := excelize.OpenFile(filePath)
	if err != nil {
		return errorLen, err
	}
	defer func(f *excelize.File) {
		_ = f.Close()
	}(f)

	sheetName := f.GetSheetName(f.GetActiveSheetIndex())
	// 获取表头行
	rows, err := f.GetRows(sheetName)
	if err != nil {
		return errorLen, err
	}
	headerRow := rows[0]

	// 获取数据行，从第二行开始（索引为1）
	dataRows := rows[1:]

	// 反射获取传入的切片类型的元素类型（也就是结构体类型）
	v := reflect.ValueOf(result)
	if v.Kind() != reflect.Ptr || v.Elem().Kind() != reflect.Slice {
		return errorLen, fmt.Errorf("result参数需要是结构体切片的指针类型")
	}
	sliceValue := v.Elem()
	sliceElementType := sliceValue.Type().Elem()

	for rl, dataRow := range dataRows {
		errorLen = rl + 2
		// 创建一个结构体实例
		elem := reflect.New(sliceElementType).Elem()
		for i, cellValue := range dataRow {
			field, ok := sliceElementType.FieldByNameFunc(func(name string) bool {
				fd, b2 := sliceElementType.FieldByName(name)
				if b2 {
					tagName := getExcelLabel(fd)
					return strings.EqualFold(tagName, headerRow[i])
				} else {
					return false
				}
			})
			if !ok {
				continue
			}
			fieldValue := elem.FieldByName(field.Name)
			switch fieldValue.Kind() {
			case reflect.String:
				fieldValue.SetString(cellValue)
			case reflect.Bool:
				if strings.EqualFold(strings.ToLower("true"), strings.ToLower(cellValue)) {
					fieldValue.SetBool(true)
				} else {
					fieldValue.SetBool(false)
				}
			case reflect.Int:
				intValue, _ := strconv.Atoi(cellValue)
				fieldValue.SetInt(int64(intValue))
			case reflect.Int64:
				intValue, _ := strconv.ParseInt(cellValue, 10, 64)
				fieldValue.SetInt(intValue)
			case reflect.Float64:
				floatValue, _ := strconv.ParseFloat(cellValue, 64)
				fieldValue.SetFloat(floatValue)
			// 可以根据需要继续添加更多类型的转换处理
			default:
				if field.Type == reflect.TypeOf(time.Time{}) {
					var e error
					var t time.Time
					t, e = time.ParseInLocation(common_const.DataFormat_Excel, cellValue, time.Local)
					if e == nil {
						fieldValue.Set(reflect.ValueOf(t))
					} else {
						t, e = time.ParseInLocation(common_const.DataFormat_YYYY_MM_DD_HH_MM_SS, cellValue, time.Local)
						if e == nil {
							fieldValue.Set(reflect.ValueOf(t))
						} else {
							t, e = time.ParseInLocation(common_const.DataFormat_YYYY_MM_DD, cellValue, time.Local)
							if e == nil {
								fieldValue.Set(reflect.ValueOf(t))
							} else {
								return errorLen, errors.New(fmt.Sprintf("数据格式错误,%s%v", convertTo26(i), errorLen))
							}
						}
					}
				} else {
					fieldValue.SetString(cellValue)
					return errorLen, errors.New(fmt.Sprintf("数据格式错误,%s%v", convertTo26(i), errorLen))
				}
			}
		}
		sliceValue = reflect.Append(sliceValue, elem)
	}
	v.Elem().Set(sliceValue)
	return errorLen, nil
}

func ReadExcelToMap(filePath string, result *[]map[string]string) error {
	f, err := excelize.OpenFile(filePath)
	if err != nil {
		return err
	}
	defer func(f *excelize.File) {
		_ = f.Close()
	}(f)

	sheetName := f.GetSheetName(f.GetActiveSheetIndex())
	// 获取表头行
	rows, err := f.GetRows(sheetName)
	if err != nil {
		return err
	}
	headerRow := rows[0]
	// 获取数据行，从第二行开始（索引为1）
	dataRows := rows[1:]
	for _, dataRow := range dataRows {
		row := make(map[string]string)
		for i, cellValue := range dataRow {

			row[headerRow[i]] = cellValue
		}
		*result = append(*result, row)
	}
	return nil
}

func convertTo26(num int) string {
	var result []byte
	for num != 0 {
		result = append([]byte{byte(num%26) + 'A'}, result...)
		num /= 26
	}
	if len(result) == 0 {
		result = append(result, 'A')
	}
	return string(result)
}