Mercurial > gemma
view pkg/xlsx/templater.go @ 5448:25d0d3159376 uiimprovements
Esc/click to stop operation.
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Tue, 13 Jul 2021 15:22:37 +0200 |
parents | 1adbd6a5f849 |
children | 699048c86848 |
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" "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 }