cas_tt_cloud_backend/lib/excel.go

package lib

import (
	"SciencesServer/utils"
	"errors"
	"fmt"
	"github.com/tealeg/xlsx"
	"reflect"
	"strings"
)

type Excel struct {
	File *xlsx.File

	name    string
	sheet   string
	header  string
	title   []string
	content *ExcelContent

	body []map[string]string
}

type ExcelContent struct {
	Kind    ExcelContentKind
	Content []interface{}
}

type ExcelContentKind int

const (
	ExcelContentKindForStruct ExcelContentKind = iota + 1
	ExcelContentKindForSlice
	ExcelContentKindForText
)

type OptionExcel func(excel *Excel)

func WithExcelName(name string) OptionExcel {
	return func(excel *Excel) {
		excel.name = name
	}
}

func WithExcelSheet(sheet string) OptionExcel {
	return func(excel *Excel) {
		excel.sheet = sheet
	}
}

func WithExcelHeader(header string) OptionExcel {
	return func(excel *Excel) {
		excel.header = header
	}
}

func WithExcelTitle(title []string) OptionExcel {
	return func(excel *Excel) {
		excel.title = title
	}
}

func WithExcelContent(content *ExcelContent) OptionExcel {
	return func(excel *Excel) {
		excel.content = content
	}
}

// Export 导出
func (this *Excel) Export() error {
	this.File = xlsx.NewFile()
	sheet, _ := this.File.AddSheet(this.sheet)
	row := new(xlsx.Row)
	cell := new(xlsx.Cell)

	style := xlsx.NewStyle()

	style.Alignment = xlsx.Alignment{
		Horizontal: "center",
		Vertical:   "center",
	}
	if this.header != "" {
		row = sheet.AddRow()
		row.SetHeight(28.0)
		cell = row.AddCell()
		cell.HMerge = 1 // 横向合并
		//cell.VMerge = 1 // 纵向合并
		cell.Value = this.header
		cell.SetStyle(style)

		if this.title != nil && len(this.title) > 0 {
			cell.HMerge = len(this.title) - 1
		}
	}
	if this.title != nil && len(this.title) > 0 {
		row = sheet.AddRow()

		for _, v := range this.title {
			cell = row.AddCell()
			cell.Value = v
		}
	}
	if this.content != nil {
		for _, v := range this.content.Content {
			row = sheet.AddRow()

			if this.content.Kind == ExcelContentKindForStruct {
				row.WriteStruct(v, -1)
				continue
			}
			if this.content.Kind == ExcelContentKindForSlice {
				row.WriteSlice(v, -1)
				continue
			}
			for i := 0; i < len(this.title); i++ {
				cell = row.AddCell()
				// TODO：存在不同类型，请自行加判断
				switch v.(type) {
				case []string:
					cell.Value = v.([]string)[i]
					break
				case []int:
					cell.Value = fmt.Sprintf("%v", v.([]int)[i])
					break
				case []interface{}:
					cell.Value = fmt.Sprintf("%v", v.([]interface{})[i])
					break
				default:
					return errors.New("未知数据类型")
				}
			}
		}
	}
	return nil
}

// Import 导入
// @file：文件信息
// @headers：头部标题数量
// @sheet：读取的sheet，0：第一页
func (this *Excel) Import(file string, headers, sheet int) error {
	_file, err := xlsx.OpenFile(file)

	if err != nil {
		return err
	}
	if len(_file.Sheets) < sheet {
		return errors.New("异常sheet")
	}
	out := make([]map[string]string, 0)

	_sheet := _file.Sheets[sheet]

	local := 0

	if headers > 0 {
		local += headers
	}
	for i := local + 1; i < len(_sheet.Rows); i++ {
		body := _sheet.Rows[i].Cells
		_data := make(map[string]string, 0)

		for key, cell := range _sheet.Rows[local].Cells {
			_data[cell.String()] = body[key].String()
		}
		out = append(out, _data)
	}
	this.body = out

	return nil
}

// Import 导入
// @file：文件信息
// @headers：标题数量
//func (this *Excel) Import(file string, headers int) error {
//	_file, err := xlsx.OpenFile(file)
//
//	if err != nil {
//		return err
//	}
//	out := make(map[string][]map[string]string, 0)
//	// 循环Sheet
//	for _, sheet := range _file.Sheets {
//		data := make([]map[string]string, 0)
//
//		local := 0
//
//		if headers > 0 {
//			local += headers
//		}
//		for i := local + 1; i < len(sheet.Rows); i++ {
//			body := sheet.Rows[i].Cells
//			_data := make(map[string]string, 0)
//
//			for key, cell := range sheet.Rows[local].Cells {
//				_data[cell.String()] = body[key].String()
//			}
//			data = append(data, _data)
//		}
//		out[sheet.Name] = data
//	}
//	this.body = out
//
//	return nil
//}

// Save 保存
func (this *Excel) Save(path string) error {
	return this.File.Save(this.name + ".xlsx")
	isExist, err := utils.PathExists(path)

	if err != nil {
		return err
	}
	if !isExist {
		if err = utils.MkdirAll(path); err != nil {
			return err
		}
	}
	fmt.Printf("%s/%s.xlsx", path, this.name)
	return nil
	//return this.Save(fmt.Sprintf("%s\%s.xlsx", path, this.name))
}

// Analysis 解析
// @dist：标识对象
func (this *Excel) Analysis(dist interface{}) []interface{} {
	if dist == nil {
		return nil
	}
	out := make([]interface{}, len(this.body))
	// 原指针
	_dist := reflect.TypeOf(dist)

	// 指针类型获取真正type需要调用Elem
	if _dist.Kind() == reflect.Ptr {
		_dist = _dist.Elem()
	}
	for key, val := range this.body {
		tRef := reflect.New(_dist)

		fieldNum := tRef.Elem().NumField()

		for k, v := range val {
			for i := 0; i < fieldNum; i++ {
				// 匹配结构字段名称
				if k != strings.ToLower(tRef.Elem().Type().Field(i).Tag.Get("xlsx")) {
					continue
				}
				vRef := tRef.Elem().FieldByName(tRef.Elem().Type().Field(i).Name)

				if !vRef.CanSet() {
					continue
				}
				switch vRef.Type().String() {
				case "string":
					vRef.SetString(v)
					break
				case "int", "int32", "int64":
					vRef.SetInt(utils.StringToInt64(v))
					break
				case "uint", "uint32", "uint64":
					vRef.SetUint(utils.StringToUnit64(v))
					break
				case "float", "float32", "float64":
					f, _ := utils.StringToFloat(v)
					vRef.SetFloat(f)
					break
				default:
					break
				}
			}
		}
		out[key] = tRef.Interface()
	}
	return out
}

func NewExcel(options ...OptionExcel) *Excel {
	out := new(Excel)

	for _, opt := range options {
		opt(out)
	}
	return out
}