Mercurial > gemma

changeset 5321:0919946f624b extented-report

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added a report controller.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Thu, 27 May 2021 01:22:10 +0200
parents 866eae1bd888
children 80d9fd782f00
files go.mod go.sum pkg/config/config.go pkg/controllers/report.go pkg/controllers/routes.go pkg/xlsx/handlebars.go pkg/xlsx/sql.go pkg/xlsx/templater.go
diffstat 8 files changed, 713 insertions(+), 1 deletions(-) [+]
line wrap: on
line diff
--- a/go.mod	Wed May 26 19:10:45 2021 +0200
+++ b/go.mod	Thu May 27 01:22:10 2021 +0200
@@ -3,6 +3,8 @@
 go 1.13
 
 require (
+	github.com/360EntSecGroup-Skylar/excelize/v2 v2.4.0
+	github.com/PaesslerAG/gval v1.1.0
 	github.com/cockroachdb/apd v1.1.0 // indirect
 	github.com/fatih/structs v1.1.0
 	github.com/fogleman/contourmap v0.0.0-20190814184649-9f61d36c4199
@@ -35,4 +37,5 @@
 	gopkg.in/gomail.v2 v2.0.0-20160411212932-81ebce5c23df
 	gopkg.in/ini.v1 v1.62.0 // indirect
 	gopkg.in/robfig/cron.v1 v1.2.0
+	gopkg.in/yaml.v2 v2.4.0
 )
--- a/go.sum	Wed May 26 19:10:45 2021 +0200
+++ b/go.sum	Thu May 27 01:22:10 2021 +0200
@@ -12,9 +12,15 @@
 cloud.google.com/go/storage v1.0.0/go.mod h1:IhtSnM/ZTZV8YYJWCY8RULGVqBDmpoyjwiyrjsg+URw=
 dmitri.shuralyov.com/gpu/mtl v0.0.0-20190408044501-666a987793e9/go.mod h1:H6x//7gZCb22OMCxBHrMx7a5I7Hp++hsVxbQ4BYO7hU=
 gioui.org v0.0.0-20210308172011-57750fc8a0a6/go.mod h1:RSH6KIUZ0p2xy5zHDxgAM4zumjgTw83q2ge/PI+yyw8=
+github.com/360EntSecGroup-Skylar/excelize/v2 v2.4.0 h1:X+2CWGf5W1tm2+W7Y/LLrAPLFSNlHATnqDudGoIzaxY=
+github.com/360EntSecGroup-Skylar/excelize/v2 v2.4.0/go.mod h1:p9lGPoVX3HYEbFRfjgrPWaaKsHe/2u4EM9DB/qoctgU=
 github.com/BurntSushi/toml v0.3.1/go.mod h1:xHWCNGjB5oqiDr8zfno3MHue2Ht5sIBksp03qcyfWMU=
 github.com/BurntSushi/xgb v0.0.0-20160522181843-27f122750802/go.mod h1:IVnqGOEym/WlBOVXweHU+Q+/VP0lqqI8lqeDx9IjBqo=
 github.com/OneOfOne/xxhash v1.2.2/go.mod h1:HSdplMjZKSmBqAxg5vPj2TmRDmfkzw+cTzAElWljhcU=
+github.com/PaesslerAG/gval v1.1.0 h1:k3RuxeZDO3eejD4cMPSt+74tUSvTnbGvLx0df4mdwFc=
+github.com/PaesslerAG/gval v1.1.0/go.mod h1:y/nm5yEyTeX6av0OfKJNp9rBNj2XrGhAf5+v24IBN1I=
+github.com/PaesslerAG/jsonpath v0.1.0 h1:gADYeifvlqK3R3i2cR5B4DGgxLXIPb3TRTH1mGi0jPI=
+github.com/PaesslerAG/jsonpath v0.1.0/go.mod h1:4BzmtoM/PI8fPO4aQGIusjGxGir2BzcV0grWtFzq1Y8=
 github.com/ajstarks/svgo v0.0.0-20180226025133-644b8db467af/go.mod h1:K08gAheRH3/J6wwsYMMT4xOr94bZjxIelGM0+d/wbFw=
 github.com/alecthomas/template v0.0.0-20160405071501-a0175ee3bccc/go.mod h1:LOuyumcjzFXgccqObfd/Ljyb9UuFJ6TxHnclSeseNhc=
 github.com/alecthomas/units v0.0.0-20151022065526-2efee857e7cf/go.mod h1:ybxpYRFXyAe+OPACYpWeL0wqObRcbAqCMya13uyzqw0=
@@ -162,6 +168,8 @@
 github.com/mitchellh/mapstructure v1.4.1/go.mod h1:bFUtVrKA4DC2yAKiSyO/QUcy7e+RRV2QTWOzhPopBRo=
 github.com/modern-go/concurrent v0.0.0-20180306012644-bacd9c7ef1dd/go.mod h1:6dJC0mAP4ikYIbvyc7fijjWJddQyLn8Ig3JB5CqoB9Q=
 github.com/modern-go/reflect2 v1.0.1/go.mod h1:bx2lNnkwVCuqBIxFjflWJWanXIb3RllmbCylyMrvgv0=
+github.com/mohae/deepcopy v0.0.0-20170929034955-c48cc78d4826 h1:RWengNIwukTxcDr9M+97sNutRR1RKhG96O6jWumTTnw=
+github.com/mohae/deepcopy v0.0.0-20170929034955-c48cc78d4826/go.mod h1:TaXosZuwdSHYgviHp1DAtfrULt5eUgsSMsZf+YrPgl8=
 github.com/mwitkow/go-conntrack v0.0.0-20161129095857-cc309e4a2223/go.mod h1:qRWi+5nqEBWmkhHvq77mSJWrCKwh8bxhgT7d/eI7P4U=
 github.com/oklog/ulid v1.3.1/go.mod h1:CirwcVhetQ6Lv90oh/F+FBtV6XMibvdAFo93nm5qn4U=
 github.com/pascaldekloe/goe v0.0.0-20180627143212-57f6aae5913c/go.mod h1:lzWF7FIEvWOWxwDKqyGYQf6ZUaNfKdP144TG7ZOy1lc=
@@ -187,6 +195,10 @@
 github.com/prometheus/procfs v0.0.0-20181005140218-185b4288413d/go.mod h1:c3At6R/oaqEKCNdg8wHV1ftS6bRYblBhIjjI8uT2IGk=
 github.com/prometheus/procfs v0.0.0-20190507164030-5867b95ac084/go.mod h1:TjEm7ze935MbeOT/UhFTIMYKhuLP4wbCsTZCD3I8kEA=
 github.com/prometheus/tsdb v0.7.1/go.mod h1:qhTCs0VvXwvX/y3TZrWD7rabWM+ijKTux40TwIPHuXU=
+github.com/richardlehane/mscfb v1.0.3 h1:rD8TBkYWkObWO0oLDFCbwMeZ4KoalxQy+QgniCj3nKI=
+github.com/richardlehane/mscfb v1.0.3/go.mod h1:YzVpcZg9czvAuhk9T+a3avCpcFPMUWm7gK3DypaEsUk=
+github.com/richardlehane/msoleps v1.0.1 h1:RfrALnSNXzmXLbGct/P2b4xkFz4e8Gmj/0Vj9M9xC1o=
+github.com/richardlehane/msoleps v1.0.1/go.mod h1:BWev5JBpU9Ko2WAgmZEuiz4/u3ZYTKbjLycmwiWUfWg=
 github.com/rogpeppe/fastuuid v0.0.0-20150106093220-6724a57986af/go.mod h1:XWv6SoW27p1b0cqNHllgS5HIMJraePCO15w5zCzIWYg=
 github.com/rogpeppe/go-internal v1.3.0/go.mod h1:M8bDsm7K2OlrFYOpmOWEs/qY81heoFRclV5y23lUDJ4=
 github.com/rs/cors v1.7.0 h1:+88SsELBHx5r+hZ8TCkggzSstaWNbDvThkVK8H6f9ik=
@@ -228,8 +240,9 @@
 github.com/stretchr/objx v0.1.1/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
 github.com/stretchr/testify v1.2.2/go.mod h1:a8OnRcib4nhh0OaRAV+Yts87kKdq0PP7pXfy6kDkUVs=
 github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI=
-github.com/stretchr/testify v1.4.0 h1:2E4SXV/wtOkTonXsotYi4li6zVWxYlZuYNCXe9XRJyk=
 github.com/stretchr/testify v1.4.0/go.mod h1:j7eGeouHqKxXV5pUuKE4zz7dFj8WfuZ+81PSLYec5m4=
+github.com/stretchr/testify v1.6.1 h1:hDPOHmpOpP40lSULcqw7IrRb/u7w6RpDC9399XyoNd0=
+github.com/stretchr/testify v1.6.1/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg=
 github.com/subosito/gotenv v1.2.0 h1:Slr1R9HxAlEKefgq5jn9U+DnETlIUa6HfgEzj0g5d7s=
 github.com/subosito/gotenv v1.2.0/go.mod h1:N0PQaV/YGNqwC0u51sEeR/aUtSLEXKX9iv69rRypqCw=
 github.com/tidwall/cities v0.1.0 h1:CVNkmMf7NEC9Bvokf5GoSsArHCKRMTgLuubRTHnH0mE=
@@ -242,6 +255,8 @@
 github.com/tidwall/rtree v1.2.8/go.mod h1:S+JSsqPTI8LfWA4xHBo5eXzie8WJLVFeppAutSegl6M=
 github.com/tmc/grpc-websocket-proxy v0.0.0-20190109142713-0ad062ec5ee5/go.mod h1:ncp9v5uamzpCO7NfCPTXjqaC+bZgJeR0sMTm6dMHP7U=
 github.com/xiang90/probing v0.0.0-20190116061207-43a291ad63a2/go.mod h1:UETIi67q53MR2AWcXfiuqkDkRtnGDLqkBTpCHuJHxtU=
+github.com/xuri/efp v0.0.0-20210322160811-ab561f5b45e3 h1:EpI0bqf/eX9SdZDwlMmahKM+CDBgNbsXMhsN28XrM8o=
+github.com/xuri/efp v0.0.0-20210322160811-ab561f5b45e3/go.mod h1:ybY/Jr0T0GTCnYjKqmdwxyxn2BQf2RcQIIvex5QldPI=
 go.etcd.io/bbolt v1.3.2/go.mod h1:IbVyRI1SCnLcuJnV2u8VeU0CEYM7e686BmAb1XKL+uU=
 go.etcd.io/bbolt v1.3.5 h1:XAzx9gjCb0Rxj7EoqcClPD1d5ZBxZJk0jbuoPHenBt0=
 go.etcd.io/bbolt v1.3.5/go.mod h1:G5EMThwa9y8QZGBClrRx5EY+Yw9kAhnjy3bSjsnlVTQ=
@@ -256,6 +271,7 @@
 golang.org/x/crypto v0.0.0-20190510104115-cbcb75029529/go.mod h1:yigFU9vqHzYiE8UmvKecakEJjdnWj3jj499lnFckfCI=
 golang.org/x/crypto v0.0.0-20190605123033-f99c8df09eb5/go.mod h1:yigFU9vqHzYiE8UmvKecakEJjdnWj3jj499lnFckfCI=
 golang.org/x/crypto v0.0.0-20190820162420-60c769a6c586/go.mod h1:yigFU9vqHzYiE8UmvKecakEJjdnWj3jj499lnFckfCI=
+golang.org/x/crypto v0.0.0-20210415154028-4f45737414dc/go.mod h1:T9bdIzuCu7OtxOm1hfPfRQxPLYneinmdGuTeoZ9dtd4=
 golang.org/x/crypto v0.0.0-20210513164829-c07d793c2f9a h1:kr2P4QFmQr29mSLA43kwrOcgcReGTfbE9N577tCTuBc=
 golang.org/x/crypto v0.0.0-20210513164829-c07d793c2f9a/go.mod h1:P+XmwS30IXTQdn5tA2iutPOUgjI07+tq3H3K9MVA1s8=
 golang.org/x/exp v0.0.0-20180321215751-8460e604b9de/go.mod h1:CJ0aWSM057203Lf6IL+f9T1iT9GByDxfZKAQTCR3kQA=
@@ -277,6 +293,8 @@
 golang.org/x/image v0.0.0-20200618115811-c13761719519/go.mod h1:FeLwcggjj3mMvU+oOTbSwawSJRM1uh48EjtB4UJZlP0=
 golang.org/x/image v0.0.0-20201208152932-35266b937fa6/go.mod h1:FeLwcggjj3mMvU+oOTbSwawSJRM1uh48EjtB4UJZlP0=
 golang.org/x/image v0.0.0-20210216034530-4410531fe030/go.mod h1:FeLwcggjj3mMvU+oOTbSwawSJRM1uh48EjtB4UJZlP0=
+golang.org/x/image v0.0.0-20210220032944-ac19c3e999fb h1:fqpd0EBDzlHRCjiphRR5Zo/RSWWQlWv34418dnEixWk=
+golang.org/x/image v0.0.0-20210220032944-ac19c3e999fb/go.mod h1:FeLwcggjj3mMvU+oOTbSwawSJRM1uh48EjtB4UJZlP0=
 golang.org/x/lint v0.0.0-20181026193005-c67002cb31c3/go.mod h1:UVdnD1Gm6xHRNCYTkRU2/jEulfH38KcIWyp/GAMgvoE=
 golang.org/x/lint v0.0.0-20190227174305-5b3e6a55c961/go.mod h1:wehouNa3lNwaWXcvxsM5YxQ5yQlVC4a0KAMCusXpPoU=
 golang.org/x/lint v0.0.0-20190301231843-5614ed5bae6f/go.mod h1:UVdnD1Gm6xHRNCYTkRU2/jEulfH38KcIWyp/GAMgvoE=
@@ -303,6 +321,7 @@
 golang.org/x/net v0.0.0-20190603091049-60506f45cf65/go.mod h1:HSz+uSET+XFnRR8LxR5pz3Of3rY3CfYBVs4xY44aLks=
 golang.org/x/net v0.0.0-20190620200207-3b0461eec859/go.mod h1:z5CRVTTTmAJ677TzLLGU+0bjPO0LkuOLi4/5GtJWs/s=
 golang.org/x/net v0.0.0-20210226172049-e18ecbb05110/go.mod h1:m0MpNAwzfU5UDzcl9v0D8zg8gWTRqZa9RBIspLL5mdg=
+golang.org/x/net v0.0.0-20210415231046-e915ea6b2b7d/go.mod h1:9tjilg8BloeKEkVJvy7fQ90B1CfIiPueXVOjqfkSzI8=
 golang.org/x/net v0.0.0-20210525063256-abc453219eb5 h1:wjuX4b5yYQnEQHzd+CBcrcC6OVR2J1CN6mUy0oSxIPo=
 golang.org/x/net v0.0.0-20210525063256-abc453219eb5/go.mod h1:9nx3DQGgdP8bBQD5qxJ1jj9UTztislL4KSBs9R2vV5Y=
 golang.org/x/oauth2 v0.0.0-20180821212333-d2e6202438be/go.mod h1:N/0e6XlmueqKjAGxoOufVs8QHGRruUQn6yWY3a++T0U=
@@ -330,6 +349,7 @@
 golang.org/x/sys v0.0.0-20200202164722-d101bd2416d5/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
 golang.org/x/sys v0.0.0-20201119102817-f84b799fce68/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
 golang.org/x/sys v0.0.0-20210304124612-50617c2ba197/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
+golang.org/x/sys v0.0.0-20210330210617-4fbd30eecc44/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
 golang.org/x/sys v0.0.0-20210423082822-04245dca01da/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
 golang.org/x/sys v0.0.0-20210525143221-35b2ab0089ea h1:+WiDlPBBaO+h9vPNZi8uJ3k4BkKQB7Iow3aqwHVA5hI=
 golang.org/x/sys v0.0.0-20210525143221-35b2ab0089ea/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
@@ -414,6 +434,8 @@
 gopkg.in/yaml.v2 v2.2.4/go.mod h1:hI93XBmqTisBFMUTm0b8Fm+jr3Dg1NNxqwp+5A1VGuI=
 gopkg.in/yaml.v2 v2.4.0 h1:D8xgwECY7CYvx+Y2n4sBz93Jn9JRvxdiyyo8CTfuKaY=
 gopkg.in/yaml.v2 v2.4.0/go.mod h1:RDklbk79AGWmwhnvt/jBztapEOGDOx6ZbXqjP6csGnQ=
+gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c h1:dUUwHk2QECo/6vqA44rthZ8ie2QXMNeKRTHCNY2nXvo=
+gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
 honnef.co/go/tools v0.0.0-20190102054323-c2f93a96b099/go.mod h1:rf3lG4BRIbNafJWhAfAdb/ePZxsR/4RtNHQocxwk9r4=
 honnef.co/go/tools v0.0.0-20190106161140-3f1c8253044a/go.mod h1:rf3lG4BRIbNafJWhAfAdb/ePZxsR/4RtNHQocxwk9r4=
 honnef.co/go/tools v0.0.0-20190418001031-e561f6794a2a/go.mod h1:rf3lG4BRIbNafJWhAfAdb/ePZxsR/4RtNHQocxwk9r4=
--- a/pkg/config/config.go	Wed May 26 19:10:45 2021 +0200
+++ b/pkg/config/config.go	Thu May 27 01:22:10 2021 +0200
@@ -122,6 +122,9 @@
 // SOAPTimeout is the timeout till a SOAP request is canceled.
 func SOAPTimeout() time.Duration { return viper.GetDuration("soap-timeout") }
 
+// ReportPath is a path where report templates are stored.
+func ReportPath() string { return viper.GetString("report-path") }
+
 var (
 	proxyKeyOnce       sync.Once
 	proxyKey           []byte
@@ -290,6 +293,8 @@
 
 	str("published-config", "", "path to a config file served to client.")
 
+	str("report-path", "", "path to a report templates.")
+
 	d("soap-timeout", 3*time.Minute, "Timeout till a SOAP request is canceled.")
 }
 
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pkg/controllers/report.go	Thu May 27 01:22:10 2021 +0200
@@ -0,0 +1,101 @@
+// 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 controllers
+
+import (
+	"fmt"
+	"log"
+	"net/http"
+	"os"
+	"path/filepath"
+
+	"gemma.intevation.de/gemma/pkg/config"
+	"gemma.intevation.de/gemma/pkg/middleware"
+	"gemma.intevation.de/gemma/pkg/xlsx"
+
+	"github.com/360EntSecGroup-Skylar/excelize/v2"
+	"github.com/gorilla/mux"
+)
+
+func report(rw http.ResponseWriter, req *http.Request) {
+
+	vars := mux.Vars(req)
+
+	name := vars["name"]
+
+	config.WaitReady()
+	path := config.ReportPath()
+	if path == "" {
+		http.NotFound(rw, req)
+		return
+	}
+
+	stat, err := os.Stat(path)
+	if err != nil {
+		http.Error(rw, "Error: "+err.Error(), http.StatusInternalServerError)
+		return
+	}
+
+	if !stat.Mode().IsDir() {
+		http.NotFound(rw, req)
+		return
+	}
+
+	xlsxFilename := filepath.Join(path, name+".xlsx")
+	yamlFilename := filepath.Join(path, name+".yaml")
+
+	_, errX := os.Stat(xlsxFilename)
+	_, errY := os.Stat(yamlFilename)
+	if errX != nil || errX != nil {
+		if (errX != nil && os.IsNotExist(errX)) || (errY != nil && os.IsNotExist(errY)) {
+			http.NotFound(rw, req)
+		} else {
+			http.Error(rw, "Something is wrong", http.StatusInternalServerError)
+		}
+		return
+	}
+
+	template, err := excelize.OpenFile(xlsxFilename)
+	if err != nil {
+		http.Error(rw, "Error: "+err.Error(), http.StatusInternalServerError)
+		log.Printf("error: %v\n", err)
+		return
+	}
+
+	action, err := xlsx.ActionFromFile(yamlFilename)
+	if err != nil {
+		http.Error(rw, "Error: "+err.Error(), http.StatusInternalServerError)
+		log.Printf("error: %v\n", err)
+		return
+	}
+
+	ctx := req.Context()
+	conn := middleware.GetDBConn(req)
+
+	if err := action.Execute(ctx, conn, template); err != nil {
+		http.Error(rw, "Error: "+err.Error(), http.StatusInternalServerError)
+		log.Printf("error: %v\n", err)
+		return
+	}
+	rw.Header().Set(
+		"Content-Disposition",
+		fmt.Sprintf("attachment; filename=%s.xlsx", name))
+	rw.Header().Set(
+		"Content-Type",
+		"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
+
+	if _, err := template.WriteTo(rw); err != nil {
+		log.Printf("error: %v\n", err)
+	}
+}
--- a/pkg/controllers/routes.go	Wed May 26 19:10:45 2021 +0200
+++ b/pkg/controllers/routes.go	Thu May 27 01:22:10 2021 +0200
@@ -322,6 +322,11 @@
 			NoConn: true,
 		})).Methods(http.MethodPut)
 
+	// Handler for reporting
+
+	api.Handle("/data/report/{name:[a-zA-Z0-9_]+}", waterwayAdmin(
+		mw.DBConn(http.HandlerFunc(report)))).Methods(http.MethodGet)
+
 	// Handler to serve data to the client.
 
 	api.Handle("/data/{type:availability|fairway}/{kind:stretch|section|bottleneck}/{name:.+}", any(
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pkg/xlsx/handlebars.go	Thu May 27 01:22:10 2021 +0200
@@ -0,0 +1,83 @@
+// 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 "strings"
+
+func handlebars(s string, replace func(string) string) string {
+
+	var (
+		out, repl strings.Builder
+		mode      int
+	)
+
+	for _, c := range s {
+		switch mode {
+		case 0:
+			if c == '{' {
+				mode = 1
+			} else {
+				out.WriteRune(c)
+			}
+		case 1:
+			if c == '{' {
+				mode = 2
+			} else {
+				out.WriteByte('{')
+				out.WriteRune(c)
+				mode = 0
+			}
+		case 2:
+			switch c {
+			case '\\':
+				mode = 3
+			case '}':
+				mode = 4
+			default:
+				repl.WriteRune(c)
+			}
+		case 3:
+			repl.WriteRune(c)
+			mode = 2
+		case 4:
+			if c == '}' {
+				out.WriteString(replace(repl.String()))
+				repl.Reset()
+				mode = 0
+			} else {
+				repl.WriteByte('}')
+				repl.WriteRune(c)
+				mode = 2
+			}
+		}
+	}
+
+	switch mode {
+	case 1:
+		out.WriteByte('{')
+	case 2:
+		out.WriteString("{{")
+		out.WriteString(repl.String())
+	case 3:
+		out.WriteString("{{")
+		out.WriteString(repl.String())
+		out.WriteByte('\\')
+	case 4:
+		out.WriteString("{{")
+		out.WriteString(repl.String())
+		out.WriteByte('}')
+	}
+
+	return out.String()
+}
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pkg/xlsx/sql.go	Thu May 27 01:22:10 2021 +0200
@@ -0,0 +1,109 @@
+// 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 (
+	"context"
+	"database/sql"
+	"strconv"
+	"strings"
+)
+
+type sqlResult struct {
+	columns []string
+	rows    [][]interface{}
+}
+
+func (sr *sqlResult) find(column string) int {
+	for i, name := range sr.columns {
+		if name == column {
+			return i
+		}
+	}
+	return -1
+}
+
+func replaceStmt(stmt string) (string, []string) {
+
+	var names []string
+
+	add := func(name string) int {
+		for i, n := range names {
+			if n == name {
+				return i
+			}
+		}
+		names = append(names, name)
+		return len(names)
+	}
+
+	replace := func(s string) string {
+		n := add(strings.TrimSpace(s))
+		return "$" + strconv.Itoa(n)
+	}
+
+	out := handlebars(stmt, replace)
+	return out, names
+}
+
+func query(
+	ctx context.Context,
+	db *sql.Conn,
+	stmt string,
+	eval func(string) (interface{}, error),
+) (*sqlResult, error) {
+
+	nstmt, nargs := replaceStmt(stmt)
+	args := make([]interface{}, len(nargs))
+	for i, n := range nargs {
+		var err error
+		if args[i], err = eval(n); err != nil {
+			return nil, err
+		}
+	}
+
+	rs, err := db.QueryContext(ctx, nstmt, args...)
+	if err != nil {
+		return nil, err
+	}
+	defer rs.Close()
+
+	columns, err := rs.Columns()
+	if err != nil {
+		return nil, err
+	}
+	var rows [][]interface{}
+
+	ptrs := make([]interface{}, len(columns))
+
+	for rs.Next() {
+		row := make([]interface{}, len(columns))
+		for i := range row {
+			ptrs[i] = &row[i]
+		}
+		if err := rs.Scan(ptrs...); err != nil {
+			return nil, err
+		}
+		rows = append(rows, row)
+	}
+
+	if err := rs.Err(); err != nil {
+		return nil, err
+	}
+
+	return &sqlResult{
+		columns: columns,
+		rows:    rows,
+	}, nil
+}
--- /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
+}