Percentage type not working when cell is a formula

[MartinDawson]

Hi,

I believe this is a bug as it's currently not possible to format percentages properly if it's a result of a formula. It doesn't work with type percent or with mask.

How to replicate:

      const instance = jspreadsheet(spreadsheetRef.current, {
          data: [
             ["0.0305"],
             ["=A1"]
          ],
          cells: {
            A1: {
              type: "percent",
            },
            B1: {
              type: "percent",
            },
          },
      });

The problem is that B1 is not formatted as a percentage, it's still 0.0305.

Result:

This example works: https://jspreadsheet.com/v7/cases/payment-calculator

Because the percentage is hard coded and not a formula.

[hodeware]

I will take a look at that soon.

[MartinDawson]

Thank you!

It also seems that you can't edit the formulas for a { type: percent }, i.e it's just a dropdown to increment/decrement the number. However it works if { type: number }. So I guess I need to make it a type number and have a percentage mask.

Hopefully this will work as well if the above issue is fixed.

[hodeware]

I have created an example to show how it works. Please be aware of the types of raw data. Ideally, make that as float/numbers https://jsfiddle.net/spreadsheet/onpce9dz/3/

[MartinDawson]

@hodeware Your jsfiddle is not the bug I'm describing. This should be re-opened I think

You are just multiplying the percentage with a formula. I am specifically trying to format the resulting formula to be a percent.

See my jsfiddle: https://jsfiddle.net/martindawson/fabq97xy/1/

The result should be 3.05% for B1 and 50% for B2.

However it's showing 0%. That is the problem, there is no possible way to format the percentage if it's the result of a formula.

[hodeware]

Sorry, in this case, it is better to use type:'text' with mask since the percent column has already a 'format' pre-defined.

[MartinDawson]

Okay however I still can't get the mask to work correctly for percent, could you please take a look at this jsfiddle?https://jsfiddle.net/martindawson/fabq97xy/8/

I've tried a ton of combinations and I can't get the correct mask.

I want to format B1 to be 3.05% (the same as A1).

[hodeware]

The raw data of the percent type column is the number divided by 100. If you wish to convert from a type percent to a number, you can use something like that. https://jsfiddle.net/spreadsheet/ucejt7gn/

If that does not fit what you looking for, you might need to use type: number or type: text (which is the generic version of general in excel), and go from there.

[MartinDawson]

@hodeware I don't want to modify the actual value formula like that with =CONCATENATE(A1*100, '%') because then it's not in decimal format anymore so other cells that rely on it will be messed up. I only want to change what the presentational value that the user is viewing, I.e the mask of it.

If that does not fit what you looking for, you might need to use type: number or type: text (which is the generic version of general in excel), and go from there.

Yes that's what I've tried.

Here's what excel mask of 0.00% does:

It formats 0.0305 to 3.05%, regardless of whether it's a formula or not.

Whereas with jspreadsheet this doesn't work and #.#% does not work.

There's currently no possible way to format a number as a percentage in decimal format in jspreadsheet if it's the result of a formula.

Please could this be re-opened. It's quite important I think.

The only thing I can think of to get around it is to loop through all the cells and change the innerHTML of the percentage cells manually which is slow.

[hodeware]

Sure, I will re-open.... that is a feature request for jSuites.mask, which I can include in the next release

[MartinDawson]

Thanks @hodeware.

For anyone who is trying to get around this for now, here's what I have done:

  const formatIfCellIsPercent = (spreadsheet, cellKey) => {
    const type = cells[cellKey].type;

    if (type === "percent") {
      const cell = spreadsheet.getCell(cellKey);
      const existingValue = spreadsheet[cellKey];
      const content = renderToString(
        <FormatRawNumberToPercent value={existingValue} />,
      );

      cell.innerHTML = content;
    }
  };

  const { default: jspreadsheet } = await import("jspreadsheet-pro");
  const helpers = jspreadsheet.helpers;

  const instance = jspreadsheet(initSpreadsheetRef.current, {
    onchange: (_, __, x, y) => {
      const cellKey = helpers.getColumnNameFromCoords(x, y);

      formatIfCellIsPercent(instance, cellKey);
    },
  }

  const allCells = instance.getCells();

  Object.keys(allCells).forEach((cellKey) => {
    formatIfCellIsPercent(instance, cellKey);
  });

And I set the type for jspreadsheet to by of type text for the percents and my default cells object to be type percent.

What this does is format all cells that are a percent type to look like percentages and not decimals. Also onChange because if a user edits the cell.

This way the underlying values stay as decimals.

It's only a temp fix because I imagine there are bugs lurking with this approach if you update any data and such. It's also slow looping through all cells.