# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1624402871 -7200
# Node ID 1adbd6a5f8495611ede4e1b8eed77ede3cb3acb5
# Parent  04b3fb35910c88ca70f61478c6bc4d12d77d20b9
XLSX templater: Cache cell formulas for performance.

diff -r 04b3fb35910c -r 1adbd6a5f849 pkg/xlsx/templater.go
--- a/pkg/xlsx/templater.go	Tue Jun 22 21:58:28 2021 +0200
+++ b/pkg/xlsx/templater.go	Wed Jun 23 01:01:11 2021 +0200
@@ -48,6 +48,16 @@
 	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
@@ -57,6 +67,8 @@
 	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 {
@@ -110,12 +122,18 @@
 	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{},
+		ctx:          ctx,
+		tx:           tx,
+		template:     template,
+		keep:         map[string]bool{},
+		expressions:  map[string]gval.Evaluable{},
+		formulaCache: map[sheetAxis]cellValue{},
 	}
 
 	oldSheets := template.GetSheetList()
@@ -132,6 +150,26 @@
 	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)
@@ -291,8 +329,8 @@
 							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.getCellFormula(e.sourceSheet, src); err == nil {
+								e.setCellFormula(e.destinationSheet, dst, s)
 							}
 						}
 					}
@@ -358,8 +396,8 @@
 			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.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 {