Jump to content
kongondo

Excel-like CRUD in PW using Handsontable

Recommended Posts

In some previous posts, I demonstrated a simple proof-of-concept CRUD “application” for PW using jTable. I really wanted to use DataTables since it is older, wiser and with a huge fan base. One thing I wanted was for the CRUD system to be as simple as possible and possibly be Excel-like (more below). With DataTables plugins, you can perform stuff like inline-editing and Auto-fill. The latter works just like in Excel and allows you to quickly populate cells (quick copy). But that’s as far as it goes. Google led me to other Table management systems. Some of the front runners include jqGrid and SlickGrid. SlickGrid is nice and using virtual rendering can display millions of rows with ease. Its support for displaying data delivered on demand (Ajax/server) is minimal; it requires you to download all your data before it starts manipulating it. Someone has created an Excel-like extension for it allowing copy-pasting between SlickGrid and Excel. Awesome! But, it still didn't rock my boat completely, especially the Ajax content issue. Then I stumbled upon Handsontable, a new kid on the block that describes itself as “a minimalistic Excel-like data grid editor for HTML, JavaScript & jQuery”. Though relatively new, it is as Excel-like as you can get. You can copy-paste from/to Excel [single to multiple cells and multiple to multiple cells], use the usual shortcuts (Ctrl-A, Ctrl-X, Ctrl-C, Ctrl-V, Ctrl-Y, Ctrl-Z - i.e., undo/redo, etc., and tab navigation, etc.), use Auto-fill (vertically and horizontally), freeze cells/columns, make cells/columns read only, Right-click context menus, insert/delete rows and columns, perform inline-editing, etc. Double awesome!

Handsontable (HoT) is minimalistic but comes with a rich API. However, most of the implementation is down to you (sounds familiar?). It will render the Table and provide you with Methods/Events/Options to manipulate it. You just have to provide the data as an Array or JSON defining your columns and containing your data. 

Soma has a DataTable module that is currently read only; you cannot edit the displayed data directly in the table. It is also in lazy development :). So, I started toying with the idea of using HoT as an Excel-like CRUD system (module) for PW. I wanted something that could quickly and easily do mass editing (Batcher-like). Imagine quickly creating basic pages/records by copy-pasting from Excel. Or quickly changing records by dragging (Auto-fill) or pasting new values over old ones. Well, the result can be seen in the screencast below.

I’d like to hear your thoughts whether this is something that I should consider developing into a module; something that could be used by Superusers. Depending on implementation, it could be used by other users as well. All HoT needs is data. What you do with that data is up to you (the developer) and your code! This can be very dangerous if not implemented correctly, of course! You could easily delete important records! However, HoT does not touch your database! Deletions, insertions, etc., will not alter your database unless you specifically write the PHP code to do that. All it does is send data to the server via a method your specify (post, get, Ajax, etc.), and even that is your implementation. You do not need to implement deletions or additions. You can use it for updating content only or as read only, but where’s the fun in that ;)?

In the screencast , apart from the table itself, all the other implementations are custom - the buttons, selects, auto-save (can be tricky? can DDOS your server?). The current implementation is not perfect; it is a demo after all. Certain stuff would need to be implemented differently  :D. I didn't want to spend too much time on something that would not be useful in the end. All CRUD operations are via PW API and/or vanilla PHP. Deleted pages are Trashed and not permanently deleted. If there is interest for a module of this kind, I would request all the help I can get to develop it :D please. I would put it up on Github of course. In the demo, this is running off Diogo’s ACP module.

Let me hear your thoughts please. Should a module like this see the light of day? Thanks O0


Wasn't sure in which forum to post this...

  • Like 18

Share this post


Link to post
Share on other sites

Hi kongondo,

This is awesome! I would love to see a filter for the table that would accept a standard PW Selector rather than just a drop down for Templates. That would allow you to combine multiple templates in a result set, or get as granular as you need in terms of searching large data sets. 

Great work!

  • Like 2

Share this post


Link to post
Share on other sites

Thanks bcartier.

Yes, I plan to have a filter (if this goes to production :)) The filter would be trivial to implement I think - if not, I'll just pinch it off Wanze's Batcher ;)

Share this post


Link to post
Share on other sites

Hello kongondo.

    I don't like it, I just LOVE it.

  • Like 1

Share this post


Link to post
Share on other sites

Love it :)

I was about to say that it wouldn't work for my idea in the other thread where I was talking about using something like this for repeater fields, but then I just saw the cell types on offer here and my jaw hit the flaw: http://handsontable.com/demo/renderers.html

What impresses me with the cell types is that the config data for select fields, checkboxes, datepickers and autocompletes etc can be read straight from your field's config data in PW so there is almost certainly a way of making that happen if I ever find the time.

So many possibilities here, and I love what you've done so far!

  • Like 2

Share this post


Link to post
Share on other sites

@All thanks!

@Pete,

Ye, Marcin Warpechowski has done some amazing work with HoT. 

Share this post


Link to post
Share on other sites

This is really awesome!

Never heard of this plugin before.

I'm thinking of integrating Handsontable into Batcher, there are some other feature requests pending I wanted to integrate sooner or later.

Kongondo, are you already developing a separate module?

What do you think?

Cheers :)

  • Like 4

Share this post


Link to post
Share on other sites

@Wanze,

Thanks. Ye, HoT it is really awesome :). Yes, I am already working on a module for Handsontable but it is at a very early stage. In fact, I am basing my code on yours :):P  (Batcher). Btw, your code has taught me a lot, thanks - well-written and commented and just makes me understand PW even better. 

Hmm, just wondering whether it makes sense to have two separate modules or one (Batcher) to rule them all! I have been planning to have my module handle both PW and external tables (data handling on demand basically), although that could add to the complexity. Like I said in the first post, I would need help in coding the module, especially settings/configuration to allow the user to enter options offered by HoT. It's also a good learning experience for me to better understand PW.

I suggest we mull this over a little and hear other thoughts as well (i.e. one module or two). ATM, I don't see any reason why these should not be combined but I would (if possible) want the ability to edit external tables. What is your opinion about this (external tables)?

So, in a nutshell, yes, I'd love to collaborate with you on this :D but let's make a final decision once we hear what a few others have to say...just to be sure we are considering all angles ;)

Share this post


Link to post
Share on other sites

A SuperBatcher! I love the idea :)

  • Like 2

Share this post


Link to post
Share on other sites
 Btw, your code has taught me a lot, thanks - well-written and commented and just makes me understand PW even better.

Thanks! Glad it helps others :)

 Hmm, just wondering whether it makes sense to have two separate modules

or one (Batcher) to rule them all! I have been planning to have my

module handle both PW and external tables (data handling on demand

basically), although that could add to the complexity

With external tables you mean custom tables in the same database as Pw? So that one can select the table and columns to edit, e.g. map the columns to Pw-Fields and then handle the CRUD.

This sounds great, but IMO this would be a different module because the data is stored "outside" of Pw.

  • Like 1

Share this post


Link to post
Share on other sites

Thanks! Glad it helps others :)

With external tables you mean custom tables in the same database as Pw? So that one can select the table and columns to edit, e.g. map the columns to Pw-Fields and then handle the CRUD.

This sounds great, but IMO this would be a different module because the data is stored "outside" of Pw.

I meant both actually - custom tables in PW DB and in external DBs. But I see your point now that I've thought about it; you are right, that should be a different module....

Share this post


Link to post
Share on other sites

Hmm... well if they share most of the code then this might be a candidate for a module that contains the core functionality for CRUD and PW pages, then an add-on module for external DBs?

You wouldn't want to be duplicating too much code - have a look at dependencies if you've not read up on that already: http://processwire.com/talk/topic/778-module-dependencies/ - then you can fire up the main module from the external table version.

You might just find though that it is easier to make it as one module (I suspect this might be the case actually given the chance of code duplication).

  • Like 1

Share this post


Link to post
Share on other sites

@Pete: Thanks for your thoughts.

@Wanze; I'll PM you later to discuss how we proceed, thanks.

Share this post


Link to post
Share on other sites

This looks awesome, loved watching the video. Just let me know if there's anything I can do to support further development of this. 

  • Like 1

Share this post


Link to post
Share on other sites

Hi all,

Just to let you know that Wanze and I are now working on this (or rather, he's doing most of the coding and am doing the talking ;)). We concluded it was best to (first) develop a PW Handsontable class that other modules can load, configure settings and use its methods to render CRUD tables. This way, the class can be integrated into other modules as required, e.g. Batcher and use all or only some of its methods, e.g. limited CRUD, readOnly, etc. At this stage, this means that the module is primarily for developers since there is no UI to configure it. However, once the class has been tested, a Process Module should follow. You can follow the project here: https://github.com/wanze/PwHandsontable. Suggestions welcome, thanks!

  • Like 10

Share this post


Link to post
Share on other sites

Lookin' great so far. And about the coding & talkin:

 (or rather, he's doing most of the coding and am doing the talking  ;))

I like it that way, PLEASE stay talkin' on the forums too. (and Wanze coding !)

When Joss returns, we have a whole Social Media Platform going on right here.

Think this module gonna get really useful for lot's of things.  

  • Like 2

Share this post


Link to post
Share on other sites

That's great news!

I'd love to see the Handsontable class implemented as a read-only InputField for Page Reference fields (as an alternative to AsmSelect etc). It would be great for searching and multi-selecting pages for references  - specifically when there are tons of potential pages.

...lots of possibilities with this one.

Share this post


Link to post
Share on other sites

Also wanted to dive in and say hello. I like coffee. :D

Seriously great stuff, and you're right my DataTables module is only an example and it was a rough coding session never found any more love. But actually to just read and list data with quick edit link in a modal, which is nice for when editing sets of items, and I'm using it in some projects. It would also support inline editing and stuff but never found the energy and time to give it a shot. Making everything configurable and modular/abstract is quite some work, so all the love for people taking this "concept" further. I think there's a need for such things and I love seeing other people getting involved with PW module development. There's so many good things that can be done and PW makes it so easy.

But dude that Handsontable is a real beauty and looks like a great concept and API! Keep it up. Thanks for sharing. The right tools make a big difference.

If you need a beta tester... ;)

  • Like 4

Share this post


Link to post
Share on other sites

@Soma...Thanks! Well you and the other "usual suspects" are squarely to "blame" for slowly turning me into a coder :P! I have recently had the "aha" moment regarding module development; I couldn't agree more - PW makes it so very easy to develop modules! The API is consistent and there's tools for almost everything (well-done Ryan!). Looking at the module code written by you guys now makes much more sense to me. Teppo's write-up on Inputfields and Fieldtypes was one of the tipping points for me; that plus I looked up some really nice and short PHP OOP tutorials. Suddenly, it all made sense :). I feel like I've learnt a lot in such a short time, thanks to you guys leading the way :D;). Anyway, I will be doing a write-up sometime about my experiences in module development so watch this space. 

About "energy", incidentally, I was thinking about that recently; about that time when you feel you've "coded it all" and need something different to excite you. Anyway, I digress. Thanks for offering to test. Looking forward to your suggestions :)^-^

  • Like 4

Share this post


Link to post
Share on other sites

I need such a module to maintain a table of data. Most important feature I need are inline editable fields.

Any useable module available today or in the near future?

Share this post


Link to post
Share on other sites
I need such a module to maintain a table of data.

You can use repeaters for smallish tables of data. In my own projects I usually create a custom Fieldtype/Inputfield for managing highly custom tables of data like rate tables and such. This is the most efficient way to do it, though not the simplest. But it may be simpler than you'd think. 

Most important feature I need are inline editable fields. Any useable module available today or in the near future?

Most inline editing tools are 3rd party javascript plugins. It is possible to use just about any of these with ProcessWire, especially for text-based fields. But as ProcessWire does not get involved with the markup on the front-end of your site, it's something that you would need to implement. ProcessWire's API makes it a relatively simple job, but it's still development.  You might also want to take a look at the Fredi  module, a nice approach to front-end editing, even if not inline editing. 

Share this post


Link to post
Share on other sites
In my own projects I usually create a custom Fieldtype/Inputfield for managing highly custom tables of data like rate tables and such. This is the most efficient way to do it, though not the simplest. But it may be simpler than you'd think. 

Ryan, any chance you would be willing to share a very basic example?

A "table" Fieldtype/Inputfield would be amazing.

Share this post


Link to post
Share on other sites

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By maba
      Hello,
      I need to import regularly - every 15 or 30 days - a big .xslx file into my PW installation.
      This file now has 14 columns, 5.000 rows and grows every month.
      I'll need to group, order and work with these data to:
      analyse User monthly costs analyse User costs per Asset ... User (real AD account) has to match with a PW user - I can't join to the domain - but as you can see I have some services users (start with sca_*) or no user at all. Those rows have to be assigned to a specific user, e.g. account100.
      And:
      I would like to be able to have a kind of diff function to compare User assets between this and last month (and so on) other request is to have a notification when something change for a User between actual and latest import First request: which is the best solution to store those data in your opinion? Page, Table, Repeater Matrix, ...?
      Those are very repetitive data and I think a page reference is better than to import all the data every time but I have to understand how to manage those "dynamic" groups of software (AccType Det), hardware (Asset), ... For example Price will be imported and not stored with the description because it could be change in the future and I'll not have any control on it.
      Thanks!
      User,OE,productNmr,AccType1,AccType Det,Count,Price (€),Sum,ASNA,CC,AccType Info,Asset,AccGroup,,,,,,,,,,,,,
    • By pankaspe
      Hello everyone, I need to rewrite entire application and I want to choose PW for speed reason and logic. I love the roles/permissions system and relationships in db, but now the question..

      My app is based on users roles, foreach user role I need to write a big form (around 30fields)->store in database->edit and show the form. Simple crud system in frontend.
      I can not find a guide to do this, I would use as much as possible the PW's API can you give me some advice?

      edit: this module works fine! http://modules.processwire.com/modules/form-template-processor/
      @ryan with the upload fields the page say "The page must be saved"
      my code in my template is:
       
      $form = $modules->get('FormTemplateProcessor'); $form->template = $templates->get('my_template'); $form->requiredFields = array('field1', 'field2', 'field3'); $form->parent = $page; echo $form->render();

      Thank you all guys!
    • By FrancisChung
      Hi there,
      I've got some code that bulk uploads the contents of Tab Delimited files into our Processwire CMS.
      It looks like the $page->save functionality stops working after a certain number has been processed.
      Haven't worked out where it starts failing yet but all the rows towards the bottom have certainly failed.
      Unfortunately, there isn't anything in the error logs (at least the php error log) to see if there is an issue.
      I have tested the rows that were not working by putting them individually in seperate tsv files to prove that the code works, the data is clean and it actually updates the CMS.
      I was wondering if there's an internal limit on how many page saves the CMS can handle?   Thanks in advance.   Below is the copy of the code I'm using to do a bulk upload. I'm using a 3rd party module called SimpleExcel to help with the reading and processing of the tab delimited files.   $_rowStart = 13; $_colTitle = 1; //A $_colMeta = 10;//J Meta Title = SEO Title $_colDesc = 11;//K $_colURL = 12;//L $_colURLnew = 13;//M $_colCanonical = 14;//N $_colAlt = 15;//O $_colKeywords = 16;//P $excel = new SimpleExcel('TSV'); $excel->parser->loadFile(__DIR__."/{$file}"); //$url = $excel->parser->getCell($_rowStart, $_colURL ); $row= $_rowStart; do { $title = $excel->parser->getCell($row, $_colTitle ); $meta = $excel->parser->getCell($row, $_colMeta ); $desc = $excel->parser->getCell($row, $_colDesc ); $url = $excel->parser->getCell($row, $_colURL ); $urlnew = $excel->parser->getCell($row, $_colURLnew ); $canonical= $excel->parser->getCell($row, $_colCanonical ); $alt = $excel->parser->getCell($row, $_colAlt ); $keywords= $excel->parser->getCell($row, $_colKeywords ); $internal= str_replace("http://sprachspielspass.de",'',$url); $page = wire(pages)->get("path=$internal"); //TODO: Error handling and logging if (!IsNullPage($page)) { $page->setOutputFormatting(false); $page->title = $title; $page->seo_title = $meta; $page->seo_description = $desc; $page->url = $urlnew; $page->seo_canonical = $canonical; $page->image_alt = $alt; $page->seo_keywords = $keywords; $page->Save(); WriteLog("{$internal} saved"); } else { WriteLog("{$internal} NOT FOUND!"); } $row++; } while (!IsNullOrEmptyString($url));
    • By lokendra
      Hi, i am new to Processwire.
      I want to make an user interface using Processwire. So, please let me know, if it is possible!
      Requirements are like this : -
      1. Login System according to the user type i.e for different user, there should be a different login system.
      2. According to the user type, their view page should be different
      3. Importing and exporting the excel file(xls, xlsm, xlsx) 
      4. Editing the excel file on the user interface (I have seen on the forum, Kongondo has integrate the handsontable with the processwire, so please let me know how he did that).
      The 3rd and 4th requirement are important.
      Thanks and Regards
      Lokender Singh Shekhawat
×
×
  • Create New...