PrimeFaces: Paste Excel table to DataTable with InputNumber

Question

I have a p:dataTable that has multiple columns for month values like this:

<p:column width="80" headerText="Januar">
    <p:inputNumber value="#{mto.month01}" symbol="€" symbolPosition="s" decimalPlaces="0"
        disabled="#{((mto.year eq indexController.currentYear) and (indexController.currentMonth gt 1)) or (mto.year lt indexController.currentYear) or (indexController.isComponentDisabled('fieldPMForecastOutflow', 'PM'))}">
        <p:ajax process="@this" partialSubmit="true"
            update=":contentform:dt-PMDetail contentform:blockButtonDeletePMYear"
            listener="#{indexController.updateWorkingCopyProjectManagementFinancesDTO}" />
    </p:inputNumber>
</p:column>

My users want to be able to paste excel cells into this DataTable.

I tried different javascript snippets, e. g. this and adapted it like this:

$('input').bind('paste', function (e) {
    var $start = $(this);
    var source

    //check for access to clipboard from window or event
    if (window.clipboardData !== undefined) {
        source = window.clipboardData
    } else {
        source = e.originalEvent.clipboardData;
    }
    var data = source.getData("Text");
    if (data.length > 0) {
        if (data.indexOf("\t") > -1) {
            var columns = data.split("\n");
            $.each(columns, function () {
                var values = this.split("\t");
                $.each(values, function () {
                    $start.val(this);
                    if ($start.next('input')) {
                        $start = $start.next('input');
                        $start.val(this);
                    }
                    if ($start.closest('td').next('td').find('input')) {
                        $start = $start.closest('td').next('td').find('input');
                    }
                    else
                    {
                        return false;  
                    }
                });
                $start = $start.closest('td').parent().next('tr').children('td:first').find('input');
            });
            e.preventDefault();
        }
    }
});

When I paste data, the currency symbol is lost, no validation happens (I can paste letters) and as soon as I hover over a field with the mouse, the value is reset to its original value.

What I am looking for is a solution that 'tabs' to the next field and inputs the value like I would via the keyboard (e. g. numbers only). If too many columns are pasted, they can be omitted. A new line in the excel data should result in a new line in the DataTable (if I paste a 3x3 table in column 4 of line 1, columns 4 to 6 in rows 1 to 3 should be filled).

score 2 · Accepted Answer · answered Apr 11 '23 at 08:17

2

Instead of directly writing the value to the component's input field, use the widget API and use its setValue function to set the value.

From the documentation:

Sets the value of this input number widget to the given value. Makes sure that the number is formatted correctly.

answered Apr 11 '23 at 08:17

Jasper de Vries

19,370
6
64
102

Thanks for your answer. I had the idea of taking this route but didn't quite find the way. AFAIK I have to use a widgetVar within the `PF(...)` function? Until now I navigate between the elements using the html path and didn't find a way how to address it with PF? – Hypernia Apr 11 '23 at 08:38
1

I agree the widget setValue() for InputNumber is exactly what you need. Just need to figure out how to get the widget for the cell you are editing. It should be possible though. – Melloware Apr 11 '23 at 14:37
In the browsers console the setValue function works fine for a given widgetVar. I tried adding the following tags to my : `id="dtMTO#{mto.year}01" widgetVar="dtMTO#{mto.year}01"` to be able to use the element.id as my widgetVar in PF() function but I guess my rowKey is not available when building the id (id is mto01, widgetVar is mto202301 for first row). Any help is appreciated to get my PF widgetVar from a given js element? – Hypernia Apr 12 '23 at 08:21
1

That's a different problem. Please see if you can find the answer to that question on SO. If you cannot, please ask a new question and tag it with PrimeFaces. See for example https://stackoverflow.com/questions/15660969/set-id-of-a-component-within-jsf-datatable-to-value-from-current-item-in-the-arr – Jasper de Vries Apr 12 '23 at 08:37
I built the `widgetVar` based upon the column and the `rowIndexVar`. I can then iterate over my input, read the column's ID and the generated row number which is equivalent to the index to use PrimeFace's `setValue()`. What still doesn't work: I am partial processing these values within the ajax event and showing a sum. Is there any way to trigger the ajax event upon `setValue()` or to process the whole table from within my javascript? – Hypernia Apr 12 '23 at 13:13
1

https://stackoverflow.com/questions/16588327/how-to-invoke-a-jsf-managed-bean-on-a-html-dom-event-using-native-javascript – Jasper de Vries Apr 12 '23 at 13:28
Found it: I used a `` that updates and processes everything needed and is called at the end of my javascript. – Hypernia Apr 12 '23 at 13:31

PrimeFaces: Paste Excel table to DataTable with InputNumber

1 Answers1