Fields with wanted Excel formulas don't work anymore

I think perhaps adding some dedicated field formatters for select formula (an obvious formula would be HYPERLINK) may make sense to work around this issue.

HYPERLINK is a formula we use, so that would be useful. But it isn't the only formula, for example:

We have a form where people can submit nominations for an award. The form has several questions. Once the nomination period ends, we export the nominations in an Excel sheet. This sheet contains some blank fields for the judges to fill in according to their desires. Excel formulas calculate scores for each nomination, making it easy for judges to determine the award winners.

Another use could be a semi-filled order form with products and prices listed, along with a user-fillable quantity column. When something is put in the quantity, the price for the item and the order total get calculated.

It looks like we cannot reenable formulas in content for security reasons.

But we could allow predefined formulas on a per column basis I guess.

Could it help?

I would be even better to be able to add some new formula field to the list of fields.

Don't know if it's feasible though. Will have to have a look.

Worked on an MR that allows adding a new "Excel formula" type of field:

Any feedback will be greatly appreciated.

That looks like it could solve most of our problems! I will give it a try as soon as I can.

It would be helpful to allow formulas to include data from other columns. Referencing, as in #7 screenshot, will be tremendously useful. But when a column is excluded from the display, its data would need to be included directly in the formula (HYPERLINK, seems like a good example of this need). It seems that implementing this would necessitate complete TWIG templating.

The following issue will allow to hide columns on Excel side: ✨ Allow styling table cells using field formatters from core Needs work

We could also imagine using Drupal tokens in the formula field.

Trying the MR as a patch on 2.0.3 and 2.1.x-dev, and no combination of settings is making the Excel Formula field available for me on an existing view - am I missing something?

Just checked, works fine here. We need more details to be able to help.

Thank you for this MR ! Any hint on how to make this field plugin work with tokens ?

@damiengr could you be more explicit?

As you mentioned earlier, "We could also imagine using Drupal tokens in the formula field to reference other fields on the server side."
Any idea how to implement this ?
I've tried calling $this->getRenderTokens($item) in the getFormula() method but I'm not sure what $item should be ?

@damiengr I updated the MR to handle default render tokens.

Here is the updated getFormula method code:

  public function getFormula($row_index) {
    $tokens = $this->getRenderTokens([]);
    $tokens['{row}'] = $row_index;
    return strtr($this->options['formula'], $tokens);
  }

Tell me if it works for you.

Thank you but no, it doesn't work as getRenderTokens([]) always return the first row tokens.
You don't have the same behavior on your side ?

@damiengr you're totally right.

Updated MR, problem should be fixed now.

Tried another solution using a custom normalizer in MR 48.

Can now be tested in the new "sandbox" module Excel Serialization Extras → .