office/Excel.go

package office

import (
	"fmt"
	"regexp"
	"strconv"

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

// func WriteExcel(excelFile, sheetName string, table [][]string, password *string) error {
// 	var f *excelize.File
// 	var err error
// 	if f, err = excelize.OpenFile(excelFile, excelize.Options{Password: u.String(password)}); err != nil {
// 		f = excelize.NewFile()
// 	}
// 	if sheetName == "" {
// 		sheetName = "Sheet1"
// 	}
// 	if sheetName >= "0" && sheetName <= "9" {
// 		sheetName = f.GetSheetName(u.Int(sheetName))
// 	}

// 	for y, row := range table {
// 		for x, v := range row {
// 			_ = f.SetCellStr(sheetName, MakeCellID(x, y), v)
// 		}
// 	}
// 	if err == nil {
// 		err = f.Save()
// 	} else {
// 		err = f.SaveAs(excelFile, excelize.Options{Password: u.String(password)})
// 	}
// 	return err
// }

type Excel struct {
	filename string
	password string
	excel    *excelize.File
}

func OpenExcel(filename string, password *string) (*Excel, error) {
	xls := &Excel{
		filename: filename,
		password: u.String(password),
	}
	var err error
	if u.FileExists(filename) {
		if xls.excel, err = excelize.OpenFile(filename, excelize.Options{Password: xls.password}); err != nil {
			xls.excel = excelize.NewFile()
		}
	} else {
		xls.excel = excelize.NewFile()
	}
	return xls, err
}

func (xls *Excel) Save(filename, password *string) error {
	if filename != nil {
		xls.filename = *filename
	}
	if password != nil {
		xls.password = *password
	}
	return xls.excel.SaveAs(xls.filename, excelize.Options{Password: xls.password})
}

var isIntMatcher = regexp.MustCompile(`^\d{1,8}$`)
var isFloatMatcher = regexp.MustCompile(`^[\d.]{1,8}$`)

func (xls *Excel) Set(sheetName string, table [][]any, start, end *string) error {
	if sheetName == "" {
		sheetName = "Sheet1"
	}
	if isIntMatcher.MatchString(sheetName) {
		sheetName = xls.excel.GetSheetName(u.Int(sheetName))
	} else {
		if idx, err := xls.excel.GetSheetIndex(sheetName); err != nil || idx == -1 {
			if _, err = xls.excel.NewSheet(sheetName); err != nil {
				return err
			}
		} else {
			return err
		}
	}

	startX, startY := ParseCellID(u.String(start))
	endX, endY := ParseCellID(u.String(end))
	for y, row := range table {
		if endY > 0 && startY+y > endY {
			break
		}
		for x, v := range row {
			if endX > 0 && startX+x > endX {
				break
			}
			// fmt.Println(startX+x, startY+y, MakeCellID(startX+x, startY+y), reflect.TypeOf(v).String(), v)
			if err := xls.excel.SetCellValue(sheetName, MakeCellID(startX+x, startY+y), v); err != nil {
				return err
			}
			// fmt.Println(xls.excel.GetCellType(sheetName, MakeCellID(startX+x, startY+y)))
		}
	}
	return nil
}

func (xls *Excel) Sheets() []string {
	return xls.excel.GetSheetList()
}

func (xls *Excel) Get(sheetName string, start, end *string) ([][]any, error) {
	if sheetName >= "0" && sheetName <= "9" {
		sheetName = xls.excel.GetSheetName(u.Int(sheetName))
	}

	rows, err := xls.excel.GetRows(sheetName)
	if err != nil {
		return nil, err
	}

	startX, startY := ParseCellID(u.String(start))
	endX, endY := ParseCellID(u.String(end))
	result := make([][]any, 0)
	for y, row := range rows {
		if startY > 0 && y < startY {
			continue
		}
		if endY > 0 && y > endY {
			break
		}
		// result[y-startY] = make([]any, len(row)-startX)
		rowData := make([]any, 0)
		for x, v := range row {
			if startX > 0 && x < startX {
				continue
			}
			if endX > 0 && x > endX {
				break
			}
			cellType, err := xls.excel.GetCellType(sheetName, MakeCellID(x, y))
			if err != nil {
				return nil, err
			}
			switch cellType {
			case excelize.CellTypeInlineString, excelize.CellTypeSharedString:
				rowData = append(rowData, v)
			case excelize.CellTypeFormula:
				// TODO 获得公式结果
				// formula, err := xls.excel.GetCellFormula(sheetName, MakeCellID(j, i))
				rowData = append(rowData, v)
			case excelize.CellTypeNumber:
				if isFloatMatcher.MatchString(v) {
					rowData = append(rowData, u.Float64(v))
				} else {
					rowData = append(rowData, u.Int64(v))
				}
			case excelize.CellTypeBool:
				rowData = append(rowData, u.Bool(v))
			case excelize.CellTypeDate:
				// TODO 获得日期结果
				rowData = append(rowData, v)
			case excelize.CellTypeUnset:
				if isFloatMatcher.MatchString(v) {
					rowData = append(rowData, u.Float64(v))
				} else if isIntMatcher.MatchString(v) {
					rowData = append(rowData, u.Int64(v))
				} else {
					rowData = append(rowData, v)
				}
			case excelize.CellTypeError:
				rowData = append(rowData, v)
			default:
				rowData = append(rowData, v)
			}
		}
		result = append(result, rowData)
	}
	return result, nil
}

func (xls *Excel) SetData(sheetName string, data []map[string]any, start, end *string) error {
	table, err := xls.Get(sheetName, start, end)
	if err != nil {
		return err
	}
	fieldIndex := map[string]int{}
	fieldNum := 0
	if len(table) > 0 {
		fieldNum = len(table[0])
		for i, v := range table[0] {
			fieldIndex[u.String(v)] = i
		}
	}

	for i, item := range data {
		if len(table) <= i+1 {
			table = append(table, []any{})
		}
		for k, v := range item {
			idx, idxOk := fieldIndex[k]
			if !idxOk {
				idx = fieldNum
				fieldIndex[k] = idx
				fieldNum++

				for len(table[0]) < idx+1 {
					table[0] = append(table[0], "")
				}
				table[0][idx] = k
			}
			for len(table[i+1]) < idx+1 {
				table[i+1] = append(table[i+1], "")
			}
			table[i+1][idx] = v
		}
	}
	return xls.Set(sheetName, table, start, end)
}

func (xls *Excel) GetData(sheetName string, start, end *string) ([]map[string]any, error) {
	rows, err := xls.Get(sheetName, start, end)
	if err != nil {
		return nil, err
	}

	fields := []string{}
	if len(rows) > 0 {
		for _, v := range rows[0] {
			v1 := u.String(v)
			if v1 == "" {
				v1 = MakeCellID(len(fields), 0)
			}
			fields = append(fields, v1)
		}
	}

	data := make([]map[string]any, 0)
	for i := 1; i < len(rows); i++ {
		row := map[string]any{}
		for j := 0; j < len(fields); j++ {
			if j < len(rows[i]) {
				row[fields[j]] = rows[i][j]
			} else {
				row[fields[j]] = ""
			}
		}
		data = append(data, row)
	}
	return data, nil
}

func MakeCellID(col, row int) string {
	colName := ""
	for col >= 0 {
		i := col % 26
		colName = string(rune(i+65)) + colName
		col = col/26 - 1
	}
	return fmt.Sprint(colName, row+1)
}

func ParseCellID(cell string) (col, row int) {
	row = 0
	col = 0
	for i, c := range cell {
		if c >= 'A' && c <= 'Z' {
			col = col*26 + int(c-'A'+1)
		} else if c >= 'a' && c <= 'z' {
			col = col*26 + int(c-'a'+1)
		} else {
			row, _ = strconv.Atoi(cell[i:])
			break
		}
	}
	col--
	row--
	if col < 0 {
		col = 0
	}
	if row < 0 {
		row = 0
	}
	return col, row
}