[MatthewSchenker]

Greetings,
I'm working with CodeIgniter and Grocery CRUD more and more, and like both very much!

I have a project where I need to generate CSV files from a databases of form submissions.

I know CodeIgniter's "$this->dbutil->csv_from_result()" together with "write_file" can probably accomplish this. But I was thinking Grocery CRUD might have a more direct approach.

Any ideas?

Thanks again,
Matthew

[web-johnny]

No I don't have anything for this.
I still don't have any export button or a working example to show you. I have it of course in mind to have something like this in the future, but it is still not planned when and at what version. So you have to do something custom to make it work. I don't know if any other has an extension for this and help you.

[MatthewSchenker]

Hi web-johnny,
OK, no problem!

I'll use the built-in CodeIgniter functions I mentioned above. I just wanted to make sure.

Thanks again for your excellent work,
Matthew

[telnasser]

Hi MatthewSchenker,

I'm a newbie here ... Do you have to run the same query again and execute dbutil ?

[jajubear]

I try to avoid touching the framework at all, but to get this export button functionality, I have no choice.

Below is my hack, not very elegant but it works and if version changes, it should be easy to maintain.

You can skip item 1 (unset_exp), it will be even more maintainable, but this will mean that every list page will display the 'Export CSV' button and you cannot hide it...

1. create an 'unset_exp' similar to 'unset_add', in /application/libraries/grocery_crud.php
I simple searched for unset_add, and added unset_exp below

e.g.
...
$data->unset_add = $this->unset_add;
$data->unset_exp = $this->unset_exp; // add this
...

protected $unset_add = false;
protected $unset_exp = false; // add this

...


2. also, add the following line after $this->getAddUrl(); in function showList(...) in /application/libraries/grocery_crud.php

$data->list_url = $this->getListUrl();

3. next edit the list_template.php (for both Datatables and Flexigrid), example for Datatables:

<?php if(!$unset_add){?>

<a role="button" class="edit_button ui-button ui-widget ui-state-default ui-corner-all ui-button-text-icon-primary" href="<?php echo $add_url?>">

	<span class="ui-button-icon-primary ui-icon ui-icon-circle-plus"></span>

	<span class="ui-button-text"><?php echo $this->l('list_add'); ?> <?php echo $subject?></span>

</a>

<?php }?>

<!-- ADD THE LINES BELOW -->

<?php if(!$unset_exp){?>

<a role="button" class="edit_button ui-button ui-widget ui-state-default ui-corner-all ui-button-text-icon-primary" target='blank' href="<?php echo $list_url.'/export'; ?>">

<span class="ui-button-icon-primary ui-icon ui-icon-circle-plus"></span>

<span class="ui-button-text"><?php echo 'EXPORT CSV' ?></span>

</a>

<?php }?>

For Flexigrid,find below line 37 of grocerycrud version 1.2.3:

<div id='main-table-box'>

Replace:

    <?php if(!$unset_add){?>

    <div class="tDiv">

        <div class="tDiv2">

	        <a href='<?php echo $add_url?>' title='<?php echo $this->l('list_add'); ?> <?php echo $subject?>' class='add-anchor'>

            <div class="fbutton">

                <div>

                    <span class="add"><?php echo $this->l('list_add'); ?> <?php echo $subject?></span>

                </div>

            </div>

		    </a>

            <div class="btnseparator">

            </div>

        </div>

        <div class='clear'></div>

    </div>

    <?php }?>

with

    <?php if(!$unset_add || !$unset_exp){ ?>

    <div class="tDiv">

        <div class="tDiv2">

<?php if(!$unset_add){?>

	        <a href='<?php echo $add_url?>' title='<?php echo $this->l('list_add'); ?> <?php echo $subject?>' class='add-anchor'>

            <div class="fbutton">

                <div>

                    <span class="add"><?php echo $this->l('list_add'); ?> <?php echo $subject?></span>

                </div>

            </div>

		    </a>

            <div class="btnseparator">

            </div>

<?php }?>

<?php if(!$unset_exp){?>

	        <a href='<?php echo $list_url.'/export'; ?>' target='blank' title='EXPORT CSV' class='add-anchor'>

            <div class="fbutton">

                <div>

                    <span class="add">EXPORT CSV</span>

                </div>

            </div>

		    </a>

            <div class="btnseparator">

            </div>

<?php }?>

        </div>

        <div class='clear'></div>

    </div>

    <?php }?>

Two Notes:
I added target='blank' to open export in new window
href points to $list_url.'/export';



In my controller...

function report_SIM_registration_by_year($operation = null) {

		try{

if ($operation == 'export')

{

$this->load->dbutil();

$query = $this->db->query("SELECT * FROM mytable");

header('Content-type: text/csv');

header('Content-disposition: attachment;filename=xyz.csv');

echo $this->dbutil->csv_from_result($query);

die();

}

// otherwise display as per normal...

...

			$crud = new grocery_CRUD();

...

}

Hope this helps.

[Raboo]

export to csv have been implemented into the code..

[edramirez]

Exporting to CSV instead of XLS can be accomplished several ways. The neater way of doing this is to replace the default grocerycrud action for export using the getState function. Always remember that this procedure works only if you use the datatables theme.

 

step 1 - Insert the code to use the datatables theme.

example:

  $gcrud->set_theme('datatables');

 

step 2 - Insert the code to override using getState. The code is placed between the render and the view.

For example, your code might look like this:

  $output = $gcrud->render();

  $this->load->view('report.php',$output); 

 

Insert getState, adjusting the location of your view:

  $output = $gcrud->render();

  // overriding the export action

  $state = $gcrud->getState();

  $state_info = $gcrud->getStateInfo();

  if($state == 'export')

  {

    // this new function replaces grocerycrud's export action

    $this->export_to_csv($state_info);

  }

  else

  {

    // your view is repositioned to this location

    $this->load->view('report.php',$output); 

  }

 

step 3 - Add these 2 new functions to replace grocerycrud's export function. Copy exactly as is:

public function export_to_csv($state_info = null)

{

$data = $this->get_common_data();

 

$data->order_by = $this->order_by;

$data->types = $this->get_field_types();

 

$data->list = $this->get_list();

$data->list = $this->change_list($data->list , $data->types);

$data->list = $this->change_list_add_actions($data->list);

 

$data->total_results = $this->get_total_results();

 

$data->columns = $this->get_columns();

$data->primary_key = $this->get_primary_key();

 

@ob_end_clean();

$this->_export_to_csv($data);

}

 

 

protected function _export_to_csv($data)

{

$string_to_export = "";

foreach($data->columns as $column){

$string_to_export .= $column->display_as."\t";

}

$string_to_export .= "\n";

 

foreach($data->list as $num_row => $row){

foreach($data->columns as $column){

$string_to_export .= $this->_trim_export_string($row->{$column->field_name})."\t";

}

$string_to_export .= "\n";

}

 

// Convert to UTF-16LE and Prepend BOM

$string_to_export = "\xFF\xFE" .mb_convert_encoding($string_to_export, 'UTF-16LE', 'UTF-8');

 

$filename = "export-".date("Y-m-d_H:i:s").".csv";

 

header('Content-type: application/vnd.ms-excel;charset=UTF-16LE');

header('Content-Disposition: attachment; filename='.$filename);

header("Cache-Control: no-cache");

echo $string_to_export;

die();

}

 

Then you're done!

 

Don't forget to click the Like button!

 

Ed Ramirez

[Hemant Randive]

I am exporting the content from a table.But when it get exported in one column it show ####.I am using custom model for this. my code is

 

public function export_accounts_data() {

          $crud = new grocery_CRUD();

        //$month = $this->input->post('month');

         // $month = $this->uri->segment(5);

          $month= intval($_POST['month']);

          //var_dump($month);

      

        $crud->set_model('custom_query_model');

    $crud->set_theme('datatables');

     

        $crud->set_subject('E-counseling');

        $crud->set_table('e_counseling_session_master'); //Change to your table name

        $crud->basic_model->set_query_str('SELECT e.session_id,e.session_name,e.case_id,e.description,e.date,e.time,u.id,u.name,em.name,em.lname,em.Gender,em.org_code,em.email,e.status,e.type,e.fees 

        FROM e_counseling_session_master e, users u,end_employee_master em WHERE e.counselor_id = u.id AND MONTH(e.date)= '.$month. ' AND em.id = (Select DISTINCT sfm.eemp_id from session_feedback_master sfm where sfm.session_id = e.session_id ) AND

        e.fees_status = "Completed" ORDER BY e.case_id'); //Query text here

        $crud->display_as('id', 'Counselor ID');

        //$crud->display_as('name', 'Counselor Name');

        $crud->columns('session_id', 'id', 'session_name', 'case_id', 'description', 'date', 'time', 'status', 'type', 'fees', 'name', 'lname', 'Gender', 'org_code', 'email');

 

$crud->order_by('date', 'desc');

 

        $crud->unset_add();

        $crud->unset_edit();

        $crud->unset_delete();

 

        $output = $crud->render();

        $this->_example_output($output);

    }