goext/excelext/mapper.go

package excelext

import (
	"reflect"

	"git.blackforestbytes.com/BlackForestBytes/goext/dataext"
	"git.blackforestbytes.com/BlackForestBytes/goext/exerr"
	"git.blackforestbytes.com/BlackForestBytes/goext/langext"
	excelize360 "github.com/360EntSecGroup-Skylar/excelize"
	"github.com/xuri/excelize/v2"
)

type excelMapperColDefinition[T any] struct {
	style  *int
	header string
	width  *float64
	fn     func(T) (any, error)
}

type ExcelMapper[T any] struct {
	StyleDate       *int
	StyleDatetime   *int
	StyleEUR        *int
	StylePercentage *int
	StyleHeader     *int
	StyleWSHeader   *int

	SkipColumnHeader bool

	sheetName      string
	wsHeader       []dataext.Tuple[string, *int]
	colDefinitions []excelMapperColDefinition[T]
	colFilter      []func(v T) bool
}

func NewExcelMapper[T any]() (*ExcelMapper[T], error) {

	em := &ExcelMapper[T]{
		StyleDate:       nil,
		StyleDatetime:   nil,
		StyleEUR:        nil,
		StylePercentage: nil,
		StyleHeader:     nil,
		StyleWSHeader:   nil,
		sheetName:       "",

		SkipColumnHeader: false,

		wsHeader:       make([]dataext.Tuple[string, *int], 0),
		colDefinitions: make([]excelMapperColDefinition[T], 0),
	}

	return em, nil
}

func (em *ExcelMapper[T]) InitNewFile(sheetName string) (*excelize.File, error) {
	f := excelize.NewFile()

	defSheet := f.GetSheetList()[0]

	sheet1 := sheetName

	sheetIdx, err := f.NewSheet(sheet1)
	if err != nil {
		return nil, err
	}

	f.SetActiveSheet(sheetIdx)
	err = f.DeleteSheet(defSheet)

	err = em.InitStyles(f)
	if err != nil {
		return nil, err
	}

	return f, nil
}

func (em *ExcelMapper[T]) InitStyles(f *excelize.File) error {
	styleDate, err := f.NewStyle(&excelize.Style{
		CustomNumFmt: langext.Ptr("dd.mm.yyyy"),
	})
	if err != nil {
		return err
	}

	styleDatetime, err := f.NewStyle(&excelize.Style{
		NumFmt: 22,
	})
	if err != nil {
		return err
	}

	styleEUR, err := f.NewStyle(&excelize.Style{
		NumFmt: 218,
	})
	if err != nil {
		return err
	}

	stylePercentage, err := f.NewStyle(&excelize.Style{
		NumFmt: 10,
	})
	if err != nil {
		return err
	}

	styleHeader, err := f.NewStyle(&excelize.Style{
		Font: &excelize.Font{Bold: true, Size: 11},
	})
	if err != nil {
		return err
	}

	styleWSHeader, err := f.NewStyle(&excelize.Style{
		Font: &excelize.Font{Bold: true, Size: 24},
	})
	if err != nil {
		return err
	}

	em.StyleDate = &styleDate
	em.StyleDatetime = &styleDatetime
	em.StyleEUR = &styleEUR
	em.StylePercentage = &stylePercentage
	em.StyleHeader = &styleHeader
	em.StyleWSHeader = &styleWSHeader

	return nil
}

func (em *ExcelMapper[T]) AddWorksheetHeader(header string, style *int) {
	em.wsHeader = append(em.wsHeader, dataext.NewTuple(header, style))
}

func (em *ExcelMapper[T]) AddColumn(header string, style *int, width *float64, fn func(T) any) {
	em.colDefinitions = append(em.colDefinitions, excelMapperColDefinition[T]{
		style:  style,
		header: header,
		width:  width,
		fn:     func(t T) (any, error) { return fn(t), nil },
	})
}

func (em *ExcelMapper[T]) AddColumnErr(header string, style *int, width *float64, fn func(T) (any, error)) {
	em.colDefinitions = append(em.colDefinitions, excelMapperColDefinition[T]{
		style:  style,
		header: header,
		width:  width,
		fn:     fn,
	})
}

func (em *ExcelMapper[T]) Build(sheetName string, data []T) ([]byte, error) {
	f, err := em.InitNewFile(sheetName)
	if err != nil {
		return nil, exerr.Wrap(err, "failed to init new file").Build()
	}

	err = em.BuildSingleSheet(f, sheetName, data)
	if err != nil {
		return nil, exerr.Wrap(err, "").Build()
	}

	buffer, err := f.WriteToBuffer()
	if err != nil {
		return nil, exerr.Wrap(err, "failed to build xls").Build()
	}

	return buffer.Bytes(), nil
}

func (em *ExcelMapper[T]) BuildSingleSheet(f *excelize.File, sheetName string, data []T) error {
	if em.StyleHeader == nil || em.StyleDate == nil || em.StyleDatetime == nil || em.StyleEUR == nil || em.StylePercentage == nil || em.StyleWSHeader == nil {
		err := em.InitStyles(f)
		if err != nil {
			return exerr.Wrap(err, "failed to init styles").Build()
		}
	}

	rowOffset := 0

	if len(em.wsHeader) > 0 {
		for range em.wsHeader {
			rowOffset += 1
		}
		rowOffset += 1
	}

	if !em.SkipColumnHeader {
		for i, col := range em.colDefinitions {
			err := f.SetCellValue(sheetName, c(rowOffset+1, i), col.header)
			if err != nil {
				return err
			}
		}
	}

	for i, col := range em.colDefinitions {
		if col.style != nil {
			err := f.SetColStyle(sheetName, excelize360.ToAlphaString(i), *col.style)
			if err != nil {
				return err
			}
		}
	}

	for i, col := range em.colDefinitions {
		if col.width != nil {
			err := f.SetColWidth(sheetName, excelize360.ToAlphaString(i), excelize360.ToAlphaString(i), *col.width)
			if err != nil {
				return err
			}
		}
	}

	err := f.SetRowStyle(sheetName, rowOffset+1, rowOffset+1, *em.StyleHeader)
	if err != nil {
		return err
	}

	if len(em.wsHeader) > 0 {
		for i, hdr := range em.wsHeader {
			style := *langext.CoalesceOpt(hdr.V2, em.StyleWSHeader)

			err = f.SetCellValue(sheetName, c(i+1, 0), hdr.V1)
			if err != nil {
				return err
			}

			err = f.MergeCell(sheetName, c(i+1, 0), c(i+1, len(em.colDefinitions)-1))
			if err != nil {
				return err
			}

			err = f.SetRowStyle(sheetName, 1, 1, style)
			if err != nil {
				return err
			}
		}
	}

	iRow := rowOffset + 1
	if !em.SkipColumnHeader {
		iRow += 1
	}

	for _, dat := range data {

		skip := false
		for _, filter := range em.colFilter {
			if !filter(dat) {
				skip = true
				break
			}
		}
		if skip {
			continue
		}

		for iCol, col := range em.colDefinitions {

			cellVal, err := col.fn(dat)
			if err != nil {
				return err
			}

			for reflect.ValueOf(cellVal).Kind() == reflect.Pointer && !reflect.ValueOf(cellVal).IsNil() {
				cellVal = reflect.ValueOf(cellVal).Elem().Interface()
			}

			if langext.IsNil(cellVal) {
				err = f.SetCellValue(sheetName, c(iRow, iCol), "")
				if err != nil {
					return err
				}
			} else {
				err = f.SetCellValue(sheetName, c(iRow, iCol), cellVal)
				if err != nil {
					return err
				}
			}

		}

		iRow++
	}

	return nil
}

func (em *ExcelMapper[T]) AddFilter(f func(v T) bool) {
	em.colFilter = append(em.colFilter, f)
}