Good morning everyone. I would like to integrate in my table the sum function as on Excel, i.e. write “=” in one cell and then click on two others to get the sum. In another topic I saw that the beforeOnCellMouseDown and afterOnCellMouseUp functions were needed. I tried to integrate them but after writing “=”, upon clicking in the other cell, “#ERROR!” is displayed. I use the latest versions of Handsontable, Handsontable/Angular and Hyperformula.
Any solution?
Adding sum formula
I’m not sure if I understand your requirements correctly. To be able to perform SUM operations you just need to setup a HyperFormula instance: https://handsontable.com/docs/javascript-data-grid/formula-calculation/ Then you can perform calculations directly within the cells.
If you have more specific requirements, please describe them and share a code demo with your current implementation.
this is the topic I was talking about. I need to do the same thing.
In the demo on https://jsfiddle.net/8otg3qk7/ all works great, but when I try to replicate it in my application, it gives me that error that I reported above.
Thank you for the explanation. In this case I will need to see your implementation to check the problem.
Here’s the code:
HTML:
<hot-table [hotId]=“id” [settings]=“settings”>
TS (the original without beforeOnCellMouseDown and afterOnCellMouseUp functions):
import Handsontable from 'handsontable/base';
import { HyperFormula } from 'hyperformula';
import { HotTableComponent, HotTableRegisterer } from '@handsontable/angular';
@ViewChild(HotTableComponent) hotTableComponent!: HotTableComponent;
hyperformulaInstance = HyperFormula.buildEmpty({
licenseKey: 'internal-use-in-handsontable',
});
id = 'my-custom-id';
settings: any = {
columns: [
{ type: 'numeric',
data: 'Spesa campionata',
editor: false
},
{
type: 'text',
data: 'Tipologia',
editor: false
},
{
type: 'text',
data: 'Numero Documento',
editor: false
},
{
type: 'date',
data: 'Data Documento',
dateFormat: 'DD/MM/YYYY',
correctFormat: true,
className: 'htRight',
editor: false
},
{
type: 'text',
data: 'Denominazione Fornitore',
editor: false
},
{
type: 'text',
data: 'Voce di spesa',
editor: false,
},
{
type: 'numeric',
data: 'Importo rendicontato su voce (€)',
numericFormat: {
pattern: '0.00,00',
// culture: 'it-IT',
},
editor: false
},
{
type: 'numeric',
data: 'Importo validato su voce (€)',
numericFormat: {
pattern: '0.00,00',
// culture: 'it-IT',
},
},
{
type: 'dropdown',
data: 'Stato Validazione',
source: this.dropdownOptions
},
{
type: 'text',
data: 'RICHIESTA INTEGRAZIONI SU SINGOLO DOCUMENTO DI SPESA'
},
{
type: 'text',
data: 'NOTE VALIDAZIONE'
},
{
type: 'text',
data: 'row_index'
}
],
data: [],
nestedHeaders: [
['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'L', 'M','N'],
['Spesa campionata','Tipologia', 'Numero Documento', 'Data Documento', 'Denominazione Fornitore', 'Voce di spesa', 'Importo rendicontato su voce (€)', 'Importo validato su voce (€)', 'Stato Validazione', 'RICHIESTA INTEGRAZIONI SU SINGOLO DOCUMENTO DI SPESA', 'NOTE VALIDAZIONE', 'row_index'],
],
filters: true,
stretchH: 'none', // Disable auto stretching of columns to fit container
width: '100%',
height: 'auto',
colWidths:[160,130,160,150,200,450,250,220,200,450,300,200],
contextMenu: ['hidden_columns_show', 'hidden_columns_hide'],
hiddenColumns:{
columns:[11],
indicators: true,
},
dropdownMenu: true,
rowHeaders: true,
autoWrapRow: true,
autoWrapCol: true,
licenseKey: 'non-commercial-and-evaluation',
formulas: {
engine: this.hyperformulaInstance
},
multiColumnSorting: true,
manualColumnResize: true,
manualRowResize: true,
afterChange: (changes: [any, any][], source: string) => {
changes.forEach(([row, prop]) => {
const hotInstance = this.hotRegisterer.getInstance(this.id);
if (prop === 'Importo rendicontato su voce (€)' || prop === 'Importo validato su voce (€)') {
const rendicontato = hotInstance.getDataAtRowProp(row, 'Importo rendicontato su voce (€)');
const validato = hotInstance.getDataAtRowProp(row, 'Importo validato su voce (€)');
console.log( 'prova', rendicontato, validato);
if (rendicontato == 0 || validato < rendicontato) {
hotInstance.setDataAtRowProp(row, 'Stato Validazione', 'non validato');
}if (rendicontato == validato) {
hotInstance.setDataAtRowProp(row, 'Stato Validazione', 'validato');
}
}
});
},
};I am not able to reproduce the issue with this code. Can you please share it in a chosen sandbox, like jsFiddle or StackBlitz?
https://jsfiddle.net/gianlu_ursino/cr3Lvbpn/27
I inserted date and example columns. I added the two function I need and I noticed that typing ‘=’ and then clicking on another cell copies the value in the empty cell, however it does not stay ‘in memory’ to then add a second value
Thank you for the example. I had to change the instance reference as you’re not using Angular in this example, but that’s the only change I did and the implementation seems to work just fine now: https://jsfiddle.net/6xztqejo/
in my application gives error on getActiveEditor, telling me that 'getActiveEditor doesn’t exist on type ‘GridSettings’
I will need to see the example where the issue is replicable, without I can’t do much. Please, prepare the example where the problem can be replicated.
I noticed that adrian.szymanski’s example and the package version introduced in gianluca.ursino’s example are different. One is handsontable@9 It can achieve his function, but the other is handsontable@14 Can be achieved
@1104171008
Handsontable v9 was the first version to introduce the formula engine we use today (in v14).
@gianluca.ursino
@adrian.szymanski is away for a conference. If you need any help with that issue, please let me know. I see that it’s been more than a week since the last update.
Hi, I’m trying to implement the Excel like feature which allows the user to click on a cell to get its index filled into the formula he is working on. For example the user types “=A1+” in a cell in edit mode and then clicks the cell A2, the content of the cell the user was typing in should become “=A1+A2” and be still in edit mode so the user can insert more content without having to go to edit mode by clicking the cell again.
This feature can be implemented in v9, but not in v14
This is v9 https://jsfiddle.net/8otg3qk7/
This is v14 https://jsfiddle.net/ni_qian/ykehwo7t/
Ah, I see. We call this feature point and click.
We have it on the to-do list for the Formulas plugin for Handsontable, but it is not a priority.
The main challenge here is the cell editor. Once you click another cell, it automatically closes. The only exception is when you have a cell that does not accept an invalid value through the cell validator, and an incorrect value is provided. But that will give you some hints on how to set it up.
Here is the option I mentioned - it is called allowInvalid. Reference: https://handsontable.com/docs/javascript-data-grid/api/options/#allowinvalid
And here is the code https://github.com/handsontable/handsontable/blob/5709dd181b28d07f2373ee074fe5e3449ac8ce50/handsontable/src/core.js#L1248
To help you more with the code and the custom functionality, we’d need to confirm your current support plan. So, if you require any more help regarding this subject, please message us at support@handsontable.com