FieldtypeHandsontable: Excel-like Inputfield

[bernhard]

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:

 

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:

 

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:

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:

Example with rowheaders:

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,
});

 

 

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:

// 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

 

 

[Juergen]

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

[bernhard]

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).

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.

[bernhard]

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) 
  

  hot.updateSettings({
      colHeaders: ['Foods'],
      minCols: 1,
      maxCols: 1,
  
      rowHeaders: false,
      minRows: 1,
      minSpareRows: 1,
  });

   

• auto-add-cols (like tagging)
  

  hot.updateSettings({
      colHeaders: false,
      minCols: 1,
      minSpareCols: 1,
  
      rowHeaders: ['Foods'],
      minRows: 1,
      maxRows: 1,
  });

   

[Juergen]

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

[dragan]

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!

[bernhard]

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

[Robin S]

@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"?

[bernhard]

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  

[dragan]

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? 

 

 

[bernhard]

thank you @dragan for reporting that. took me some time to get it working and in the end it was only one function call to addslashes ^^

please check the latest version of the inputfield: https://gitlab.com/baumrock/FieldtypeHandsontable/commit/57d5e117fa87470cfea784bb1f9a24e3895b8ac2

[dragan]

Thanks! Re-installed it and everything works like a charm. 

[pmichaelis]

Hey Bernahrd,

really good work. Thank you so much for your effort!

 

[zota]

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

[Macrura]

1 hour ago, zota said:

no chances of being easy queriable :/ 

fyi MySQL >= 5.7.8 supports searching inside JSON

[bernhard]

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

[Macrura]

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

[Macrura]

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:

output:

[bernhard]

version 3 adds support of file-based code snippets.

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

[bernhard]

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; }

[Juergen]

Hello @bernhard,

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

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

Best regards Jürgen

[bernhard]

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

 

[Juergen]

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

[bernhard]

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...

[bernhard]

edit: i changed the download url! @Juergen can you try it again please?