New Case Study:See how Anthropic automated 95% of dependency reviews with Socket.Learn More →
Socket
Sign inDemoInstall
Socket
Sign inDemoInstall

@jsreport/jsreport-xlsx

Package Overview
Dependencies
Maintainers
0
Versions
18
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

@jsreport/jsreport-xlsx - npm Package Compare versions

Comparing version 4.1.1 to 4.2.0

lib/processAutofitCols.js
lib/processCalcChain.js
lib/processStyle.js

107

lib/cellUtils.js

@@ -0,2 +1,4 @@

const { decode } = require('html-entities')
const { col2num } = require('xlsx-coordinates')
const pixelWidth = require('string-pixel-width')

@@ -70,2 +72,63 @@ const CELL_REG_REGEXP = /^(?:('?(?:\[([A-Za-z0-9_. ]+\.xlsx)\])?([A-Za-z0-9_. ]+)'?)!)?(\$?[A-Z]+)(\$?\d+)$/

function getPixelWidthOfValue (value, fontSize) {
const fontSizeInPx = fontSize * (96 / 72)
const size = pixelWidth(value, { font: 'Arial', size: fontSizeInPx })
return parseFloat(size.toFixed(2))
}
function getFontSizeFromStyle (_styleId, styleInfo, cache) {
let styleId
if (_styleId != null && _styleId !== '') {
styleId = parseInt(_styleId, 10)
} else {
styleId = 0
}
const cacheKey = styleId.toString()
if (cache != null && cache.has(cacheKey)) {
return cache.get(cacheKey)
}
const { cellXfsEls, cellStyleXfsEls, fontEls } = styleInfo
const selectedXfEl = cellXfsEls[styleId]
let fontId = selectedXfEl.getAttribute('fontId')
const applyFont = selectedXfEl.getAttribute('applyFont')
const xfId = selectedXfEl.getAttribute('xfId')
if (
applyFont == null ||
applyFont === '' ||
applyFont === '0'
) {
const selectedStyleXfEl = cellStyleXfsEls[xfId]
const nestedFontId = selectedStyleXfEl.getAttribute('fontId')
const nestedApplyFont = selectedStyleXfEl.getAttribute('applyFont')
if (
nestedApplyFont == null ||
nestedApplyFont === '' ||
nestedApplyFont === '1'
) {
fontId = nestedFontId
}
}
const fontEl = fontEls[fontId]
const sizeEl = fontEl.getElementsByTagName('sz')[0]
// size stored in xlsx is in pt
const fontSize = parseFloat(sizeEl.getAttribute('val'))
if (cache != null) {
cache.set(cacheKey, fontSize)
}
return fontSize
}
function evaluateCellRefsFromExpression (valueExpr, replacer) {

@@ -124,3 +187,3 @@ const cellRefs = []

function getNewFormula (helperType, originalFormula, parsedOriginCellRef, meta) {
function getNewFormula (originalFormula, parsedOriginCellRef, meta) {
let newFormula

@@ -251,2 +314,5 @@ const result = {}

formula: newValue,
// just placeholder for the key, this is going to be fill at runtime
// with the updated formula
newFormula: null,
parsedOriginCellRef,

@@ -297,5 +363,44 @@ originCellIsFromLoop,

const xmlEscapeMap = {
'>': '>',
'<': '&lt;',
"'": '&apos;',
'"': '&quot;',
'&': '&amp;'
}
// we dont'se the encode function of html-entities because want to have the chance to
// escape just some characters
function encodeXML (str, mode = 'all') {
let pattern
switch (mode) {
case 'all':
pattern = /([&"<>'])/g
break
case 'basic':
pattern = /([&<>])/g
break
default:
throw new Error('Invalid mode for encodeXML')
}
const output = str.replace(pattern, (_, item) => {
return xmlEscapeMap[item]
})
return output
}
function decodeXML (str) {
return decode(str, { level: 'xml' })
}
module.exports.parseCellRef = parseCellRef
module.exports.getPixelWidthOfValue = getPixelWidthOfValue
module.exports.getFontSizeFromStyle = getFontSizeFromStyle
module.exports.evaluateCellRefsFromExpression = evaluateCellRefsFromExpression
module.exports.getNewFormula = getNewFormula
module.exports.generateNewCellRefFromRow = generateNewCellRefFromRow
module.exports.encodeXML = encodeXML
module.exports.decodeXML = decodeXML

4

lib/fsproxy.js

@@ -1,9 +0,9 @@

const { v1: uuidv1 } = require('uuid')
const path = require('path')
const fs = require('fs')
const mkdirp = require('mkdirp')
const { nanoid } = require('nanoid')
const zlib = require('zlib')
module.exports.write = (tmp, data) => {
const file = path.join(tmp, uuidv1() + '.xml')
const file = path.join(tmp, `${nanoid()}.xml'`)

@@ -10,0 +10,0 @@ mkdirp.sync(tmp)

206

lib/generation/postprocess/loop.js
const { DOMParser } = require('@xmldom/xmldom')
const { col2num } = require('xlsx-coordinates')
const recursiveStringReplaceAsync = require('../../recursiveStringReplaceAsync')
const stringReplaceAsync = require('../../stringReplaceAsync')
const { nodeListToArray, isWorksheetFile, serializeXml, getSheetInfo } = require('../../utils')
const { nodeListToArray, isWorksheetFile, getSheetInfo } = require('../../utils')

@@ -11,3 +9,2 @@ module.exports = async (files) => {

const workbookRelsDoc = files.find((file) => file.path === 'xl/_rels/workbook.xml.rels').doc
const calcChainDoc = files.find((f) => f.path === 'xl/calcChain.xml')?.doc
const workbookSheetsEls = nodeListToArray(workbookDoc.getElementsByTagName('sheet'))

@@ -23,86 +20,2 @@ const workbookRelsEls = nodeListToArray(workbookRelsDoc.getElementsByTagName('Relationship'))

const updatedCalcChainCountMap = new Map()
// update the calcChain.xml to the new updated cells
sheetFile.data = await recursiveStringReplaceAsync(
sheetFile.data.toString(),
'<calcChainCellUpdated>',
'</calcChainCellUpdated>',
'g',
async (val, content, hasNestedMatch) => {
if (hasNestedMatch) {
return val
}
const doc = new DOMParser().parseFromString(val)
const calcChainCellRefUpdatedEl = doc.documentElement.firstChild
const calcChainEls = nodeListToArray(calcChainDoc.getElementsByTagName('c'))
const matches = calcChainEls.filter((c) => (
c.getAttribute('i') === sheetInfo.id &&
c.getAttribute('oldR') === calcChainCellRefUpdatedEl.getAttribute('oldR')
))
const calcChainCellRefEl = matches[matches.length - 1]
if (calcChainCellRefEl != null) {
const oldRef = calcChainCellRefEl.getAttribute('oldR')
const newRef = calcChainCellRefUpdatedEl.getAttribute('r')
const existingCount = updatedCalcChainCountMap.get(oldRef) || 0
if (oldRef !== newRef) {
if (existingCount === 0) {
calcChainCellRefEl.setAttribute('r', newRef)
} else {
const newCalcChainCellRefEl = calcChainCellRefEl.cloneNode(true)
newCalcChainCellRefEl.setAttribute('r', newRef)
calcChainCellRefEl.parentNode.insertBefore(newCalcChainCellRefEl, calcChainCellRefEl.nextSibling)
}
}
updatedCalcChainCountMap.set(oldRef, existingCount + 1)
}
return ''
}
)
if (calcChainDoc != null) {
const newCalcChainEls = nodeListToArray(calcChainDoc.getElementsByTagName('c')).filter((c) => c.getAttribute('i') === sheetInfo.id)
// we clean the oldR attribute that we used for the conditions
for (const calcChainEl of newCalcChainEls) {
calcChainEl.removeAttribute('oldR')
}
}
let dimensionUpdatedRef
// search if we should update the dimension
sheetFile.data = await stringReplaceAsync(
sheetFile.data.toString(),
/<dimensionUpdated [^>]*\/>/g,
async (val) => {
const dimensionUpdatedEl = new DOMParser().parseFromString(val).documentElement
dimensionUpdatedRef = dimensionUpdatedEl.getAttribute('ref')
return ''
}
)
// update the dimension with latest cellRef values
if (dimensionUpdatedRef != null) {
sheetFile.data = await stringReplaceAsync(
sheetFile.data.toString(),
/<dimension [^>]*\/>/g,
async (val) => {
const dimensionEl = new DOMParser().parseFromString(val).documentElement
dimensionEl.setAttribute('ref', dimensionUpdatedRef)
return serializeXml(dimensionEl)
}
)
}
// check if we need to updates tables

@@ -143,120 +56,3 @@ sheetFile.data = await recursiveStringReplaceAsync(

)
const autofitCols = {}
// check if we need to update the cols with autofit information
sheetFile.data = await recursiveStringReplaceAsync(
sheetFile.data.toString(),
'<autofitUpdated>',
'</autofitUpdated>',
'g',
async (val, content, hasNestedMatch) => {
if (hasNestedMatch) {
return val
}
const doc = new DOMParser().parseFromString(val)
const autofitUpdatedEl = doc.documentElement
const colEls = nodeListToArray(autofitUpdatedEl.getElementsByTagName('col'))
for (const colEl of colEls) {
const letter = colEl.getAttribute('ref')
const size = parseFloat(colEl.getAttribute('size'))
autofitCols[letter] = size
}
return ''
}
)
if (Object.keys(autofitCols).length > 0) {
sheetFile.data = await recursiveStringReplaceAsync(
sheetFile.data.toString(),
'<cols>',
'</cols>',
'g',
async (val, content, hasNestedMatch) => {
if (hasNestedMatch) {
return val
}
const doc = new DOMParser().parseFromString(val)
const colsEl = doc.documentElement
const existingColEls = nodeListToArray(colsEl.getElementsByTagName('col'))
// cleaning
for (let idx = 0; idx < colsEl.childNodes.length; idx++) {
const el = colsEl.childNodes[idx]
colsEl.removeChild(el)
}
for (const [colLetter, colPxSize] of Object.entries(autofitCols)) {
const colSizeInNumberCharactersMDW = (colPxSize / 6.5) + 2 // 2 is for padding
const colNumber = col2num(colLetter) + 1
const existingColEl = existingColEls.find((el) => (
el.getAttribute('min') === colNumber.toString() &&
el.getAttribute('max') === colNumber.toString()
))
if (existingColEl != null) {
existingColEl.setAttribute('width', colSizeInNumberCharactersMDW)
existingColEl.setAttribute('customWidth', '1')
} else {
const newCol = doc.createElement('col')
newCol.setAttribute('min', colNumber.toString())
newCol.setAttribute('max', colNumber.toString())
newCol.setAttribute('width', colSizeInNumberCharactersMDW)
newCol.setAttribute('customWidth', '1')
colsEl.appendChild(newCol)
}
}
return serializeXml(colsEl)
}
)
}
const allLazyFormulaEls = {}
// lazy formulas
sheetFile.data = await recursiveStringReplaceAsync(
sheetFile.data.toString(),
'<lazyFormulas>',
'</lazyFormulas>',
'g',
async (val, content, hasNestedMatch) => {
if (hasNestedMatch) {
return val
}
const doc = new DOMParser().parseFromString(val)
const lazyFormulasEl = doc.documentElement
const itemEls = nodeListToArray(lazyFormulasEl.getElementsByTagName('item'))
for (const itemEl of itemEls) {
allLazyFormulaEls[itemEl.getAttribute('id')] = itemEl.textContent
}
return ''
}
)
if (Object.keys(allLazyFormulaEls).length > 0) {
sheetFile.data = await stringReplaceAsync(
sheetFile.data.toString(),
/\$lazyFormulaRef[\d]+/g,
async (lazyFormulaId) => {
const newFormula = allLazyFormulaEls[lazyFormulaId]
if (newFormula == null) {
throw new Error(`Could not find lazyFormula internal data with id "${lazyFormulaId}"`)
}
return newFormula
}
)
}
}
}

366

lib/generation/preprocess/loop.js
const path = require('path')
const { num2col } = require('xlsx-coordinates')
const { nodeListToArray, isWorksheetFile, isWorksheetRelsFile, getSheetInfo } = require('../../utils')
const { parseCellRef, evaluateCellRefsFromExpression } = require('../../cellUtils')
const { nodeListToArray, isWorksheetFile, isWorksheetRelsFile, getSheetInfo, getStyleFile, getStyleInfo } = require('../../utils')
const { parseCellRef, getPixelWidthOfValue, getFontSizeFromStyle, evaluateCellRefsFromExpression } = require('../../cellUtils')
const startLoopRegexp = /{{#each ([^{}]{0,500})}}/
module.exports = (files) => {
const workbookPath = 'xl/workbook.xml'
const workbookDoc = files.find((file) => file.path === workbookPath)?.doc
module.exports = (files, meta) => {
// console.time('pre:loop process setup')
const workbookDoc = files.find((file) => file.path === 'xl/workbook.xml')?.doc
const workbookRelsDoc = files.find((file) => file.path === 'xl/_rels/workbook.xml.rels')?.doc
const sharedStringsDoc = files.find((f) => f.path === 'xl/sharedStrings.xml')?.doc
const calcChainDoc = files.find((f) => f.path === 'xl/calcChain.xml')?.doc
const styleInfo = getStyleInfo(getStyleFile(files)?.doc)
const workbookCalcPrEl = workbookDoc.getElementsByTagName('calcPr')[0]
let stylesInfo = {}
let workbookSheetsEls = []
let workbookRelsEls = []
let sharedStringsEls = []
let calcChainEls = []
const calcChainMap = new Map()

@@ -28,15 +28,2 @@ if (workbookDoc) {

workbookRelsEls = nodeListToArray(workbookRelsDoc.getElementsByTagName('Relationship'))
const styleRel = workbookRelsEls.find((el) => el.getAttribute('Type') === 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles')
if (styleRel != null) {
const stylePath = path.posix.join(path.posix.dirname(workbookPath), styleRel.getAttribute('Target'))
const stylesDoc = files.find((file) => file.path === stylePath)?.doc
if (stylesDoc != null) {
stylesInfo = {
doc: stylesDoc
}
}
}
}

@@ -49,3 +36,3 @@

if (calcChainDoc != null) {
calcChainEls = nodeListToArray(calcChainDoc.getElementsByTagName('c'))
const calcChainCellEls = nodeListToArray(calcChainDoc.getElementsByTagName('c'))

@@ -55,4 +42,4 @@ // we store the existing cell ref into other attribute

// is going to be updated
for (const calcChainEl of calcChainEls) {
calcChainEl.setAttribute('oldR', calcChainEl.getAttribute('r'))
for (const calcChainEl of calcChainCellEls) {
calcChainMap.set(`${calcChainEl.getAttribute('i')}-${calcChainEl.getAttribute('r')}`, calcChainEl)
}

@@ -72,3 +59,3 @@ }

const sheetDataEl = sheetDoc.getElementsByTagName('sheetData')[0]
const colsEl = sheetDoc.getElementsByTagName('cols')[0]
let colsEl = sheetDoc.getElementsByTagName('cols')[0]

@@ -94,20 +81,35 @@ if (sheetDataEl == null) {

// wrap the <sheetData> into wrapper so we can store data during helper calls
const rootBlockStartEl = processOpeningTag(sheetDoc, sheetDataEl, `{{#xlsxSData type='root'${isAutofitConfigured ? ` autofit="${autoFitColLettersStr}"` : ''}}}`)
const sheetDataCallProps = {}
let rootEdgeEl = sheetDataEl
if (isAutofitConfigured) {
sheetDataCallProps.autofit = autoFitColLettersStr
while (rootEdgeEl.nextSibling != null) {
const nextSibling = rootEdgeEl.nextSibling
if (colsEl == null) {
colsEl = sheetDoc.createElement('cols')
sheetDataEl.parentNode.insertBefore(colsEl, sheetDataEl)
}
if (meta.autofitConfigured !== true) {
meta.autofitConfigured = true
}
}
const sheetDataBlockStartEl = processOpeningTag(sheetDoc, sheetDataEl, getDataHelperCall('sd', sheetDataCallProps))
let sheetDataEdgeEl = sheetDataEl
while (sheetDataEdgeEl.nextSibling != null) {
const nextSibling = sheetDataEdgeEl.nextSibling
if (nextSibling.nodeName === 'mergeCells') {
rootEdgeEl = nextSibling
sheetDataEdgeEl = nextSibling
break
} else {
rootEdgeEl = nextSibling
sheetDataEdgeEl = nextSibling
}
}
rootEdgeEl = rootEdgeEl.nodeName === 'mergeCells' ? rootEdgeEl : sheetDataEl
sheetDataEdgeEl = sheetDataEdgeEl.nodeName === 'mergeCells' ? sheetDataEdgeEl : sheetDataEl
processClosingTag(sheetDoc, rootEdgeEl, '{{/xlsxSData}}')
processClosingTag(sheetDoc, sheetDataEdgeEl, '{{/_D}}')

@@ -122,5 +124,3 @@ const mergeCellsEl = sheetDoc.getElementsByTagName('mergeCells')[0]

// the ref by the handlebars
const newDimensionEl = sheetDoc.createElement('dimensionUpdated')
newDimensionEl.setAttribute('ref', `{{xlsxSData type="dimensionRef" originalCellRefRange="${dimensionEl.getAttribute('ref')}" }}`)
sheetDataEl.appendChild(newDimensionEl)
dimensionEl.setAttribute('ref', `{{_D t='dimension' o='${dimensionEl.getAttribute('ref')}'}}`)
}

@@ -148,3 +148,3 @@

newTableUpdatedEl.setAttribute('ref', `{{xlsxSData type='newCellRef' originalCellRefRange='${tableDoc.documentElement.getAttribute('ref')}'}}`)
newTableUpdatedEl.setAttribute('ref', `{{_D t='newCellRef' originalCellRefRange='${tableDoc.documentElement.getAttribute('ref')}'}}`)

@@ -155,3 +155,3 @@ const autoFilterEl = tableDoc.getElementsByTagName('autoFilter')[0]

const newAutoFilterRef = sheetDoc.createElement('autoFilterRef')
newAutoFilterRef.setAttribute('ref', `{{xlsxSData type='newCellRef' originalCellRefRange='${autoFilterEl.getAttribute('ref')}'}}`)
newAutoFilterRef.setAttribute('ref', `{{_D t='newCellRef' originalCellRefRange='${autoFilterEl.getAttribute('ref')}'}}`)
newTableUpdatedEl.appendChild(newAutoFilterRef)

@@ -192,12 +192,2 @@ }

}
const newAutofitEl = sheetDoc.createElement('autofitUpdated')
newAutofitEl.textContent = '{{xlsxSData type="autofit"}}'
sheetDataEl.appendChild(newAutofitEl)
if (colsEl == null) {
const newColsEl = sheetDoc.createElement('cols')
newColsEl.textContent = 'placeholder for autofit'
sheetDataEl.parentNode.insertBefore(newColsEl, sheetDataEl)
}
}

@@ -210,2 +200,3 @@

const cellsElsByRefMap = new Map()
const colMaxSizeMap = new Map()

@@ -217,5 +208,4 @@ const lastRowIdx = rowsEls.length - 1

const isLastRow = rowIdx === lastRowIdx
const standardCellElsToHandle = []
const staticCellElsToHandle = []
const contentDetectCellElsToHandle = []
const calcCellElsToHandle = []

@@ -229,28 +219,47 @@ if (originalRowNumber == null || originalRowNumber === '') {

// wrap the <row> into wrapper so we can store data during helper calls
processOpeningTag(sheetDoc, rowEl, `{{#xlsxSData type='row' originalRowNumber=${originalRowNumber}}}`)
processClosingTag(sheetDoc, rowEl, '{{/xlsxSData}}')
processOpeningTag(sheetDoc, rowEl, `{{#_R ${originalRowNumber}}}`)
processClosingTag(sheetDoc, rowEl, '{{/_R}}')
// update the row number to be based on helper call
rowEl.setAttribute('r', "{{xlsxSData type='rowNumber'}}")
rowEl.setAttribute('r', '{{@r}}')
const cellsEls = nodeListToArray(rowEl.getElementsByTagName('c'))
if (cellsEls.length === 0) {
// there can be cases when the row has no cells but it has merge cell defined, if yes
// then queue it to process it later
const foundMergeCellEls = mergeCellEls.filter((mergeCellEl) => {
const ref = mergeCellEl.getAttribute('ref')
const mergeStartCellRef = ref.split(':')[0]
return parseCellRef(mergeStartCellRef).rowNumber === originalRowNumber
})
for (const mergeCellEl of foundMergeCellEls) {
mergeCellElsToHandle.push({ ref: mergeCellEl.getAttribute('ref'), rowEl })
}
}
for (const cellEl of cellsEls) {
const cellRef = cellEl.getAttribute('r')
const parsedCellRef = parseCellRef(cellRef)
cellsElsByRefMap.set(cellRef, cellEl)
const cellMeta = {
letter: parsedCellRef.letter
}
cellEl.setAttribute('r', `{{xlsxSData type='cellRef' originalCellRef='${cellRef}'}}`)
cellsElsByRefMap.set(cellRef, [cellEl, cellMeta])
let cellCallType = '_C'
// search if we need to update some calc cell
const calcCellEl = findCellElInCalcChain(sheetInfo.id, cellRef, calcChainEls)
const calcCellEl = calcChainMap.get(`${sheetInfo.id}-${cellRef}`)
if (calcCellEl != null) {
calcCellElsToHandle.push({
calcCellEl,
cellRef,
cellEl
})
cellCallType = '_c'
cellMeta.calcChainUpdate = true
}
// using alias for helper to optimize the size of generated xml
cellEl.setAttribute('r', `{{${cellCallType} '${cellMeta.letter}'}}`)
// check if the cell starts a merge cell, if yes

@@ -277,5 +286,5 @@ // then queue it to process it later

if (info.value.includes('{{') && info.value.includes('}}')) {
contentDetectCellElsToHandle.push(cellEl)
contentDetectCellElsToHandle.push(cellRef)
} else if (isAutofitConfigured) {
standardCellElsToHandle.push(cellEl)
staticCellElsToHandle.push(cellRef)
}

@@ -344,3 +353,2 @@

const formulaInfo = {
cellRef,
formula: info.value,

@@ -411,7 +419,7 @@ formulaEl: info.valueEl

const parsedLoopEnd = parseCellRef(currentRowLoopDetected.end.cellRef)
return `{{#xlsxSData ${valueInsideEachCall} type='loop' hierarchyId='${currentRowLoopDetected.hierarchyId}' start=${originalRowNumber} columnStart=${parsedLoopStart.columnNumber} columnEnd=${parsedLoopEnd.columnNumber} }}`
return `{{#_D ${valueInsideEachCall} t='loop' hierarchyId='${currentRowLoopDetected.hierarchyId}' start=${originalRowNumber} columnStart=${parsedLoopStart.columnNumber} columnEnd=${parsedLoopEnd.columnNumber} }}`
}))
// we want to put the loop wrapper around the row wrapper
currentRowLoopDetected.blockEndEl = processClosingTag(sheetDoc, rowEl.nextSibling, '{{/xlsxSData}}')
currentRowLoopDetected.blockEndEl = processClosingTag(sheetDoc, rowEl.nextSibling, '{{/_D}}')
}

@@ -464,20 +472,24 @@

const parsedLoopEnd = parseCellRef(currentBlockLoopDetected.end.cellRef)
return `{{#xlsxSData ${valueInsideEachCall} type='loop' hierarchyId='${currentBlockLoopDetected.hierarchyId}' start=${currentBlockLoopDetected.start.originalRowNumber} columnStart=${parsedLoopStart.columnNumber} end=${currentBlockLoopDetected.end.originalRowNumber} columnEnd=${parsedLoopEnd.columnNumber} }}`
return `{{#_D ${valueInsideEachCall} t='loop' hierarchyId='${currentBlockLoopDetected.hierarchyId}' start=${currentBlockLoopDetected.start.originalRowNumber} columnStart=${parsedLoopStart.columnNumber} end=${currentBlockLoopDetected.end.originalRowNumber} columnEnd=${parsedLoopEnd.columnNumber} }}`
}))
// we want to put the loop wrapper around the end row wrapper
currentBlockLoopDetected.blockEndEl = processClosingTag(sheetDoc, endingRowEl.nextSibling, '{{/xlsxSData}}')
currentBlockLoopDetected.blockEndEl = processClosingTag(sheetDoc, endingRowEl.nextSibling, '{{/_D}}')
}
for (const cellEl of standardCellElsToHandle) {
for (const cellRef of staticCellElsToHandle) {
const [cellEl, cellMeta] = cellsElsByRefMap.get(cellRef)
const cellInfo = getCellInfo(cellEl, sharedStringsEls, sheetFilepath)
const fontSize = findCellFontSize(cellEl, stylesInfo)
const fontSize = getFontSizeFromStyle(cellEl.getAttribute('s'), styleInfo)
// wrap the cell <v> into wrapper so we can check the value size for
// auto-size logic
processOpeningTag(sheetDoc, cellEl.firstChild, `{{#xlsxSData type='cellValue' value="${cellInfo.value}" fontSize=${fontSize}}}`)
processClosingTag(sheetDoc, cellEl.firstChild.nextSibling, '{{/xlsxSData}}')
const currentMaxSize = colMaxSizeMap.get(cellMeta.letter)
const currentSize = getPixelWidthOfValue(cellInfo.value, fontSize)
if (currentMaxSize == null || currentSize > currentMaxSize) {
colMaxSizeMap.set(cellMeta.letter, currentSize)
}
}
for (const cellEl of contentDetectCellElsToHandle) {
for (const cellRef of contentDetectCellElsToHandle) {
const [cellEl, cellMeta] = cellsElsByRefMap.get(cellRef)
const cellInfo = getCellInfo(cellEl, sharedStringsEls, sheetFilepath)

@@ -498,11 +510,11 @@

const newContentEl = sheetDoc.createElement('xlsxRemove')
const cellValueWrapperEl = sheetDoc.createElement('xlsxRemove')
const cellValueWrapperEndEl = sheetDoc.createElement('xlsxRemove')
const rawEl = sheetDoc.createElement('xlsxRemove')
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
const fontSize = findCellFontSize(cellEl, stylesInfo)
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')
if (isSingleMatch) {

@@ -512,44 +524,12 @@ const match = matches[0]

const expressionValue = match[2]
const value = expressionValue.includes(' ') ? `(${expressionValue})` : expressionValue
cellValueWrapperEl.textContent = `{{#xlsxSData type='cellValue' value=${expressionValue.includes(' ') ? `(${expressionValue})` : expressionValue}${shouldEscape ? ' escape=true' : ''}`
} else {
cellValueWrapperEl.textContent = "{{#xlsxSData type='cellValue'"
cellEl.setAttribute('__CT_v__', value)
cellEl.setAttribute('__CT_ve__', shouldEscape ? '1' : '0')
}
cellValueWrapperEl.textContent += ` fontSize=${fontSize}}}`
if (!isSingleMatch) {
rawEl.textContent = `{{#xlsxSData type='cellValueRaw' }}${newTextValue}{{/xlsxSData}}`
}
cellEl.setAttribute('t', "{{xlsxSData type='cellValueType' }}")
newContentEl.textContent = "{{xlsxSData type='cellContent' }}"
cellValueWrapperEndEl.textContent = '{{/xlsxSData}}'
cellEl.replaceChild(newContentEl, cellInfo.contentEl)
cellEl.parentNode.insertBefore(cellValueWrapperEl, cellEl)
if (!isSingleMatch) {
cellEl.parentNode.insertBefore(rawEl, cellValueWrapperEl.nextSibling)
}
cellEl.parentNode.insertBefore(cellValueWrapperEndEl, cellEl.nextSibling)
// 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
}
for (const { calcCellEl, cellRef, cellEl } of calcCellElsToHandle) {
// we add the referenced cell in the calcChain in the cell
// to be able to update the ref by the handlebars
const newCalcCellEl = calcCellEl.cloneNode(true)
newCalcCellEl.setAttribute('r', `{{xlsxSData type='cellRef' originalCellRef='${cellRef}' shadow=true}}`)
newCalcCellEl.setAttribute('oldR', cellRef)
const wrapperElement = sheetDoc.createElement('calcChainCellUpdated')
wrapperElement.appendChild(newCalcCellEl)
// on the contrary with the merge cells case, the calcChainCellUpdated is inserted
// in the cell, so there is no need for a wrapper that only renders it
// for the first item in loop
cellEl.insertBefore(wrapperElement, cellEl.firstChild)
}
}

@@ -587,6 +567,6 @@

processOpeningTag(sheetDoc, outOfLoopEl.firstChild, rowHandlebarsWrapperText)
processClosingTag(sheetDoc, outOfLoopEl.lastChild, '{{/xlsxSData}}')
processClosingTag(sheetDoc, outOfLoopEl.lastChild, '{{/_R}}')
processOpeningTag(sheetDoc, outOfLoopEl.firstChild, `{{#xlsxSData type='outOfLoop' item='${outLoopItemIndex}' }}`)
processClosingTag(sheetDoc, outOfLoopEl.lastChild, '{{/xlsxSData}}')
processOpeningTag(sheetDoc, outOfLoopEl.firstChild, `{{#_D t='outOfLoop' item='${outLoopItemIndex}' }}`)
processClosingTag(sheetDoc, outOfLoopEl.lastChild, '{{/_D}}')

@@ -607,3 +587,3 @@ const loopEdgeEl = loopDetected.blockStartEl

const contentEl = sheetDoc.createElement('xlsxRemove')
contentEl.textContent = `{{xlsxSData type='outOfLoopPlaceholder' item='${outLoopItemIndex}' }}`
contentEl.textContent = `{{_D t='outOfLoopPlaceholder' item='${outLoopItemIndex}' }}`

@@ -644,3 +624,3 @@ outOfLoopPlaceholderEl.appendChild(contentEl)

newMergeCellCallEl.textContent = `{{xlsxSData type='mergeCell' originalCellRefRange='${ref}'}}`
newMergeCellCallEl.textContent = `{{_D t='m' o='${ref}'}}`

@@ -680,6 +660,6 @@ const mergeStartCellRef = ref.split(':')[0]

mergeCellsEl.setAttribute('count', '{{@mergeCellsCount}}')
mergeCellsEl.setAttribute('count', "{{_D t='mergeCellsCount'}}")
processOpeningTag(sheetDoc, mergeCellsEl, "{{#xlsxSData type='mergeCells'}}")
processClosingTag(sheetDoc, mergeCellsEl, '{{/xlsxSData}}')
processOpeningTag(sheetDoc, mergeCellsEl, "{{#_D t='mergeCells'}}")
processClosingTag(sheetDoc, mergeCellsEl, '{{/_D}}')

@@ -690,8 +670,8 @@ for (const mergeCellEl of mergeCellEls) {

processOpeningTag(sheetDoc, mergeCellEl, `{{#xlsxSData type='mergeCellItem' originalCellRefRange='${originalCellRefRange}'}}`)
processClosingTag(sheetDoc, mergeCellEl, '{{/xlsxSData}}')
processOpeningTag(sheetDoc, mergeCellEl, `{{#_D t='mI' o='${originalCellRefRange}'}}`)
processClosingTag(sheetDoc, mergeCellEl, '{{/_D}}')
}
processOpeningTag(sheetDoc, mergeCellsEl.firstChild, "{{#xlsxSData type='mergeCellsItems'}}")
processClosingTag(sheetDoc, mergeCellsEl.lastChild, '{{/xlsxSData}}')
processOpeningTag(sheetDoc, mergeCellsEl.firstChild, "{{#_D t='mergeCellsItems'}}")
processClosingTag(sheetDoc, mergeCellsEl.lastChild, '{{/_D}}')
}

@@ -701,3 +681,3 @@

for (const { cellRef, formula, formulaEl, sharedFormula, cellRefsInFormula } of formulaCellElsToHandle) {
for (const { formula, formulaEl, sharedFormula, cellRefsInFormula } of formulaCellElsToHandle) {
if (sharedFormula?.type === 'reference') {

@@ -707,6 +687,6 @@ continue

formulaEl.textContent = `{{xlsxSData type='formula' originalCellRef='${cellRef}' originalFormula='${jsSingleQuoteEscape(formula)}'`
formulaEl.textContent = `{{_D t='f' o='${jsSingleQuoteEscape(formula)}'`
if (sharedFormula?.type === 'source') {
formulaEl.setAttribute('ref', `{{xlsxSData type='formulaSharedRefRange' originalSharedRefRange='${jsSingleQuoteEscape(sharedFormula.sourceRef)}'}}`)
formulaEl.setAttribute('ref', `{{_D t='fs' o='${jsSingleQuoteEscape(sharedFormula.sourceRef)}'}}`)
}

@@ -724,3 +704,3 @@

const normalizedCellRef = cellRefInfo.localRef
const targetCellEl = cellsElsByRefMap.get(normalizedCellRef)
const targetCellEl = cellsElsByRefMap.get(normalizedCellRef)?.[0]

@@ -751,3 +731,3 @@ if (targetCellEl != null) {

if (formulaNotExistingCellRefs.size > 0) {
rootBlockStartEl.textContent = rootBlockStartEl.textContent.replace('}}', ` nonExistingCellRefs='${[...formulaNotExistingCellRefs].join(',')}'}}`)
sheetDataCallProps.nonExistingCellRefs = [...formulaNotExistingCellRefs].join(',')
}

@@ -769,4 +749,26 @@

const newLazyFormulasCallEl = sheetDoc.createElement('xlsxRemove')
newLazyFormulasCallEl.textContent = '{{xlsxSData type="lazyFormulas"}}'
newLazyFormulasCallEl.textContent = "{{_D t='lazyFormulas'}}"
sheetDataEl.appendChild(newLazyFormulasCallEl)
if (isAutofitConfigured) {
const colsProps = {}
const baseCols = []
for (const [cellLetter, maxSize] of colMaxSizeMap) {
baseCols.push(`${cellLetter}:${maxSize}`)
}
if (baseCols.length > 0) {
sheetDataCallProps.autofitBCols = baseCols.join(',')
}
processOpeningTag(sheetDoc, colsEl, getDataHelperCall('autofit', colsProps))
processClosingTag(sheetDoc, colsEl, '{{/_D}}')
}
sheetDataBlockStartEl.textContent = getDataHelperCall('sd', sheetDataCallProps)
processOpeningTag(sheetDoc, sheetDoc.documentElement.firstChild, getDataHelperCall('ws', { sheetId: sheetInfo.id }))
processClosingTag(sheetDoc, sheetDoc.documentElement.lastChild, '{{/_D}}')
}

@@ -783,7 +785,37 @@

if (tEl.textContent.includes('{{') && tEl.textContent.includes('}}')) {
tEl.textContent = `{{{{xlsxSData type='raw'}}}}${tEl.textContent}{{{{/xlsxSData}}}}`
tEl.textContent = `{{{{_D t='raw'}}}}${tEl.textContent}{{{{/_D}}}}`
}
}
// place handlebars call that handle updating the calcChain
if (calcChainDoc != null) {
processOpeningTag(calcChainDoc, calcChainDoc.documentElement.firstChild, "{{#_D t='calcChain'}}")
processClosingTag(calcChainDoc, calcChainDoc.documentElement.lastChild, '{{/_D}}')
}
}
function getDataHelperCall (type, props, isBlock = true) {
let callStr = `{{${isBlock ? '#' : ''}_D t='${type}'`
const targetProps = props || {}
const keys = Object.keys(targetProps)
for (const key of keys) {
const value = targetProps[key]
if (value == null) {
continue
}
if (typeof value === 'number' || typeof value === 'boolean') {
callStr += ` ${key}=${value}`
} else {
callStr += ` ${key}='${value}'`
}
}
callStr += '}}'
return callStr
}
function getOutOfLoopElsSortedByHierarchy (outOfLoopElsToHandle) {

@@ -930,3 +962,3 @@ const hierarchy = new Map()

currentEl[target].nodeName === 'xlsxRemove' &&
currentEl[target].textContent.startsWith(target === 'previousSibling' ? '{{#xlsxSData' : '{{/xlsxSData')
currentEl[target].textContent.startsWith(target === 'previousSibling' ? '{{#_D' : '{{/_D')
)

@@ -968,3 +1000,3 @@

// if it is xlsxRemove but it is not end point then we are ready
!currentEl[target].textContent.startsWith(type === 'previous' ? '{{#xlsxSData' : '{{/xlsxSData')
!currentEl[target].textContent.startsWith(type === 'previous' ? '{{#_D' : '{{/_D')
)

@@ -1131,60 +1163,2 @@ }

function findCellFontSize (cellEl, styleInfo) {
let styleId = cellEl.getAttribute('s')
if (styleId != null && styleId !== '') {
styleId = parseInt(styleId, 10)
} else {
styleId = 0
}
const cellXfsEls = styleInfo.cellXfsEls ?? nodeListToArray(styleInfo.doc.getElementsByTagName('cellXfs')[0]?.getElementsByTagName('xf'))
const cellStyleXfsEls = styleInfo.cellStyleXfsEls ?? nodeListToArray(styleInfo.doc.getElementsByTagName('cellStyleXfs')[0]?.getElementsByTagName('xf'))
const fontEls = styleInfo.fontEls ?? nodeListToArray(styleInfo.doc.getElementsByTagName('fonts')[0]?.getElementsByTagName('font'))
const selectedXfEl = cellXfsEls[styleId]
let fontId = selectedXfEl.getAttribute('fontId')
const applyFont = selectedXfEl.getAttribute('applyFont')
const xfId = selectedXfEl.getAttribute('xfId')
if (
applyFont == null ||
applyFont === '' ||
applyFont === '0'
) {
const selectedStyleXfEl = cellStyleXfsEls[xfId]
const nestedFontId = selectedStyleXfEl.getAttribute('fontId')
const nestedApplyFont = selectedStyleXfEl.getAttribute('applyFont')
if (
nestedApplyFont == null ||
nestedApplyFont === '' ||
nestedApplyFont === '1'
) {
fontId = nestedFontId
}
}
const fontEl = fontEls[fontId]
const sizeEl = fontEl.getElementsByTagName('sz')[0]
// size stored in xlsx is in pt
return parseFloat(sizeEl.getAttribute('val'))
}
function findCellElInCalcChain (sheetId, cellRef, calcChainEls) {
const foundIndex = calcChainEls.findIndex((el) => {
return el.getAttribute('r') === cellRef && el.getAttribute('i') === sheetId
})
if (foundIndex === -1) {
return
}
const cellEl = calcChainEls[foundIndex]
return cellEl
}
function findAutofitConfigured (sheetFilepath, sheetDoc, sheetRelsDoc, files) {

@@ -1191,0 +1165,0 @@ const result = []

2

lib/generation/preprocess/processChart.js

@@ -155,3 +155,3 @@ const path = require('path')

if (ptValueEl.textContent.includes('{{') && ptValueEl.textContent.includes('}}')) {
ptValueEl.textContent = `{{{{xlsxSData type='raw'}}}}${ptValueEl.textContent}{{{{/xlsxSData}}}}`
ptValueEl.textContent = `{{{{_D t='raw'}}}}${ptValueEl.textContent}{{{{/_D}}}}`
}

@@ -158,0 +158,0 @@ }

150

lib/generation/processXlsx.js
const { DOMParser, XMLSerializer } = require('@xmldom/xmldom')
const { decode } = require('html-entities')
const { customAlphabet } = require('nanoid')
const { decompress, saveXmlsToOfficeFile } = require('@jsreport/office')
const generateRandomId = customAlphabet('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 4)
const preprocess = require('./preprocess/preprocess')
const postprocess = require('./postprocess/postprocess')
const { contentIsXML, isWorksheetFile } = require('../utils')
const { contentIsXML, isWorksheetFile, getStyleFile } = require('../utils')
const { decodeXML } = require('../cellUtils')
const decodeXML = (str) => decode(str, { level: 'xml' })
module.exports = (reporter) => async (inputs, req) => {

@@ -34,28 +34,92 @@ const { xlsxTemplateContent, options, outputPath } = inputs

const filesToRender = files.filter(f => contentIsXML(f.data))
const [filesToRender, styleFile] = ensureOrderOfFiles(files.filter(f => contentIsXML(f.data)))
const contentToRender = filesToRender.map(f => {
const xmlStr = new XMLSerializer().serializeToString(f.doc, undefined, (node) => {
const normalizeAttributeAndTextNode = (node) => {
if (node.nodeType === 2 && node.nodeValue && node.nodeValue.includes('{{')) {
// we need to decode the xml entities for the attributes for handlebars to work ok
if (node.nodeType === 2 && node.nodeValue && node.nodeValue.includes('{{')) {
const str = new XMLSerializer().serializeToString(node)
return decodeXML(str)
} else if (
// we need to decode the xml entities in text nodes for handlebars to work ok with partials
node.nodeType === 3 && node.nodeValue &&
(node.nodeValue.includes('{{>') || node.nodeValue.includes('{{#>'))
) {
const str = new XMLSerializer().serializeToString(node)
const str = new XMLSerializer().serializeToString(node)
return decodeXML(str)
} else if (
// we need to decode the xml entities in text nodes for handlebars to work ok with partials
node.nodeType === 3 && node.nodeValue &&
(node.nodeValue.includes('{{>') || node.nodeValue.includes('{{#>'))
) {
const str = new XMLSerializer().serializeToString(node)
return str.replace(/{{#?&gt;/g, (m) => {
return decodeXML(m)
})
return str.replace(/{{#?&gt;/g, (m) => {
return decodeXML(m)
})
}
return node
}
let contentToRender = filesToRender.map(f => {
let xmlStr = new XMLSerializer().serializeToString(f.doc, undefined, (node) => {
if (node.nodeType === 1 && node.localName === 'c' && node.hasAttribute('__CT_t__')) {
let callType = node.getAttribute('__CT_t__')
const hasMultipleExpressionCall = node.getAttribute('__CT_m__') === '1'
const calcChainUpdate = node.getAttribute('__CT_cCU__') === '1'
const expressionValue = node.getAttribute('__CT_v__')
const escapeValue = node.getAttribute('__CT_ve__') === '1'
if (calcChainUpdate) {
callType = callType.toLowerCase()
}
node.removeAttribute('__CT_t__')
node.removeAttribute('__CT_m__')
node.removeAttribute('__CT_cCU__')
node.removeAttribute('__CT_v__')
node.removeAttribute('__CT_ve__')
// this will take care of removing xmlns, xmlns:prefix attributes that we don't want here
const str = new XMLSerializer().serializeToString(
node,
undefined,
normalizeAttributeAndTextNode
).replace(/ xmlns(:[a-z0-9]+)?="[^"]*"/g, '')
const isSelfClosing = node.childNodes.length === 0
let attrs
let content
if (isSelfClosing) {
const closeTagStartIdx = str.lastIndexOf('/>')
attrs = str.slice(2, closeTagStartIdx)
content = ''
} else {
const openTagEndIdx = str.indexOf('>')
const closeTagStartIdx = str.lastIndexOf('</')
attrs = str.slice(2, openTagEndIdx)
content = str.slice(openTagEndIdx + 1, closeTagStartIdx)
}
if (hasMultipleExpressionCall) {
return `{{#${callType}${attrs}}}${content}{{/${callType}}}`
}
// for the handlebars call we want to avoid the extra character for escape param,
// (size optimization) since the common case is that the handlebars call is
// escaped {{}} expression, so instead we expect in the helper receive if the
// value should be raw or not (the inverse of escape)
return `{{${callType} ${expressionValue}${escapeValue ? '' : ' 1'}${attrs}}}`
}
return node
return normalizeAttributeAndTextNode(node)
})
return xmlStr.replace(/<xlsxRemove>/g, '').replace(/<\/xlsxRemove>/g, '')
xmlStr = xmlStr.replace(/<xlsxRemove>/g, '').replace(/<\/xlsxRemove>/g, '')
if (meta.autofitConfigured && styleFile?.path === f.path) {
xmlStr = `{{#_D t='style'}}${xmlStr}{{/_D}}`
}
return xmlStr
}).join('$$$xlsxFile$$$')
contentToRender = `{{#xlsxContext type="global" evalId="${generateRandomId()}"}}${contentToRender}{{/xlsxContext}}`
reporter.logger.debug('Starting child request to render xlsx dynamic parts for generation step', req)

@@ -123,1 +187,45 @@

}
function ensureOrderOfFiles (files) {
// we want to ensure a specific order of files for the render processing,
// 1. ensure style file comes as the first
// 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
const calcChainIdx = files.findIndex((file) => file.path === 'xl/calcChain.xml')
const filesSorted = []
const skipIndexesSet = new Set()
const styleFile = getStyleFile(files)
let styleIdx = -1
if (styleFile != null) {
styleIdx = files.findIndex((file) => file.path === styleFile.path)
}
for (const idx of [styleIdx, calcChainIdx]) {
if (idx === -1) {
continue
}
skipIndexesSet.add(idx)
}
if (styleIdx !== -1) {
filesSorted.push(files[styleIdx])
}
for (let fileIdx = 0; fileIdx < files.length; fileIdx++) {
if (skipIndexesSet.has(fileIdx)) {
continue
}
filesSorted.push(files[fileIdx])
}
if (calcChainIdx !== -1) {
filesSorted.push(files[calcChainIdx])
}
return [filesSorted, styleFile]
}

44

lib/utils.js

@@ -211,2 +211,44 @@ const path = require('path')

function getStyleFile (files) {
const workbookRelsDoc = files.find((file) => file.path === 'xl/_rels/workbook.xml.rels')?.doc
if (workbookRelsDoc == null) {
return
}
const workbookRelsEls = nodeListToArray(workbookRelsDoc.getElementsByTagName('Relationship'))
const styleRel = workbookRelsEls.find((el) => el.getAttribute('Type') === 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles')
if (styleRel == null) {
return
}
const stylePath = path.posix.join(path.posix.dirname('xl/workbook.xml'), styleRel.getAttribute('Target'))
const stylesFile = files.find((file) => file.path === stylePath)
if (stylesFile == null) {
return
}
return stylesFile
}
function getStyleInfo (styleDoc) {
if (styleDoc == null) {
return
}
const cellXfsEls = nodeListToArray(styleDoc.getElementsByTagName('cellXfs')[0]?.getElementsByTagName('xf'))
const cellStyleXfsEls = nodeListToArray(styleDoc.getElementsByTagName('cellStyleXfs')[0]?.getElementsByTagName('xf'))
const fontEls = nodeListToArray(styleDoc.getElementsByTagName('fonts')[0]?.getElementsByTagName('font'))
return {
doc: styleDoc,
cellXfsEls,
cellStyleXfsEls,
fontEls
}
}
function isWorksheetFile (filePath) {

@@ -249,2 +291,4 @@ return path.posix.dirname(filePath) === 'xl/worksheets' && filePath.endsWith('.xml')

module.exports.getSheetInfo = getSheetInfo
module.exports.getStyleFile = getStyleFile
module.exports.getStyleInfo = getStyleInfo
module.exports.findOrCreateChildNode = findOrCreateChildNode

@@ -251,0 +295,0 @@ module.exports.findChildNode = findChildNode

20

lib/worker.js

@@ -16,2 +16,17 @@ const fs = require('fs').promises

reporter.options.sandbox.modules.push({
alias: 'xlsxProcessStyle',
path: path.join(__dirname, './processStyle.js')
})
reporter.options.sandbox.modules.push({
alias: 'xlsxProcessCalcChain',
path: path.join(__dirname, './processCalcChain.js')
})
reporter.options.sandbox.modules.push({
alias: 'xlsxProcessAutofitCols',
path: path.join(__dirname, './processAutofitCols.js')
})
reporter.options.sandbox.modules.push({
alias: 'lodash',

@@ -26,7 +41,2 @@ path: require.resolve('lodash')

reporter.options.sandbox.modules.push({
alias: 'string-pixel-width',
path: require.resolve('string-pixel-width')
})
if (reporter.options.sandbox.allowedModules !== '*') {

@@ -33,0 +43,0 @@ reporter.options.sandbox.allowedModules.push('path')

9

package.json
{
"name": "@jsreport/jsreport-xlsx",
"version": "4.1.1",
"version": "4.2.0",
"description": "jsreport recipe rendering excels directly from open xml",

@@ -40,2 +40,3 @@ "keywords": [

"moment": "2.29.4",
"nanoid": "3.2.0",
"node.extend.without.arrays": "1.1.6",

@@ -45,3 +46,2 @@ "semaphore-async-await": "1.5.1",

"string-replace-async": "2.0.0",
"uuid": "8.3.2",
"xlsx-coordinates": "1.0.1",

@@ -52,4 +52,4 @@ "xml2js-preserve-spaces": "0.0.1"

"@jsreport/jsreport-assets": "4.1.1",
"@jsreport/jsreport-components": "4.0.1",
"@jsreport/jsreport-core": "4.3.0",
"@jsreport/jsreport-components": "4.0.2",
"@jsreport/jsreport-core": "4.4.1",
"@jsreport/jsreport-data": "4.1.0",

@@ -62,2 +62,3 @@ "@jsreport/jsreport-handlebars": "4.0.1",

"mocha": "10.1.0",
"sax": "1.4.1",
"should": "13.2.3",

@@ -64,0 +65,0 @@ "standard": "16.0.4",

7

README.md

@@ -10,2 +10,9 @@ # @jsreport/jsreport-xlsx

### 4.2.0
- keep existing dimension when appropriate
- fix escaping issue in formulas
- optimization for autofit cols and cell type auto detection helpers
- performance improvement for templates with lots of cells in calcChain (using lot of formulas)
### 4.1.1

@@ -12,0 +19,0 @@

1635

static/helpersGeneration.js

@@ -11,2 +11,16 @@ /* eslint no-unused-vars: 0 */

const enabledFor = options?.data?.meta?.autofit?.enabledFor ?? []
const allColsEnabled = enabledFor[0] === true
if (!allColsEnabled) {
const cols = options?.data?.meta?.autofit?.cols ?? {}
for (const colLetter of Object.keys(cols)) {
// remove cells that are not enabled for autofit
if (!enabledFor.includes(colLetter)) {
delete cols[colLetter]
}
}
}
return ''

@@ -37,3 +51,3 @@ }

) {
throw new Error('docxChart helper when options parameter is set, it should be an object')
throw new Error('xlsxChart helper when options parameter is set, it should be an object')
}

@@ -44,65 +58,73 @@

function xlsxSData (data, options) {
function xlsxContext (options) {
const Handlebars = require('handlebars')
const optionsToUse = options == null ? data : options
const type = optionsToUse.hash.type
let data
if (type == null) {
throw new Error('xlsxSData helper type arg is required')
if (options.hash.type === 'global') {
data = Handlebars.createFrame(options.data)
data.evalId = options.hash.evalId
data.calcChainUpdatesMap = new Map()
data.styleInfo = null
data.styleFontSizeMap = new Map()
}
if (
arguments.length === 1 &&
type === 'raw'
) {
return optionsToUse.fn()
const context = {}
if (data) {
context.data = data
}
if (
arguments.length === 1 &&
type === 'root'
) {
const newData = Handlebars.createFrame({})
let nonExistingCellRefs = optionsToUse.hash.nonExistingCellRefs != null ? optionsToUse.hash.nonExistingCellRefs.split(',') : []
const autofit = optionsToUse.hash.autofit != null ? optionsToUse.hash.autofit.split(',') : []
const trackedCells = {}
const result = options.fn(this, context)
if (nonExistingCellRefs.length > 0) {
nonExistingCellRefs = nonExistingCellRefs.map((cellRef) => {
const parts = cellRef.split('|')
const result = {
ref: parts[0]
}
return result
}
if (parts.length === 2) {
result.inLoop = true
result.loopHierarchyId = parts[1]
}
const __xlsxD = (function () {
function ws (options) {
const Handlebars = require('handlebars')
const newData = Handlebars.createFrame(options.data)
const sheetId = options.hash.sheetId
return result
})
assertOk(sheetId != null, 'sheetId arg is required')
for (const cellRefEntry of nonExistingCellRefs) {
trackedCells[cellRefEntry.ref] = {
first: cellRefEntry.ref,
last: cellRefEntry.ref,
count: 0
newData.sheetId = sheetId
const tasks = new Map()
newData.tasks = {
wait (key) {
return tasks.get(key)?.promise
},
add (key) {
let taskExecution = tasks.get(key)
if (taskExecution != null) {
return [taskExecution.resolve, taskExecution.reject]
}
if (cellRefEntry.inLoop) {
trackedCells[cellRefEntry.ref].inLoop = cellRefEntry.inLoop
trackedCells[cellRefEntry.ref].loopHierarchyId = cellRefEntry.loopHierarchyId
} else {
trackedCells[cellRefEntry.ref].inLoop = false
}
taskExecution = {}
taskExecution.promise = new Promise((resolve, reject) => {
taskExecution.resolve = resolve
taskExecution.reject = reject
})
tasks.set(key, taskExecution)
return [taskExecution.resolve, taskExecution.reject]
}
}
// init tasks
newData.tasks.add('sd')
newData.tasks.add('lazyFormulas')
newData.meta = {
calcChainCellRefsSet: null,
autofit: {
cols: {},
enabledFor: autofit
enabledFor: null
},
mergeCells: [],
trackedCells,
trackedCells: null,
updatedOriginalCells: {},

@@ -117,99 +139,123 @@ lazyFormulas: {},

return optionsToUse.fn(this, { data: newData })
return options.fn(this, { data: newData })
}
const getLoopItemById = (byTarget, loopItems) => {
if (byTarget == null) {
throw new Error('getLoopItemById byTarget arg is invalid')
}
function sd (options) {
const [resolveTask, rejectTask] = options.data.tasks.add('sd')
if (!Array.isArray(loopItems)) {
throw new Error('getLoopItemById loopItems arg is invalid')
}
try {
const Handlebars = require('handlebars')
const { idName, idValue } = byTarget
options.data.meta.calcChainCellRefsSet = new Set(options.hash.calcChainCellRefs != null ? options.hash.calcChainCellRefs.split(',') : [])
if (idName == null || typeof idName !== 'string') {
throw new Error('getLoopItemById byTarget.idName arg is invalid')
}
let nonExistingCellRefs = options.hash.nonExistingCellRefs != null ? options.hash.nonExistingCellRefs.split(',') : []
const autofitEnabledFor = options.hash.autofit != null ? options.hash.autofit.split(',') : []
if (idName !== 'hierarchyId' && idName !== 'id') {
throw new Error('getLoopItemById byTarget.idName should be either "hierarchyId" or "id"')
}
const autofitBaseCols = options.hash.autofitBCols != null
? options.hash.autofitBCols.split(',').map((entry) => {
const [colLetter, size] = entry.split(':')
return [colLetter, parseFloat(size)]
})
: []
if (idValue == null || typeof idValue !== 'string') {
throw new Error('getLoopItemById byTarget.idValue arg is invalid')
}
for (const [colLetter, size] of autofitBaseCols) {
options.data.meta.autofit.cols[colLetter] = {
size
}
}
const idParts = idValue.split('#')
let ctx = { children: loopItems }
let targetIdValue = ''
let parent
const trackedCells = {}
while (idParts.length > 0) {
const idx = idParts.shift()
if (nonExistingCellRefs.length > 0) {
nonExistingCellRefs = nonExistingCellRefs.map((cellRef) => {
const parts = cellRef.split('|')
const result = {
ref: parts[0]
}
targetIdValue = targetIdValue !== '' ? `${targetIdValue}#${idx}` : `${idx}`
if (parts.length === 2) {
result.inLoop = true
result.loopHierarchyId = parts[1]
}
const matches = ctx.children.filter((c) => c[idName] === targetIdValue)
const result = matches[matches.length - 1]
return result
})
if (result == null) {
break
for (const cellRefEntry of nonExistingCellRefs) {
trackedCells[cellRefEntry.ref] = {
first: cellRefEntry.ref,
last: cellRefEntry.ref,
count: 0
}
if (cellRefEntry.inLoop) {
trackedCells[cellRefEntry.ref].inLoop = cellRefEntry.inLoop
trackedCells[cellRefEntry.ref].loopHierarchyId = cellRefEntry.loopHierarchyId
} else {
trackedCells[cellRefEntry.ref].inLoop = false
}
}
}
ctx = result
options.data.meta.autofit.enabledFor = autofitEnabledFor
options.data.meta.trackedCells = trackedCells
if (idParts.length === 0) {
parent = ctx
}
const result = options.fn(this, { data: options.data })
resolveTask()
return result
} catch (e) {
rejectTask(e)
throw e
}
return parent
}
const getParentLoopItemByHierarchy = (childLoopItem, loopItems) => {
if (childLoopItem == null) {
throw new Error('getParentLoopItemByHierarchy childLoopItem arg is invalid')
}
// this helper is async on purpose so it can wait for the sd helper to finish.
// this is needed because the dimension tag appears before the sheetData tag and
// we want to keep to logic for updating this node in handlebars
async function dimension (options) {
const originalCellRefRange = options.hash.o
if (!Array.isArray(loopItems)) {
throw new Error('getParentLoopItemByHierarchy loopItems arg is invalid')
}
assertOk(originalCellRefRange != null, 'originalCellRefRange arg is required')
const parentHierarchyId = childLoopItem.hierarchyId.split('#').slice(0, -1).join('#')
await options.data.tasks.wait('sd')
if (parentHierarchyId === '') {
return
const refsParts = originalCellRefRange.split(':')
if (refsParts.length === 1) {
return refsParts[0]
}
return getLoopItemById({ idName: 'hierarchyId', idValue: parentHierarchyId }, loopItems)
}
const { parseCellRef } = require('cellUtils')
const lastCellRef = options.data.meta.lastCellRef
const parsedEndCellRef = parseCellRef(refsParts[1])
const parsedLastCellRef = parseCellRef(lastCellRef)
if (
arguments.length === 2 &&
type === 'loop'
) {
const start = optionsToUse.hash.start
const columnStart = optionsToUse.hash.columnStart
const end = optionsToUse.hash.end
const columnEnd = optionsToUse.hash.columnEnd
const hierarchyId = optionsToUse.hash.hierarchyId
const newData = Handlebars.createFrame(optionsToUse.data)
if (start == null) {
throw new Error('xlsxSData type="loop" helper start arg is required')
// 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
}
if (columnStart == null) {
throw new Error('xlsxSData type="loop" helper columnStart arg is required')
}
return `${refsParts[0]}:${parsedEndCellRef.letter}${parsedLastCellRef.rowNumber}`
}
if (columnEnd == null) {
throw new Error('xlsxSData type="loop" helper columnEnd arg is required')
}
function loop (data, options) {
const Handlebars = require('handlebars')
const start = options.hash.start
const columnStart = options.hash.columnStart
const end = options.hash.end
const columnEnd = options.hash.columnEnd
const hierarchyId = options.hash.hierarchyId
const newData = Handlebars.createFrame(options.data)
if (hierarchyId == null) {
throw new Error('xlsxSData type="loop" helper hierarchyId arg is required')
}
assertOk(start != null, 'start arg is required')
assertOk(columnStart != null, 'columnStart arg is required')
assertOk(columnEnd != null, 'columnEnd arg is required')
assertOk(hierarchyId != null, 'hierarchyId arg is required')

@@ -236,3 +282,3 @@ let targetData = data

length: targetData.length,
parentLoopIndex: optionsToUse.data.index,
parentLoopIndex: options.data.index,
children: [],

@@ -259,3 +305,3 @@ completed: false

const result = Handlebars.helpers.each(targetData, { ...optionsToUse, data: newData })
const result = Handlebars.helpers.each(targetData, { ...options, data: newData })

@@ -267,14 +313,10 @@ loopItem.completed = true

if (
arguments.length === 1 &&
type === 'outOfLoop'
) {
const item = optionsToUse.hash.item
function outOfLoop (options) {
const Handlebars = require('handlebars')
const item = options.hash.item
if (item == null) {
throw new Error('xlsxSData type="outOfLoop" helper item arg is required')
}
assertOk(item != null, 'item arg is required')
optionsToUse.data.outOfLoopTemplates[item] = (currentLoopId, currentIdx) => {
const newData = Handlebars.createFrame(optionsToUse.data)
options.data.outOfLoopTemplates[item] = (currentLoopId, currentIdx) => {
const newData = Handlebars.createFrame(options.data)

@@ -287,3 +329,3 @@ newData.currentLoopId = currentLoopId

return optionsToUse.fn(this, { data: newData })
return options.fn(this, { data: newData })
}

@@ -294,25 +336,17 @@

if (
arguments.length === 1 &&
type === 'outOfLoopPlaceholder'
) {
const item = optionsToUse.hash.item
function outOfLoopPlaceholder (options) {
const Handlebars = require('handlebars')
const item = options.hash.item
if (item == null) {
throw new Error('xlsxSData type="outOfLoopPlaceholder" helper item arg is required')
}
assertOk(item != null, 'item arg is required')
const outOfLoopTemplate = optionsToUse.data.outOfLoopTemplates[item]
const outOfLoopTemplate = options.data.outOfLoopTemplates[item]
if (outOfLoopTemplate == null) {
throw new Error('xlsxSData type="outOfLoopPlaceholder" helper invalid usage, outOfLoopItem was not found')
}
assertOk(outOfLoopTemplate != null, 'outOfLoopItem was not found')
const currentLoopId = optionsToUse.data.currentLoopId
const currentLoopId = options.data.currentLoopId
if (currentLoopId == null) {
throw new Error('xlsxSData type="outOfLoopPlaceholder" helper invalid usage, currentLoopId not found')
}
assertOk(currentLoopId != null, 'currentLoopId not found')
const currentIdx = optionsToUse.data.index
const currentIdx = options.data.index

@@ -324,164 +358,10 @@ const output = outOfLoopTemplate(currentLoopId, currentIdx)

const { parseCellRef, evaluateCellRefsFromExpression, getNewFormula, generateNewCellRefFromRow } = require('cellUtils')
function r (options) {
const Handlebars = require('handlebars')
const originalRowNumber = options.hash.o
const getCurrentLoopItem = (loopId, loopItems) => {
if (!Array.isArray(loopItems)) {
throw new Error('getCurrentLoopItem loopItems arg is invalid')
}
assertOk(originalRowNumber != null, 'originalRowNumber arg is required')
if (loopId == null) {
return
}
const newData = Handlebars.createFrame(options.data)
return getLoopItemById({ idName: 'id', idValue: loopId }, loopItems)
}
const getPreviousLoopItems = (loopId, evaluatedLoopsIds, loopItems) => {
if (!Array.isArray(evaluatedLoopsIds)) {
throw new Error('getPreviousLoopItems evaluatedLoopsIds arg is invalid')
}
if (!Array.isArray(loopItems)) {
throw new Error('getPreviousLoopItems loopItems arg is invalid')
}
const lastEvaluatedLoopId = evaluatedLoopsIds[evaluatedLoopsIds.length - 1]
const loopItemsToGet = loopId != null && loopId === lastEvaluatedLoopId ? evaluatedLoopsIds.slice(0, -1) : evaluatedLoopsIds
const result = []
for (const lId of loopItemsToGet) {
const loopItem = getLoopItemById({ idName: 'id', idValue: lId }, loopItems)
if (loopItem == null) {
throw new Error(`Can not find loop item by id "${lId}"`)
}
if (!loopItem.completed) {
continue
}
result.push(loopItem)
}
return result
}
const getCurrentAndPreviousLoopItemsByTarget = (byTarget, loopItems) => {
if (byTarget == null) {
throw new Error('getCurrentAndPreviousLoopItemsByTarget byTarget arg is invalid')
}
if (byTarget.rowNumber == null) {
throw new Error('getCurrentAndPreviousLoopItemsByTarget byTarget.rowNumber arg is required')
}
if (byTarget.columnNumber == null) {
throw new Error('getCurrentAndPreviousLoopItemsByTarget byTarget.columnNumber arg is required')
}
if (!Array.isArray(loopItems)) {
throw new Error('getCurrentAndPreviousLoopItemsByTarget loopItems arg is invalid')
}
const { rowNumber, columnNumber } = byTarget
const matchedLoopItems = loopItems.filter((item) => {
if (!item.completed) {
throw new Error('getCurrentAndPreviousLoopItemsByTarget invalid usage, it should be called only after all loop items are completed evaluated')
}
return item.start <= rowNumber
})
let current
const previousAll = [...matchedLoopItems]
const targetLoopItem = previousAll[previousAll.length - 1]
const previous = previousAll.slice(0, -1)
if (targetLoopItem != null) {
let isInside = false
const limit = targetLoopItem.type === 'block' ? targetLoopItem.end : targetLoopItem.start
if (rowNumber === limit) {
// for row loops we assume the row is inside when the row just matches the limit
// (even if technically on the out of loop right case we should check columnEnd,
// we don't do that because in that case the cell will anyway keep on its original place)
isInside = targetLoopItem.type === 'block' ? targetLoopItem.columnEnd > columnNumber : true
} else {
isInside = limit > rowNumber
}
if (!isInside) {
previous.push(targetLoopItem)
} else {
current = targetLoopItem
}
}
return {
current,
previousAll,
previous
}
}
const getLoopItemTemplateLength = (loopItem) => {
if (loopItem == null) {
throw new Error('getLoopItemTemplateLength loopItem arg is invalid')
}
let templateLength = 1
if (loopItem.type === 'block') {
templateLength = (loopItem.end - loopItem.start) + 1
}
return templateLength
}
const getParentsLoopItems = (loopId, loopItems) => {
if (loopId == null) {
throw new Error('getParentsLoopItems loopId arg is invalid')
}
if (!Array.isArray(loopItems)) {
throw new Error('getParentsLoopItems loopItems arg is invalid')
}
const results = []
const parentIdParts = loopId.split('#').slice(0, -1)
if (parentIdParts.length === 0) {
return results
}
let parentId = ''
for (let index = 0; index < parentIdParts.length; index++) {
parentId += parentId === '' ? parentIdParts[index] : `#${parentIdParts[index]}`
const result = getLoopItemById({ idName: 'id', idValue: parentId }, loopItems)
if (!result) {
throw new Error(`Can not find loop item by id "${parentId}"`)
}
results.push(result)
}
return results
}
if (
arguments.length === 1 &&
type === 'row'
) {
const originalRowNumber = optionsToUse.hash.originalRowNumber
if (originalRowNumber == null) {
throw new Error('xlsxSData type="row" helper originalRowNumber arg is required')
}
const newData = Handlebars.createFrame(optionsToUse.data)
const currentLoopItem = getCurrentLoopItem(newData.currentLoopId, newData.loopItems)

@@ -527,7 +407,5 @@ // this gets the previous loops (loops defined before a cell) and also on the case of nested loops

if (currentLoopItem) {
const loopIndex = optionsToUse.data.index
const loopIndex = options.data.index
if (loopIndex == null) {
throw new Error('xlsxSData type="row" helper expected loop index to be defined')
}
assertOk(loopIndex != null, 'expected loop index to be defined')

@@ -551,3 +429,4 @@ const parents = getParentsLoopItems(currentLoopItem.id, newData.loopItems)

newData.rowNumber = originalRowNumber + increment
newData.originalRowNumber = originalRowNumber
newData.r = originalRowNumber + increment
// only a value that represents the increment of previous loops defined before the cell

@@ -559,5 +438,6 @@ newData.previousLoopIncrement = previousRootLoopIncrement

newData.columnLetter = null
newData.originalCellRef = null
newData.currentCellRef = null
const result = optionsToUse.fn(this, { data: newData })
const result = options.fn(this, { data: newData })

@@ -567,38 +447,25 @@ return result

if (
arguments.length === 1 &&
type === 'rowNumber'
) {
return optionsToUse.data.rowNumber
}
function c (info, options) {
const Handlebars = require('handlebars')
const originalRowNumber = options.data.originalRowNumber
const rowNumber = options.data.r
const trackedCells = options.data.meta.trackedCells
const isMultipleExpression = typeof options.fn === 'function'
const { type, originalCellLetter, calcChainUpdate } = info
if (
arguments.length === 1 &&
type === 'cellRef'
) {
const rowNumber = optionsToUse.data.rowNumber
const trackedCells = optionsToUse.data.meta.trackedCells
const originalCellRef = optionsToUse.hash.originalCellRef
const isShadowCall = optionsToUse.hash.shadow === true
const generateCellTag = type === 'autodetect'
if (rowNumber == null) {
throw new Error(`xlsxSData type="${type}" invalid usage, rowNumber needs to exists on internal data`)
}
assertOk(originalRowNumber != null, 'originalRowNumber needs to exists on internal data')
assertOk(rowNumber != null, 'rowNumber needs to exists on internal data')
assertOk(trackedCells != null, 'trackedCells needs to exists on internal data')
if (trackedCells == null) {
throw new Error(`xlsxSData type="${type}" invalid usage, trackedCells needs to exists on internal data`)
}
const { parseCellRef } = require('cellUtils')
const originalCellRef = `${originalCellLetter}${originalRowNumber}`
const updatedCellRef = `${originalCellLetter}${rowNumber}`
if (originalCellRef == null) {
throw new Error('xlsxSData type="cellRef" helper originalCellRef arg is required')
}
const parsedOriginalCellRef = parseCellRef(originalCellRef)
const updatedCellRef = `${parsedOriginalCellRef.letter}${rowNumber}`
// keeping the lastCellRef updated
if (optionsToUse.data.meta.lastCellRef == null) {
optionsToUse.data.meta.lastCellRef = updatedCellRef
if (options.data.meta.lastCellRef == null) {
options.data.meta.lastCellRef = updatedCellRef
} else {
const parsedLastCellRef = parseCellRef(optionsToUse.data.meta.lastCellRef)
const parsedLastCellRef = parseCellRef(options.data.meta.lastCellRef)
const parsedUpdatedCellRef = parseCellRef(updatedCellRef)

@@ -611,3 +478,3 @@

) {
optionsToUse.data.meta.lastCellRef = updatedCellRef
options.data.meta.lastCellRef = updatedCellRef
}

@@ -619,6 +486,6 @@ }

// if we are in loop then don't add item to updatedOriginalCells
if (optionsToUse.data.currentLoopId != null) {
if (options.data.currentLoopId != null) {
shouldUpdateOriginalCell = false
} else {
shouldUpdateOriginalCell = originalCellRef !== updatedCellRef && optionsToUse.data.meta.updatedOriginalCells[originalCellRef] == null
shouldUpdateOriginalCell = originalCellRef !== updatedCellRef && options.data.meta.updatedOriginalCells[originalCellRef] == null
}

@@ -628,171 +495,626 @@

// keeping a registry of the original cells that were updated
optionsToUse.data.meta.updatedOriginalCells[originalCellRef] = updatedCellRef
options.data.meta.updatedOriginalCells[originalCellRef] = updatedCellRef
}
if (!isShadowCall) {
trackedCells[originalCellRef] = trackedCells[originalCellRef] || { first: null, last: null, count: 0 }
trackedCells[originalCellRef] = trackedCells[originalCellRef] || { first: null, last: null, count: 0 }
if (trackedCells[originalCellRef].inLoop == null) {
trackedCells[originalCellRef].inLoop = optionsToUse.data.currentLoopId != null
if (trackedCells[originalCellRef].inLoop == null) {
trackedCells[originalCellRef].inLoop = options.data.currentLoopId != null
}
if (trackedCells[originalCellRef].first == null) {
trackedCells[originalCellRef].first = updatedCellRef
}
trackedCells[originalCellRef].last = updatedCellRef
trackedCells[originalCellRef].count += 1
if (calcChainUpdate) {
const sheetId = options.data.sheetId
const cellRefKey = `${sheetId}-${originalCellRef}`
let calcChainUpdatesForCellRef = options.data.calcChainUpdatesMap.get(cellRefKey)
if (calcChainUpdatesForCellRef == null) {
calcChainUpdatesForCellRef = []
options.data.calcChainUpdatesMap.set(cellRefKey, calcChainUpdatesForCellRef)
}
if (trackedCells[originalCellRef].first == null) {
trackedCells[originalCellRef].first = updatedCellRef
calcChainUpdatesForCellRef.push(updatedCellRef)
}
options.data.columnLetter = originalCellLetter
options.data.originalCellRef = originalCellRef
options.data.currentCellRef = updatedCellRef
if (!generateCellTag) {
return updatedCellRef
}
let cellValue = isMultipleExpression ? options.fn(this, { data: options.data }) : info.value
if (!isMultipleExpression && info.escape && typeof cellValue === 'string') {
cellValue = Handlebars.escapeExpression(cellValue)
}
const enabledForCol = options.data.meta.autofit.enabledFor[0] === true ? true : options.data.meta.autofit.enabledFor.includes(originalCellLetter)
if (enabledForCol) {
const { getPixelWidthOfValue, getFontSizeFromStyle } = require('cellUtils')
const fontSize = getFontSizeFromStyle(options.hash.s, options.data.styleInfo, options.data.styleFontSizeMap)
const colInfo = options.data.meta.autofit.cols[originalCellLetter]
const size = getPixelWidthOfValue(cellValue, fontSize)
if (colInfo == null) {
options.data.meta.autofit.cols[originalCellLetter] = {
size
}
} else if (size > colInfo.size) {
options.data.meta.autofit.cols[originalCellLetter] = {
size
}
}
}
trackedCells[originalCellRef].last = updatedCellRef
trackedCells[originalCellRef].count += 1
let cellType
const serializedAttrs = []
const attrsKeys = Object.keys(options.hash)
if (!attrsKeys.includes('t')) {
attrsKeys.push('t')
}
optionsToUse.data.columnLetter = parsedOriginalCellRef.letter
optionsToUse.data.currentCellRef = updatedCellRef
for (let idx = 0; idx < attrsKeys.length; idx++) {
const key = attrsKeys[idx]
let value
return updatedCellRef
if (key === 'r') {
// ensure updated cellRef is part of cell xml
value = updatedCellRef
} else if (key === 't') {
// ensure we put the type according to cell content
if (cellValue == null) {
cellType = 'inlineStr'
} else if (
typeof cellValue === 'boolean' ||
(
cellValue != null &&
typeof cellValue === 'object' &&
Object.prototype.toString.call(cellValue) === '[object Boolean]'
)
) {
cellType = 'b'
} else if (
typeof cellValue === 'number' ||
(
cellValue != null &&
typeof cellValue === 'object' &&
Object.prototype.toString.call(cellValue) === '[object Number]'
)
) {
cellType = 'n'
} else {
cellType = 'inlineStr'
}
value = cellType
} else {
// keep rest of attrs
value = options.hash[key]
}
serializedAttrs.push(`${key}="${value}"`)
}
let cellContent
if (cellType === 'inlineStr') {
cellContent = `<is><t>${cellValue == null ? '' : cellValue}</t></is>`
} else if (cellType === 'b') {
cellContent = `<v>${cellValue ? '1' : '0'}</v>`
} else if (cellType === 'n') {
cellContent = `<v>${cellValue}</v>`
}
assertOk(cellContent != null, `cell type "${cellType}" not supported`)
return new Handlebars.SafeString(`<c ${serializedAttrs.join(' ')}>${cellContent}</c>`)
}
if (
arguments.length === 1 &&
type === 'cellValue'
) {
const newData = Handlebars.createFrame(optionsToUse.data)
function mergeOrFormulaCell (type, options) {
const Handlebars = require('handlebars')
const rowNumber = options.data.r
newData.currentCellValueInfo = {}
assertOk(rowNumber != null, 'rowNumber needs to exists on internal data')
if (Object.prototype.hasOwnProperty.call(optionsToUse.hash, 'value')) {
newData.currentCellValueInfo.value = optionsToUse.hash.value
let output = ''
let toEscape = false
if (type === 'mergeCell') {
const originalCellRefRange = options.hash.o
// escape should be there when the original handlebars expression was intended
// to be escaped, we preserve that intend here and escape it, we need to do this
// because handlebars does not escape automatically the helper parameter hash,
// which we use as an implementation detail of our auto detect cell type logic
if (Object.prototype.hasOwnProperty.call(optionsToUse.hash, 'escape')) {
toEscape = optionsToUse.hash.escape === true && typeof newData.currentCellValueInfo.value === 'string'
assertOk(originalCellRefRange != null, 'originalCellRefRange arg is required')
const { evaluateCellRefsFromExpression, generateNewCellRefFromRow } = require('cellUtils')
const { newValue } = evaluateCellRefsFromExpression(originalCellRefRange, (cellRefInfo) => {
const isRange = cellRefInfo.type === 'rangeStart' || cellRefInfo.type === 'rangeEnd'
assertOk(isRange, `cell ref expected to be a range. value: "${originalCellRefRange}`)
const increment = cellRefInfo.type === 'rangeEnd' ? cellRefInfo.parsedRangeEnd.rowNumber - cellRefInfo.parsedRangeStart.rowNumber : 0
const newCellRef = generateNewCellRefFromRow(cellRefInfo.parsed, rowNumber + increment)
return newCellRef
})
const mergeCell = {
original: originalCellRefRange,
value: newValue
}
if (toEscape) {
newData.currentCellValueInfo.value = Handlebars.escapeExpression(newData.currentCellValueInfo.value)
options.data.meta.mergeCells.push(mergeCell)
} else {
const { parseCellRef, getNewFormula, decodeXML, encodeXML } = require('cellUtils')
const currentCellRef = options.data.currentCellRef
const trackedCells = options.data.meta.trackedCells
const lazyFormulas = options.data.meta.lazyFormulas
const originalCellRef = options.data.originalCellRef
// formulas can contain characters that are encoded as part of the xlsx processing,
// we ensure here that we decode the xml entities
const originalFormula = options.hash.o != null ? decodeXML(options.hash.o) : null
const previousLoopIncrement = options.data.previousLoopIncrement
const currentLoopIncrement = options.data.currentLoopIncrement
assertOk(currentCellRef != null, 'currentCellRef needs to exists on internal data')
assertOk(trackedCells != null, 'trackedCells needs to exists on internal data')
assertOk(lazyFormulas != null, 'lazyFormulas needs to exists on internal data')
assertOk(originalCellRef != null, 'originalCellRef needs to exists on internal data')
assertOk(originalFormula != null, 'originalFormula arg is required')
assertOk(currentLoopIncrement != null, 'currentLoopIncrement needs to exists on internal data')
const parsedOriginCellRef = parseCellRef(originalCellRef)
const originCellIsFromLoop = options.data.currentLoopId != null
const { lazyCellRefs = {}, formula: newFormula } = getNewFormula(originalFormula, parsedOriginCellRef, {
type: 'normal',
originCellIsFromLoop,
previousLoopIncrement,
currentLoopIncrement,
trackedCells,
includeLoopIncrementResolver: (cellRefIsFromLoop, cellRefInfo) => {
return (
cellRefIsFromLoop &&
trackedCells[cellRefInfo.localRef] != null &&
trackedCells[cellRefInfo.localRef].loopHierarchyId === getCurrentLoopItem(options.data.currentLoopId, options.data.loopItems)?.hierarchyId
)
},
lazyFormulas,
currentCellRef
})
// ensure we encode just some basic xml entities, formula values does not need to
// have the full xml entities escaped
if (Object.keys(lazyCellRefs).length > 0) {
return options.data.tasks.wait('lazyFormulas').then(() => {
const finalFormula = lazyFormulas.data[newFormula].newFormula
return encodeXML(finalFormula, 'basic')
})
}
output = encodeXML(newFormula, 'basic')
}
const result = optionsToUse.fn(this, { data: newData })
const enabledForCol = newData.meta.autofit.enabledFor[0] === true ? true : newData.meta.autofit.enabledFor.includes(newData.columnLetter)
return new Handlebars.SafeString(output)
}
if (enabledForCol) {
const pixelWidth = require('string-pixel-width')
const fontSize = optionsToUse.hash.fontSize
const fontSizeInPx = fontSize * (96 / 72)
const currentValue = newData.currentCellValueInfo.value
const maxInfo = newData.meta.autofit.cols[newData.columnLetter]
function mergeCells (options) {
const Handlebars = require('handlebars')
const newData = Handlebars.createFrame(options.data)
const size = pixelWidth(currentValue, { font: 'Arial', size: fontSizeInPx })
const [resolveTask, rejectTask] = options.data.tasks.add('mergeCells')
if (maxInfo == null) {
newData.meta.autofit.cols[newData.columnLetter] = {
value: currentValue,
size
newData.mergeCellsCount = 0
newData.mergeCellsTemplatesMap = new Map()
try {
const result = options.fn(this, { data: newData })
resolveTask()
return result
} catch (e) {
rejectTask(e)
throw e
}
}
async function mergeCellsCount (options) {
await options.data.tasks.wait('mergeCells')
return options.data.mergeCellsCount
}
function mergeCellsItems (options) {
const Handlebars = require('handlebars')
const targetItems = options.data.meta.mergeCells
// run the body to fulfill the merge cells templates
options.fn(this)
const mergeCellsTemplatesMap = options.data.mergeCellsTemplatesMap
let original = []
const generated = []
const originalOrderScore = new Map(
Array.from(mergeCellsTemplatesMap.keys()).map((key, idx) => [key, idx])
)
for (const targetItem of targetItems) {
const template = mergeCellsTemplatesMap.get(targetItem.original)
const output = template({ newRef: targetItem.value })
if (targetItem.original === targetItem.value) {
original.push({ score: originalOrderScore.get(targetItem.original), output })
} else {
generated.push(output)
}
}
// preserve the original order from xlsx file, so we avoid noise in diffs when
// comparing xlsx output
original.sort((a, b) => a.score - b.score)
original = original.map((item) => item.output)
options.data.mergeCellsCount = original.length + generated.length
const output = `${original.join('\n')}${generated.join('\n')}`
return new Handlebars.SafeString(output)
}
function mI (options) {
const originalCellRefRange = options.hash.o
assertOk(originalCellRefRange != null, 'originalCellRefRange arg is required')
options.data.mergeCellsTemplatesMap.set(originalCellRefRange, options.fn)
return ''
}
function formulaShared (options) {
const rowNumber = options.data.r
assertOk(rowNumber != null, 'rowNumber needs to exists on internal data')
const originalSharedRefRange = options.hash.o
assertOk(originalSharedRefRange != null, 'originalSharedRefRange arg is required')
const { evaluateCellRefsFromExpression, generateNewCellRefFromRow } = 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)
return newCellRef
})
return newValue
}
// TODO: this should be refactored at some point to be more generic
// and support nested loops, maybe the logic will be similar to mergeCell or formula helpers
// when this is done we can remove all the methods that are only used here "getNewCellRef", "getCurrentAndPreviousLoopItemsByTarget"
function newCellRef (options) {
const Handlebars = require('handlebars')
const updatedOriginalCells = options.data.meta.updatedOriginalCells
const loopItems = options.data.loopItems
let targetItems = []
const updated = []
const type = 'newCellRef'
if (type === 'newCellRef') {
targetItems = [{ value: options.hash.originalCellRefRange }]
}
for (const targetItem of targetItems) {
const regexp = /(\$?[A-Z]+\$?\d+:)?(\$?[A-Z]+\$?\d+)/g
const newValue = targetItem.value.replace(regexp, (...args) => {
const [match, _startingCellRef, endingCellRef] = args
const isRange = _startingCellRef != null
let newCellRef
const ctx = {
updatedOriginalCells,
loopItems
}
} else if (size > maxInfo.size) {
newData.meta.autofit.cols[newData.columnLetter] = {
value: currentValue,
size
if (isRange) {
const startingCellRef = _startingCellRef.slice(0, -1)
const newStartingCellRef = getNewCellRef(type === 'formulas' ? [targetItem.cellRef, startingCellRef] : startingCellRef, targetItem.loopMeta, 'rangeStart', ctx)
const newEndingCellRef = getNewCellRef(type === 'formulas' ? [targetItem.cellRef, endingCellRef] : endingCellRef, targetItem.loopMeta, 'rangeEnd', ctx)
return `${newStartingCellRef}:${newEndingCellRef}`
} else {
newCellRef = getNewCellRef(type === 'formulas' ? [targetItem.cellRef, endingCellRef] : endingCellRef, targetItem.loopMeta, 'standalone', ctx)
}
return newCellRef
})
if (type === 'newCellRef') {
updated.push(newValue)
}
}
return result
return new Handlebars.SafeString(updated.join('\n'))
}
if (
arguments.length === 1 &&
type === 'cellValueRaw'
) {
const newData = Handlebars.createFrame(optionsToUse.data)
const result = optionsToUse.fn(this, { data: newData })
async function autofit (options) {
const Handlebars = require('handlebars')
const processAutofitCols = require('xlsxProcessAutofitCols')
if (
optionsToUse?.data?.currentCellValueInfo != null &&
!Object.prototype.hasOwnProperty.call(optionsToUse.data.currentCellValueInfo, 'value')
) {
optionsToUse.data.currentCellValueInfo.value = result
}
const existingColsXml = options.fn(this)
return ''
await options.data.tasks.wait('sd')
return new Handlebars.SafeString(processAutofitCols(options.data.meta.autofit, existingColsXml))
}
if (
arguments.length === 1 &&
type === 'cellValueType'
) {
const cellValue = optionsToUse.data.currentCellValueInfo.value
let cellType
function lazyFormulas (options) {
const [resolveTask, rejectTask] = options.data.tasks.add('lazyFormulas')
if (cellValue == null) {
cellType = 'inlineStr'
} else if (
typeof cellValue === 'boolean' ||
(
cellValue != null &&
typeof cellValue === 'object' &&
Object.prototype.toString.call(cellValue) === '[object Boolean]'
)
) {
cellType = 'b'
} else if (
typeof cellValue === 'number' ||
(
cellValue != null &&
typeof cellValue === 'object' &&
Object.prototype.toString.call(cellValue) === '[object Number]'
)
) {
cellType = 'n'
} else {
cellType = 'inlineStr'
try {
const trackedCells = options.data.meta.trackedCells
const lazyFormulas = options.data.meta.lazyFormulas
assertOk(trackedCells != null, 'trackedCells needs to exists on internal data')
assertOk(lazyFormulas != null, 'lazyFormulas needs to exists on internal data')
if (lazyFormulas.count == null || lazyFormulas.count === 0) {
resolveTask()
return ''
}
const lazyFormulaIds = Object.keys(lazyFormulas.data)
const { getNewFormula } = require('cellUtils')
for (const lazyFormulaId of lazyFormulaIds) {
const lazyFormulaInfo = lazyFormulas.data[lazyFormulaId]
const {
formula,
parsedOriginCellRef,
originCellIsFromLoop,
previousLoopIncrement,
currentLoopIncrement,
cellRefs
} = lazyFormulaInfo
const { formula: newFormula } = getNewFormula(formula, parsedOriginCellRef, {
type: 'lazy',
originCellIsFromLoop,
previousLoopIncrement,
currentLoopIncrement,
trackedCells,
lazyCellRefs: cellRefs
})
// ensure we encode just some basic xml entities, formula values does not need to
// have the full xml entities escaped
lazyFormulaInfo.newFormula = newFormula
}
resolveTask()
return ''
} catch (e) {
rejectTask(e)
throw e
}
}
optionsToUse.data.currentCellValueInfo.type = cellType
function style (options) {
const Handlebars = require('handlebars')
const { parseStyle, getStyleInfo } = require('xlsxProcessStyle')
return cellType
const styleXml = options.fn(this)
const styleDoc = parseStyle(styleXml)
const styleInfo = getStyleInfo(styleDoc)
if (styleInfo != null) {
options.data.styleInfo = styleInfo
}
return new Handlebars.SafeString(styleXml)
}
if (
arguments.length === 1 &&
type === 'cellContent'
) {
const cellType = optionsToUse.data.currentCellValueInfo.type
const cellValue = optionsToUse.data.currentCellValueInfo.value
let result
function calcChain (options) {
const Handlebars = require('handlebars')
const processCalcChain = require('xlsxProcessCalcChain')
if (cellType === 'inlineStr') {
result = `<is><t>${cellValue == null ? '' : cellValue}</t></is>`
} else if (cellType === 'b') {
result = `<v>${cellValue ? '1' : '0'}</v>`
} else if (cellType === 'n') {
result = `<v>${cellValue}</v>`
const existingCalcChainXml = options.fn(this)
return new Handlebars.SafeString(processCalcChain(options.data.calcChainUpdatesMap, existingCalcChainXml))
}
function raw (options) {
return options.fn()
}
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
}
}
if (result == null) {
throw new Error(`xlsxSData type="cellContent" helper does not support cell type "${cellType}"`)
return parent
}
function getParentLoopItemByHierarchy (childLoopItem, loopItems) {
assertOk(childLoopItem != null, 'getParentLoopItemByHierarchy childLoopItem arg is required')
assertOk(Array.isArray(loopItems), 'getParentLoopItemByHierarchy loopItems arg is invalid')
const parentHierarchyId = childLoopItem.hierarchyId.split('#').slice(0, -1).join('#')
if (parentHierarchyId === '') {
return
}
return new Handlebars.SafeString(result)
return getLoopItemById({ idName: 'hierarchyId', idValue: parentHierarchyId }, loopItems)
}
if (
arguments.length === 1 &&
type === 'autofit'
) {
function getCurrentLoopItem (loopId, loopItems) {
assertOk(Array.isArray(loopItems), 'getCurrentLoopItem loopItems arg is invalid')
if (loopId == null) {
return
}
return getLoopItemById({ idName: 'id', idValue: loopId }, loopItems)
}
function getPreviousLoopItems (loopId, evaluatedLoopsIds, loopItems) {
assertOk(Array.isArray(evaluatedLoopsIds), 'getPreviousLoopItems evaluatedLoopsIds arg is invalid')
assertOk(Array.isArray(loopItems), 'getPreviousLoopItems loopItems arg is invalid')
const lastEvaluatedLoopId = evaluatedLoopsIds[evaluatedLoopsIds.length - 1]
const loopItemsToGet = loopId != null && loopId === lastEvaluatedLoopId ? evaluatedLoopsIds.slice(0, -1) : evaluatedLoopsIds
const result = []
const autofitInfo = optionsToUse.data.meta.autofit
for (const [colLetter, colInfo] of Object.entries(autofitInfo.cols)) {
result.push(`<col ref="${colLetter}" size="${colInfo.size}" />`)
for (const lId of loopItemsToGet) {
const loopItem = getLoopItemById({ idName: 'id', idValue: lId }, loopItems)
assertOk(loopItem != null, `Can not find loop item by id "${lId}"`)
if (!loopItem.completed) {
continue
}
result.push(loopItem)
}
return new Handlebars.SafeString(result.join('\n'))
return result
}
const getNewCellRef = (cellRefInput, originLoopMeta, mode = 'standalone', context) => {
function getCurrentAndPreviousLoopItemsByTarget (byTarget, loopItems) {
assertOk(byTarget != null, 'getCurrentAndPreviousLoopItemsByTarget byTarget arg is invalid')
assertOk(byTarget.rowNumber != null, 'getCurrentAndPreviousLoopItemsByTarget byTarget.rowNumber arg is required')
assertOk(byTarget.columnNumber != null, 'getCurrentAndPreviousLoopItemsByTarget byTarget.columnNumber arg is required')
assertOk(Array.isArray(loopItems), 'getCurrentAndPreviousLoopItemsByTarget loopItems arg is invalid')
const { rowNumber, columnNumber } = byTarget
const matchedLoopItems = loopItems.filter((item) => {
assertOk(item.completed, 'getCurrentAndPreviousLoopItemsByTarget invalid usage, it should be called only after all loop items are completed evaluated')
return item.start <= rowNumber
})
let current
const previousAll = [...matchedLoopItems]
const targetLoopItem = previousAll[previousAll.length - 1]
const previous = previousAll.slice(0, -1)
if (targetLoopItem != null) {
let isInside = false
const limit = targetLoopItem.type === 'block' ? targetLoopItem.end : targetLoopItem.start
if (rowNumber === limit) {
// for row loops we assume the row is inside when the row just matches the limit
// (even if technically on the out of loop right case we should check columnEnd,
// we don't do that because in that case the cell will anyway keep on its original place)
isInside = targetLoopItem.type === 'block' ? targetLoopItem.columnEnd > columnNumber : true
} else {
isInside = limit > rowNumber
}
if (!isInside) {
previous.push(targetLoopItem)
} else {
current = targetLoopItem
}
}
return {
current,
previousAll,
previous
}
}
function getLoopItemTemplateLength (loopItem) {
assertOk(loopItem != null, 'getLoopItemTemplateLength loopItem arg is invalid')
let templateLength = 1
if (loopItem.type === 'block') {
templateLength = (loopItem.end - loopItem.start) + 1
}
return templateLength
}
function getParentsLoopItems (loopId, loopItems) {
assertOk(loopId != null, 'getParentsLoopItems loopId arg is invalid')
assertOk(Array.isArray(loopItems), 'getParentsLoopItems loopItems arg is invalid')
const results = []
const parentIdParts = loopId.split('#').slice(0, -1)
if (parentIdParts.length === 0) {
return results
}
let parentId = ''
for (let index = 0; index < parentIdParts.length; index++) {
parentId += parentId === '' ? parentIdParts[index] : `#${parentIdParts[index]}`
const result = getLoopItemById({ idName: 'id', idValue: parentId }, loopItems)
assertOk(result != null, `Can not find loop item by id "${parentId}"`)
results.push(result)
}
return results
}
function getNewCellRef (cellRefInput, originLoopMeta, mode = 'standalone', context) {
const type = 'newCellRef'
const { updatedOriginalCells, loopItems } = context

@@ -809,2 +1131,3 @@ let cellRef

const { parseCellRef } = require('cellUtils')
const parsedCellRef = parseCellRef(cellRef)

@@ -820,5 +1143,3 @@ const parsedOriginCellRef = originCellRef != null ? parseCellRef(originCellRef) : undefined

const {
current: currentLoopItemForTarget,
previousAll: previousAllLoopItemsForTarget,
previous: previousLoopItemsForTarget
current: currentLoopItemForTarget, previousAll: previousAllLoopItemsForTarget, previous: previousLoopItemsForTarget
} = getCurrentAndPreviousLoopItemsByTarget({

@@ -864,12 +1185,10 @@ rowNumber: parsedCellRef.rowNumber,

if (
currentLoopItemForTarget != null &&
if (currentLoopItemForTarget != null &&
(
(type === 'newCellRef' && mode === 'rangeEnd') ||
(type === 'formulas' &&
originCellRef != null &&
!originIsLoopItem &&
mode === 'rangeEnd')
)
) {
originCellRef != null &&
!originIsLoopItem &&
mode === 'rangeEnd')
)) {
includeAll = true

@@ -903,297 +1222,179 @@ }

if (
arguments.length === 1 &&
(type === 'mergeCell' || type === 'formula')
) {
const rowNumber = optionsToUse.data.rowNumber
if (rowNumber == null) {
throw new Error(`xlsxSData type="${type}" invalid usage, rowNumber needs to exists on internal data`)
function assertOk (valid, message) {
if (!valid) {
throw new Error(message)
}
}
let output = ''
const helpers = {
ws,
sd,
dimension,
loop,
outOfLoop,
outOfLoopPlaceholder,
r,
c: function (info, options) {
delete options.hash.t
if (type === 'mergeCell') {
const originalCellRefRange = optionsToUse.hash.originalCellRefRange
if (originalCellRefRange == null) {
throw new Error(`xlsxSData type="${type}" helper originalCellRefRange arg is required`)
// restoring original t attribute, needed when we render xml of cell
if (options.hash.__originalT__ != null) {
options.hash.t = options.hash.__originalT__
delete options.hash.__originalT__
}
const { newValue } = evaluateCellRefsFromExpression(originalCellRefRange, (cellRefInfo) => {
const isRange = cellRefInfo.type === 'rangeStart' || cellRefInfo.type === 'rangeEnd'
return c.call(this, info, options)
},
m: function (options) {
return mergeOrFormulaCell.call(this, 'mergeCell', options)
},
f: function (options) {
return mergeOrFormulaCell.call(this, 'formula', options)
},
fs: formulaShared,
mergeCells,
mergeCellsCount,
mergeCellsItems,
mI,
newCellRef,
autofit,
lazyFormulas,
style,
calcChain,
raw
}
if (!isRange) {
throw new Error(`xlsxSData type="mergeCell" helper only support range for cell refs. value: "${originalCellRefRange}"`)
}
return {
resolveHelper: (helperName, argumentsLength, context, values, options) => {
const targetHelper = helpers[helperName]
const validCall = targetHelper != null ? argumentsLength === targetHelper.length : false
const increment = cellRefInfo.type === 'rangeEnd' ? cellRefInfo.parsedRangeEnd.rowNumber - cellRefInfo.parsedRangeStart.rowNumber : 0
const newCellRef = generateNewCellRefFromRow(cellRefInfo.parsed, rowNumber + increment)
return newCellRef
})
const mergeCell = {
original: originalCellRefRange,
value: newValue
if (!validCall) {
throw new Error(`Invalid usage of _D helper${helperName != null ? ` (t: ${helperName})` : ''}`)
}
optionsToUse.data.meta.mergeCells.push(mergeCell)
} else {
const currentCellRef = optionsToUse.data.currentCellRef
const trackedCells = optionsToUse.data.meta.trackedCells
const lazyFormulas = optionsToUse.data.meta.lazyFormulas
const originalCellRef = optionsToUse.hash.originalCellRef
const originalFormula = optionsToUse.hash.originalFormula
const previousLoopIncrement = optionsToUse.data.previousLoopIncrement
const currentLoopIncrement = optionsToUse.data.currentLoopIncrement
try {
if (values.length > 0) {
return targetHelper.call(context, ...values, options)
}
if (currentCellRef == null) {
throw new Error(`xlsxSData type="${type}" invalid usage, currentCellRef needs to exists on internal data`)
return targetHelper.call(context, options)
} catch (error) {
error.message = `_D t="${helperName}" helper, ${error.message}`
throw error
}
},
assertDataArg: assertOk
}
})()
if (trackedCells == null) {
throw new Error(`xlsxSData type="${type}" invalid usage, trackedCells needs to exists on internal data`)
}
function _D () {
const values = []
const argsLength = arguments.length
let optionsToUse
if (lazyFormulas == null) {
throw new Error(`xlsxSData type="${type}" invalid usage, lazyFormulas needs to exists on internal data`)
}
if (argsLength > 1) {
optionsToUse = arguments[argsLength - 1]
if (originalCellRef == null) {
throw new Error('xlsxSData type="formula" helper originalCellRef arg is required')
}
if (originalFormula == null) {
throw new Error('xlsxSData type="formula" helper originalFormula arg is required')
}
if (currentLoopIncrement == null) {
throw new Error(`xlsxSData type="${type}" invalid usage, currentLoopIncrement needs to exists on internal data`)
}
const parsedOriginCellRef = parseCellRef(originalCellRef)
const originCellIsFromLoop = optionsToUse.data.currentLoopId != null
const { formula: newFormula } = getNewFormula(type, originalFormula, parsedOriginCellRef, {
type: 'normal',
originCellIsFromLoop,
previousLoopIncrement,
currentLoopIncrement,
trackedCells,
includeLoopIncrementResolver: (cellRefIsFromLoop, cellRefInfo) => {
return (
cellRefIsFromLoop &&
trackedCells[cellRefInfo.localRef] != null &&
trackedCells[cellRefInfo.localRef].loopHierarchyId === getCurrentLoopItem(optionsToUse.data.currentLoopId, optionsToUse.data.loopItems)?.hierarchyId
)
},
lazyFormulas,
currentCellRef
})
output = newFormula
for (let idx = 0; idx < argsLength - 1; idx++) {
values.push(arguments[idx])
}
return output
} else {
optionsToUse = arguments[0]
}
if (
arguments.length === 1 &&
type === 'mergeCells'
) {
const targetItems = optionsToUse.data.meta.mergeCells
const newData = Handlebars.createFrame(optionsToUse.data)
const type = optionsToUse.hash.t
newData.mergeCellsCount = targetItems.length
newData.mergeCellsTemplates = Object.create(null)
__xlsxD.assertDataArg(type != null, '_D helper t arg is required')
return optionsToUse.fn(this, { data: newData })
}
return __xlsxD.resolveHelper(type, arguments.length, this, values, optionsToUse)
}
if (
arguments.length === 1 &&
type === 'mergeCellsItems'
) {
const targetItems = optionsToUse.data.meta.mergeCells
// alias for {{_D t='r'}} helper call, we do it this way to optimize size of the generated xml
function _R (data, options) {
options.hash.t = 'r'
options.hash.o = data
return _D.call(this, options)
}
// run the body to fulfill the merge cells templates
optionsToUse.fn(this)
// alias for {{_D t='c'}} helper call, we do it this way to optimize size of the generated xml
function _C (data, options) {
options.hash.t = 'c'
return _D.call(this, { type: 'normal', originalCellLetter: data, calcChainUpdate: false }, options)
}
const mergeCellsTemplates = optionsToUse.data.mergeCellsTemplates
// alias for {{_D t='c'}} helper call with calcChainUpdate: true
function _c (data, options) {
options.hash.t = 'c'
return _D.call(this, { type: 'normal', originalCellLetter: data, calcChainUpdate: true }, options)
}
const updated = []
// alias for {{_D t='c'}} helper with autodetect call
function _T () {
let value
let raw
let options
for (const targetItem of targetItems) {
const template = mergeCellsTemplates[targetItem.original]
const output = template({ newRef: targetItem.value })
updated.push(output)
}
return new Handlebars.SafeString(updated.join('\n'))
if (arguments.length === 1) {
options = arguments[0]
} else if (arguments.length === 2) {
value = arguments[0]
options = arguments[1]
} else {
value = arguments[0]
raw = arguments[1] === 1
options = arguments[2]
}
if (
arguments.length === 1 &&
type === 'mergeCellItem'
) {
const originalCellRefRange = optionsToUse.hash.originalCellRefRange
if (originalCellRefRange == null) {
throw new Error('xlsxSData type="mergeCellItem" helper originalCellRefRange arg is required')
}
optionsToUse.data.mergeCellsTemplates[originalCellRefRange] = optionsToUse.fn
return ''
if (options.hash.t != null) {
options.hash.__originalT__ = options.hash.t
}
if (
arguments.length === 1 &&
type === 'formulaSharedRefRange'
) {
const rowNumber = optionsToUse.data.rowNumber
options.hash.t = 'c'
if (rowNumber == null) {
throw new Error('xlsxSData type="formulaSharedRefRange" invalid usage, rowNumber needs to exists on internal data')
}
const data = { type: 'autodetect', originalCellLetter: options.hash.r, calcChainUpdate: false }
const originalSharedRefRange = optionsToUse.hash.originalSharedRefRange
data.value = value
data.escape = true
if (originalSharedRefRange == null) {
throw new Error('xlsxSData type="formulaSharedRefRange" helper originalSharedRefRange arg is required')
}
const { newValue } = evaluateCellRefsFromExpression(originalSharedRefRange, (cellRefInfo) => {
const newCellRef = generateNewCellRefFromRow(cellRefInfo.parsed, rowNumber)
return newCellRef
})
return newValue
if (raw != null && raw) {
data.escape = false
}
if (
arguments.length === 1 &&
type === 'lazyFormulas'
) {
const trackedCells = optionsToUse.data.meta.trackedCells
const lazyFormulas = optionsToUse.data.meta.lazyFormulas
return _D.call(this, data, options)
}
if (trackedCells == null) {
throw new Error(`xlsxSData type="${type}" invalid usage, trackedCells needs to exists on internal data`)
}
// alias for {{_D t='c'}} helper with autodetect call with calcChainUpdate: true
function _t () {
let value
let raw
let options
if (lazyFormulas == null) {
throw new Error(`xlsxSData type="${type}" invalid usage, lazyFormulas needs to exists on internal data`)
}
if (lazyFormulas.count == null || lazyFormulas.count === 0) {
return new Handlebars.SafeString('')
}
const result = []
const lazyFormulaIds = Object.keys(lazyFormulas.data)
for (const lazyFormulaId of lazyFormulaIds) {
const lazyFormulaInfo = lazyFormulas.data[lazyFormulaId]
const {
formula,
parsedOriginCellRef,
originCellIsFromLoop,
previousLoopIncrement,
currentLoopIncrement,
cellRefs
} = lazyFormulaInfo
const { formula: newFormula } = getNewFormula(type, formula, parsedOriginCellRef, {
type: 'lazy',
originCellIsFromLoop,
previousLoopIncrement,
currentLoopIncrement,
trackedCells,
lazyCellRefs: cellRefs
})
result.push(`<item id="${lazyFormulaId}">${newFormula}</item>`)
}
return new Handlebars.SafeString(`<lazyFormulas>${result.join('\n')}</lazyFormulas>`)
if (arguments.length === 1) {
options = arguments[0]
} else if (arguments.length === 2) {
value = arguments[0]
options = arguments[1]
} else {
value = arguments[0]
raw = arguments[1] === 1
options = arguments[2]
}
// TODO: this should be refactored at some point to be more generic
// and support nested loops, maybe the logic will be similar to mergeCell or formula helpers
// when this is done we can remove all the methods that are only used here "getNewCellRef", "getCurrentAndPreviousLoopItemsByTarget"
if (
arguments.length === 1 &&
type === 'newCellRef'
) {
const updatedOriginalCells = optionsToUse.data.meta.updatedOriginalCells
const loopItems = optionsToUse.data.loopItems
let targetItems = []
const updated = []
if (type === 'newCellRef') {
targetItems = [{ value: optionsToUse.hash.originalCellRefRange }]
}
for (const targetItem of targetItems) {
const regexp = /(\$?[A-Z]+\$?\d+:)?(\$?[A-Z]+\$?\d+)/g
const newValue = targetItem.value.replace(regexp, (...args) => {
const [match, _startingCellRef, endingCellRef] = args
const isRange = _startingCellRef != null
let newCellRef
const ctx = {
updatedOriginalCells,
loopItems
}
if (isRange) {
const startingCellRef = _startingCellRef.slice(0, -1)
const newStartingCellRef = getNewCellRef(type === 'formulas' ? [targetItem.cellRef, startingCellRef] : startingCellRef, targetItem.loopMeta, 'rangeStart', ctx)
const newEndingCellRef = getNewCellRef(type === 'formulas' ? [targetItem.cellRef, endingCellRef] : endingCellRef, targetItem.loopMeta, 'rangeEnd', ctx)
return `${newStartingCellRef}:${newEndingCellRef}`
} else {
newCellRef = getNewCellRef(type === 'formulas' ? [targetItem.cellRef, endingCellRef] : endingCellRef, targetItem.loopMeta, 'standalone', ctx)
}
return newCellRef
})
if (type === 'newCellRef') {
updated.push(newValue)
}
}
return new Handlebars.SafeString(updated.join('\n'))
if (options.hash.t != null) {
options.hash.__originalT__ = options.hash.t
}
if (
arguments.length === 1 &&
type === 'dimensionRef'
) {
const originalCellRefRange = optionsToUse.hash.originalCellRefRange
options.hash.t = 'c'
if (originalCellRefRange == null) {
throw new Error('xlsxSData type="dimensionRef" helper originalCellRefRange arg is required')
}
const data = { type: 'autodetect', originalCellLetter: options.hash.r, calcChainUpdate: true }
const refsParts = originalCellRefRange.split(':')
data.value = value
data.escape = true
if (refsParts.length === 1) {
return refsParts[0]
}
const lastCellRef = optionsToUse.data.meta.lastCellRef
const parsedEndCellRef = parseCellRef(refsParts[1])
const parsedLastCellRef = parseCellRef(lastCellRef)
return `${refsParts[0]}:${parsedEndCellRef.letter}${parsedLastCellRef.rowNumber}`
if (raw != null && raw) {
data.escape = false
}
throw new Error(`invalid usage of xlsxSData helper${type != null ? ` (type: ${type})` : ''}`)
return _D.call(this, data, options)
}
SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc