Change keys of array object after inserting in the middle/first of array object

Question

I am working on an Excel-like application, but I am having problems with a multi-dimensional array.

I have array of columns that looks like this:

var columns = [
  {A: {binding: "A",header: "A"},
  {B: {binding: "B",header: "B"},
  {C: {binding: "C",header: "C"}
];

And then I have an array representing the value of every cell that looks like this:

var data = [
   {A:"row 0 col A", B: "row 0 col B", C:"row 0 col C"},
   {A:"row 1 col A", B: "row 1 col B", C:"row 1 col C"},
   {A:"row 2 col A", B: "row 2 col B", C:"row 2 col C"}
];

The binding property of the objects in the columns array will be used to get the column's value for each row.

However, I am encountering an issue when inserting a new column in the middle of some alphabetical keys. Let's say I need a new column between column A and column B. The result looks like this:

var columns = [
  {A: {binding: "A",header: "A"},
  {B: {binding: "B",header: "B"},
  {C: {binding: "C",header: "C"},
  {D: {binding: "D",header: "D"}
];

It's just pushing the new column onto columns.

I think I have to insert a new cell/item between all alphabetical keys for every row, and rename all the keys (in this case, rename keys B to C, C to D, etc).

I would like the result to look like this:

var data = [
   {A:"row 0 col A", B:"new col inserted here", C:"row 0 col B", D:"row 0 col C"},
   {A:"row 1 col A", B:"new col inserted here", C:"row 1 col B", D:"row 1 col C"},
   {A:"row 2 col A", B:"new col inserted here", C:"row 2 col B", D:"row 2 col C"}
];

I'm concerned about performance issues if I were to rename all these keys, especially if I have 120 columns (A-DZ) and 100 rows.

I have two questions:

• What is the most efficient way to do this?
• Will I have performance issues when renaming these keys if I have lots of rows and columns?

Answer 1

Don't use literal names for rows or columns.

To increase performance, link one data object directly to column and/or row (make some attributes like _row and _col in cell array) and put _cells in rows and cols.

Example:

var data = {};

data.rows = [
{idx: 'A', _cells: []},
{idx: 'B', _cells: []},
{idx: 'C', _cells: []}
]

data.cols = [
{idx: 'A', _cells: []},
{idx: 'B', _cells: []},
{idx: 'C', _cells: []}
]

data.cells = [];

function addCell( rowIdx, colIdx, value) {
  var row = data.rows[rowIdx];
  var col = data.cols[rowIdx];
  var cell = {value: value, _row: row, _col: col};
  data.cells.push(cell);
  row._cells.push(cell);
  col._cells.push(cell);
}

for(var r=0; r<3; r++)
  for (var c=0;c<3; c++)
    addCell(r,c,'row ' + r + ' col ' + String.fromCharCode(65 + c));

console.log(data);

so... you can insert/delete/sort/rename rows without difficulty.

You can insert/delete/sort/rename all cells from a column without difficulty.

If order is important, you can "map" cells sorted by row/col

Answer 2

First off, I think the comment to the question from Andam is correct. There are certainly better ways of making a spreadsheet-like display out of an array of objects. My thought would simply to maintain an array of header objects containing (at a minimum) a column title and the name of the property to show in the column. Then inserting a column would simply involve updating that array (and perhaps defaulting that property value for your rows.)

But you still want to proceed in the manner suggested by your question, here's a possible implementation:

// Utility function
const alphaNum = (cs) => (n) => 
  n < cs.length
    ? cs [n % cs.length]
    : alphaNum (cs) (Math.floor(n / cs.length) - 1) + cs [n % cs.length]

// Helper function
const toColumnName = alphaNum ('ABCDEFGHIJKLMNOPQRSTUVWXYZ' .split (''))

// Main function
const insertColumn = (index, data, makeVal) => 
  data .map ((item, i, arr, vals = Object .values (item)) => Object.fromEntries ([
    ...vals .slice (0, index),
    makeVal (item, i, index),
    ...vals .slice (index)
  ] .map ((item, i) => [toColumnName(i), item])))

// Dummy cell creation function
const createCell = (row, rowNbr, colNbr) => 
  `new col inserted at row ${rowNbr} col ${colNbr}`

// Sample data
const data = [{A: 'row 0 col A', B: 'row 0 col B', C: 'row 0 col C'}, {A: 'row 1 col A', B: 'row 1 col B', C: 'row 1 col C'}, {A: 'row 2 col A', B: 'row 2 col B', C: 'row 2 col C'}]

// Demo
console .log (insertColumn (1, data, createCell))

.as-console-wrapper {max-height: 100% !important; top: 0}

• toColumnName simply converts a (zero-based) index into a spreadsheet-like column identifier ('A', 'B', 'C', ... 'Z', 'AA', 'AB', ... 'AZ', 'BA', 'BB', ... 'BZ', ... 'ZZ', 'AAA', ...)

• It uses alphaNum, which does the heavy-lifting, based on an array of characters to represent the pseudo-digits used.

• insertColumn is the main function. It takes the (zero-based) index of the inserted column, the data, and a function used to create your cell value. It changes each row by extracting the values, inserting the new one created by your function, and then mapping back to the column names from the new indices, then rebuilding the object with Object .fromEntries.

Here we pass it a dummy column-creation function, which, we can note, is passed the row object, the row index and the new column index.

I have no idea how it will perform on your realistic data, but the numbers you described wouldn't worry me overmuch. There are some minor optimizations you could do here. For instance, we don't need to recalculate the column names for each row and could cache them instead. But I would only bother with them if the performance doesn't meet your expectations.

And, once again, I think there are better ways to solve the likely underlying problem.