Best way to read a file and import it into HF?

[mmahalwy]

Description

What's the best way to read a file and import it into HF?

Links

Should be here: https://handsontable.github.io/hyperformula/guide/advanced-usage.html#demo

Type of issue

• Missing documentation
• Error in the documentation
• Request for a demo
• Request for a tutorial, guide etc.
• Other issues, suggestions or ideas

[AMBudnik]

Hi @mmahalwy

HyperFormula works on an array of arrays.

ref: https://handsontable.github.io/hyperformula/guide/basic-usage.html

So if you would like to pass JSON, you need to parse it and build an array out of the data first. Using variables is permitted, so once you import any function you can use it on a regular basis (replace the data in the screenshot above).

Ps. your data can be of different types https://handsontable.github.io/hyperformula/guide/types-of-values.html#getting-cell-type

If I got your question wrong or missed anything, feel free to let me know.

[mmahalwy]

@AMBudnik makes sense. I was looking forward to something like Hyperformula.buildFromFile('./file.xlsx'). Instead of importing the file on my own, parsing and then building it into HF.

[AMBudnik]

We do not have this kind of functionality yet. But I also think that it would be a useful feature.
I will move this idea to discussions so other developers can also share their ideas and concepts.

[lopezjurip]

This is an essential feature this module is missing (or at least an example or documentation about how to do it).

Here is my approach (TypeScript):

import { ConfigParams, HyperFormula } from "hyperformula";
import XLSX from "xlsx";

// Load file using SheetJS (https://sheetjs.com/)
const route = path.join(process.cwd(), "files", "foo.xlsx");
const workbook = XLSX.readFile(route);

// Excel compatibility
const config: Partial<ConfigParams> = {
  licenseKey: "gpl-v3",
  evaluateNullToZero: true,
  leapYear1900: true,
};

const hf = HyperFormula.buildEmpty(config);

// Excel compatibility
hf.addNamedExpression("FALSE", "=FALSE()");
hf.addNamedExpression("TRUE", "=TRUE()");

// Prevent calculations and evaluations while building
hf.suspendEvaluation();

// Build HyperFormula sheets
for (const name of workbook.SheetNames) {
  const sheet = workbook.Sheets[name];

  const hf_sheet = hf.addSheet(name);
  const hf_sheetId = hf.getSheetId(hf_sheet)!;
  
  for (const [key, cell] of Object.entries(sheet)) {
    if (key.startsWith("!")) continue; // skip meta data
    
    const address = hf.simpleCellAddressFromString(key, hf_sheetId);
    if (cell.f) {
      const value = `=${cell.f}`;
      hf.setCellContents(address, value);
    } else {
      const value = cell.v;
      hf.setCellContents(address, value);
    }
  }
}

// Let's say you want to use a sheet named 'main'
const hf_sheetId = hf.getSheetId("main")!;
const hf_addressInput = hf.simpleCellAddressFromString("A1", hf_sheetId)!;
const hf_addressOutput = hf.simpleCellAddressFromString("A2", hf_sheetId)!;

// Modify input value
hf.setCellContents(hf_addressInput, "42");

// Turn on evaluations
hf.resumeEvaluation();

// Get computed result
const result = hf.getCellValue(hf_addressOutput);
console.log(result)

Regards

[MartinDawson]

It doesn't make sense to have this in HyperFormula. HyperFormula is for cell calculations, not parsing files.

Sheetjs and other libraries already does parsing of .xlsx files. What you should do is use something like utils.sheet_to_json to convert the xlsx to JSON and then convert it into an array of arrays.

SheetJS/sheetjs#613

You then call hf.buildFromArray().

Should this be in HF/Handonstable docs? Probably because a lot of users will want to know the answer to this question.