+ AutoMapping

tribe-health · Jan 17, 2022 · 952a0e9 · 952a0e9
1 parent 2fb6698
commit 952a0e9
Show file tree

Hide file tree

Showing 6 changed files with 210 additions and 25 deletions.
diff --git a/importer/from/source.go b/importer/from/source.go
@@ -1,21 +1,42 @@
 package from
 
+const (
+	// TUnknown 未知
+	TUnknown byte = iota
+	// TBool bool
+	TBool
+	// TDatetime 日期时间
+	TDatetime
+	// TError 错误
+	TError
+	// TNumber 数字
+	TNumber
+	// TString 字符串
+	TString
+)
+
 // Source 导入文件接口
 type Source interface {
 	Data(page int, size int) []map[string]interface{}
 	Columns() []Column
-	Bind(mapping Mapping)
+	Inspect() Inspect
+	Bind()
 	Close() error
 }
 
 // Column 源数据列
 type Column struct {
 	Name string
-	Type string
+	Type byte
 	Col  int
 	Row  int
-	Pos  string
+	Axis string
 }
 
-// Mapping 数值映射表
-type Mapping struct{}
+// Inspect 基础信息
+type Inspect struct {
+	SheetName  string
+	SheetIndex int
+	ColStart   int
+	RowStart   int
+}
diff --git a/importer/importer.go b/importer/importer.go
@@ -9,6 +9,7 @@ import (
 
 	jsoniter "github.com/json-iterator/go"
 	"github.com/yaoapp/kun/exception"
+	"github.com/yaoapp/kun/utils"
 	"github.com/yaoapp/xiang/config"
 	"github.com/yaoapp/xiang/importer/from"
 	"github.com/yaoapp/xiang/share"
@@ -49,12 +50,53 @@ func Select(name string) *Importer {
 }
 
 // AutoMapping 根据文件信息获取字段映射表
-func (imp *Importer) AutoMapping(src from.Source) *from.Mapping {
+func (imp *Importer) AutoMapping(src from.Source) *Mapping {
+	sourceColumns := getSourceColumns(src)
+	sourceInspect := src.Inspect()
+	mapping := &Mapping{
+		Columns:      []*Binding{},
+		AutoMatching: true,
+		Sheet:        sourceInspect.SheetName,
+		ColStart:     sourceInspect.ColStart,
+		RowStart:     sourceInspect.RowStart,
+	}
+
+	for i := range imp.Columns {
+		col := imp.Columns[i].ToMap()
+		name, ok := col["name"].(string)
+		if !ok {
+			continue
+		}
+		binding := Binding{Name: "", Axis: "", Rules: []string{}, Field: name, Label: imp.Columns[i].Label}
+		for _, suggest := range imp.Columns[i].Match {
+			if srcCol, has := sourceColumns[suggest]; has {
+				binding = Binding{
+					Label: imp.Columns[i].Label,
+					Field: name,
+					Name:  srcCol.Name,
+					Axis:  srcCol.Axis,
+					Rules: imp.Columns[i].Rules,
+				}
+				continue
+			}
+		}
+		mapping.Columns = append(mapping.Columns, &binding)
+	}
+
+	utils.Dump(mapping)
 	return nil
 }
 
-// Preview 预览数据
-func (imp *Importer) Preview(src from.Source, page int, size int) []map[string]interface{} {
+// MappingPreview 预览字段映射关系
+func (imp *Importer) MappingPreview(src from.Source) *Mapping {
+
+	// 读取文件结构指纹
+	// tpl := imp.Fingerprint(src)
+	// 查找已有模板
+
+	// 自动匹配
+	imp.AutoMapping(src)
+
 	return nil
 }
 
@@ -68,12 +110,12 @@ func (imp *Importer) MappingSetting(src from.Source) []map[string]interface{} {
 	return nil
 }
 
-// Fingerprint 导入文件指纹
+// Fingerprint 文件结构指纹
 func (imp *Importer) Fingerprint(src from.Source) string {
 	keys := []string{}
 	columns := src.Columns()
 	for _, col := range columns {
-		keys = append(keys, fmt.Sprintf("%s|%s", col.Name, col.Type))
+		keys = append(keys, fmt.Sprintf("%s|%d", col.Name, col.Type))
 	}
 	sort.Strings(keys)
 	hash := sha256.New()
@@ -90,3 +132,29 @@ func (imp *Importer) Run() {}
 
 // Start 运行导入(异步)
 func (imp *Importer) Start() {}
+
+// getSourceColumns 读取源数据字段映射表
+func getSourceColumns(src from.Source) map[string]from.Column {
+	res := map[string]from.Column{}
+	columns := src.Columns()
+	for _, col := range columns {
+		name := col.Name
+		if name != "" {
+			res[name] = col
+		}
+	}
+	return res
+}
+
+// getColumns 读取目标字段映射表
+func (imp *Importer) getColumns() map[string]*Column {
+	columns := map[string]*Column{}
+	for i := range imp.Columns {
+		colmap := imp.Columns[i].ToMap()
+
+		if name, ok := colmap["name"].(string); ok && name != "" {
+			columns[name] = &imp.Columns[i]
+		}
+	}
+	return columns
+}
diff --git a/importer/importer_test.go b/importer/importer_test.go
@@ -1,24 +1,34 @@
 package importer
 
 import (
-	"fmt"
 	"path/filepath"
 	"testing"
 
 	"github.com/stretchr/testify/assert"
+	"github.com/yaoapp/kun/utils"
 	"github.com/yaoapp/xiang/config"
 	"github.com/yaoapp/xiang/importer/xlsx"
 )
 
 func TestLoad(t *testing.T) {
 	LoadFrom("not a path", "404.")
-	assert.IsType(t, &Importer{}, Select("manu"))
+	assert.IsType(t, &Importer{}, Select("order"))
 }
-func TestFingerprint(t *testing.T) {
+func TestFingerprintSimple(t *testing.T) {
 	simple := filepath.Join(config.Conf.Root, "imports", "assets", "simple.xlsx")
 	file := xlsx.Open(simple)
 	defer file.Close()
-	imp := Select("manu")
-	fp := imp.Fingerprint(file)
-	fmt.Println(fp)
+	imp := Select("order")
+	fingerprint := imp.Fingerprint(file)
+	assert.Equal(t, "3451ca87d71801687abba8993e5a69af79482914435d7cc064236fd93160f999", fingerprint)
+}
+
+func TestAutoMappingSimple(t *testing.T) {
+	simple := filepath.Join(config.Conf.Root, "imports", "assets", "simple.xlsx")
+	file := xlsx.Open(simple)
+	defer file.Close()
+
+	imp := Select("order")
+	mapping := imp.AutoMapping(file)
+	utils.Dump(mapping)
 }
diff --git a/importer/types.go b/importer/types.go
@@ -38,3 +38,21 @@ type Option struct {
 	MappingPreview string `json:"mappingPreview,omitempty"` // 显示字段映射界面方式 auto 匹配模板失败显示, always 一直显示, never 不显示
 	DataPreview    string `json:"dataPreview,omitempty"`    // 数据预览界面方式 auto 有异常数据时显示, always 一直显示, never 不显示
 }
+
+// Mapping 字段映射表
+type Mapping struct {
+	Sheet        string     `json:"sheet"`        // 数据表
+	ColStart     int        `json:"colStart"`     // 第一列的位置
+	RowStart     int        `json:"rowStart"`     // 第一行的位置
+	Columns      []*Binding `json:"data"`         // 字段数据列表
+	AutoMatching bool       `json:"autoMatching"` // 是否自动匹配
+}
+
+// Binding 数据绑定
+type Binding struct {
+	Label string   `json:"label"` // 目标字段标签
+	Field string   `json:"field"` // 目标字段名称
+	Name  string   `json:"name"`  // 源关联字段名称
+	Axis  string   `json:"axis"`  // 源关联字段坐标
+	Rules []string `json:"rules"` // 清洗规则
+}
diff --git a/importer/xlsx/xlsx.go b/importer/xlsx/xlsx.go
@@ -2,6 +2,7 @@ package xlsx
 
 import (
 	"fmt"
+	"strconv"
 
 	"github.com/xuri/excelize/v2"
 	"github.com/yaoapp/kun/exception"
@@ -14,6 +15,8 @@ type Xlsx struct {
 	File       *excelize.File
 	SheetName  string
 	SheetIndex int
+	ColStart   int
+	RowStart   int
 	Cols       *excelize.Cols
 	Rows       *excelize.Rows
 }
@@ -57,31 +60,56 @@ func (xlsx *Xlsx) Close() error {
 	return nil
 }
 
+// Inspect 基本信息
+func (xlsx *Xlsx) Inspect() from.Inspect {
+	return from.Inspect{
+		SheetName:  xlsx.SheetName,
+		SheetIndex: xlsx.SheetIndex,
+		RowStart:   xlsx.RowStart,
+		ColStart:   xlsx.ColStart,
+	}
+}
+
 // Data 读取数据
 func (xlsx *Xlsx) Data(page int, size int) []map[string]interface{} {
 	return nil
 }
 
 // Columns 读取列
 func (xlsx *Xlsx) Columns() []from.Column {
-	fmt.Println(xlsx.SheetName, xlsx.Rows.TotalRows(), xlsx.Cols.TotalCols())
+	columns := []from.Column{}
 
 	// 扫描标题位置坐标 扫描行
-	pos := []int{0, 0, 0} // {行, 开始列, 结束列}
+	// 从第一行开始扫描，识别第一个不为空的列
 	line := 0
 	success := false
 	for xlsx.Rows.Next() {
 		row, err := xlsx.Rows.Columns()
 		if err != nil {
-			exception.New("数据表 %s 扫描行 %d 信息失败 %", 400, xlsx.SheetName, line, err.Error()).Throw()
+			exception.New("数据表 %s 扫描行 %d 信息失败 %s", 400, xlsx.SheetName, line, err.Error()).Throw()
 		}
 
 		// 扫描列
+		// 从第一列开始扫描，识别第一个不为空的列
 		for i, cell := range row {
 			if cell != "" {
-				pos = []int{line, i, len(row)}
 				success = true
-				break
+				axis := positionToAxis(line, i)
+				if xlsx.RowStart == 0 && xlsx.ColStart == 0 {
+					xlsx.RowStart = line + 1
+					xlsx.ColStart = i + 1
+				}
+				cellType, err := xlsx.File.GetCellType(xlsx.SheetName, axis)
+				if err != nil {
+					xlog.Printf("读取数据类型失败 %s", err.Error())
+				}
+				columns = append(columns, from.Column{
+					Name: cell,
+					Col:  i,
+					Row:  line,
+					Axis: axis,
+					Type: byte(cellType),
+				})
 			}
 		}
 
@@ -90,12 +118,52 @@ func (xlsx *Xlsx) Columns() []from.Column {
 		}
 		line++
 	}
+	return columns
+}
 
-	fmt.Println(pos)
+// Bind 绑定映射表
+func (xlsx *Xlsx) Bind() {
+}
 
-	return nil
+func (xlsx *Xlsx) getMergeCells() {
+	cells, err := xlsx.File.GetMergeCells(xlsx.SheetName)
+	if err != nil {
+		exception.New("读取单元格 %s 失败 %s", 400, xlsx.SheetName, err.Error()).Throw()
+		return
+	}
+
+	for _, cell := range cells {
+		fmt.Println(cell.GetStartAxis())
+	}
 }
 
-// Bind 绑定映射表
-func (xlsx *Xlsx) Bind(mapping from.Mapping) {
+func positionToAxis(row, col int) string {
+	if row < 0 || col < 0 {
+		return ""
+	}
+	rowString := strconv.Itoa(row + 1)
+	colString := ""
+	col++
+	for col > 0 {
+		colString = fmt.Sprintf("%c%s", 'A'+col%26-1, colString)
+		col /= 26
+	}
+	return colString + rowString
+}
+
+func axisToPosition(axis string) (int, int, error) {
+	col := 0
+	for i, char := range axis {
+		if char >= 'A' && char <= 'Z' {
+			col *= 26
+			col += int(char - 'A' + 1)
+		} else if char >= 'a' && char <= 'z' {
+			col *= 26
+			col += int(char - 'a' + 1)
+		} else {
+			row, err := strconv.Atoi(axis[i:])
+			return row - 1, col - 1, err
+		}
+	}
+	return -1, -1, fmt.Errorf("invalid axis format %s", axis)
 }
diff --git a/tests/imports/manu.imp.json → tests/imports/order.imp.json b/tests/imports/manu.imp.json → tests/imports/order.imp.json