bernhard

FieldtypeHandsontable: Excel-like Inputfield

Recommended Posts

bernhard    1,322

Hi everyone,

I'm proud to share my first fieldtype module and I think it's a quite handy one :) It helps you to create all kinds of table/matrix inputs very quickly and easily. You have loads of options for customizing your field via plain javascript. See the handsontable docs for that

 

Please consider this module ALPHA until i got some more time to test it. Any help would be highly appreciated :)

Numbers are for example always tricky. Different locale settings, different types, rounding errors and so on...

 

Download:

https://gitlab.com/baumrock/FieldtypeHandsontable

 

Result:

594fc7a584cbf_2017-06-2516_21_20-EditPage_Homehandsontable_dev.png.f6a082a1d109b32779d8b4541bdb219c.png

 

Installation/Configuration:

Just install the Fieldtype, add a field to your template and set the handsontable options in the fields details. If you have InputfieldAceExtended installed you will also have code highlighting for your code:

594fc8b053b88_2017-06-2516_27_56-EditField_handsonhandsontable_dev.png.7124d5f4b079fc85ebc472ef1576932a.png

 

Get data:

If you retrieve the data from the API with outputformatting ON you have some helper methods available:

  • getData() + getRows()
    get all data of the field
     
  • getRow($row)
    get one special row, eg getRow(1) or getRow("2017")
     
  • getCols()
    get all data but by columns not by rows
     
  • getCol($col)
    get one special column, eg getCol(1) or getCol("mycolumnheader")

You can also access rowHeaders and colHeaders directly (see examples)

 

Simple Example:

Caution: the examples below are outdated! see this post: 

Spoiler

Result:

594fc7a584cbf_2017-06-2516_21_20-EditPage_Homehandsontable_dev.png.f6a082a1d109b32779d8b4541bdb219c.png

Field config:


var colheaders = ['Jan', 'Feb', 'März', 'Apr', 'Mai', 'Juni', 'Juli', 'Aug', 'Sep', 'Okt', 'Nov', 'Dez'];
hot.updateSettings({
    colHeaders: colheaders,
    minCols: colheaders.length,
    maxCols: colheaders.length,

    rowHeaders: false,
    minRows: 1,
    maxRows: 1,
});

Get data:

594fc836e13ad_2017-06-2516_26_32-EditPage_Homehandsontable_dev.png.d1e6e20dadc53c447b7067f6eef31d1d.png

Example with rowheaders:

594fcb13c5662_2017-06-2516_38_50-EditPage_Homehandsontable_dev.png.79f055f420dff24ed5d3d74f3985a6fe.png


var colheaders = ['Jan', 'Feb', 'März', 'Apr', 'Mai', 'Juni', 'Juli', 'Aug', 'Sep', 'Okt', 'Nov', 'Dez'];
var rowheaders = [2017, 2018, 2019];
hot.updateSettings({
    colHeaders: colheaders,
    minCols: colheaders.length,
    maxCols: colheaders.length,

    rowHeaders: rowheaders,
    minRows: rowheaders.length,
    maxRows: rowheaders.length,
});

594fcb9b94ffb_2017-06-2516_41_14-Console.png.b8132e53941863befa5d4fb35671c9e6.png 594fcc3c67ae8_2017-06-2516_43_05-EditPage_Homehandsontable_dev.png.98efcea57dc22a2cc1f2b5b1a06014a4.png

 

Use data from the server-side:

You can do all kinds of stuff with the table when communicating with your backend via the pw js() function:

594fcd26e8f36_2017-06-2516_47_39-EditPage_Homehandsontable_dev.png.5897691f58eeb11fb8fe2e2eeb225df0.png


// inside /site/ready.php
if($page->template == 'admin') {
    $paths = $pages->find('include=all, limit=10')->each('path');
    $config->js('demodata', $paths);
}

var colheaders = ['one', 'two', 'three', 'four'];
var rowheaders = ProcessWire.config.demodata;
hot.updateSettings({
    rowHeaders: rowheaders,
    minRows: rowheaders.length,
    maxRows: rowheaders.length,

    colHeaders: colheaders,
    minCols: colheaders.length,
    maxCols: colheaders.length,
    rowHeaderWidth: 250,
});

You get the idea ;)

 

 

  • Like 23

Share this post


Link to post
Share on other sites
Juergen    272

Thanks for the new fieldtype @bernhard

Can you give me an example of a particular use case where you have used this fieldtype. I know there are some examples in the post above, but I guess they are only for demonstration purposes. Do you have an example of a real life usecase so I can get an idea about how it can be used?

Best regards

Share this post


Link to post
Share on other sites
bernhard    1,322

hi @Juergen

of course i can, there are a lot ;)

In general this field is useful whenever you need array- or matrix-like inputs. I use the field for my custom CRM to input the controlling values for my clients revenues.

        |  jan     |  feb   |  mar  ...
-------------------------------------
goal 1  |  10.000  | 15.000 | 20.000
goal 2  | ...
goal 3

It replaces lots of repeaters and not so userfriendly inputs.

I didn't mention that you also have lots of possibilities of live-validating your data (you could for example make all cells with not properly formatted dates have a red background).

5950ce982ff16_2017-06-2611_03_09-EinstellungenProcessWire.thumb.png.37fbbd9092e143400e71d640d8e0efef.png

You see the inputfields at the bottom of the screenshot? It was too complicated to build a table-like input for that values (month values). Pagetable is too bloated for that. Maybe the matrix inputfield would have been an option, but still i think the handsontable has a huge potential. So i ended up having a simple textfield input and telling my client to devide all values by commas... not the best way!!

So whenever you have thought of a field telling your clients "one on each line" or "devided by comma..." this field could help you out.

  • Like 3

Share this post


Link to post
Share on other sites
bernhard    1,322

Some more nice features

  • copy&paste (eg from excel)
    that way the field could be used as an import-interface with live preview (much more userfriendly than CSV in my opinion)
     
  • auto-add-rows (coulb be used as something like a repeater-replacement) 
    sparerows.gif.79cdcd21c01a77628cc36c3bc1213702.gif
    hot.updateSettings({
        colHeaders: ['Foods'],
        minCols: 1,
        maxCols: 1,
    
        rowHeaders: false,
        minRows: 1,
        minSpareRows: 1,
    });

     

  • auto-add-cols (like tagging)
    sparecols.gif.b2d21ed028d4440a80b61b5c121ff7bb.gif
    hot.updateSettings({
        colHeaders: false,
        minCols: 1,
        minSpareCols: 1,
    
        rowHeaders: ['Foods'],
        minRows: 1,
        maxRows: 1,
    });

     

  • Like 7

Share this post


Link to post
Share on other sites
Juergen    272

Thanks for the detailed response @bernhard

I havent build a CRM with PW til now, but maybe a pricing table with different prices for different product sizes could be a useful application in my case :)

Best regards

  • Like 1

Share this post


Link to post
Share on other sites
dragan    213

That looks awesome. I finally found some time to play around with it, and I'm quite impressed.

I can already see some concrete usage scenarios - especially what you already mentioned re: when it's too tedious to go the "export CSV from Excel, import CSV to PW" route.

I am somehow stuck though when trying to add i18n options to the datepicker. I took your example (Jan-Dec), added a few options, and everything worked fine in the back- and frontend, but now I don't see that handsontable anymore in "page edit" mode. I get a message saying "Please save this page to be able to change this content". I did that repeatedly, but it's always the same. Funnily enough, I don't see any errors in the PW error log, and the frontend works just fine (granted, it's only a print_r() so far, but at least it still fetches something from PW and spits it out correctly).

Did you run into similar situations? Any idea what I did wrong? This is my JSON:

var colheaders = ['Jan', 'Feb', 'März', 'Apr', 'Mai', 'Juni', 'Juli', 'Aug', 'Sep', 'Okt', 'Nov', 'Dez', 'Datum'];
hot.updateSettings({
    colHeaders: colheaders,
    minCols: colheaders.length,
    maxCols: colheaders.length,

    rowHeaders: false,
    minRows: 1,
    copyPaste: true,
    autoWrapRowBoolean: true,
    autoWrapColBoolean: true,
    minSpareRows: 1,
    columns: [
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {
            type: 'date',
            dateFormat: 'DD.MM.YYYY',
            correctFormat: true,
            defaultDate: '01.07.2017',
            i18n: {
               previousMonth : 'Voriger Monat',
                nextMonth     : 'Nächster Monat',
                months        : ['Januar','Februar','März','April','Mai','Juni','Juli','August','September','Oktober','November','Dezember'],
                weekdays      : ['Sonntag','Montag','Dienstag','Mittwoch','Donnerstag','Freitag','Samstag'],
                weekdaysShort : ['So','Mo','Di','Mi','Do','Fr','Sa']
            }
            datePickerConfig: {
              firstDay: 1,
              showWeekNumber: true,
              numberOfMonths: 3,
              disableDayFn: function(date) {
                return date.getDay() === 0 || date.getDay() === 6;
              }
            }
        }
  ]
});

Edit: D'oh - I missed a comma after the i18n block... OK so the table is back, but although Saturdays + Sundays are deactivated, and Monday is set as the 1st day of the week, the days and months still display in english. Guess I have to dig into the original docs a bit further...

Thanks for this great addition to PW!

  • Like 2

Share this post


Link to post
Share on other sites
bernhard    1,322

your example works like a charm here, i just added a comma after the i18n object  ;)

5960be0118a83_2017-07-0812_58_43-EditPage_Homehandsontable_dev.thumb.png.3f88a7f1dcdf13bb58d2c5711ec444a9.png

Share this post


Link to post
Share on other sites
Robin S    2,528

@bernhard, this looks like a really useful module, thanks for making it.

When using FieldtypeHandsontable values as part of a $pages->find() selector, is it possible to search for values in particular column or row, or in a specific column/row combination? Like in the "row headers" example in the first post, is it possible to match pages where "Juni 2018 = 60"?

  • Like 1

Share this post


Link to post
Share on other sites
bernhard    1,322

hi robin, thanks for your words :)

i've not yet found the time to make the field queryable via pw selectors. i'm not sure how that should (could) be implemented in the best way. as i've never built a queryable inputfield i would be happy to hear some thoughts about it from someone more experienced.

... or a PR where i can learn from :P 

  • Like 1

Share this post


Link to post
Share on other sites
dragan    213

Sorry if I ruin your Saturday... :-)

I ran into a problem when I copy and pasted something from MS-Excel, where some fields contain line breaks.

The frontend displays everything correctly. But in page-edit, I get a JS error, and the same msg again: "Please save this page to be able to change this content"

When I inspect the offending line, it's the first occurance of a line break in var field = JSON.parse

Do you have any idea what could cause this? 

 

 

pls-save-this-page.png

pw-backend-hot-js-error.png

  • Like 1

Share this post


Link to post
Share on other sites
zota    7

Hi, thanks for the nice module, I like HoT.
About this:

On 08/07/2017 at 1:31 PM, bernhard said:

i've not yet found the time to make the field queryable via pw selectors. i'm not sure how that should (could) be implemented in the best way. as i've never built a queryable inputfield i would be happy to hear some thoughts about it from someone more experienced.

HoT fields are stored as a json string, so seems that there are no chances of being easy queriable :/ 
I would like to see some workarounds or some clues on that subject.
Thanks again

Share this post


Link to post
Share on other sites
Macrura    2,142
1 hour ago, zota said:

no chances of being easy queriable :/ 

fyi MySQL >= 5.7.8 supports searching inside JSON

  • Like 1

Share this post


Link to post
Share on other sites
bernhard    1,322

Thanks! Looks very interesting. Though I have to say that I have no time to implement that in the near future.

Share this post


Link to post
Share on other sites
Macrura    2,142

looking forward to using this module... thanks for building it!

  • Like 1

Share this post


Link to post
Share on other sites
Macrura    2,142

Finally got a chance to try it and this module is great, and will solve innumerable content management conundrums.. Here's my first use, on a quotes rotator section:

Field Config:

var colheaders = ['Author', 'Publication', 'Quote'];
hot.updateSettings({
    colHeaders: colheaders,
    minCols: colheaders.length,
    maxCols: colheaders.length,

    rowHeaders: false,
    minRows: 1,
    minSpareRows: 1,
    maxRows: 5,
    width: 900,
    contextMenu: true,
    autoWrapRow: true,
    autoWrapCol: true,
    colWidths: [200,200,500],
});

 

edit screen:

lk_quotes_section.thumb.jpg.4a6e64693b9b4d8546990716f5ee3fb5.jpg

output:

lk_home_quotes.jpg.e7c32216e6e3a76b933ac5b7af1efdc3.jpg

  • Like 6

Share this post


Link to post
Share on other sites
bernhard    1,322

version 3 adds support of file-based code snippets.

just put a file with the field's name inside the /fields folder

  • Like 1

Share this post


Link to post
Share on other sites
bernhard    1,322

just pushed another update!

CAUTION: THIS HAS A BREAKING CHANGE since I changed how the settings get modified.

the reason why i did this is that some settings can only be set BEFORE initialisation of the table. the customBorders is an example.

you now have two events that you can hook into:

  • beforeInit.rht
  • afterInit.rht

If you want to modify the settings before initialisation you can do this:

// make custom borders
$(document).on('beforeInit.rht', '.handsontable', function(e, settings) {
  $.extend(settings, {
    customBorders: [{
      range: { from: { row: 0, col: 2 }, to: { row: 10, col: 2 } },
      left: { width: 4, color: 'red' }}],
  });
});

after initialisation you can do this (similar to how it was before):

$(document).on('afterInit.rht', '.handsontable', function(e, hot) {
  var colheaders = ['one', 'two', 'three'];
  hot.updateSettings({
    colHeaders: colheaders,
    minCols: colheaders.length,
    maxCols: colheaders.length,
  });
});

now you can also add CSS files to your table by naming it like your field and saving it inside the /fields folder:

#wrap_Inputfield_archimport .handsontable td:nth-child(2),
#wrap_Inputfield_archimport .handsontable th:nth-child(2) { border-right: 4px solid #6c6c6c; }

#importbutton { margin-top: 10px; }

59b95eeb8a520_2017-09-1318_37_48-EditPage_mailtestneu(Feedbackarchitecture)360.hrdiamonds_com.png.c86afcd1582672d7e1b911afc72da4f3.png

  • Like 1

Share this post


Link to post
Share on other sites
Juergen    272

Hello @bernhard,

I have tried to update Handson tables but I always get this error:

Screenshot_4.thumb.jpg.9ca29423887a2c1fc2c7ed575317729e.jpg

It also happens after uninstall and deletion of the older version and a fresh install.

Best regards Jürgen

Share this post


Link to post
Share on other sites
bernhard    1,322

hi jürgen, why did you do the update?

is this post somehow related to your problem or sounds similar? 

can you try to make a fresh install and see if there are any errors?

thanks

 

Share this post


Link to post
Share on other sites
Juergen    272

Thanks @bernhard

but the problem is only there by installing or upgrading Handson tables. I did a lot of other updates (PW, some field types and modules,...) - without any problems.

21 minutes ago, bernhard said:

hi jürgen, why did you do the update?

I guess you mean how, because I did it to get the latest version? I made it with the default PW update - like all others.

After the update failed, I deinstalled and deleted the module. Afterwards a fresh install of the module was done from the PW module directory. There are no other error messages than the one I have posted above. I have Tracy running beside - but no error message.

Best regards

Share this post


Link to post
Share on other sites
bernhard    1,322

i guess that's an issue with the modules directory. can you please download the latest ZIP and install it manually?

i don't know if the modules directory also works with gitlab... i just tried that by submitting my module and it seems it does NOT work.

be advised that the update has a breaking change and you need to rewrite your javascript field settings after the update. the module is still alpha and more of those changes are still likely to come...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Recently Browsing   0 members

    No registered users viewing this page.