Object or model data

[IcepiQ]

I'm trying to load an object/model as data into hyperformula but the setCellContents won't allow it, is there a different way to load this sort of data?

The reason I need the mode/object is because I want hyperformula to do calculations then send the output to a kendo ui grid which requires model data.
According to your documentation hyperformula is supposed to integrate with grids such as Kendo Grid UI, do you have documentation on how to achieve this?

If I have this model which I adapted from your demo:

export const tableData = [
  { Name: "Greg Black", Year_1: 4.66, Year_2: "=B1*1.3", Avg_1: "=AVERAGE(B1:C1)", Sum_1: "=SUM(B1:C1)" },
  { Name: "Anne Carpenter", Year_1: 5.25, Year_2: "=$B$2*30%", Avg_1: "=AVERAGE(B2:C2)", Sum_1: "=SUM(B2:C2)" },
  { Name: "Natalie Dem", Year_1: 3.59, Year_2: "=B3*2.7+2+1", Avg_1: "=AVERAGE(B3:C3)", Sum_1: "=SUM(B3:C3)" },
  { Name: "John Sieg", Year_1: 12.51, Year_2: "=B4*(1.22+1)", Avg_1: "=AVERAGE(B4:C4)", Sum_1: "=SUM(B4:C4)" },
  {
    Name: "Chris Aklips",
    Year_1: 7.63,
    Year_2: "=B5*1.1*SUM(10,20)+1",
    Avg_1: "=AVERAGE(B5:C5)",
    Sum_1: "=SUM(B5:C5)"
  },
];

then the error happens in:
hf.setCellContents(
  {
    row: 0,
    col: 0,
    sheet: sheetId
  },
  tableData
);

Regards
Raff

[sequba]

Hi @IcepiQ, HyperFormula is a spreadsheet engine, so it operates on arrays, not objects/models.

For integration with Kendo UI you need to convert your models to arrays and after calculation convert it back to models. Here is an example of how you can achieve it:

// Define the props of your models
const ALL_MODEL_PROPS = ["Name", "Year_1", "Year_2", "Avg_1", "Sum_1"];

// Conversion functions
function modelToArray(model) {
  return ALL_MODEL_PROPS.map((prop) => model[prop]);
}

function arrayToModel(array) {
  const entries = ALL_MODEL_PROPS.map((key, index) => [key, array[index]]);
  return Object.fromEntries(entries);
}

// Define your data as an array of models
const sourceModelData = [
  {
    Name: "Greg Black",
    Year_1: 42,
    Year_2: 24,
    Avg_1: "=AVERAGE(B1:C1)",
    Sum_1: "=SUM(B1:C1)"
  }
  //...
];

// Pass data to HyperFormula
hf.setCellContents(
  {
    row: 0,
    col: 0,
    sheet: sheetId
  },
  sourceModelData.map(modelToArray) // the conversion happens here
);

// Get calculated values back from HyperFormula and convert it back to models
const resultModelData = hf.getSheetValues(sheetId).map(arrayToModel);
console.log(resultModelData);

Handsontable

You can also check out the Handsontable grid. It supports formula calculation of out the box by using HyperFormula internally.

[IcepiQ]

Hi sequba,

Yes, this is what I was looking for, I was already looking at the best way to convert the model to array and back but this made it quite easy.

Thank you for the response.

I did have a look at hansontable, unfortunately I need to use kendo grid to look and feel like the rest of the application.

Regards