[Bug Report]: Invalid merged row position in XLSX files

[iDarush]

Environment
Carbone Version: 2.0.1
Node Version: 10.15.1
Desktop OS: Ubuntu 20.04.1 LTS

Expected Behaviour
If XLSX template contains merged cells after repetiton, this cells should be moved down

Actual Behaviour
Cell merging applies inside of filled table and breaks formatting.
This section of xl/worksheets/sheetX.xml should be updated

<mergeCells count="1">
    <mergeCell ref="A6:XFD6"/>
</mergeCells>

Screenshots
Template

Result

Other comments
ODS templates works fine

[steevepay]

Hello @iDarush, thank you for reaching us, and sorry for the delay!
The method used by Carbone to manipulate XLSX reports may create overflows. We plan to rewrite the XLSX rendering and we will fix your issue at the same time.
In the meantime, you can use an ODS spreadsheet as a temporary solution.

[nicoladefranceschi]

I editing my answer to this issue to fix also the mergeCells problem
#86 (comment)

code:

// HACK: fix excel empty columns/rows disappears
let _excelRowR = null
const ACharCode = 'A'.charCodeAt(0)
function columnLettersToNumber (letters) {
  let n = 0
  letters.split('').forEach(letter => {
    const v = letter.charCodeAt(0) - ACharCode + 1
    n = n * 26 + v
  })
  return n
}
function columnNumberToLetters (n) {
  let letters = ''
  do {
    n = n - 1
    const v = n % 26
    letters = String.fromCharCode(ACharCode + v) + letters
    n = (n - v) / 26
  } while (n > 0)
  return letters
}
preprocessor.removeRowCounterInWorksheet = function (xml) {
  if (typeof (xml) !== 'string') {
    return xml
  }

  if (!_excelRowR) {
    const prefix = '_t_' + new Date().getTime() + '_' + Math.round(Math.random() * 100000) + '_'

    _excelRowR = {
      prefix: prefix,
      bothRegexp: new RegExp(`<(?:c|row)[^>]*\\s(${prefix}="([rc])([0-9]+)_([0-9]*)_([0-9]+)")[^>]*>`, 'g')
    }
  }

  let lastRowIndex = 0
  let lastColIndex = 0
  let lastRowKey = null
  let lastColKey = null
  const spaceRight = {}
  let hasSpaceRight = false

  let content = xml.replace(/<(?:c|row)[^>]*\s(r="([A-Z]*)([0-9]+)")[^>]*>/g, function (m, rowValueString, columnValue, rowValue) {
    let what
    let value
    rowValue = parseInt(rowValue, 10)
    if (!columnValue) {
      what = 'r'
      value = rowValue - lastRowIndex// delta
      lastRowIndex = rowValue
      lastColIndex = 0
      if (lastRowKey) {
        spaceRight[lastRowKey] = value
        hasSpaceRight = true
      }
      lastRowKey = 'r' + rowValue
    } else {
      columnValue = columnLettersToNumber(columnValue)
      what = 'c'
      value = columnValue - lastColIndex
      lastColIndex = columnValue
      if (lastColKey) {
        spaceRight[lastColKey] = value
        hasSpaceRight = true
      }
      lastColKey = 'c' + columnValue
    }
    return m.replace(rowValueString, `${_excelRowR.prefix}="${what}${value}_${columnValue}_${rowValue}"`)
  }).replace(/<(?:c|row)[^>]*(spans="\S+")[^>]*>/g, function (m, rowValue) {
    return m.replace(rowValue, '')
  })

  if (hasSpaceRight) {
    content = _excelRowR.prefix + '_spaceRight=' + JSON.stringify(spaceRight) + '#' + content
  }
  return content
}
const _buildXML = builder.buildXML
builder.buildXML = function (xml, data, options, callback) {
  return _buildXML.call(this, xml, data, options, (err, xmlResult) => {
    if (_excelRowR && typeof xmlResult === 'string') {
      let spaceRight = {}
      if (xmlResult.startsWith(_excelRowR.prefix + '_spaceRight=')) {
        const start = xmlResult.indexOf('=')
        const end = xmlResult.indexOf('#')
        const objString = xmlResult.substring(start + 1, end)
        spaceRight = JSON.parse(objString)
        xmlResult = xmlResult.substring(end + 1)
      }

      let realRowIndex = 0
      let realColIndex = 0
      let lastRow = 0
      let lastCol = 0
      let foundRows = false
      const oldToNewCellsMap = {}
      xmlResult = xmlResult.replace(_excelRowR.bothRegexp, function (m, rowValueString, what, delta, columnValue, rowValue) {
        foundRows = true
        delta = parseInt(delta, 10)
        // console.log(rowValueString, what, delta, columnValue, rowValue)

        if (what === 'r') {
          rowValue = parseInt(rowValue, 10)
          if (lastRow >= rowValue) {
            const s = spaceRight['r' + lastRow]
            if (s) {
              delta = s
            }
          }
          realRowIndex += delta
          realColIndex = 0
          lastCol = 0
          lastRow = rowValue
          return m.replace(rowValueString, `r="${realRowIndex}"`)
        } else if (what === 'c') {
          rowValue = parseInt(rowValue, 10)
          columnValue = parseInt(columnValue, 10)
          if (lastCol >= columnValue) {
            const s = spaceRight['c' + lastCol]
            if (s) {
              delta = s
            }
          }
          realColIndex += delta
          lastCol = columnValue
          const oldCellName = `${columnNumberToLetters(columnValue)}${rowValue}`
          const newCellName = `${columnNumberToLetters(realColIndex)}${realRowIndex}`
          oldToNewCellsMap[oldCellName] = oldToNewCellsMap[oldCellName] || []
          oldToNewCellsMap[oldCellName].push(newCellName)
          return m.replace(rowValueString, `r="${newCellName}"`)
        }

        return m
      })

      if (foundRows) {
        // check merge
        xmlResult = xmlResult.replace(/<mergeCells[^>]*>(.*)<\/mergeCells>/g, function (m, content) {
          const matches = [...content.matchAll(/<mergeCell[^>]*\sref="([A-Z0-9]+):([A-Z0-9]+)"[^>]*>/g)]
          const newContent = []
          matches.forEach(match => {
            const cell1 = match[1]
            const cell2 = match[2]
            const newCells1 = oldToNewCellsMap[cell1]
            const newCells2 = oldToNewCellsMap[cell2]
            if (newCells1 && newCells2) {
              if (newCells1.length === newCells2.length) {
                for (let index = 0; index < newCells1.length; index++) {
                  const newCell1 = newCells1[index]
                  const newCell2 = newCells2[index]
                  // console.log(cell1, cell2, newCell1, newCell2)
                  newContent.push(`<mergeCell ref="${newCell1}:${newCell2}"/>`)
                }
              } else {
                console.log(cell1, cell2, newCells1, newCells2)
              }
            }
          })
          return `<mergeCells count="${newContent.length}">${newContent.join('')}</mergeCells>`
        })
      }
    }
    return callback(err, xmlResult)
  })
}