@jsreport/jsreport-xlsx - npm Package Compare versions

Comparing version 4.2.0 to 4.3.0

lib/cellUtils.js

 const { decode } = require('html-entities')
-const { col2num } = require('xlsx-coordinates')
+const { col2num, num2col } = require('xlsx-coordinates')
 const pixelWidth = require('string-pixel-width')
@@ -72,2 +72,16 @@
 
+function getNewCellLetter (cellLetter, increment) {
+  if (typeof increment !== 'number' || isNaN(increment)) {
+    throw new Error('Increment must be a number')
+  }
+
+  const colNumber = col2num(cellLetter) + increment
+
+  if (colNumber < 0) {
+    throw new Error('Column number can not be negative')
+  }
+
+  return num2col(colNumber)
+}
+
 function getPixelWidthOfValue (value, fontSize) {
@@ -199,3 +213,6 @@ const fontSizeInPx = fontSize * (96 / 72)
 
-  const { originCellIsFromLoop, previousLoopIncrement, currentLoopIncrement, trackedCells } = meta
+  const {
+    originCellIsFromLoop, rowPreviousLoopIncrement, rowCurrentLoopIncrement,
+    columnPreviousLoopIncrement, columnCurrentLoopIncrement, trackedCells
+  } = meta
 
@@ -213,3 +230,3 @@ const { newValue } = evaluateCellRefsFromExpression(originalFormula, (cellRefInfo, cellRefPosition) => {
       if (!shouldEvaluate) {
-        return generateNewCellRefFromRow(cellRefInfo.parsed, cellRefInfo.parsed.rowNumber)
+        return generateNewCellRefFrom(cellRefInfo.parsed, { rowNumber: cellRefInfo.parsed.rowNumber })
       }
@@ -245,16 +262,12 @@ }
     let newRowNumber
+    let newColumnLetter
 
     if (!isLocalRef) {
       // cell references to other sheets
+      newColumnLetter = cellRefInfo.parsed.letter
       newRowNumber = cellRefInfo.parsed.rowNumber
 
       if (!cellRefInfo.parsed.lockedRow) {
-        newRowNumber += currentLoopIncrement
+        newRowNumber += rowCurrentLoopIncrement
       }
-
-      // else if (parsedNewOriginCellRef.rowNumber === parsedOriginCellRef.rowNumber) {
-      //   // if cells were not changed then we don't need to do anything and let
-      //   // the normal cell reference of the formula as it is
-      //   newRowNumber = cellRefInfo.parsed.rowNumber
-      // }
     } else if (meta.type === 'normal' && parsedOriginCellRef.rowNumber < cellRefInfo.parsed.rowNumber) {
@@ -269,7 +282,8 @@ // if formula has a cell reference that is greater than origin then we
 
-      // left the cell as it is
+      // let the cell as it is
       // (the final row number will be calculated later in other helper)
       newRowNumber = cellRefInfo.parsed.rowNumber
     } else {
-      let increment
+      let columnIncrement
+      let rowIncrement
 
@@ -279,3 +293,3 @@ if (trackedCells[cellRefInfo.localRef] != null && trackedCells[cellRefInfo.localRef].count > 0) {
 
-        const shouldUseFirst = (
+        const shouldUseFirstForRow = (
           cellRefInfo.parsed.lockedRow ||
@@ -285,17 +299,35 @@ (!originCellIsFromLoop && cellRefIsFromLoop && cellRefInfo.type === 'rangeStart')
 
-        const parsedLastCellRef = shouldUseFirst ? parseCellRef(tracked.first) : parseCellRef(tracked.last)
-        increment = parsedLastCellRef.rowNumber - cellRefInfo.parsed.rowNumber
+        let parsedLastCellRef = shouldUseFirstForRow ? parseCellRef(tracked.first) : parseCellRef(tracked.last)
+        rowIncrement = parsedLastCellRef.rowNumber - cellRefInfo.parsed.rowNumber
+
+        const shouldUseFirstForColumn = (
+          cellRefInfo.parsed.lockedColumn ||
+          (!originCellIsFromLoop && cellRefIsFromLoop && cellRefInfo.type === 'rangeStart')
+        )
+
+        parsedLastCellRef = shouldUseFirstForColumn ? parseCellRef(tracked.first) : parseCellRef(tracked.last)
+        columnIncrement = parsedLastCellRef.columnNumber - cellRefInfo.parsed.columnNumber
       } else {
         // cell reference points to cell which does not exists as content of the template
-        increment = previousLoopIncrement
+        rowIncrement = rowPreviousLoopIncrement
 
         if (includeLoopIncrement) {
-          increment += currentLoopIncrement
+          rowIncrement += rowCurrentLoopIncrement
         }
+
+        columnIncrement = columnPreviousLoopIncrement
+
+        if (includeLoopIncrement) {
+          columnIncrement += columnCurrentLoopIncrement
+        }
       }
 
-      newRowNumber = cellRefInfo.parsed.rowNumber + increment
+      newColumnLetter = getNewCellLetter(cellRefInfo.parsed.letter, columnIncrement)
+      newRowNumber = cellRefInfo.parsed.rowNumber + rowIncrement
     }
 
-    const newCellRef = generateNewCellRefFromRow(cellRefInfo.parsed, newRowNumber)
+    const newCellRef = generateNewCellRefFrom(cellRefInfo.parsed, {
+      columnLetter: newColumnLetter,
+      rowNumber: newRowNumber
+    })
 
@@ -324,4 +356,6 @@ return newCellRef
       originCellIsFromLoop,
-      previousLoopIncrement,
-      currentLoopIncrement,
+      rowPreviousLoopIncrement,
+      rowCurrentLoopIncrement,
+      columnPreviousLoopIncrement,
+      columnCurrentLoopIncrement,
       cellRefs: result.lazyCellRefs
@@ -340,3 +374,3 @@ }
 
-function generateNewCellRefFromRow (parsedCellRef, rowNumber, fullMetadata = false) {
+function generateNewCellRefFrom (parsedCellRef, { columnLetter, rowNumber }, fullMetadata = false) {
   let prefix = ''
@@ -366,3 +400,6 @@
 
-  return `${prefix}${parsedCellRef.lockedColumn ? '$' : ''}${parsedCellRef.letter}${parsedCellRef.lockedRow ? '$' : ''}${rowNumber}`
+  const letter = columnLetter != null ? columnLetter : parsedCellRef.letter
+  const number = rowNumber != null ? rowNumber : parsedCellRef.rowNumber
+
+  return `${prefix}${parsedCellRef.lockedColumn ? '$' : ''}${letter}${parsedCellRef.lockedRow ? '$' : ''}${number}`
 }
@@ -406,2 +443,3 @@
 module.exports.parseCellRef = parseCellRef
+module.exports.getNewCellLetter = getNewCellLetter
 module.exports.getPixelWidthOfValue = getPixelWidthOfValue
@@ -411,4 +449,4 @@ module.exports.getFontSizeFromStyle = getFontSizeFromStyle
 module.exports.getNewFormula = getNewFormula
-module.exports.generateNewCellRefFromRow = generateNewCellRefFromRow
+module.exports.generateNewCellRefFrom = generateNewCellRefFrom
 module.exports.encodeXML = encodeXML
 module.exports.decodeXML = decodeXML

lib/generation/preprocess/loop.js

@@ -5,6 +5,5 @@ const path = require('path')
 const { parseCellRef, getPixelWidthOfValue, getFontSizeFromStyle, evaluateCellRefsFromExpression } = require('../../cellUtils')
-const startLoopRegexp = /{{#each ([^{}]{0,500})}}/
+const startLoopRegexp = /{{#each\s+([^{|}]{0,500})(?:\s*(as\s+\|\w+\|))?\s*}}/
 
-module.exports = (files, meta) => {
-  // console.time('pre:loop process setup')
+module.exports = (files, ctx) => {
   const workbookDoc = files.find((file) => file.path === 'xl/workbook.xml')?.doc
@@ -88,4 +87,4 @@ const workbookRelsDoc = files.find((file) => file.path === 'xl/_rels/workbook.xml.rels')?.doc
 
-      if (meta.autofitConfigured !== true) {
-        meta.autofitConfigured = true
+      if (ctx.autofitConfigured !== true) {
+        ctx.autofitConfigured = true
       }
@@ -188,2 +187,3 @@ }
 
+    const previousRows = []
     const loopsDetected = []
@@ -193,4 +193,7 @@ const outOfLoopElsToHandle = []
     const formulaCellElsToHandle = []
+    const cellsByRowMap = new Map()
     const cellsElsByRefMap = new Map()
     const colMaxSizeMap = new Map()
+    const staticCellElsToHandle = []
+    const contentDetectCellElsToHandle = []
 
@@ -202,4 +205,2 @@ const lastRowIdx = rowsEls.length - 1
       const isLastRow = rowIdx === lastRowIdx
-      const staticCellElsToHandle = []
-      const contentDetectCellElsToHandle = []
 
@@ -240,3 +241,4 @@ if (originalRowNumber == null || originalRowNumber === '') {
         const cellMeta = {
-          letter: parsedCellRef.letter
+          letter: parsedCellRef.letter,
+          columnNumber: parsedCellRef.columnNumber
         }
@@ -246,2 +248,8 @@
 
+        if (!cellsByRowMap.has(originalRowNumber)) {
+          cellsByRowMap.set(originalRowNumber, [])
+        }
+
+        cellsByRowMap.get(originalRowNumber).push(cellRef)
+
         let cellCallType = '_C'
@@ -271,3 +279,2 @@
 
-        const currentLoopDetected = getLatestNotClosedLoop(loopsDetected)
         const info = getCellInfo(cellEl, sharedStringsEls, sheetFilepath)
@@ -280,52 +287,135 @@
         ) {
-          // only do content detection for the cells with handlebars
-          if (info.value.includes('{{') && info.value.includes('}}')) {
-            contentDetectCellElsToHandle.push(cellRef)
-          } else if (isAutofitConfigured) {
-            staticCellElsToHandle.push(cellRef)
-          }
+          const loopStartOrEndRegExp = /{{[#/]each/
+          const textWithoutLoopParts = []
+          let remainingToCheck = info.value
+          let lastStartCall = ''
 
-          if (
-            currentLoopDetected != null &&
-            info.value.includes('{{/each}}') &&
-            !info.value.includes('{{#each')
-          ) {
-            currentLoopDetected.end = {
-              el: cellEl,
-              cellRef,
-              info,
-              originalRowNumber
-            }
+          const normalizeIfVerticalLoop = (cLoop, callStr) => {
+            if (cLoop?.end == null && callStr !== '') {
+              const loopHelperCall = callStr.match(startLoopRegexp)[0]
+              const isVertical = cLoop.type === 'block' && loopHelperCall != null && loopHelperCall.includes('vertical=')
 
-            if (currentLoopDetected.end.originalRowNumber === currentLoopDetected.start.originalRowNumber) {
-              currentLoopDetected.type = 'row'
+              if (isVertical) {
+                cLoop.type = 'vertical'
+              }
             }
-          } else if (
-            info.value.includes('{{#each') &&
-            !info.value.includes('{{/each}}')
-          ) {
-            const hierarchyIdPrefix = currentLoopDetected == null ? '' : `${currentLoopDetected.hierarchyId}#`
-            const hierarchyIdCounter = currentLoopDetected == null ? loopsDetected.length : currentLoopDetected.children.length
+          }
 
-            const hierarchyId = `${hierarchyIdPrefix}${hierarchyIdCounter}`
+          do {
+            const match = remainingToCheck.match(loopStartOrEndRegExp)
+            let currentLoopDetected = getLatestNotClosedLoop(loopsDetected)
 
-            const newLoopItem = {
-              type: 'block',
-              hierarchyId,
-              blockStartEl: null,
-              blockEndEl: null,
-              children: [],
-              start: {
-                el: cellEl,
-                cellRef,
-                info,
-                originalRowNumber
+            if (match != null) {
+              const partType = match[0] === '{{#each' ? 'start' : 'end'
+              let inlineLoop = false
+              let newLoopItem
+
+              if (currentLoopDetected != null && partType === 'end') {
+                // if loop starts and end in same cell then
+                // we don't consider it a loop for our purposes
+                // (it is just a normal loop that creates strings not rows/cells)
+                if (currentLoopDetected.start.el === cellEl) {
+                  inlineLoop = true
+                  loopsDetected.pop()
+                } else {
+                  if (currentLoopDetected.type === 'vertical') {
+                    let foundMatchingVerticalLoop = false
+                    const latestNotClosedLoops = getLatestNotClosedLoop(loopsDetected, true).reverse()
+
+                    for (const latestLoop of latestNotClosedLoops) {
+                      if (latestLoop.type === 'vertical' && parsedCellRef.columnNumber === latestLoop.start.originalColumnNumber) {
+                        foundMatchingVerticalLoop = latestLoop
+                        break
+                      }
+                    }
+
+                    if (!foundMatchingVerticalLoop) {
+                      throw new Error(`Unable to match start {{#each}} and end {{/each}} of vertical loop for multiple rows in ${f.path}. both start and end of loop must be on same column`)
+                    }
+
+                    currentLoopDetected = foundMatchingVerticalLoop
+                  }
+
+                  currentLoopDetected.end = {
+                    el: cellEl,
+                    cellRef,
+                    info,
+                    originalRowNumber,
+                    originalColumnNumber: parsedCellRef.columnNumber
+                  }
+
+                  if (currentLoopDetected.end.originalRowNumber === currentLoopDetected.start.originalRowNumber) {
+                    currentLoopDetected.type = 'row'
+                  }
+                }
+              } else if (partType === 'start') {
+                normalizeIfVerticalLoop(currentLoopDetected, lastStartCall)
+
+                const isNested = currentLoopDetected != null && currentLoopDetected.type !== 'vertical'
+
+                const hierarchyIdPrefix = isNested ? `${currentLoopDetected.hierarchyId}#` : ''
+                const hierarchyIdCounter = isNested ? currentLoopDetected.children.length : loopsDetected.length
+
+                const hierarchyId = `${hierarchyIdPrefix}${hierarchyIdCounter}`
+
+                newLoopItem = {
+                  type: 'block',
+                  hierarchyId,
+                  blockStartEl: null,
+                  blockEndEl: null,
+                  children: [],
+                  start: {
+                    el: cellEl,
+                    cellRef,
+                    info,
+                    originalRowNumber,
+                    originalColumnNumber: parsedCellRef.columnNumber
+                  }
+                }
+
+                if (isNested) {
+                  currentLoopDetected.children.push(newLoopItem)
+                }
+
+                loopsDetected.push(newLoopItem)
               }
-            }
 
-            if (currentLoopDetected != null) {
-              currentLoopDetected.children.push(newLoopItem)
+              const restLeft = remainingToCheck.slice(0, match.index)
+
+              if (restLeft !== '') {
+                textWithoutLoopParts.push(inlineLoop ? `${lastStartCall}${restLeft}{{/each}}` : restLeft)
+              }
+
+              const endLoopPartIdx = remainingToCheck.indexOf('}}', match.index + match[0].length)
+              let lastPartIdx
+
+              if (endLoopPartIdx !== -1) {
+                lastPartIdx = endLoopPartIdx + 2
+              } else {
+                lastPartIdx = match.index + match[0].length
+              }
+
+              if (partType === 'start') {
+                lastStartCall = remainingToCheck.slice(match.index, lastPartIdx)
+                newLoopItem.start.helperCall = lastStartCall
+              }
+
+              remainingToCheck = remainingToCheck.slice(lastPartIdx)
+            } else {
+              textWithoutLoopParts.push(remainingToCheck)
+              remainingToCheck = ''
+              normalizeIfVerticalLoop(currentLoopDetected, lastStartCall)
             }
+          } while (remainingToCheck !== '')
 
-            loopsDetected.push(newLoopItem)
+          // only do content detection for the cells with handlebars
+          if (info.value.includes('{{') && info.value.includes('}}')) {
+            const textWithoutLoop = textWithoutLoopParts.join('')
+
+            contentDetectCellElsToHandle.push({
+              cellRef,
+              normalizedText: textWithoutLoop
+            })
+          } else if (isAutofitConfigured) {
+            staticCellElsToHandle.push(cellRef)
           }
@@ -373,21 +463,18 @@ } else if (
 
-      const rowLoops = loopsDetected.filter((l) => l.type === 'row' && l.start.originalRowNumber === originalRowNumber)
-      const invalidRowLoop = rowLoops.find((l) => l.end == null)
+      const loopsToProcess = checkAndGetLoopsToProcess(f, loopsDetected, originalRowNumber, isLastRow)
 
-      if (invalidRowLoop != null) {
-        throw new Error(`Unable to find end of loop (#each) in ${f.path}. {{/each}} is missing`)
-      }
-
-      for (const currentRowLoopDetected of rowLoops) {
+      for (const currentLoop of loopsToProcess) {
         // we should remove the handlebars loop call from the start/end cell
-        normalizeLoopStartEndCell(currentRowLoopDetected)
+        normalizeLoopStartEndCell(currentLoop)
 
-        const startingRowEl = currentRowLoopDetected.start.el.parentNode
-        const loopHelperCall = currentRowLoopDetected.start.info.value.match(startLoopRegexp)[0]
+        const startingRowEl = currentLoop.start.el.parentNode
+        const endingRowEl = currentLoop.end.el.parentNode
+        const loopHelperCall = currentLoop.start.helperCall
+        const isVertical = currentLoop.type === 'vertical'
 
         const outOfLoopTypes = []
-        const previousEls = getCellElsAndWrappersFrom(currentRowLoopDetected.start.el, 'previous')
-        const nextEls = getCellElsAndWrappersFrom(currentRowLoopDetected.end.el, 'next')
+        const previousEls = getCellElsAndWrappersFrom(currentLoop.start.el, 'previous')
+        const nextEls = getCellElsAndWrappersFrom(currentLoop.end.el, 'next')
 
-        if (previousEls.length > 0) {
+        if (!isVertical && previousEls.length > 0) {
           // specify that there are cells to preserve that are before the each
@@ -397,3 +484,3 @@ outOfLoopTypes.push('left')
 
-        if (nextEls.length > 0) {
+        if (!isVertical && nextEls.length > 0) {
           // specify that there are cells to preserve that are after the each
@@ -405,5 +492,5 @@ outOfLoopTypes.push('right')
           outOfLoopElsToHandle.push({
-            loopDetected: currentRowLoopDetected,
+            loopDetected: currentLoop,
             startingRowEl,
-            endingRowEl: startingRowEl,
+            endingRowEl,
             types: outOfLoopTypes
@@ -413,118 +500,111 @@ })
 
-        // we want to put the loop wrapper around the row wrapper
-        currentRowLoopDetected.blockStartEl = processOpeningTag(sheetDoc, rowEl.previousSibling, loopHelperCall.replace(startLoopRegexp, (match, valueInsideEachCall) => {
-          const parsedLoopStart = parseCellRef(currentRowLoopDetected.start.cellRef)
-          const parsedLoopEnd = parseCellRef(currentRowLoopDetected.end.cellRef)
-          return `{{#_D ${valueInsideEachCall} t='loop' hierarchyId='${currentRowLoopDetected.hierarchyId}' start=${originalRowNumber} columnStart=${parsedLoopStart.columnNumber} columnEnd=${parsedLoopEnd.columnNumber} }}`
-        }))
+        const parsedLoopStart = parseCellRef(currentLoop.start.cellRef)
+        const parsedLoopEnd = parseCellRef(currentLoop.end.cellRef)
 
-        // we want to put the loop wrapper around the row wrapper
-        currentRowLoopDetected.blockEndEl = processClosingTag(sheetDoc, rowEl.nextSibling, '{{/_D}}')
-      }
+        const replaceLoopMatch = (match, dataExpressionPart, asPart) => {
+          const targetDataExpressionPart = dataExpressionPart.trimEnd()
+          const targetAsPart = asPart != null && asPart !== '' ? asPart.trim() : ''
 
-      const blockLoops = loopsDetected.filter((l) => l.type === 'block' && l.end?.originalRowNumber === originalRowNumber)
+          const extraAttrs = [
+            `hierarchyId='${currentLoop.hierarchyId}'`,
+            `start=${currentLoop.start.originalRowNumber}`
+          ]
 
-      if (isLastRow) {
-        const invalidBlockLoop = blockLoops.find((l) => l.end == null)
+          if (currentLoop.type === 'block') {
+            extraAttrs.push(`end=${currentLoop.end.originalRowNumber}`)
+          }
 
-        if (invalidBlockLoop) {
-          throw new Error(`Unable to find end of block loop (#each) in ${f.path}. {{/each}} is missing`)
-        }
-      }
+          extraAttrs.push(
+            `columnStart=${parsedLoopStart.columnNumber}`,
+            `columnEnd=${parsedLoopEnd.columnNumber}`
+          )
 
-      for (const currentBlockLoopDetected of blockLoops) {
-        // we should remove the handlebars loop call from the start/end cell
-        normalizeLoopStartEndCell(currentBlockLoopDetected)
+          if (targetAsPart !== '') {
+            extraAttrs.push(targetAsPart)
+          }
 
-        const startingRowEl = currentBlockLoopDetected.start.el.parentNode
-        const endingRowEl = currentBlockLoopDetected.end.el.parentNode
-        const loopHelperCall = currentBlockLoopDetected.start.info.value.match(startLoopRegexp)[0]
-
-        const outOfLoopTypes = []
-        const previousEls = getCellElsAndWrappersFrom(currentBlockLoopDetected.start.el, 'previous')
-        const nextEls = getCellElsAndWrappersFrom(currentBlockLoopDetected.end.el, 'next')
-
-        if (previousEls.length > 0) {
-          // specify that there are cells to preserve that are before the each
-          outOfLoopTypes.push('left')
+          return `{{#_D ${targetDataExpressionPart} t='loop' ${extraAttrs.join(' ')}}}`
         }
 
-        if (nextEls.length > 0) {
-          // specify that there are cells to preserve that are after the each
-          outOfLoopTypes.push('right')
-        }
+        if (isVertical) {
+          const affectedRowNumbers = previousRows.filter(([pRowNumber]) => {
+            return (
+              pRowNumber >= parsedLoopStart.rowNumber &&
+              pRowNumber <= parsedLoopEnd.rowNumber
+            )
+          }).map(([pRowNumber]) => pRowNumber)
 
-        if (outOfLoopTypes.length > 0) {
-          outOfLoopElsToHandle.push({
-            loopDetected: currentBlockLoopDetected,
-            startingRowEl,
-            endingRowEl,
-            types: outOfLoopTypes
-          })
-        }
+          affectedRowNumbers.push(parsedLoopEnd.rowNumber)
 
-        // we want to put the loop wrapper around the start row wrapper
-        currentBlockLoopDetected.blockStartEl = processOpeningTag(sheetDoc, startingRowEl.previousSibling, loopHelperCall.replace(startLoopRegexp, (match, valueInsideEachCall) => {
-          const parsedLoopStart = parseCellRef(currentBlockLoopDetected.start.cellRef)
-          const parsedLoopEnd = parseCellRef(currentBlockLoopDetected.end.cellRef)
-          return `{{#_D ${valueInsideEachCall} t='loop' hierarchyId='${currentBlockLoopDetected.hierarchyId}' start=${currentBlockLoopDetected.start.originalRowNumber} columnStart=${parsedLoopStart.columnNumber} end=${currentBlockLoopDetected.end.originalRowNumber} columnEnd=${parsedLoopEnd.columnNumber} }}`
-        }))
+          for (const currentRowNumber of affectedRowNumbers) {
+            const cellElsInRow = cellsByRowMap.get(currentRowNumber).filter((cellRef) => {
+              const [, cellMeta] = cellsElsByRefMap.get(cellRef)
 
-        // we want to put the loop wrapper around the end row wrapper
-        currentBlockLoopDetected.blockEndEl = processClosingTag(sheetDoc, endingRowEl.nextSibling, '{{/_D}}')
-      }
+              return (
+                cellMeta.columnNumber >= parsedLoopStart.columnNumber &&
+                cellMeta.columnNumber <= parsedLoopEnd.columnNumber
+              )
+            }).sort((a, b) => {
+              const [, aMeta] = cellsElsByRefMap.get(a)
+              const [, bMeta] = cellsElsByRefMap.get(b)
+              return aMeta.columnNumber - bMeta.columnNumber
+            }).map((cellRef) => cellsElsByRefMap.get(cellRef)[0])
 
-      for (const cellRef of staticCellElsToHandle) {
-        const [cellEl, cellMeta] = cellsElsByRefMap.get(cellRef)
-        const cellInfo = getCellInfo(cellEl, sharedStringsEls, sheetFilepath)
-        const fontSize = getFontSizeFromStyle(cellEl.getAttribute('s'), styleInfo)
+            const startEl = cellElsInRow[0]
 
-        const currentMaxSize = colMaxSizeMap.get(cellMeta.letter)
-        const currentSize = getPixelWidthOfValue(cellInfo.value, fontSize)
+            processOpeningTag(sheetDoc, startEl, loopHelperCall.replace(startLoopRegexp, replaceLoopMatch))
+            processClosingTag(sheetDoc, startEl, '{{/_D}}')
+          }
+        } else {
+          // we want to put the loop wrapper around the start row wrapper
+          currentLoop.blockStartEl = processOpeningTag(sheetDoc, startingRowEl.previousSibling, loopHelperCall.replace(startLoopRegexp, replaceLoopMatch))
 
-        if (currentMaxSize == null || currentSize > currentMaxSize) {
-          colMaxSizeMap.set(cellMeta.letter, currentSize)
+          // we want to put the loop wrapper around the end row wrapper
+          currentLoop.blockEndEl = processClosingTag(sheetDoc, endingRowEl.nextSibling, '{{/_D}}')
         }
       }
 
-      for (const cellRef of contentDetectCellElsToHandle) {
-        const [cellEl, cellMeta] = cellsElsByRefMap.get(cellRef)
-        const cellInfo = getCellInfo(cellEl, sharedStringsEls, sheetFilepath)
+      previousRows.push([originalRowNumber, rowEl])
+    }
 
-        let newTextValue
+    for (const cellRef of staticCellElsToHandle) {
+      const [cellEl, cellMeta] = cellsElsByRefMap.get(cellRef)
+      const cellInfo = getCellInfo(cellEl, sharedStringsEls, sheetFilepath)
+      const fontSize = getFontSizeFromStyle(cellEl.getAttribute('s'), styleInfo)
 
-        const isPartOfLoopStart = loopsDetected.find((l) => l.start.el === cellEl) != null
-        const isPartOfLoopEnd = loopsDetected.find((l) => l.end?.el === cellEl) != null
+      const currentMaxSize = colMaxSizeMap.get(cellMeta.letter)
+      const currentSize = getPixelWidthOfValue(cellInfo.value, fontSize)
 
-        if (isPartOfLoopStart) {
-          newTextValue = cellInfo.value.replace(startLoopRegexp, '')
-        } else if (isPartOfLoopEnd) {
-          newTextValue = cellInfo.value.replace('{{/each}}', '')
-        } else {
-          newTextValue = cellInfo.value
-        }
+      if (currentMaxSize == null || currentSize > currentMaxSize) {
+        colMaxSizeMap.set(cellMeta.letter, currentSize)
+      }
+    }
 
-        const handlebarsRegexp = /{{{?(#[\w-]+ )?([\w-]+[^\n\r}]*)}?}}/g
-        const matches = Array.from(newTextValue.matchAll(handlebarsRegexp))
-        const isSingleMatch = matches.length === 1 && matches[0][0] === newTextValue && matches[0][1] == null
+    for (const { cellRef, normalizedText } of contentDetectCellElsToHandle) {
+      const [cellEl, cellMeta] = cellsElsByRefMap.get(cellRef)
 
-        cellEl.setAttribute('r', cellMeta.letter)
-        cellEl.setAttribute('__CT_t__', '_T')
-        cellEl.setAttribute('__CT_m__', isSingleMatch ? '0' : '1')
-        cellEl.setAttribute('__CT_cCU__', cellMeta.calcChainUpdate ? '1' : '0')
+      const newTextValue = normalizedText
 
-        if (isSingleMatch) {
-          const match = matches[0]
-          const shouldEscape = !match[0].startsWith('{{{')
-          const expressionValue = match[2]
-          const value = expressionValue.includes(' ') ? `(${expressionValue})` : expressionValue
+      const handlebarsRegexp = /{{{?(#[\w-]+\s+)?([\w-]+[^\n\r}]*)}?}}/g
+      const matches = Array.from(newTextValue.matchAll(handlebarsRegexp))
+      const isSingleMatch = matches.length === 1 && matches[0][0] === newTextValue && matches[0][1] == null
 
-          cellEl.setAttribute('__CT_v__', value)
-          cellEl.setAttribute('__CT_ve__', shouldEscape ? '1' : '0')
-        }
+      cellEl.setAttribute('r', cellMeta.letter)
+      cellEl.setAttribute('__CT_t__', '_T')
+      cellEl.setAttribute('__CT_m__', isSingleMatch ? '0' : '1')
+      cellEl.setAttribute('__CT_cCU__', cellMeta.calcChainUpdate ? '1' : '0')
 
-        // when multi-expression put the content with handlebars as the only content of the cell,
-        // for the rest of cases put a space to avoid the cell to be serialized as self closing tag
-        cellEl.textContent = isSingleMatch ? ' ' : newTextValue
+      if (isSingleMatch) {
+        const match = matches[0]
+        const shouldEscape = !match[0].startsWith('{{{')
+        const expressionValue = match[2]
+        const value = expressionValue.includes(' ') ? `(${expressionValue})` : expressionValue
+
+        cellEl.setAttribute('__CT_v__', value)
+        cellEl.setAttribute('__CT_ve__', shouldEscape ? '1' : '0')
       }
+
+      // when multi-expression put the content with handlebars as the only content of the cell,
+      // for the rest of cases put a space to avoid the cell to be serialized as self closing tag
+      cellEl.textContent = isSingleMatch ? ' ' : newTextValue
     }
@@ -546,14 +626,47 @@
         const toCloneEls = []
+        const currentRowNumber = type === 'left' ? loopDetected.start.originalRowNumber : loopDetected.end.originalRowNumber
         const currentEl = type === 'left' ? loopDetected.start.el : loopDetected.end.el
 
+        const remainingCellRefsInRow = cellsByRowMap.get(currentRowNumber).filter((cellRef) => {
+          const parsedCellRef = parseCellRef(cellRef)
+
+          if (type === 'left') {
+            return parsedCellRef.columnNumber < loopDetected.start.originalColumnNumber
+          }
+
+          return parsedCellRef.columnNumber > loopDetected.end.originalColumnNumber
+        })
+
+        const els = getCellElsAndWrappersFrom(currentEl, type === 'left' ? 'previous' : 'next')
+
         if (type === 'left') {
-          toCloneEls.push(...getCellElsAndWrappersFrom(currentEl, 'previous'))
-          toCloneEls.reverse()
-        } else {
-          toCloneEls.push(...getCellElsAndWrappersFrom(currentEl, 'next'))
+          els.reverse()
         }
 
-        for (const toCloneEl of toCloneEls) {
+        const items = []
+        let idx = 0
+
+        for (const cEl of els) {
+          const item = [cEl]
+
+          if (cEl.nodeName === 'c') {
+            item.push(remainingCellRefsInRow[idx])
+            idx++
+          }
+
+          items.push(item)
+        }
+
+        toCloneEls.push(...items)
+
+        for (const [toCloneEl, cellRef] of toCloneEls) {
           const newEl = toCloneEl.cloneNode(true)
           outOfLoopEl.appendChild(newEl)
+
+          if (cellRef != null) {
+            // updating map of cellEl references
+            const stored = cellsElsByRefMap.get(cellRef)
+            stored[0] = newEl
+          }
+
           toCloneEl.parentNode.removeChild(toCloneEl)
@@ -618,33 +731,23 @@ }
 
-      newMergeCellCallEl.textContent = `{{_D t='m' o='${ref}'}}`
-
       const mergeStartCellRef = ref.split(':')[0]
       const parsedMergeStart = parseCellRef(mergeStartCellRef)
+      const mergeStartMatch = cellsElsByRefMap.get(mergeStartCellRef)
 
-      // we check here if there is a loop that start/end in the same row of merged cell
-      // (this does not necessarily mean that merged cell is part of the loop)
-      const loopDetectionResult = getParentLoop(inverseLoopsDetected, parsedMergeStart)
-      const loopDetected = loopDetectionResult?.loopDetected
-      const parsedLoopEnd = loopDetectionResult != null ? loopDetectionResult.parsedLoopEnd : null
-      const insideLoop = loopDetectionResult != null ? loopDetectionResult.isInside : false
-      let stayAt = 'first'
+      if (mergeStartMatch == null) {
+        // this happens when there is merge cell that has no definition in row (row with no cells),
+        // we put a cell call just to have the correct handlebars data.l updated before the merge helper call happens
+        newMergeCellCallEl.textContent = `{{_C '${parsedMergeStart.letter}'}}{{_D t='m' o='${ref}'}}`
+        rowEl.appendChild(newMergeCellCallEl)
+      } else {
+        const [mergeStartCellEl] = mergeStartMatch
+        newMergeCellCallEl.textContent = `{{_D t='m' o='${ref}'}}`
 
-      if (
-        loopDetected != null &&
-        !insideLoop &&
-        loopDetected.type === 'block' &&
-        parsedLoopEnd != null &&
-        parsedLoopEnd.rowNumber === parsedMergeStart.rowNumber &&
-        parsedMergeStart.columnNumber > parsedLoopEnd.columnNumber
-      ) {
-        stayAt = 'last'
+        // since the updated cell letter does not have a parent-child relationship like
+        // the updated row number has, then we need to insert the merge cell call just next to
+        // the merge cell start to ensure that when it tries to read the updated cell letter
+        // it is still the right value, specially for when there is vertical loop involved
+        // in current cell or past cells
+        mergeStartCellEl.parentNode.insertBefore(newMergeCellCallEl, mergeStartCellEl.nextSibling)
       }
 
-      rowEl.appendChild(newMergeCellCallEl)
-
-      if (loopDetected != null && !insideLoop) {
-        processOpeningTag(sheetDoc, newMergeCellCallEl, `{{#if @${stayAt}}}`)
-        processClosingTag(sheetDoc, newMergeCellCallEl, '{{/if}}')
-      }
-
       if (!isLast) {
@@ -872,4 +975,4 @@ continue
 
-function getLatestNotClosedLoop (loopsDetected) {
-  let loopFound
+function getLatestNotClosedLoop (loopsDetected, all = false) {
+  const found = []
 
@@ -883,7 +986,10 @@ for (let index = loopsDetected.length - 1; index >= 0; index--) {
 
-    loopFound = currentLoop
-    break
+    found.push(currentLoop)
+
+    if (!all) {
+      break
+    }
   }
 
-  return loopFound
+  return all ? found : found[0]
 }
@@ -893,10 +999,19 @@
   const loopDetected = inverseLoopsDetected.find((l) => {
-    if (l.type === 'block') {
-      return (
-        parsedCellRef.rowNumber >= l.start.originalRowNumber &&
-        parsedCellRef.rowNumber <= l.end.originalRowNumber
-      )
+    switch (l.type) {
+      case 'row':
+        return l.start.originalRowNumber === parsedCellRef.rowNumber
+      case 'block':
+        return (
+          parsedCellRef.rowNumber >= l.start.originalRowNumber &&
+          parsedCellRef.rowNumber <= l.end.originalRowNumber
+        )
+      case 'vertical':
+        return (
+          parsedCellRef.rowNumber >= l.start.originalRowNumber &&
+          parsedCellRef.rowNumber <= l.end.originalRowNumber &&
+          parsedCellRef.columnNumber === l.start.originalColumnNumber
+        )
+      default:
+        throw new Error(`Unknown loop type ${l.type}`)
     }
-
-    return l.start.originalRowNumber === parsedCellRef.rowNumber
   })
@@ -913,15 +1028,27 @@
   // here we check if the merged cell is really part of the loop or not
-  if (loopDetected.type === 'block') {
-    if (parsedLoopStart.rowNumber === parsedCellRef.rowNumber) {
-      insideLoop = parsedCellRef.columnNumber >= parsedLoopStart.columnNumber
-    } else if (parsedLoopEnd.rowNumber === parsedCellRef.rowNumber) {
-      insideLoop = parsedCellRef.columnNumber <= parsedLoopEnd.columnNumber
-    } else {
+  switch (loopDetected.type) {
+    case 'row': {
+      insideLoop = (
+        parsedCellRef.columnNumber >= parsedLoopStart.columnNumber &&
+        parsedCellRef.columnNumber <= parsedLoopEnd.columnNumber
+      )
+      break
+    }
+    case 'block': {
+      if (parsedLoopStart.rowNumber === parsedCellRef.rowNumber) {
+        insideLoop = parsedCellRef.columnNumber >= parsedLoopStart.columnNumber
+      } else if (parsedLoopEnd.rowNumber === parsedCellRef.rowNumber) {
+        insideLoop = parsedCellRef.columnNumber <= parsedLoopEnd.columnNumber
+      } else {
+        insideLoop = true
+      }
+      break
+    }
+    case 'vertical': {
+      // if it passed the checks, nothing else to do here
       insideLoop = true
+      break
     }
-  } else {
-    insideLoop = (
-      parsedCellRef.columnNumber >= parsedLoopStart.columnNumber &&
-      parsedCellRef.columnNumber <= parsedLoopEnd.columnNumber
-    )
+    default:
+      throw new Error(`Unknown loop type ${loopDetected.type}`)
   }
@@ -1275,2 +1402,37 @@
 
+function checkAndGetLoopsToProcess (currentFile, loopsDetected, currentRowNumber, isLastRow) {
+  const rowLoops = loopsDetected.filter((l) => l.type === 'row' && l.start.originalRowNumber === currentRowNumber)
+  const invalidRowLoop = rowLoops.find((l) => l.end == null)
+
+  if (invalidRowLoop != null) {
+    throw new Error(`Unable to find end of loop (#each) in ${currentFile.path}. {{/each}} is missing`)
+  }
+
+  let blockLoops = loopsDetected.filter((l) => l.type === 'block')
+
+  if (isLastRow) {
+    const invalidBlockLoop = blockLoops.find((l) => l.end == null)
+
+    if (invalidBlockLoop) {
+      throw new Error(`Unable to find end of block loop (#each) in ${currentFile.path}. {{/each}} is missing`)
+    }
+  }
+
+  blockLoops = blockLoops.filter((l) => l.end?.originalRowNumber === currentRowNumber)
+
+  let verticalLoops = loopsDetected.filter((l) => l.type === 'vertical')
+
+  if (isLastRow) {
+    const invalidVerticalLoop = verticalLoops.find((l) => l.end == null)
+
+    if (invalidVerticalLoop) {
+      throw new Error(`Unable to find end of vertical loop (#each) in ${currentFile.path}. {{/each}} is missing`)
+    }
+  }
+
+  verticalLoops = verticalLoops.filter((l) => l.end?.originalRowNumber === currentRowNumber)
+
+  return [...rowLoops, ...blockLoops, ...verticalLoops]
+}
+
 function processOpeningTag (doc, refElement, helperCall) {
@@ -1277,0 +1439,0 @@ const fakeElement = doc.createElement('xlsxRemove')

lib/generation/preprocess/preprocess.js

@@ -5,6 +5,6 @@ const concatTags = require('./concatTags')
 
-module.exports = (files, meta) => {
+module.exports = (files, ctx) => {
   concatTags(files)
-  loop(files, meta)
-  drawingObject(files, meta)
+  loop(files, ctx)
+  drawingObject(files)
 }

lib/generation/processXlsx.js

@@ -30,5 +30,5 @@ const { DOMParser, XMLSerializer } = require('@xmldom/xmldom')
 
-    const meta = { options }
+    const ctx = { options }
 
-    await preprocess(files, meta)
+    await preprocess(files, ctx)
 
@@ -108,3 +108,3 @@ const [filesToRender, styleFile] = ensureOrderOfFiles(files.filter(f => contentIsXML(f.data)))
           // value should be raw or not (the inverse of escape)
-          return `{{${callType} ${expressionValue}${escapeValue ? '' : ' 1'}${attrs}}}`
+          return `{{${callType} ${expressionValue}${escapeValue ? '' : ' 1'}${attrs} _n="${expressionValue}"}}`
         }
@@ -117,3 +117,3 @@
 
-      if (meta.autofitConfigured && styleFile?.path === f.path) {
+      if (ctx.autofitConfigured && styleFile?.path === f.path) {
         xmlStr = `{{#_D t='style'}}${xmlStr}{{/_D}}`
@@ -129,9 +129,10 @@ }
 
-    const { content: newContent } = await reporter.render({
-      template: {
-        content: contentToRender,
-        engine: req.template.engine,
-        recipe: 'html',
-        helpers: req.template.helpers
-      }
+    const newContent = await reporter.templatingEngines.evaluate({
+      engine: req.template.engine,
+      content: contentToRender,
+      helpers: req.template.helpers,
+      data: req.data
+    }, {
+      entity: req.template,
+      entitySet: 'templates'
     }, req)
@@ -159,3 +160,3 @@
 
-    await postprocess(files, meta)
+    await postprocess(files, ctx)
 
@@ -196,3 +197,4 @@ for (const f of files) {
   // 2. ensure calcChain.xml comes after sheet files (we just put it at the end of everything)
-  // this is required in child render for our handlebars logic to correctly update the calcChain
+  // this is required in child render for our handlebars logic to
+  // correctly handle processing of our helpers
   const calcChainIdx = files.findIndex((file) => file.path === 'xl/calcChain.xml')
@@ -199,0 +201,0 @@ const filesSorted = []

package.json

 {
   "name": "@jsreport/jsreport-xlsx",
-  "version": "4.2.0",
+  "version": "4.3.0",
   "description": "jsreport recipe rendering excels directly from open xml",
@@ -32,3 +32,3 @@ "keywords": [
   "dependencies": {
-    "@jsreport/office": "4.1.1",
+    "@jsreport/office": "4.1.2",
     "@xmldom/xmldom": "0.8.6",
@@ -50,5 +50,5 @@ "html-entities": "2.4.0",
   "devDependencies": {
-    "@jsreport/jsreport-assets": "4.1.1",
+    "@jsreport/jsreport-assets": "4.2.0",
     "@jsreport/jsreport-components": "4.0.2",
-    "@jsreport/jsreport-core": "4.4.1",
+    "@jsreport/jsreport-core": "4.5.0",
     "@jsreport/jsreport-data": "4.1.0",
@@ -55,0 +55,0 @@ "@jsreport/jsreport-handlebars": "4.0.1",

README.md

@@ -10,2 +10,8 @@ # @jsreport/jsreport-xlsx
 
+### 4.3.0
+
+- generation step: add vertical loop support as beta
+- fix generation step not working when the using loop with block parameters
+- fix using folder scoped asset helpers in xlsx generation
+
 ### 4.2.0
@@ -12,0 +18,0 @@

static/helpersGeneration.js

@@ -229,15 +229,15 @@ /* eslint no-unused-vars: 0 */
 
-    // if existing dimension is bigger than the last cell with content
-    // then we keep the existing dimension
-    if (
-      parsedEndCellRef.rowNumber > parsedLastCellRef.rowNumber ||
-      (
-        parsedEndCellRef.rowNumber === parsedLastCellRef.rowNumber &&
-        parsedEndCellRef.columnNumber > parsedLastCellRef.columnNumber
-      )
-    ) {
-      return originalCellRefRange
+    let dimensionLetter = parsedEndCellRef.letter
+
+    if (parsedLastCellRef.columnNumber > parsedEndCellRef.columnNumber) {
+      dimensionLetter = parsedLastCellRef.letter
     }
 
-    return `${refsParts[0]}:${parsedEndCellRef.letter}${parsedLastCellRef.rowNumber}`
+    let dimensionRowNumber = parsedEndCellRef.rowNumber
+
+    if (parsedLastCellRef.rowNumber > parsedEndCellRef.rowNumber) {
+      dimensionRowNumber = parsedLastCellRef.rowNumber
+    }
+
+    return `${refsParts[0]}:${dimensionLetter}${dimensionRowNumber}`
   }
@@ -253,2 +253,3 @@
     const newData = Handlebars.createFrame(options.data)
+    const isVertical = Object.hasOwn(options.hash, 'vertical')
 
@@ -271,4 +272,12 @@ assertOk(start != null, 'start arg is required')
 
+    let type
+
+    if (isVertical) {
+      type = 'vertical'
+    } else {
+      type = end == null ? 'row' : 'block'
+    }
+
     const loopItem = {
-      type: end == null ? 'row' : 'block',
+      type,
       id: null,
@@ -286,2 +295,6 @@ hierarchyId,
 
+    if (type === 'vertical') {
+      loopItem.rowNumber = options.data.r
+    }
+
     const parentLoopItem = getParentLoopItemByHierarchy(loopItem, newData.loopItems)
@@ -361,73 +374,30 @@
 
-    const currentLoopItem = getCurrentLoopItem(newData.currentLoopId, newData.loopItems)
-    // this gets the previous loops (loops defined before a cell) and also on the case of nested loops
-    // all the previous executions of the current loop
-    const previousLoopItems = getPreviousLoopItems(newData.currentLoopId, newData.evaluatedLoopsIds, newData.loopItems)
-
-    const previousMeta = {
-      prev: {
-        total: 0,
-        rowLoopLength: 0
-      },
-      rest: {
-        total: 0,
-        rowLoopLength: 0
+    const {
+      increment: rowIncrement,
+      currentLoopIncrement: rowCurrentLoopIncrement,
+      previousRootLoopIncrement: rowPreviousRootLoopIncrement,
+      previousLoopIncrement: rowPreviousLoopIncrement
+    } = getIncrementWithLoop(
+      'row',
+      {
+        loopId: newData.currentLoopId,
+        loopIndex: options.data.index,
+        evaluatedLoopsIds: newData.evaluatedLoopsIds,
+        loopItems: newData.loopItems
       }
-    }
+    )
 
-    const currentRootLoopIdNum = newData.currentLoopId != null ? parseInt(newData.currentLoopId.split('#')[0], 10) : -1
-
-    let currentLoopIncrement = 0
-
-    for (const item of previousLoopItems) {
-      const previousRootLoopIdNum = parseInt(item.id.split('#')[0], 10)
-      const isPrev = currentRootLoopIdNum === -1 ? true : previousRootLoopIdNum < currentRootLoopIdNum
-      let loopItemsLength = 0
-      const target = isPrev ? previousMeta.prev : previousMeta.rest
-
-      if (item.type === 'block') {
-        loopItemsLength += getLoopItemTemplateLength(item) * (item.length - 1)
-      } else {
-        loopItemsLength += item.length
-        target.rowLoopLength += 1
-      }
-
-      target.total += loopItemsLength
-    }
-
-    const previousRootLoopIncrement = previousMeta.prev.total + (previousMeta.prev.rowLoopLength > 0 ? previousMeta.prev.rowLoopLength * -1 : 0)
-    const previousLoopIncrement = previousRootLoopIncrement + previousMeta.rest.total + (previousMeta.rest.rowLoopLength > 0 ? previousMeta.rest.rowLoopLength * -1 : 0)
-
-    if (currentLoopItem) {
-      const loopIndex = options.data.index
-
-      assertOk(loopIndex != null, 'expected loop index to be defined')
-
-      const parents = getParentsLoopItems(currentLoopItem.id, newData.loopItems)
-      let parentLoopIndex = currentLoopItem.parentLoopIndex
-
-      parents.reverse()
-
-      for (const parentLoopItem of parents) {
-        currentLoopIncrement += getLoopItemTemplateLength(parentLoopItem) * parentLoopIndex
-        parentLoopIndex = parentLoopItem.parentLoopIndex
-      }
-
-      const templateLength = getLoopItemTemplateLength(currentLoopItem)
-
-      currentLoopIncrement = currentLoopIncrement + (templateLength * loopIndex)
-    }
-
-    const increment = previousLoopIncrement + currentLoopIncrement
-
     newData.originalRowNumber = originalRowNumber
-    newData.r = originalRowNumber + increment
+    newData.r = originalRowNumber + rowIncrement
     // only a value that represents the increment of previous loops defined before the cell
-    newData.previousLoopIncrement = previousRootLoopIncrement
+    newData.rowPreviousLoopIncrement = rowPreviousRootLoopIncrement
     // this is a value that represents all the executions of the current loop (considering nested loops too)
-    newData.currentLoopIncrement = currentLoopIncrement + (previousLoopIncrement - previousRootLoopIncrement)
+    newData.rowCurrentLoopIncrement = rowCurrentLoopIncrement + (rowPreviousLoopIncrement - rowPreviousRootLoopIncrement)
 
-    newData.columnLetter = null
+    newData.originalColumnLetter = null
     newData.originalCellRef = null
+    newData.l = null
     newData.currentCellRef = null
+    newData.columnPreviousLoopIncrement = null
+    newData.columnCurrentLoopIncrement = null
 
@@ -453,6 +423,28 @@ const result = options.fn(this, { data: newData })
 
-    const { parseCellRef } = require('cellUtils')
+    const { parseCellRef, getNewCellLetter } = require('cellUtils')
     const originalCellRef = `${originalCellLetter}${originalRowNumber}`
-    const updatedCellRef = `${originalCellLetter}${rowNumber}`
 
+    const {
+      increment: columnIncrement,
+      currentLoopIncrement: columnCurrentLoopIncrement,
+      previousRootLoopIncrement: columnPreviousRootLoopIncrement,
+      previousLoopIncrement: columnPreviousLoopIncrement
+    } = getIncrementWithLoop(
+      'column',
+      {
+        loopId: options.data.currentLoopId,
+        loopIndex: options.data.index,
+        rowNumber: options.data.r,
+        evaluatedLoopsIds: options.data.evaluatedLoopsIds,
+        loopItems: options.data.loopItems
+      }
+    )
+
+    const cellLetter = getNewCellLetter(
+      originalCellLetter,
+      columnIncrement
+    )
+
+    const updatedCellRef = `${cellLetter}${rowNumber}`
+
     // keeping the lastCellRef updated
@@ -516,5 +508,10 @@ if (options.data.meta.lastCellRef == null) {
 
-    options.data.columnLetter = originalCellLetter
+    options.data.originalColumnLetter = originalCellLetter
     options.data.originalCellRef = originalCellRef
+    options.data.l = cellLetter
     options.data.currentCellRef = updatedCellRef
+    // only a value that represents the increment of previous loops defined before the cell
+    options.data.columnPreviousLoopIncrement = columnPreviousRootLoopIncrement
+    // this is a value that represents all the executions of the current loop (considering nested loops too)
+    options.data.columnCurrentLoopIncrement = columnCurrentLoopIncrement + (columnPreviousLoopIncrement - columnPreviousRootLoopIncrement)
 
@@ -619,2 +616,3 @@ if (!generateCellTag) {
     const Handlebars = require('handlebars')
+    const columnLetter = options.data.l
     const rowNumber = options.data.r
@@ -631,3 +629,3 @@
 
-      const { evaluateCellRefsFromExpression, generateNewCellRefFromRow } = require('cellUtils')
+      const { evaluateCellRefsFromExpression, generateNewCellRefFrom, getNewCellLetter } = require('cellUtils')
 
@@ -639,6 +637,13 @@ const { newValue } = evaluateCellRefsFromExpression(originalCellRefRange, (cellRefInfo) => {
 
-        const increment = cellRefInfo.type === 'rangeEnd' ? cellRefInfo.parsedRangeEnd.rowNumber - cellRefInfo.parsedRangeStart.rowNumber : 0
+        const columnIncrement = cellRefInfo.type === 'rangeEnd' ? cellRefInfo.parsedRangeEnd.columnNumber - cellRefInfo.parsedRangeStart.columnNumber : 0
+        const newColumnLetter = getNewCellLetter(columnLetter, columnIncrement)
 
-        const newCellRef = generateNewCellRefFromRow(cellRefInfo.parsed, rowNumber + increment)
+        const rowIncrement = cellRefInfo.type === 'rangeEnd' ? cellRefInfo.parsedRangeEnd.rowNumber - cellRefInfo.parsedRangeStart.rowNumber : 0
+        const newRowNumber = rowNumber + rowIncrement
 
+        const newCellRef = generateNewCellRefFrom(cellRefInfo.parsed, {
+          columnLetter: newColumnLetter,
+          rowNumber: newRowNumber
+        })
+
         return newCellRef
@@ -662,4 +667,6 @@ })
       const originalFormula = options.hash.o != null ? decodeXML(options.hash.o) : null
-      const previousLoopIncrement = options.data.previousLoopIncrement
-      const currentLoopIncrement = options.data.currentLoopIncrement
+      const rowPreviousLoopIncrement = options.data.rowPreviousLoopIncrement
+      const rowCurrentLoopIncrement = options.data.rowCurrentLoopIncrement
+      const columnPreviousLoopIncrement = options.data.columnPreviousLoopIncrement
+      const columnCurrentLoopIncrement = options.data.columnCurrentLoopIncrement
 
@@ -671,3 +678,4 @@ assertOk(currentCellRef != null, 'currentCellRef needs to exists on internal data')
       assertOk(originalFormula != null, 'originalFormula arg is required')
-      assertOk(currentLoopIncrement != null, 'currentLoopIncrement needs to exists on internal data')
+      assertOk(rowCurrentLoopIncrement != null, 'currentLoopIncrement needs to exists on internal data')
+      assertOk(columnCurrentLoopIncrement != null, 'columnCurrentLoopIncrement needs to exists on internal data')
 
@@ -680,4 +688,6 @@ const parsedOriginCellRef = parseCellRef(originalCellRef)
         originCellIsFromLoop,
-        previousLoopIncrement,
-        currentLoopIncrement,
+        rowPreviousLoopIncrement,
+        rowCurrentLoopIncrement,
+        columnPreviousLoopIncrement,
+        columnCurrentLoopIncrement,
         trackedCells,
@@ -697,3 +707,2 @@ includeLoopIncrementResolver: (cellRefIsFromLoop, cellRefInfo) => {
       // have the full xml entities escaped
-
       if (Object.keys(lazyCellRefs).length > 0) {
@@ -795,7 +804,11 @@ return options.data.tasks.wait('lazyFormulas').then(() => {
 
-    const { evaluateCellRefsFromExpression, generateNewCellRefFromRow } = require('cellUtils')
+    const { evaluateCellRefsFromExpression, generateNewCellRefFrom } = require('cellUtils')
 
     const { newValue } = evaluateCellRefsFromExpression(originalSharedRefRange, (cellRefInfo) => {
-      const increment = cellRefInfo.type === 'rangeEnd' ? cellRefInfo.parsedRangeEnd.rowNumber - cellRefInfo.parsedRangeStart.rowNumber : 0
-      const newCellRef = generateNewCellRefFromRow(cellRefInfo.parsed, rowNumber + increment)
+      const rowIncrement = cellRefInfo.type === 'rangeEnd' ? cellRefInfo.parsedRangeEnd.rowNumber - cellRefInfo.parsedRangeStart.rowNumber : 0
+      const newRowNumber = rowNumber + rowIncrement
+
+      const newCellRef = generateNewCellRefFrom(cellRefInfo.parsed, {
+        rowNumber: newRowNumber
+      })
       return newCellRef
@@ -893,4 +906,6 @@ })
           originCellIsFromLoop,
-          previousLoopIncrement,
-          currentLoopIncrement,
+          rowPreviousLoopIncrement,
+          rowCurrentLoopIncrement,
+          columnPreviousLoopIncrement,
+          columnCurrentLoopIncrement,
           cellRefs
@@ -902,4 +917,6 @@ } = lazyFormulaInfo
           originCellIsFromLoop,
-          previousLoopIncrement,
-          currentLoopIncrement,
+          rowPreviousLoopIncrement,
+          rowCurrentLoopIncrement,
+          columnPreviousLoopIncrement,
+          columnCurrentLoopIncrement,
           trackedCells,
@@ -950,39 +967,96 @@ lazyCellRefs: cellRefs
 
-  function getLoopItemById (byTarget, loopItems) {
-    assertOk(byTarget != null, 'getLoopItemById byTarget arg is required')
-    assertOk(Array.isArray(loopItems), 'getLoopItemById loopItems arg is invalid')
+  function getIncrementWithLoop (mode, { loopId, loopIndex, rowNumber, evaluatedLoopsIds, loopItems }) {
+    if (mode !== 'row' && mode !== 'column') {
+      throw new Error('mode must be "row" or "column"')
+    }
 
-    const { idName, idValue } = byTarget
+    if (mode === 'column' && rowNumber == null) {
+      throw new Error('rowNumber must be specified when using column mode')
+    }
 
-    assertOk(idName != null, 'getLoopItemById byTarget.idName arg is required')
-    assertOk(typeof idName === 'string', 'getLoopItemById byTarget.idName arg is invalid')
-    assertOk(idName === 'hierarchyId' || idName === 'id', 'getLoopItemById byTarget.idName should be either "hierarchyId" or "id"')
-    assertOk(idValue != null, 'getLoopItemById byTarget.idValue arg is required')
-    assertOk(typeof idValue === 'string', 'getLoopItemById byTarget.idValue arg is invalid')
+    const currentLoopItem = getCurrentLoopItem(loopId, loopItems)
+    // this gets the previous loops (loops defined before a cell) and also on the case of nested loops
+    // all the previous executions of the current loop
+    const previousLoopItems = getPreviousLoopItems(loopId, evaluatedLoopsIds, loopItems)
 
-    const idParts = idValue.split('#')
-    let ctx = { children: loopItems }
-    let targetIdValue = ''
-    let parent
+    const previousMeta = {
+      prev: {
+        total: 0,
+        loopLength: 0
+      },
+      rest: {
+        total: 0,
+        loopLength: 0
+      }
+    }
 
-    while (idParts.length > 0) {
-      const idx = idParts.shift()
+    const currentRootLoopIdNum = loopId != null ? parseInt(loopId.split('#')[0], 10) : -1
 
-      targetIdValue = targetIdValue !== '' ? `${targetIdValue}#${idx}` : `${idx}`
+    let currentLoopIncrement = 0
 
-      const matches = ctx.children.filter((c) => c[idName] === targetIdValue)
-      const result = matches[matches.length - 1]
+    const validForColumnMode = ['vertical']
+    const validForRowMode = ['row', 'block']
 
-      if (result == null) {
-        break
+    const isValid = (cLoop) => {
+      return mode === 'column' ? validForColumnMode.includes(cLoop.type) : validForRowMode.includes(cLoop.type)
+    }
+
+    const isValidPrevious = (cLoop, cRowNumber) => {
+      const valid = isValid(cLoop)
+
+      if (valid) {
+        return mode === 'column' ? cLoop.rowNumber === cRowNumber : true
       }
 
-      ctx = result
+      return valid
+    }
 
-      if (idParts.length === 0) {
-        parent = ctx
+    const isBlock = (cLoop) => mode === 'column' ? false : cLoop.type === 'block'
+
+    for (const item of previousLoopItems) {
+      const previousRootLoopIdNum = parseInt(item.id.split('#')[0], 10)
+      const isPrev = currentRootLoopIdNum === -1 ? true : previousRootLoopIdNum < currentRootLoopIdNum
+      let loopItemsLength = 0
+      const target = isPrev ? previousMeta.prev : previousMeta.rest
+
+      if (isValidPrevious(item, rowNumber)) {
+        if (isBlock(item)) {
+          loopItemsLength += getLoopItemTemplateLength(item) * (item.length - 1)
+        } else {
+          loopItemsLength += item.length
+          target.loopLength += 1
+        }
       }
+
+      target.total += loopItemsLength
     }
 
-    return parent
+    const previousRootLoopIncrement = previousMeta.prev.total + (previousMeta.prev.loopLength > 0 ? previousMeta.prev.loopLength * -1 : 0)
+    const previousLoopIncrement = previousRootLoopIncrement + previousMeta.rest.total + (previousMeta.rest.loopLength > 0 ? previousMeta.rest.loopLength * -1 : 0)
+
+    if (currentLoopItem) {
+      assertOk(loopIndex != null, 'expected loop index to be defined')
+
+      const parents = getParentsLoopItems(currentLoopItem.id, loopItems)
+      let parentLoopIndex = currentLoopItem.parentLoopIndex
+
+      parents.reverse()
+
+      for (const parentLoopItem of parents) {
+        if (isValid(parentLoopItem)) {
+          currentLoopIncrement += getLoopItemTemplateLength(parentLoopItem) * parentLoopIndex
+        }
+
+        parentLoopIndex = parentLoopItem.parentLoopIndex
+      }
+
+      if (isValid(currentLoopItem)) {
+        const templateLength = getLoopItemTemplateLength(currentLoopItem)
+        currentLoopIncrement = currentLoopIncrement + (templateLength * loopIndex)
+      }
+    }
+
+    const increment = previousLoopIncrement + currentLoopIncrement
+
+    return { increment, currentLoopIncrement, previousRootLoopIncrement, previousLoopIncrement }
   }
@@ -1119,2 +1193,41 @@
 
+  function getLoopItemById (byTarget, loopItems) {
+    assertOk(byTarget != null, 'getLoopItemById byTarget arg is required')
+    assertOk(Array.isArray(loopItems), 'getLoopItemById loopItems arg is invalid')
+
+    const { idName, idValue } = byTarget
+
+    assertOk(idName != null, 'getLoopItemById byTarget.idName arg is required')
+    assertOk(typeof idName === 'string', 'getLoopItemById byTarget.idName arg is invalid')
+    assertOk(idName === 'hierarchyId' || idName === 'id', 'getLoopItemById byTarget.idName should be either "hierarchyId" or "id"')
+    assertOk(idValue != null, 'getLoopItemById byTarget.idValue arg is required')
+    assertOk(typeof idValue === 'string', 'getLoopItemById byTarget.idValue arg is invalid')
+
+    const idParts = idValue.split('#')
+    let ctx = { children: loopItems }
+    let targetIdValue = ''
+    let parent
+
+    while (idParts.length > 0) {
+      const idx = idParts.shift()
+
+      targetIdValue = targetIdValue !== '' ? `${targetIdValue}#${idx}` : `${idx}`
+
+      const matches = ctx.children.filter((c) => c[idName] === targetIdValue)
+      const result = matches[matches.length - 1]
+
+      if (result == null) {
+        break
+      }
+
+      ctx = result
+
+      if (idParts.length === 0) {
+        parent = ctx
+      }
+    }
+
+    return parent
+  }
+
   function getNewCellRef (cellRefInput, originLoopMeta, mode = 'standalone', context) {
@@ -1347,2 +1460,13 @@ const type = 'newCellRef'
 
+  if (value === undefined) {
+    const Handlebars = require('handlebars')
+    if (Handlebars.helpers[options.hash._n]) {
+      value = Handlebars.helpers[options.hash._n]()
+    }
+  }
+
+  if (options.hash._n !== null) {
+    delete options.hash._n
+  }
+
   if (options.hash.t != null) {
@@ -1383,2 +1507,13 @@ options.hash.__originalT__ = options.hash.t
 
+  if (value === undefined) {
+    const Handlebars = require('handlebars')
+    if (Handlebars.helpers[options.hash._n]) {
+      value = Handlebars.helpers[options.hash._n]()
+    }
+  }
+
+  if (options.hash._n !== null) {
+    delete options.hash._n
+  }
+
   if (options.hash.t != null) {
@@ -1385,0 +1520,0 @@ options.hash.__originalT__ = options.hash.t

static/helpersTransformation.js

@@ -112,3 +112,8 @@ /* eslint no-unused-vars: 0 */
       const pathOfLastFragment = path.substring(lastFragmentIndex)
-      const holder = evalGet(options.data.root.$xlsxTemplate[filePath], pathWithoutLastFragment)
+      let holder
+      try {
+        holder = evalGet(options.data.root.$xlsxTemplate[filePath], pathWithoutLastFragment)
+      } catch (e) {
+        throw new Error(`Path ${pathWithoutLastFragment} in file ${filePath} doesn't exist. \n ${e}`)
+      }
 
@@ -115,0 +120,0 @@ this.$replacedValue = evalGet(holder, pathOfLastFragment)

No alert changes

Improved metrics

Total package byte prevSize

264516

increased by4.5%

Lines of code

5730

increased by4.98%

Number of lines in readme file

98

increased by6.52%

Dependency changes

• + Added@jsreport/office@4.1.2(transitive)

• + Addedaxios@1.7.7(transitive)

• - Removed@jsreport/office@4.1.1(transitive)

• - Removedaxios@1.6.7(transitive)

• Updated@jsreport/office@4.1.2