--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pkg/xlsx/templater.go	Wed Jul 07 11:44:40 2021 +0200
@@ -0,0 +1,672 @@
+// This is Free Software under GNU Affero General Public License v >= 3.0
+// without warranty, see README.md and license for details.
+//
+// SPDX-License-Identifier: AGPL-3.0-or-later
+// License-Filename: LICENSES/AGPL-3.0.txt
+//
+// Copyright (C) 2021 by via donau
+//   – Österreichische Wasserstraßen-Gesellschaft mbH
+// Software engineering by Intevation GmbH
+//
+// Author(s):
+//  * Sascha L. Teichmann <sascha.teichmann@intevation.de>
+
+package xlsx
+
+import (
+	"bufio"
+	"context"
+	"database/sql"
+	"errors"
+	"fmt"
+	"io"
+	"log"
+	"os"
+	"strconv"
+	"strings"
+
+	"github.com/360EntSecGroup-Skylar/excelize/v2"
+	"gopkg.in/yaml.v2"
+
+	"github.com/PaesslerAG/gval"
+)
+
+type Action struct {
+	Type        string    `yaml:"type"`
+	Actions     []*Action `yaml:"actions"`
+	Location    []string  `yaml:"location"`
+	Source      string    `yaml:"source"`
+	Destination string    `yaml:"destination"`
+	Statement   string    `yaml:"statement"`
+	Vars        []string  `yaml:"vars"`
+	Name        string    `yaml:"name"`
+	Expr        string    `yaml:"expr"`
+}
+
+type frame struct {
+	res   *sqlResult
+	index int
+}
+
+type sheetAxis struct {
+	sheet string
+	axis  string
+}
+
+type cellValue struct {
+	value string
+	err   error
+}
+
+type executor struct {
+	ctx              context.Context
+	tx               *sql.Tx
+	template         *excelize.File
+	keep             map[string]bool
+	expressions      map[string]gval.Evaluable
+	sourceSheet      string
+	destinationSheet string
+	frames           []frame
+	// Fetching formulas out of cells is very expensive so we cache them.
+	formulaCache map[sheetAxis]cellValue
+}
+
+type area struct {
+	x1 int
+	y1 int
+	x2 int
+	y2 int
+	mc excelize.MergeCell
+}
+
+func mergeCellToArea(mc excelize.MergeCell) (area, error) {
+	x1, y1, err := excelize.CellNameToCoordinates(mc.GetStartAxis())
+	if err != nil {
+		return area{}, err
+	}
+	x2, y2, err := excelize.CellNameToCoordinates(mc.GetEndAxis())
+	if err != nil {
+		return area{}, err
+	}
+	return area{
+		x1: x1,
+		y1: y1,
+		x2: x2,
+		y2: y2,
+		mc: mc,
+	}, nil
+}
+
+func (a *area) contains(x, y int) bool {
+	return a.x1 <= x && x <= a.x2 && a.y1 <= y && y <= a.y2
+}
+
+func ActionFromFile(filename string) (*Action, error) {
+	f, err := os.Open(filename)
+	if err != nil {
+		return nil, err
+	}
+	defer f.Close()
+	return ActionFromReader(f)
+}
+
+func ActionFromReader(r io.Reader) (*Action, error) {
+	action := new(Action)
+	err := yaml.NewDecoder(bufio.NewReader(r)).Decode(action)
+	return action, err
+}
+
+func (a *Action) Execute(
+	ctx context.Context,
+	tx *sql.Tx,
+	template *excelize.File,
+) error {
+
+	//if f, err := os.Create("cpu.prof"); err == nil {
+	//	pprof.StartCPUProfile(f)
+	//	defer pprof.StopCPUProfile()
+	//}
+
+	e := executor{
+		ctx:          ctx,
+		tx:           tx,
+		template:     template,
+		keep:         map[string]bool{},
+		expressions:  map[string]gval.Evaluable{},
+		formulaCache: map[sheetAxis]cellValue{},
+	}
+
+	oldSheets := template.GetSheetList()
+
+	if err := e.dispatch(a); err != nil {
+		return err
+	}
+
+	for _, sheet := range oldSheets {
+		if !e.keep[sheet] {
+			template.DeleteSheet(sheet)
+		}
+	}
+	return nil
+}
+
+var reused int
+
+func (e *executor) getCellFormula(sheet, axis string) (string, error) {
+	var (
+		k  = sheetAxis{sheet: sheet, axis: axis}
+		v  cellValue
+		ok bool
+	)
+	if v, ok = e.formulaCache[k]; !ok {
+		v.value, v.err = e.template.GetCellFormula(sheet, axis)
+		e.formulaCache[k] = v
+	}
+	return v.value, v.err
+}
+
+func (e *executor) setCellFormula(sheet, axis, formula string) {
+	e.formulaCache[sheetAxis{sheet: sheet, axis: axis}] = cellValue{value: formula}
+	e.template.SetCellFormula(sheet, axis, formula)
+}
+
+func (e *executor) dispatch(action *Action) error {
+	if len(action.Vars) > 0 {
+		e.pushVars(action.Vars)
+		defer e.popFrame()
+	}
+	switch action.Type {
+	case "sheet":
+		return e.sheet(action)
+	case "copy":
+		return e.copy(action)
+	case "select":
+		return e.sel(action)
+	case "assign":
+		return e.assign(action)
+	case "":
+		return e.actions(action)
+	}
+	return fmt.Errorf("unknown type '%s'", action.Type)
+}
+
+func (e *executor) pushVars(vars []string) {
+	e.frames = append(e.frames, frame{
+		res: &sqlResult{
+			columns: vars,
+			rows:    [][]interface{}{make([]interface{}, len(vars))},
+		},
+	})
+}
+
+func (e *executor) popFrame() {
+	n := len(e.frames)
+	e.frames[n-1].res = nil
+	e.frames = e.frames[:n-1]
+}
+
+func (e *executor) assign(action *Action) error {
+	if action.Name == "" {
+		return errors.New("missing name in assign")
+	}
+	if action.Expr == "" {
+		return errors.New("missing expr in assign")
+	}
+
+	for i := len(e.frames) - 1; i >= 0; i-- {
+		fr := &e.frames[i]
+		if idx := fr.res.find(action.Name); idx >= 0 {
+			f, err := e.expr(action.Expr)
+			if err != nil {
+				return err
+			}
+			value, err := f(e.ctx, e.vars())
+			if err != nil {
+				return err
+			}
+			fr.res.rows[fr.index][idx] = value
+			break
+		}
+	}
+	return e.actions(action)
+}
+
+func order(a, b int) (int, int) {
+	if a < b {
+		return a, b
+	}
+	return b, a
+}
+
+func (e *executor) copy(action *Action) error {
+	if n := len(action.Location); !(n == 1 || n == 2) {
+		return fmt.Errorf("length location = %d (expect 1 or 2)",
+			len(action.Location))
+	}
+
+	vars := e.vars()
+
+	var err error
+	expand := func(s string) string {
+		if err == nil {
+			s, err = e.expand(s, vars)
+		}
+		return s
+	}
+	split := func(s string) (int, int) {
+		var x, y int
+		if err == nil {
+			x, y, err = excelize.CellNameToCoordinates(s)
+		}
+		return x, y
+	}
+
+	var location []string
+	if len(action.Location) == 1 {
+		location = []string{action.Location[0], action.Location[0]}
+	} else {
+		location = action.Location
+	}
+
+	var destination string
+	if action.Destination == "" {
+		destination = location[0]
+	} else {
+		destination = action.Destination
+	}
+
+	var (
+		s1       = expand(location[0])
+		s2       = expand(location[1])
+		d1       = expand(destination)
+		sx1, sy1 = split(s1)
+		sx2, sy2 = split(s2)
+		dx1, dy1 = split(d1)
+	)
+	if err != nil {
+		return err
+	}
+	sx1, sx2 = order(sx1, sx2)
+	sy1, sy2 = order(sy1, sy2)
+
+	var areas []area
+
+	//log.Println("merged cells")
+	if mcs, err := e.template.GetMergeCells(e.sourceSheet); err == nil {
+		areas = make([]area, 0, len(mcs))
+		for _, mc := range mcs {
+			if a, err := mergeCellToArea(mc); err == nil {
+				areas = append(areas, a)
+			}
+		}
+	}
+
+	for y, i := sy1, 0; y <= sy2; y, i = y+1, i+1 {
+	nextX:
+		for x, j := sx1, 0; x <= sx2; x, j = x+1, j+1 {
+
+			// check if cell is part of a merged cell
+			for k := range areas {
+				area := &areas[k]
+
+				if area.contains(x, y) {
+					ofsX := x - area.x1
+					ofsY := y - area.y1
+
+					sx := dx1 + j - ofsX
+					sy := dy1 + i - ofsY
+					ex := sx + (area.x2 - area.x1)
+					ey := sy + (area.y2 - area.y1)
+
+					// Copy over attributes
+					for l := 0; l <= area.x2-area.x1; l++ {
+						for m := 0; m <= area.y2-area.y1; m++ {
+							src, err1 := excelize.CoordinatesToCellName(area.x1+l, area.y1+m)
+							dst, err2 := excelize.CoordinatesToCellName(sx+l, sy+m)
+							if err1 != nil || err2 != nil {
+								continue
+							}
+							if s, err := e.template.GetCellStyle(e.sourceSheet, src); err == nil {
+								e.template.SetCellStyle(e.destinationSheet, dst, dst, s)
+							}
+							if s, err := e.getCellFormula(e.sourceSheet, src); err == nil {
+								e.setCellFormula(e.destinationSheet, dst, s)
+							}
+						}
+					}
+
+					dst, err := excelize.CoordinatesToCellName(sx, sy)
+					if err != nil {
+						continue nextX
+					}
+
+					// Copy over expanded text
+					if v, err := e.typedExpand(area.mc.GetCellValue(), vars); err == nil {
+						e.template.SetCellValue(e.destinationSheet, dst, v)
+					}
+
+					// Finally merge the cells
+					if end, err := excelize.CoordinatesToCellName(ex, ey); err == nil {
+						e.template.MergeCell(e.destinationSheet, dst, end)
+					}
+
+					continue nextX
+				}
+			}
+
+			// Regular cell
+
+			src, err := excelize.CoordinatesToCellName(x, y)
+			if err != nil {
+				continue
+			}
+			dst, err := excelize.CoordinatesToCellName(dx1+j, dy1+i)
+			if err != nil {
+				continue
+			}
+
+			cn, err := excelize.ColumnNumberToName(x)
+			if err != nil {
+				continue
+			}
+
+			cw, err := e.template.GetColWidth(e.sourceSheet, cn)
+			if err != nil {
+				continue
+			}
+
+			rh, err := e.template.GetRowHeight(e.sourceSheet, y)
+			if err != nil {
+				continue
+			}
+
+			dc, err := excelize.ColumnNumberToName(dx1 + j)
+			if err != nil {
+				continue
+			}
+
+			if e.template.SetColWidth(e.destinationSheet, dc, dc, cw) != nil {
+				continue
+			}
+
+			if e.template.SetRowHeight(e.destinationSheet, dy1+i, rh) != nil {
+				continue
+			}
+
+			if s, err := e.template.GetCellStyle(e.sourceSheet, src); err == nil {
+				e.template.SetCellStyle(e.destinationSheet, dst, dst, s)
+			}
+			if s, err := e.getCellFormula(e.sourceSheet, src); err == nil {
+				e.setCellFormula(e.destinationSheet, dst, s)
+			}
+			if s, err := e.template.GetCellValue(e.sourceSheet, src); err == nil {
+				if v, err := e.typedExpand(s, vars); err == nil {
+					e.template.SetCellValue(e.destinationSheet, dst, v)
+				}
+			}
+		}
+	}
+
+	return nil
+}
+
+func (e *executor) sel(action *Action) error {
+	vars := e.vars()
+
+	eval := func(x string) (interface{}, error) {
+		f, err := e.expr(x)
+		if err != nil {
+			return nil, err
+		}
+		return f(e.ctx, vars)
+	}
+
+	res, err := query(e.ctx, e.tx, action.Statement, eval)
+	if err != nil {
+		return err
+	}
+
+	e.frames = append(e.frames, frame{res: res})
+	defer e.popFrame()
+
+	for i := range res.rows {
+		e.frames[len(e.frames)-1].index = i
+		if err := e.actions(action); err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
+func (e *executor) actions(action *Action) error {
+	for _, a := range action.Actions {
+		if err := e.dispatch(a); err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
+func (e *executor) sheet(action *Action) error {
+
+	vars := e.vars()
+	source, err := e.expand(action.Source, vars)
+	if err != nil {
+		return err
+	}
+
+	srcIdx := e.template.GetSheetIndex(source)
+	if srcIdx == -1 {
+		return fmt.Errorf("sheet '%s' not found", source)
+	}
+
+	destination := action.Destination
+	if destination == "" { // same as source
+		e.keep[source] = true
+		destination = source
+	} else { // new sheet
+		destination, err = e.expand(destination, vars)
+		if err != nil {
+			return err
+		}
+		dstIdx := e.template.NewSheet(destination)
+		if len(action.Actions) == 0 {
+			// Only copy if there are no explicit instructions.
+			if err := e.template.CopySheet(srcIdx, dstIdx); err != nil {
+				return err
+			}
+		}
+	}
+
+	if len(action.Actions) > 0 {
+		pSrc, pDst := e.sourceSheet, e.destinationSheet
+		defer func() {
+			e.sourceSheet, e.destinationSheet = pSrc, pDst
+		}()
+		e.sourceSheet, e.destinationSheet = source, destination
+		return e.actions(action)
+	}
+
+	// Simple filling
+
+	// "{{" only as a quick filter
+	result, err := e.template.SearchSheet(destination, "{{", true)
+	if err != nil {
+		return err
+	}
+	for _, axis := range result {
+		value, err := e.template.GetCellValue(destination, axis)
+		if err != nil {
+			return err
+		}
+		nvalue, err := e.typedExpand(value, vars)
+		if err != nil {
+			return err
+		}
+		if err := e.template.SetCellValue(destination, axis, nvalue); err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
+func columnToNum(col interface{}) interface{} {
+	var name string
+	switch v := col.(type) {
+	case string:
+		name = v
+	default:
+		name = fmt.Sprintf("%v", col)
+	}
+	num, err := excelize.ColumnNameToNumber(name)
+	if err != nil {
+		log.Printf("error: invalid column name '%v'\n", col)
+		return 1
+	}
+	return num
+}
+
+func asInt(i interface{}) (int, error) {
+	switch v := i.(type) {
+	case int:
+		return v, nil
+	case int8:
+		return int(v), nil
+	case int16:
+		return int(v), nil
+	case int32:
+		return int(v), nil
+	case int64:
+		return int(v), nil
+	case float32:
+		return int(v), nil
+	case float64:
+		return int(v), nil
+	case string:
+		return strconv.Atoi(v)
+	default:
+		return 0, fmt.Errorf("invalid int '%v'", i)
+	}
+}
+
+func coord2cell(ix, iy interface{}) interface{} {
+	x, err := asInt(ix)
+	if err != nil {
+		log.Printf("error: invalid x value: %v\n", err)
+		return "A1"
+	}
+	y, err := asInt(iy)
+	if err != nil {
+		log.Printf("error: invalid y value: %v\n", err)
+		return "A1"
+	}
+
+	cell, err := excelize.CoordinatesToCellName(x, y)
+	if err != nil {
+		log.Printf("error: invalid cell coord (%d, %d)\n", x, y)
+		return "A1"
+	}
+	return cell
+}
+
+var templateLang = gval.Full(
+	gval.Function("column2num", columnToNum),
+	gval.Function("coord2cell", coord2cell),
+)
+
+func (e *executor) expr(x string) (gval.Evaluable, error) {
+	if f := e.expressions[x]; f != nil {
+		return f, nil
+	}
+	f, err := templateLang.NewEvaluable(x)
+	if err != nil {
+		return nil, err
+	}
+	e.expressions[x] = f
+	return f, nil
+}
+
+func (e *executor) vars() map[string]interface{} {
+	vars := map[string]interface{}{}
+	if len(e.frames) > 0 {
+		vars["row_number"] = e.frames[len(e.frames)-1].index
+	}
+	for i := len(e.frames) - 1; i >= 0; i-- {
+		fr := &e.frames[i]
+		for j, n := range fr.res.columns {
+			if _, found := vars[n]; !found {
+				vars[n] = fr.res.rows[fr.index][j]
+			}
+		}
+	}
+	return vars
+}
+
+func (e *executor) expand(
+	str string,
+	vars map[string]interface{},
+) (string, error) {
+
+	var err error
+
+	replace := func(s string) string {
+		if err != nil {
+			return ""
+		}
+		var eval gval.Evaluable
+		if eval, err = e.expr(strings.TrimSpace(s)); err != nil {
+			return ""
+		}
+		s, err = eval.EvalString(e.ctx, vars)
+		if err != nil {
+			log.Printf("error: '%s' '%s' %v\n", str, s, err)
+		}
+		return s
+	}
+
+	str = handlebars(str, replace)
+	return str, err
+}
+
+func (e *executor) typedExpand(
+	str string,
+	vars map[string]interface{},
+) (interface{}, error) {
+
+	var (
+		err      error
+		repCount int
+		last     interface{}
+	)
+
+	replace := func(s string) string {
+		if err != nil {
+			return ""
+		}
+		var eval gval.Evaluable
+		if eval, err = e.expr(strings.TrimSpace(s)); err != nil {
+			return ""
+		}
+		repCount++
+		last, err = eval(e.ctx, vars)
+		if err != nil {
+			log.Printf("error: '%s' '%s' %v\n", str, s, err)
+		}
+		return fmt.Sprintf("%v", last)
+	}
+
+	nstr := handlebars(str, replace)
+
+	if err != nil {
+		return nil, err
+	}
+
+	if repCount == 1 &&
+		strings.HasPrefix(str, "{{") &&
+		strings.HasSuffix(str, "}}") {
+		return last, nil
+	}
+	return nstr, nil
+}