How exactly does AlwaysSparse work?

[MartinDawson]

Is their an example on what these configs actually do?

Example with AlwaysSparse on the #develop branch:

  const hyperformula = HyperFormula.buildFromArray([["1", "2", "=A1"]], {
    licenseKey: hyperformulaLicenseKey,
    chooseAddressMappingPolicy: new AlwaysSparse(),
  });

  const changes = hyperformula.setCellContents({ col: 3, row: 0, sheet: 0 }, [
    ["=B1"],
  ]);
  const changesd = hyperformula.setCellContents(
    { col: 33, row: 30, sheet: 0 },
    [["=B1"]],
  );

  console.log(hyperformula.getAllSheetsValues())

output:

[
    [
        1,
        2,
        1,
        2
    ],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        2
    ]
]

This is the same output as AlwaysDense:

 const hyperformula = HyperFormula.buildFromArray([["1", "2", "=A1"]], {
    licenseKey: hyperformulaLicenseKey,
    chooseAddressMappingPolicy: new AlwaysDense(),
  });

performance.md says:

• AlwaysSparse – uses sparse mapping for each sheet. This approach
  is useful when in your spreadsheet/dataset there are relatively few
  cells filled, but located very far from each other.

I would have expected that AlwaysSparse uses sparse arrays here instead of nulls and empty arrays to save on memory output instead of filling it with useless nulls. Is this not the case?

I'm just trying to make my sheet performant as possible due to the possibility that a user will had a cell in row 10000, column 10000 for example which I presumed the sparse strategy would help with.

Thanks

[izulin]

AlwaysSparse is a collection of sparse maps. You could see it via:
console.log(hyperformula.dependencyGraph.addressMapping)
However what you see in your output is an artefact of the exporter, and should be fixed. Could you post this as an issue?

[wojciechczerniak]

@izulin Which part is wrong? Output cannot be sparse, as we expect the correct array in array indexing. Rows have to be empty arrays and if value is deeper in row array we have fill it with undefined or null. More or less:

Thing is AlwaysSparse and AlwaysDense is for the internal memory mapping and optimization.

[MartinDawson]

So a user in my spreadsheet could enter a value in row 100000, column 100000.

And if I do getSheetValues() it will return 99999 undefined/null values.

So either I cannot use getSheetValues() and it's equivalents due to performance then or I will need to restrict the user to not be able to create a large blank spreadsheet (like you can in Excel/GSheets) and insert a value in a cell really far apart.

I would have expected that the output would return only the relevant values in AlwaysSparse mode for performance and not just for internal mapping only.

Thanks

[izulin]

@izulin Which part is wrong? Output cannot be sparse, as we expect the correct array in array indexing. Rows have to be empty arrays and if value is deeper in row array we have fill it with undefined or null.

Right

[wojciechczerniak]

Well, getSheetValue() is dumping whole sheet. Methods are grouped to work with Cell, Range and Sheet context. For other formats it should be possible to implement something that suit your needs.

function getSheetDiff(sheet: number): SheetDiff {
  const { width, height } = hf.getSheetDimensions(sheet)
  const output = [ ]

  for (let row = 0; row < height; row++) {
    for (let column = 0; column < width; column ++) {
      const value = hf.getCellValue({ sheet, row, column })
      if (isDefined(value)) {
        output.push({ sheet, row, col, value })
      }
    }
  }

  return output
}

console.log(getSheetDiff(0))
// [ { sheet: 0, col: 10000, row: 10000, value: 'some text' } ]

type SheetDiff = {
  sheet: number,
  row: number,
  column: number,
  value: CellValue
}

We just need to have current limitation #622 in mind. WDYT?

[MartinDawson]

Thanks for the example, that looks to be what is needed. I'll try and use that instead of getAllSheetValues().

[MartinDawson]

Figured it out as per the help above. I used the new AlwaysSparse() config as well for this.

Here's how I acheived it:

    const sheet = getSheetId()
    // https://github.com/handsontable/hyperformula/discussions/761
    const addressMapping = hyperformula.dependencyGraph.addressMapping.mapping;

    const serializedValues = [];

    const currentSheetMapping = addressMapping.get(sheet);

    currentSheetMapping.mapping.forEach((col, colKey) => {
      col.forEach((_, rowKey) => {
        const cellAddress = { sheet, col: colKey, row: rowKey };
        const serializedValue = hyperformula.getCellSerialized(cellAddress);

        if (!isNil(serializedValue) && serializedValue !== "") {
          if (!serializedValues[rowKey]) {
            serializedValues[rowKey] = [];
          }

          serializedValues[rowKey][colKey] = serializedValue;
        }
      });
    });

This gives me an efficient sparse array like so:

[
    [
        "Normal Debt",
        empty,
        "Convertible Debt",
        empty,
        "Preferred Stock",
        empty,
        "Employee Options",
        empty,
        "Other"
    ],,
    [
        "Pre-tax Cost of Debt",
        empty,
        "Book Value of Convertible Debt",
        empty,
        "Number of Preferred Shares",
        empty,
        "Employee Options Outstanding",
        empty,
        "Net Operating Loss"
    ],
]

Instead of an array filled with null/undefined values and is much faster to loop through.

I really think this would be a super useful function, i.e something like getSerializedValuesAsAMap() could return this sparse array or a map (Probably a object map is better).

There is also an issue with this in that setSheetContent() does not work with sparse arrays. It throws an error.

Which means I have to loop through my array and use setCellContents() but then that will mess up the internal history for copy/paste. So I think setSheetContent() should also work with sparse arrays as it's way more efficient for me than dense arrays.