xlsx/imports/xlsx.go

package imports

import (
	"fmt"
	"io"
	"reflect"
	"strconv"
	"strings"
	"time"

	"github.com/xuri/excelize/v2"
)

type Importer struct {
	Reader   io.Reader
	Sheets   []string //Sheet页
	xlsxFile *excelize.File
	startRow int // 从第几行开始读取数据
	Data     interface{}
}

func NewFileImporter(file string, data any, start_row int) (*Importer, error) {
	f, err := excelize.OpenFile(file)
	if err != nil {
		return nil, err
	}
	sheets := f.GetSheetList()
	if start_row <= 0 {
		start_row = 1
	}
	return &Importer{
		xlsxFile: f,
		Sheets:   sheets,
		Data:     data,
		startRow: start_row,
	}, nil
}

func NewFileReaderImporter(reader io.Reader, data any, start_row int) (*Importer, error) {
	f, err := excelize.OpenReader(reader)
	if err != nil {
		return nil, err
	}
	sheets := f.GetSheetList()
	if start_row <= 0 {
		start_row = 1
	}
	return &Importer{
		xlsxFile: f,
		Sheets:   sheets,
		Data:     data,
		startRow: start_row,
	}, nil
}

func (i *Importer) Import(sheetIndex int) error {
	v := reflect.ValueOf(i.Data)
	if v.Kind() != reflect.Pointer {
		return fmt.Errorf("data must be pointer")
	}
	switch v.Elem().Kind() {
	case reflect.Slice:
		return i.dealFile(sheetIndex)
	default:
		return fmt.Errorf("data must be slice")
	}
}

func (i *Importer) dealFile(sheetIndex int) error {
	sheet := i.Sheets[sheetIndex]
	rows, err := i.xlsxFile.Rows(sheet)
	if err != nil {
		return err
	}
	vdata := reflect.ValueOf(i.Data).Elem()
	if vdata.Type().Elem().Kind() != reflect.Struct {
		return fmt.Errorf("element must be struct")
	}
	testData := reflect.New(vdata.Type().Elem())
	tempDic := make(map[int]string)
	for i := 0; i < testData.Elem().NumField(); i++ {
		t := testData.Elem().Type().Field(i)
		tag, ok := t.Tag.Lookup("import")
		if ok {
			tagMap := dealTag(tag)
			index, _ := strconv.Atoi(tagMap["index"])
			tempDic[index] = t.Name
		}
	}
	rowIndex := 0
	for rows.Next() {
		rowIndex += 1
		// 从第几行开始读取数据
		if rowIndex >= i.startRow {
			row, err := rows.Columns()
			if err != nil {
				return err
			}
			newData := reflect.New(vdata.Type().Elem())
			for colIndex := range row {
				colIndex += 1
				cellName, _ := excelize.CoordinatesToCellName(colIndex, rowIndex)
				value, _ := i.xlsxFile.GetCellValue(sheet, cellName)
				if fieldName, ok := tempDic[colIndex]; ok {
					field := newData.Elem().FieldByName(fieldName)
					switch field.Kind() {
					case reflect.String:
						field.SetString(value)
					case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64,
						reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64, reflect.Uintptr:
						intValue, _ := strconv.Atoi(value)
						field.SetInt(int64(intValue))
					case reflect.Float32, reflect.Float64:
						floatValue, _ := strconv.ParseFloat(value, 64)
						field.SetFloat(floatValue)
					case reflect.Bool:
						boolValue, _ := strconv.ParseBool(value)
						field.SetBool(boolValue)
					// 如果是结构体，只支持时间类型，并且结构体的字段类型必须是time.Time，不能为自定的时间类型，并且excel里面的时间格式必须是yyyy-mm-dd或者yyyy-mm-dd hh:mm:ss
					case reflect.Struct:
						t := time.Time{}
						if strings.Contains(value, ":") {
							t, _ = time.Parse(time.DateTime, value)
						} else {
							t, _ = time.Parse(time.DateOnly, value)
						}
						field.Set(reflect.ValueOf(t))
					}
				}
			}
			vdata.Set(reflect.Append(vdata, newData.Elem()))
		}
	}
	if err = rows.Close(); err != nil {
		return err
	}
	return nil
}

func dealTag(tag string) map[string]string {
	tag_list := strings.Split(tag, ",")
	tag_map := make(map[string]string)
	for _, tag := range tag_list {
		tag_kv := strings.Split(tag, ":")
		if len(tag_kv) != 2 {
			continue
		}
		tag_map[tag_kv[0]] = tag_kv[1]
	}
	return tag_map
}