--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pkg/xlsx/templater.go	Thu May 27 01:22:10 2021 +0200
@@ -0,0 +1,384 @@
+// 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"
+	"os"
+	"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 executor struct {
+	ctx              context.Context
+	db               *sql.Conn
+	template         *excelize.File
+	keep             map[string]bool
+	expressions      map[string]gval.Evaluable
+	sourceSheet      string
+	destinationSheet string
+	frames           []frame
+}
+
+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,
+	db *sql.Conn,
+	template *excelize.File,
+) error {
+
+	e := executor{
+		ctx:         ctx,
+		db:          db,
+		template:    template,
+		keep:        map[string]bool{},
+		expressions: map[string]gval.Evaluable{},
+	}
+
+	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
+}
+
+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 (e *executor) copy(action *Action) error {
+	if len(action.Location) != 2 {
+		return fmt.Errorf("length location = %d (expect 2)",
+			len(action.Source))
+	}
+
+	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 {
+			var cell string
+			if cell, y, err = excelize.SplitCellName(s); err == nil {
+				x, err = excelize.ColumnNameToNumber(cell)
+			}
+		}
+		return x, y
+	}
+	order := func(a, b int) (int, int) {
+		if a > b {
+			return b, a
+		}
+		return a, b
+	}
+
+	var (
+		s1       = expand(action.Location[0])
+		s2       = expand(action.Location[1])
+		d1       = expand(action.Destination)
+		sx1, sy1 = split(s1)
+		sx2, sy2 = split(s2)
+		dx1, dy1 = split(d1)
+	)
+	if err != nil {
+		return err
+	}
+	sx1, sx2 = order(sx1, sx2)
+	sy1, sy1 = order(sy1, sy2)
+
+	//log.Printf("%s/%s -> %s\n", sFrom, sTo, dTo)
+
+	for y, i := sy1, 0; y <= sy2; y, i = y+1, i+1 {
+		for x, j := sx1, 0; x <= sx2; x, j = x+1, j+1 {
+			src, err1 := excelize.CoordinatesToCellName(x, y)
+			dst, err2 := excelize.CoordinatesToCellName(dx1+j, dy1+i)
+			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.template.GetCellFormula(e.sourceSheet, src); err == nil {
+				e.template.SetCellFormula(e.destinationSheet, dst, s)
+			}
+			if s, err := e.template.GetCellValue(e.sourceSheet, src); err == nil {
+				if s, err = e.expand(s, vars); err == nil {
+					e.template.SetCellStr(e.destinationSheet, dst, s)
+				}
+			}
+		}
+	}
+
+	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.db, 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
+		}
+		value, err = e.expand(value, vars)
+		if err != nil {
+			return err
+		}
+		if err := e.template.SetCellStr(destination, axis, value); err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
+func (e *executor) expr(x string) (gval.Evaluable, error) {
+	if f := e.expressions[x]; f != nil {
+		return f, nil
+	}
+	f, err := gval.Full().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)
+		return s
+	}
+
+	str = handlebars(str, replace)
+	return str, err
+}