My last project as a web developer freelancer had came with new challenge task..My client ask me to build simple accountant system to manage his clothes factory..After some questions with the client I built project analysis with this scenario:

 

• All these Is good, simple and clear right know..but the challenge was to build dynamic grid to add items like excel sheets. The client ask me to make it like excel and to make some cells with auto complete because he will not remember all items codes, he want to select item code from dropdown and then it fill item data like(item quantity-item image-item price).

 

• After made survey if found my target with this amazing plug-in it was called handsontable you can see demo from here , in this tutorial I will discuss how to integrate this beautiful plug-in with Codeigniter with easy way, I will take one example from product plan module..lets go

 

Create new controller with create function

•	/**
 *  create product_plan product_plan then redirect on overview.
 */
public function create()
{
    // load items model
    $this->load->model('items/items_model', 'items');
   // retrieve all items codes
    $drop_itemcode = $this->items->get_items_code_select();
    // retrieve all items types
    $items_type = $this->items->get_item_type();
    // retrieve all items images
    $items_image = $this->items->get_items_image();
    //    convert all these data to json
    $data['dropdown_itemcode'] = mb_convert_encoding(json_encode($drop_itemcode), "UTF-8");
    $data['items_type'] = mb_convert_encoding(json_encode($items_type), "UTF-8");
    $data['items_image'] = mb_convert_encoding(json_encode($items_image), "UTF-8");
    // form validation
    $this->form_validation->set_rules('pr_desc','pr_desc', 'trim|required|xss_clean');
    $this->form_validation->set_rules('pr_date','pr_date', 'trim|xss_clean');
    $this->form_validation->set_rules('pr_notes','pr_notes', 'trim|xss_clean');

    if ($this->form_validation->run() == false) {
        $this->load->view('product_plan_new', $data);
    } else {
        // if validation is true then insert new data from
       // form inputs and return last id
        $id = $this->product_plan->create();
        // then use this id to insert data from handsontable
         $griditems = json_decode($this->input->post('items'));
            if (isset($id) && !empty($id)) {
                // loop through handsontable grid
                foreach ($griditems as $values1 => $value1) {
                        $query1 = $this->product_plan->insert_items($value1, $id);
            }
        }


    }
}

then create new function in the model to insert grid items

    function insert_items($newVal, $id) {
// Listing all the variables
list( $prg_item_type, $prg_code, $prg_image, $prg_quantity, $prg_notes) = $newVal;
        if($prg_quantity !=''){
        $itemid = $this->getitembycode($prg_item_type);
        $query = $this->db->query("INSERT INTO d_product_plan_grid "
                . "(prg_plan_main_id,prg_item_id,prg_quantity,prg_notes)"
                . " VALUES($id,$itemid,$prg_quantity,'$prg_notes')");
        return $query;
        }else
        {
            return FALSE;
        }
    }

create new view file and put this code

        <form action="<?php echo base_url(); ?>product_plan/create" method="POST">
            <fieldset >                                
                <legend>product plan</legend>
                <?php echo $this->template->flash_msg(); ?>            

                <label ><?php echo $this->lang->line('pr_desc'); ?></label>
                <input name="pr_desc" value="<?= set_value('pr_desc') ?>" />
                <br />                    

                <label for="pr_date" ><?php echo $this->lang->line('pr_date'); ?></label>
                <input id="pr_date" class="date textBox med rnd5" name="pr_date" value="<?= set_value('pr_date') ?>"  />         
                <br />

                <label for="pr_notes" ><?php echo $this->lang->line('pr_notes'); ?></label>
                <textarea id="pr_notes" rows="6" cols="0" class="textArea lrg rnd5" name="pr_notes" ><?= set_value('pr_notes') ?></textarea>            
                <br />            
            </fieldset>
            <!-- items as hidden input !-->
            <input id="items" type="hidden"  name="items" value=""/>

            <fieldset >                                
                <legend> items grid display here</legend>
                <div id="example1" style="direction: ltr;"></div>
            </fieldset>
            <br />
            <div class="center">
                <input id="save" name="save" type="submit" value="save" />
            </div>
        </form>

<script type="text/javascript">
    var $container1 = $("#example1"); // instance
    var $parent1 = $container1.parent();
    var $console1 = $("#exampleConsole");

    var itmcodesources = <?= $dropdown_itemcode ?>;
    var ac = <?= $items_type ?>;
    var url = "<?= base_url(); ?>global/uploads/";
    var cover = <?= $items_image ?>;


//    if select item code then return item image

    var coverRenderer = function (instance, td, row, col, prop, value, cellProperties) {
        var escaped = Handsontable.helper.stringify(value);
        if (escaped.indexOf('http') === 0) {
            var $img = $('<img width="50"; hight="50">');
            $img.attr('src', value);
            $img.on('mousedown', function (event) {
                event.preventDefault(); 
                //prevent selection quirk
            });
            $(td).empty().append($img); 
            //empty is needed because 
            // you are rendering to an existing cell
        }
        else {
            Handsontable.renderers.TextRenderer.apply(this, arguments);
 //render as text
        }
        return td;
    };

    $container1.handsontable({
        startRows: 1, // start grid with one row
        startCols: 6, // add 6 columns in the grid
        rowHeaders: true, // add header to columns
        stretchH: 'all', //strech grid to the screen
        colWidths: [80, 80, 200, 100, 200],
        // column header
        colHeaders: ['item_code', 'item_type', 'item_image', 'item_quantity', 'item_notes'],
       // specify column types
        columns: [
            {
                type: 'autocomplete',
                source: itmcodesources,
                strict: true,
                allowInvalid: false
            },
            {type: 'text', readOnly: true},
            {data: cover, readOnly: true, renderer: coverRenderer},
            {type: 'numeric', allowInvalid: false},
            {type: 'text', allowInvalid: false}
        ],
        minSpareCols: 0,
        minSpareRows: 1,
        // if use right click show only these options
        contextMenu: ['row_above', 'row_below', 'remove_row'],
        // after select item code from drop down then add 
        // image and item type automaticlly
        afterChange: function (arr, op) {
            if (op == "edit" && arr.length == 1) {
                var value = arr[0][3];
                for (var i = 0; i < ac.length; i++) {
                    if (ac[i].it_code == value) {
                        for (var j = 0; j < cover.length; j++) {
                            
                            if (cover[j].it_code == value) {
                        
           $container1.handsontable("setDataAtCell", arr[0][0], 2, url + cover[j].it_image);
                            }
                        }
           $container1.handsontable("setDataAtCell", arr[0][0], 1, ac[i].it_ty_ar_desc);
                       
                        return false;
                    }
                }
            }
        }
    });


    // if click on submit button then get json data from handsontable
    $('#save').click(function () {
        var handsontable1 = $container1.data('handsontable');
        $('#items').val(JSON.stringify(handsontable1.getData()));
       // alert(JSON.stringify(handsontable1.getData()));


    });



</script>