Mercurial > gemma
view pkg/xlsx/templater.go @ 5321:0919946f624b extented-report
Added a report controller.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Thu, 27 May 2021 01:22:10 +0200 |
parents | |
children | 313bf3f3a8b1 |
line wrap: on
line source
// 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 }