"use strict"
// get the rows as an array
const rows = [...document.querySelectorAll('tbody tr')]
const table = rows.map(row => [...row.children].map(
cell => cell.textContent
))
const headers = [...document.querySelectorAll('th')]
.map(header => ({
header: header.textContent,
type: header.dataset.type,
precision: header.dataset.precision || 2,
radix: header.dataset.radix || 10
}))
const parse = (options, x) => {
switch (options.type) {
case 'Number':
return Number(x)
case 'Float':
return toFixed(options.precision, x)
case 'Int':
return parseInt(x, options.radix || 10)
default:
return Number(x)
}
}
const alfaToInt =
cell => cell.charCodeAt(0) - 65
const alfaToNum = x =>
x.charCodeAt(0) - 65
const zeros = x => [0, 10, 100, 1000, 10000, 100000][x]
const toFixed = (precision, x) =>
Math.round(Number(x) * zeros(precision)) / zeros(precision)
const AVERAGE = range => SUM(range) / range.length
const COUNT = range => range.length
const INT = x => parseInt(x)
const MIN = range => Math.min(...range)
const MAX = range => Math.max(...range)
const SUM = range => range.reduce((a, b) => a + Number(b), 0)
// iterate over the rows
rows.forEach(row => {
// get the child tds
const tds = row.querySelectorAll('td')
// iterate over the tds to find the equations
tds.forEach((td, j) => {
const text = td.textContent
const match = text.match(/\=(.*)/)
// check if the first character is = followed by anything
if (match) {
const equation = match[1]
// convert the placeholders into their values for the sibling td's
const result = equation
// get values from a range
.replace(/\[([A-Z]+[0-9]+):([A-Z]+[0-9]+)\]/g, (_, start, end) => {
const [_s, startColumn, startRow] = start.match(/([A-Z]+)([0-9]+)/)
const [_e, endColumn, endRow] = end.match(/([A-Z]+)([0-9]+)/)
let range
if (startRow == endRow) {
const length = alfaToNum(endColumn) - alfaToNum(startColumn) + 1
range = [...rows[startRow - 1].children]
.slice(alfaToNum(startColumn), length)
.map(cell => cell.textContent)
} else if (startColumn === endColumn) {
range = rows.slice(startRow, endRow).map((row, i) => {
const index = alfaToNum(startColumn)
return parse(headers[index], row.children[index].textContent)
})
}
return '(' + JSON.stringify(range) + ')'
})
.replace(/([A-Z]+)([0-9]+)/g, (_, column, row) => {
const index = alfaToNum(column)
return parse(
headers[index],
rows[row - 1].children[index].textContent
)
})
// get values in the same row
.replace(/([A-Z]+)+\$/g, (_, cell) => {
const index = cell.charCodeAt(0) - 65
return tds[index].textContent
})
// set the equations field to the result
try {
const precision = headers[j].precision
td.textContent = toFixed(precision, eval(result))
} catch (e) {
// if there is an error in the equation display 'Error'
td.textContent = `Error: ${e.message}`
}
}
})
})
body { font-family: sans-serif }
table { border-collapse: collapse }
thead td { color: #aaa; }
td, th { padding: 0.25em 0.5em }
tbody tr:nth-child(odd) { background: #f7f7f7 }
<table border='1'>
<thead>
<tr>
<td>A</td>
<td>B</td>
<td>C</td>
<td>D</td>
</tr>
<tr>
<th data-type="Number">score</th>
<th data-type="Number">count</th>
<th data-type="Float" data-precision="2">average</th>
<th data-type="Number">sum</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>2</td>
<td>=A$/B$</td>
<td>=A$+B$</td>
</tr>
<tr>
<td>3</td>
<td>4</td>
<td>=A$/B$</td>
<td>=A$+B$</td>
</tr>
<tr>
<td>5</td>
<td>6</td>
<td>=A$/B$</td>
<td>=fail</td>
</tr>
<tr>
<td>=AVERAGE([A1:A3])</td>
<td>=([B1:B3]).reduce((a,b) => a+b, 0)</td>
<td>=([C1:C3]).reduce((a,b) => a+b, 0)</td>
<td>=SUM([A1:C1])</td>
</tr>
</tbody>
</table>
