287 lines
6.5 KiB
Go
287 lines
6.5 KiB
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
|
|
}
|