Yii 1.1: htmltable

A simple, collapsible, sortable, editable, exportable to CSV data grid.
55 followers

(New) Export table to CSV!

Demo is back click here

  • Rev 1.3.8 - Fixed an issue with form when using widget in a page that contains another form. Thanks to hairylunch for finding this bug.
  • Rev 1.3.7 - Improved IE compatibility fixing the trailing commas issue in javascript. Editing form now has a close form button on the form's header.
  • Rev 1.3.6 - Refactored some parts of the code to make it more flexible. Also modified default CSS to cover full width by default.
  • Rev 1.3.5 - Now includes an action class for the export functionality. Will use internal CSS if no cssFile is specified.
  • Rev 1.3.4 - Fixed download csv code. Updated the controller sample code.
  • Rev 1.3.3 - Added new button to export all rows to a comma separated values (CSV) file in the server. See sample controller included in download file. Now uses internal CSS by default.
  • Rev 1.3.2 - Added built-in theme (false by default) and a parameter, useInternalCss, to use the internal theme and ignore the cssFile path.
  • Rev 1.3.1 - Now a themeable version is available for use with jQuery's UI themes. The edit form now is draggable.
  • Rev 1.3 - Now the table is Editable. Updated static method calls to support PHP < 5.3.
  • Rev 1.2.1 - Added default sorting parameters
  • Rev 1.2 - Supports client sided sorting.
  • Rev 1.1 - Supports CActiveDataProvider as a parameter.
  • Rev 1.0 - Initial version.

Description

This is a simple but very powerful and versatile table-based data grid, focused on simple client sided interaction. Originally, I developed this to avoid the overhead of Yii's GridView. Now the widget offers a range of powerful unique features, while preserving the simplicity.

Features

The widgets main functions are to display data in a collapsible table that can be configured to use a jQuery UI theme and that allows modifications to its presented data and exporting in CSV.

The main features are:

  • Collapsible: Its title is clickable, to provide a collapsible body, for applications that need to draw several tables in the same page.
  • Sortable: The table includes client-sided sorting.
  • Editable: When editable is set to true, the table can be edited live! Then its possible to send the edited rows to the server through AJAX.
  • Themeable: This new version supports passing the css file location of a jQuery UI's theme and will apply the look and feel to the widget.
  • Export to CSV functionality. This requires the use of a new property: exportUrl and a controller/action handler. Includes an action class to take care of exporting to CSV for you with minimal configuration needed in your controller. Just add it to the actions() declaration in your controller and specify the css export path under webroot. You might modify it to fit your needs.
  • Uniquely Identified: Each instance of the widget is uniquely identified to allow multiple tables working independently in the same page.

Parameters

  1. title - The title of the table
  2. sub-title - A subtitle, shown below the title
  3. collapsed - Determines if the table will be drawn collapsed or expanded. False by default.
  4. extra - Additional information about the table contents that will be shown in the title area. If not provided, it will show the count of rows in the table.
  5. arProvider - An instance of CActiveDataProvider from which to extract the data.
  6. enableSort - Turn on or off sorting, true by default.
  7. sortColumn - Used to specify which column to sort by default if sorting is enabled. Columns are specified by passing an zero-based integer. First column will be 0, second 1 and so on. Will sort first column by default.
  8. sortOrder - Used to specify the order of the default column sort. 'asc' will sort default sort column in ascending order, and 'desc' will sort the column in descending order. Ascending by default.
  9. editable - If true, sets the mode of the table to edit mode, allowing changes to the table live and sending modified rows to the server for appropriate action.
  10. ajaxUrl - A string describing the controller/action that will handle the updates to the table data. This is used only when editable is set to true.
  11. cssFile - A string representing the path to a css file compliant with jQuery's UI Themes. Example: 'cssFile'=>'/css/trontastic/jquery-ui-1.8.16.custom.css'. You can create your own theme in here: jQuery UI ThemeRoller. If not specified the table will use its internal CSS definition.
  12. exportUrl - (Required for CSV exporting) A string describing the controller/action that will create the CSV file. If not provided the export button will not be shown.

Requirements

I developed this extension using Yii 1.1.7 and PHP 5.3. Static calls to methods of a class have been fixed to support PHP < 5.3. Also might be too obvious but Javascript must be enabled.

Installation

To install extract the files under yourapplication/protected/extensions/.

The widget can be included in any view file by using

$this->widget('ext.htmlTableUi.htmlTableUi',array(
    'ajaxUrl'=>'site/handleHtmlTable',
    'arProvider'=>'',    
    'collapsed'=>false,
    'columns'=>$columnsArray,
    'cssFile'=>'',
    'editable'=>true,
    'enableSort'=>true,
    'exportUrl'=>'site/exportTable',
    'extra'=>'Additional Information',
    'footer'=>'Total rows: '.count($rowsArray).' By: José Rullán',
    'formTitle'=>'Form Title',
    'rows'=>$rowsArray,
    'sortColumn'=>1,
    'sortOrder'=>'desc',
    'subtitle'=>'Rev 1.3.5',
    'title'=>'Table 2',
));

How to load data

This widget can be used with static data or by providing a CActiveDataProvider. You will probably want to "massage" the data coming from the provider, and then prepare the two arrays required. However if you want to tabulate the data directly from the provider then you can omit the rows and columns arrays. See below for examples.

Usage with static data

To use this extension first prepare the data to be shown as explained above. Minimally you should create the columns array and a rows array. The columns array will consist of an array that holds strings to be used as columns header. The rows array holds the data to be tabulated. Data in the rows must correspond to the columns.

Let's see the following example (not using the themeable version).

$columnsArray = array('id','name','lastname','tel','email');
$rowsArray = array(
    array(1,'Jose','Rullan','123-123-1234','jose@email.com'),
    array(2,'Fred','Frederick','123-123-1234','fred@email.com'),
    array(3,'Paul','Horstmann','123-123-1234','phor@email.com'),
    array(4,'Kim','Guptha','123-123-1234','kgup@email.com'),
    array(5,'Fred','Frederick','123-123-1234','fred@email.com'),
    array(6,'Querty','Uiop','123-123-1234','querty@email.com'),
    array(7,'Albert','Febensburg','123-123-1234','a@email.com'),
    array(8,'Dan','Sieg','123-123-1234','da@email.com'),
    array(9,'Janice','Breyfogle','123-123-1234','janice@email.com'),
    array(10,'Cornelious','Ape','123-123-1234','potapes@email.com'),    
);
 
$this->widget('ext.htmlTableUi.htmlTableUi',array(
    'collapsed'=>true,
    'enableSort'=>true,
    'title'=>'My Simple HTML Table',
    'subtitle'=>'Rev 1.3.3',
    'columns'=>$columnsArray,
    'rows'=>$rowsArray,
    'footer'=>'Total rows: '.count($rowsArray).' By: José Rullán'
));
 
$this->widget('ext.htmlTableUi.htmlTableUi',array(
    'ajaxUrl'=>'site/handleHtmlTable',
    'arProvider'=>'',    
    'collapsed'=>false,
    'columns'=>$columnsArray,
    'cssFile'=>'',
    'editable'=>true,
    'enableSort'=>true,
    'exportUrl'=>'site/exportTable',
    'extra'=>'Additional Information',
    'footer'=>'Total rows: '.count($rowsArray).' By: José Rullán',
    'formTitle'=>'Form Title',
    'rows'=>$rowsArray,
    'sortColumn'=>1,
    'sortOrder'=>'desc',
    'subtitle'=>'Rev 1.3.5',
    'title'=>'Table 2',
));

This example will render two tables. The first table is initially collapsed, and by default it will be sorted using the first column in ascending order. The second will be expanded and it will use the second column as the default sorting column in descending order.

Usage with CActiveDataProvider

As an alternative you can pass a CActiveDataProvider to the table as a parameter, arProvider, and it will get the rows and columns automatically from the model based on the provider's criteria. This is simpler to use, see the code:

// <-- Create ActiveDataProvider -->
$woProvider = new CActiveDataProvider('Workorder', array(
    'criteria'=>$criteria,
));
 
$this->widget('ext.htmlTableUi.htmlTableUi',array(
    'collapsed'=>false,
    'arProvider'=>$woProvider,
));

In this example the htmltable receives a CActiveDataProvider. If the title is not set, it will use the data provider's model classname as title.

Edit data in the table

The feature to edit the data in the table requires you to set the editable parameter to true and specify a controller/action url parameter for the AJAX request.

The table will verify all modified rows and will send them to an action for server-side processing, using the ajaxUrl property.

When editable is set to true, the table will display an edit icon edit mode in the upper right corner that will enable the edit mode. Once clicked, the table will be in edit mode and upon clicking any row, a form will pop-up with the row's data.

To return to view mode click the view icon view icon.

When a change is made to the table, a send icon send icon will show up in the upper right corner. This icon indicates that a row has been modified (without validation) and upon clicking on it it will trigger the AJAX http POST request to the server.

Export to CSV

To set up the CSV export feature you need to declare the "export" action in your controller. The "export" action included in the widget's action folder will export the csv to a folder of your choosing. The folder to put the csv files must be specified in the declaration of the action. For example, if you want to add this action to SiteController you would add the following code to the actions() function:

public function actions()
    {
        return array(
            'exportTable'=>array(
                'class'=>'ext.htmltableui.actions.HtmlExportCsv',
                'path'=>'/csv/',
            ),
        );
    }

Here I named my action 'exportTable'. You could change that to any name you would like the action to have. You then need to configure the widget's exportUrl property with this controller/action. In this case I would set the exportUrl property to 'site/exportTable'. What's important is that the class property must be set to the right CAction class path as shown above. Here I'm telling the widget that the generated files will be in /path/to/myapplication/csv/.

=====================================================================================

Example

version 1.3.3

View Code

This is the code in the demo example.

$columnsArray = array('id','name','lastname','tel','email');
$rowsArray = array(
    array(1,'Andres','Irizarry','123-123-1234','jose@email.com'),
    array(2,'Fred','Frederick','123-123-1234','fred@email.com'),
    array(3,'Paul','Horstmann','123-123-1234','phor@email.com'),
    array(4,'Kim','Guptha','123-123-1234','kgup@email.com'),
    array(5,'Fred','Frederick','123-123-1234','fred@email.com'),
    array(6,'Elizabeth','Espiano','123-123-1234','querty@email.com'),
    array(7,'Albert','Febensburg','123-123-1234','a@email.com'),
    array(8,'Dan','Sieg','123-123-1234','da@email.com'),
    array(9,'Janice','Breyfogle','123-123-1234','janice@email.com'),
    array(10,'Cesar','Subots','123-123-1234','potapes@email.com'),  
);
 
$this->widget('ext.htmltableui.htmlTableUi',array(
    'ajaxUrl'=>'site/handleHtmlTable',
    'arProvider'=>'',    
    'collapsed'=>true,
    'columns'=>$columnsArray,
    'cssFile'=>'',
    'editable'=>true,
    'enableSort'=>true,
    'exportUrl'=>'site/exportTable',
    'extra'=>'Additional Information',
    'footer'=>'Total rows: '.count($rowsArray).' By: José Rullán',
    'formTitle'=>'Form Title',
    'rows'=>$rowsArray,
    'sortColumn'=>1,
    'sortOrder'=>'desc',
    'subtitle'=>'Rev 1.3.5',
    'title'=>'Table 2',
));

Controller Code

You might want to handle the data sent after editing the table in the controller. Basically you need to add an action to the controller and then do whatever you want with the data. This example just returns the data back to the widget to show it in a javascript pop-up.

class SiteController extends Controller
{
 
    public function actionHandleHtmlTable(){
        sleep(2);
        if(isset($_POST)){
            //return the POST variable back
            //the widget will show an alert() with this data
            print_r($_POST);
        }
    }

Data Format

This is the resulting array as received by the controller/action:

Array
(
    [id] => yw1
    [title] => Table 2
    [subtitle] => Rev 1.3.3
    [extra] => Additional Information
    [columns] => Array
        (
            [0] => id
            [1] => name
            [2] => lastname
            [3] => tel
            [4] => email
        )
 
    [row-0] => Array
        (
            [id] => 1
            [name] => Jose
            [lastname] => Rullan
            [tel] => 123-123-1234
            [email] => jose@email.com
        )
 
    [row-1] => Array
        (
            [id] => 2
            [name] => Fred
            [lastname] => Frederick
            [tel] => 123-123-1234
            [email] => fred@email.com
        )
 
    [footer] => Total rows: 10 By: José Rullán
    [timezone] => 4
)

Each table element is sent using an associative array key. For example the widget's id is sent using the [id] key.

How the editable and export functionalities work

To implement these two functionalities the widget relies on jQuery selectors. When editing the widget will use the controller/action specified in ajaxUrl. The jQuery selectors will include only the modified rows in the data to be sent. Then the widget will use a jQuery ajax request to send the data to the controller/action.

Similarly, when exporting the widget will use the exportUrl to specify the controller/action that will handle the data sent. The difference is that when exporting the jQuery selectors will include all the data in the table, not just the modified values. Because they are very similar that code might be refactored in future iterations of the widget.

Thanks to mesmer for his feedback.

Themes

To use themes you just need to specify the location of your css file in the cssFile property. If left blank or not used, the widget will use its default theme.

For Example:

$this->widget('ext.htmlTableUi.htmlTableUi',array(
    'title'=>'Simple, Sortable, and Editable Table',
    'subtitle'=>'Rev 1.3.3',
    'columns'=>$columnsArray,
    'rows'=>$rowsArray,
    'footer'=>'Total rows: '.count($rowsArray).' By: José Rullán',
    'collapsed'=>false,
    'enableSort'=>true,
    'editable'=>true,
    'ajaxUrl'=>'site/handleHtmlTable',
    'cssFile'=>'/css/trontastic/jquery-ui-1.8.16.custom.css',
));

Possible future updates:

  1. Simple client sided pagination

Total 20 comments

#16629 report it
Girish at 2014/03/13 02:09am
How to add new row?

Is it possible to add/delete the rows?

#13710 report it
savariya at 2013/06/19 03:58am
problem with updateing column

how update edited column with action:

The actionHandleHtmlTable() is gives me only array.

how update data table with that array.

plz answer me, thanks in advance..:)

#12547 report it
Bluenica at 2013/03/28 02:31am
Help: Dataprovider as the title

I made your extension work, now from the application i'm currently doing i need the title of the table to be the one i'm filtering from my dataprovider, and another content is IN the table ... is it possible to do it like that? i want the the title to be city

public function actionView($id)
    {
        $model = $this->loadModel($id);
        $cityDataProvider=new CActiveDataProvider('City', array(
                'criteria'=>array(
                        'condition'=>'project_site_id=:siteId',
                        'params'=>array(':siteId'=>$model->id),
                ),
        ));
 
        $this->render('view',array(
            'model'=>$model,
            'cityDataProvider'=>$cityDataProvider,
        ));
    }

i want the cityDataprovider to be the title of the table, is it possible?

#12131 report it
Maurice_ at 2013/02/28 06:29am
All columns are displayed? Why

There seems not to be any way to just display specific columns in the datatable. If I use the following data provider, it displays the values for "testcolumn1" in the wrong column. Furthermore all the other columns are also being displayed in the datatable though I filtered them out over the select statement ('select'=>'testcolumn1') in the criteria.

$dataProvider = new CActiveDataProvider('Alarms', array(
                         'criteria'=>array(
                         'select'=>'testcolumn1',
                         'condition'=> ($currentStatus === 'show all'? '': 'overcreditlimit=\'Yes\''),
                         'order'=>'accountno DESC',
                         //'with'=>array('overcreditlimit'),
                        ),
                        'pagination'=>false,                        
                ));
#12127 report it
Maurice_ at 2013/02/28 05:27am
Bug or desired?

When I use the extension as usual and give it my CActiveDataProvider that returns no data, because none of the conditions matches, I get the following error:


Undefined variable: rows
 
[php]
..../protected/extensions/htmltableui/htmlTableUi.php(294)
 
282     
283     /**
284      * This is a helper function for getting
285      * rows values from an CActiveDataProvider
286      */
287     protected function getArRows($arProvider){
288         // 2. Get Rows based on Data arProvider
289         unset($rows);
290         $arRecords = $arProvider->getData();
291         foreach($arRecords as $record){
292             $rows[]=$record->attributes;
293         }
294         return $rows;        
295     }

#10676 report it
Łukasz Piotrowski at 2012/11/13 03:34pm
Update realtions table data

Hi! I have questions, Is the widget supports data updates from a relational table?

#9827 report it
w_grace at 2012/09/13 04:16pm
Really nice extension, but I seem to have a problem..

I set the Criteria, there is alot more in the table, and I call in and then I display it, the problem is when it displays it shows all the colunms and then displays the criteria under the wrong headings.

$criteria = new CDbCriteria;
$criteria->select = array('login', 'contact_email', 'first_name', 'last_name', 'credits', 'credits_consumed', 'credit_notified', 'last_notified', 'created_at', 'updated_at',);
 
$woProvider = new CActiveDataProvider('Users', array(
                    'criteria' => $criteria,
                ));
 
$this->widget('ext.htmlTableUi.htmlTableUi',array(
    'collapsed'=>false,
    'arProvider'=>$woProvider,
    'ajaxUrl'=>'site/handleHtmlTable',
    'cssFile'=>'',
    'editable'=>true,
    'enableSort'=>true,
    'exportUrl'=>'site/exportTable',
    'extra'=>'Additional Information',
    'formTitle'=>'Detailed Information',
    'sortColumn'=>1,
    'sortOrder'=>'desc',
    'subtitle'=>'Rev 1.3.5',
    'title'=>'Detailed Information',
));

I get all the contents as colunms

id login contact_email first_name last_name password_hash password_salt last_request expiry_date licensed_credits licensed_credits_consumed last_credit_notified last_notified es_limit ea_limit ca_limit da_limit la_limit created_at updated_at

then it gives me the contents I asked for in Criteria under the wrong headings.

#9694 report it
Ivanda Nothabeer at 2012/09/03 10:11pm
Column Headers

If you are using an ActiveDataProvider it is possible to Use the correct attributeLabels from the Model as Column Headers with a small modification to htmlTableUi.php

protected function getArColumns($arProvider){
        // 1. Get Columns based on the Data arProvider's model
        $class = get_class($arProvider->model);
        unset($columns);
        // Used this approach instead of the commented code below
        // to support PHP < 5.3. The commented syntax works
        // only in PHP 5.3+. -- Thanks to Pomstiborius who brought it
        // to my attention.
        $modelInst = call_user_func(array($class, 'model'));
        /*
        foreach($class::model()->attributes as $column=>$value){
            $columns[]=$column;
        }
         */
        foreach($modelInst->attributes as $column=>$value){
            $columns[]=$modelInst->getAttributeLabel($column);
        }
        return $columns;
    }

It's a great extension, but it really does need a way to make some fields ReadOnly and Hide other fields that should not be edited.

Also, if editing a large table with many rows, the Edit Window does not change position as you scroll down through the display. This means that the edit window can open above the top of the screen and hence cant be seen.

#9641 report it
Jose Rullan at 2012/08/31 09:17am
Demo is back - jquery ui files

@nights

Demo is back! In regards to your comment regarding the jquery js files, it is included in the widget folder. You don't need to include it yourself. Try creating a new empty application and use the sample code provided and see if it works. Then check your other application to determine what's different.

Enjoy!

#9167 report it
nights at 2012/07/25 12:58pm
worked

got it to work when i manually included jquery-ui-1.8.16.custom.min.js

surprised no one else got in trouble with this...doesnt say anywhere that jquery-ui is required what ican see.

#9166 report it
nights at 2012/07/25 12:35pm
please provide a working demo

for widgets like this it would be very useful if there was an actual live demo, i have no idea besides the description what this will actually provide.

currently like 1% of the yii extensions have working demos. best would be of course if yiiframework.com allowed like a sandbox where demos could be posted using the latest version of yii and an auxilary folder for extension files.

#9165 report it
nights at 2012/07/25 12:31pm
hey

can't get this to work. the table renders properly with dataprovider etc, but no javascript seem to be loaded. atleast nothing happens when i select the edit button. is there anything required for this to load? im loading everything in an ordinary controller and using the controller code provided in the example. let me know please.

#9156 report it
Backslider at 2012/07/24 12:45pm
Lost data with apostrophes

The following code will lose data if the data contains apostrophes:

var input = "<td><input type='text' name='"+columns[index]+"' class='text' value='"+$(this).text()+"'></input></td>";

I solved like this:

var rowText = $(this).text().replace(/\"/g,"&quot;");
 
    var input = '<td><input type="text" name="'+columns[index]+'" class="text" value="'+rowText+'"></input></td>';
#9140 report it
Backslider at 2012/07/23 07:49pm
Posted Rows

I think it better to post rows as a multidimentional array. I changed code as follows:

var rowData = new Array();
 
    $.each($(modifiedSelector),function(key,value){
 
        var myElements = new Object();
 
        $(value).children().each(function(index){
            myElements[columns[index]] = $(this).text();
        });
 
        rowData[key] = myElements;
        myElements = null;
    });
 
    rows['rows'] = rowData;
#9136 report it
Backslider at 2012/07/23 02:21pm
Columns that should not be editable

Need a way to set a column as not editable.

I am displaying music tracks and one of my columns has a play button image. When the edit dialog opens, this column data is empty and when I save the image disappears.

*edit - I was able to hack my way around this, but I think that non-editable column(s) is a needed feature.

#8743 report it
glyph at 2012/06/22 05:39pm
sort is always on

Any idea why I can't turn enableSort off?

It's set to false in the view:

$this->widget('ext.htmlTableUi.htmlTableUi',array(
            'columns'=>$data['gridArr']['columns'],
            'rows'=>$data['gridArr']['rows'],
            'collapsed'=>false,
            'enableSort'=>false,
            'editable'=>false,
            'sortColumn'=>false,
            'ajaxUrl'=>'site/handleHtmlTable',
            'id'=>'fp-table-'.$data['patient_id']
        ));

and I also edited the extension to default it to false, still always ends up being true!

/*
     * enableSort
     * 
     * This parameter is used to add the
     * TableSorter2.0 script to the table.
     */
    public $enableSort = false;

What am I missing here? Thanks much for your help.

#8188 report it
Anas AbuDayah at 2012/05/15 12:28pm
Good Job

That is my taken about.. keep it up .. we support u :)

#8058 report it
Jose Rullan at 2012/05/07 02:33pm
Fixed IE issues with trailing commas

@sillen

Thank you for detecting the issue with IE. I didn't really tested it thoroughly in IE. Have already submitted a fixed version 1.3.7. Hopefully there are no other issues with IE or any other main browser.

#8004 report it
kesotroll at 2012/05/03 05:21am
ie troubles

There are a couple of trailing commas within curly brackets that causes IE to go haywire. Example:

function htmltableUiSort(myId,sortColumn,sortOrder){
            // Enable table sorter for the table
            var selector = '#'+myId+'-body-table';
            $(selector).tablesorter({
                         cssHeader: "unsorted",
                         sortList: [[sortColumn,sortOrder]], <--- omg
            });
#5778 report it
wtfbrb at 2011/11/11 03:40pm
Filters?

Is there anyway to add the filters back in?

Leave a comment

Please to leave your comment.

Create extension