add export to csv in datagrid

Login or register to post comments
16 replies [Last post]
grossini's picture
Offline
Joined: 01/05/2009

From a customer request I started an exercize to export a datagrid content to excel using csv format.
I started from idea in http://www.qcodo.com/forums/forum.php/2/2103 and finally
ended in a QDataGrid class that in conjunction with my simple ergonomic tip on datagrid interface posted the 11/18/2009 - 16:58 put in paginator row a link to download the binded datagrid to csv.

Happy new year....Gianni -
2009-12-27 added some enhancement - e.g. remove initial unwanted chr(09) from csv

Position of link to download csv can be improved, but in my ergonomic trick appear correctly at (was -left- now) right of paginator line.
To exercize on this we can operate on line 33/34 and 46.

33             $this->lnkDownload_Create();
            if  ($this->addcvs == true){
              $this->lnkDownload_Create();
              $strToReturn .= "</span><br>";
              $strToReturn .= $this->lnkDownload->Render(false);
            }
            else
            {
               $strToReturn .= "</span>\r\n";
            }
            return $strToReturn;

46 replaced
46         $this->lnkDownload->Text = '<span class="left"> <img src="'.__VIRTUAL_DIRECTORY__.__IMAGE_ASSETS__.'/save_16.png">'.QApplication::Translate('Download as CSV').'</span>';

Here attached is a zip with QDataGrid.class.php and a save_16.png image

for Qcubed version 1.1 location are:

includes\qcubed\controls\QDataGrid.class.php
assets\_core\images\save_16.png

to have the download link simply put this line in your list generated or created

$this->dtgAutovs->addcvs = true;

$this->dtgAutovs is the istantiated MetaGrid
as you can see in partial example drawn out from one of my real Panel

// Instantiate the Meta DataGrid
$this->dtgAutovs = new AutovDataGrid($this);

// Style the DataGrid (if desired)
$this->dtgAutovs->CssClass = 'datagrid';
$this->dtgAutovs->AlternateRowStyle->CssClass = 'alternate';

        // magia 2009 11 - enable export to excel
        //  see    \include\qcodo\qform\QDataGrid.class.php
        $this->dtgAutovs->addcvs = true;

alex94040's picture
Offline
Joined: 11/06/2008

Grossini - thanks so much for your contribution, it's extremely interesting.

One potential suggestion to decouple your work from the core QDataGrid class and thus make it easily distributable: move your logic into a separate control, called QDatagridExporterButton (extending from QButton or QLinkButton). Have the constructor for your control accept a reference to an existing QDataGrid. Then, distribute your custom control as a QCubed 1.1 plugin. This will let you
1) Keep upgrading the plugin at your own schedule - ex. easily add an "export to excel" functionality instead of just "export to CSV".
2) Have a way to put up examples for how to use the functionality - plugins infrastructure is really flexible.
3) Get all the credit you deserve for it :)

Plugins are really easy to make - take a look at the examples site, all the info is there (start here: http://examples.qcu.be/assets/_core/php/examples/plugins/about.php). There's already a couple dozen plugins that the community made; take a look if you need examples: http://trac.qcu.be/projects/qcubed/wiki/plugins.

grossini's picture
Offline
Joined: 01/05/2009

I apologize for my english..

Is a real honour for me to seize your attention and replay.

About plugin, actually i have to study the architecture before try to make a plugin.
Very interesting is QSliderPaginator that extend Qpaginator, but also my cvs download interested in extending QPaginator class.

How can I extend a plugin already extended? Can I retain slider paginator and add csv download.... as a new plugin? So, a plugin of plugin..???

alex94040's picture
Offline
Joined: 11/06/2008

Grossini - there's no reason for you to worry about bad English... We all are humans here, and we all try our best. Even if your English isn't perfect, we'll make our best effort to understand you and help you.

I don't know if you want your CSV downloader to be an extension of a paginator class... I was imagining a different architecture. I thoght you would write a class like this for your plugin:

class DataGridExporter extens QButton {
public __construct ($objParentObject, QDataGrid $objGrid, $objId) {
....
}
}

What I mean here is that the plugin you'd write would be a special kind of button, that can be rendered separately from the datagrid. When created, that button would require a pointer to the QDataGrid that has to be exported. When the button is clicked, the user gets to download the contents of the datagrid as a CSV file.

grossini's picture
Offline
Joined: 01/05/2009

Good. I started the task.

Now in my working installation of qcubed_110 there is the new class
..\includes\qcubed\plugins\DataGridExporter\includes\DataGridExporter.class.php
I added in plugin_includes.php by hand the new class so I can proceed.
The progress is very slow because of my reluctance to think in Object Orientented paradigm.

All are agreed on the huge difficult of procedural programmers and analyst (as I am
from 1970) to think in OOP.

Now the button is created, and I have a suggestion to let it better (whit image inside).
This is my proposed change to method protected function GetControlHtml() of QButtonBase.class. So we can use and ->Text attribut of my button can be enriched with image.

.....
//old commented out
/*
               $strToReturn = sprintf('<input type="%s" name="%s" id="%s" value="%s" %s%s />',
$strCommand,
$this->strControlId,
$this->strControlId,
($this->blnHtmlEntities) ?
QApplication::HtmlEntities($this->strText) :
$this->strText,
$this->GetAttributes(),
$strStyle);

return $strToReturn;

*/

// proposed change

$strToReturn = sprintf('<button type="%s" name="%s" id="%s" %s%s > %s </button> ',
$strCommand,
$this->strControlId,
                    $this->strControlId,
                    $this->GetAttributes(),
$strStyle,                   
                    ($this->blnHtmlEntities) ?
QApplication::HtmlEntities($this->strText) :
$this->strText);
 
return $strToReturn;

}

Now the inital plugin is

class DataGridExporter extends QButton {

public $datagrid = "";
//public function __construct($objParentObject, $objgrid, $strControlId=null) {
     //         parent::__construct($objParentObject, $objgrid, $strControlId);
         public function __construct($objParentObject, $strControlId=null) {
             parent::__construct($objParentObject, $strControlId);

     // help on  how can I get datagrid header and row from form 
//$this->$datagrid = $objGrid;
//print_r($objGrid);


//     $this->Text = "download CVS";
       $this->HtmlEntities = false;
      $this->Text = '<img src="' . __VIRTUAL_DIRECTORY__ . __IMAGE_ASSETS__ .                    '/save_16.png">' . 'Download CVS';
}

}
?>

I used the person_list.php in drafts to test the progress and now the button is rendered as expected, but it (correctly) shows no sign of life.

class PersonListForm extends QForm {

.........
public $btnCVS;
.....
protected function Form_Create() {
.....
.....

$this->btnCVS = new DataGridExporter($this->dtgPeople);
....

Now my aid request...to my master:
how can I add life to my button here in plugin (add action ) and in response
create from data or datagrid of the page where the button exist the CVS file
with the functions I already done and works in my patched QDataGrid (that now is ok also for 1.0.1

Thank's in advance and my best wishes for an happy and extrahordinary 2010.

alex94040's picture
Offline
Joined: 11/06/2008

grossini - I'm thrilled to hear you're making progress. Most certainly, OOP is very different from functional programming.. It takes a while to get all the concepts right. But you're on a great track. You're making huge progress! And I'm super-excited about working with the result :)

A few things:
1) It looks like you'd like to add image-button functionality to QButtonBase. This seems separate from the exporter work; can you please do me a favor and open a ticket in Trac, copying over the details of such change? We'll then be able to discuss including it into the core of 1.1.1 release.

2) On adding a button action to your new DataGridExporter:
- try using AjaxControlAction. $this->btnCVS->AddAction(new QClickEvent(), QAjaxControlAction($this->btnCVS, 'buttonCVS_clicked'). Example on AjaxControlActions: http://examples.qcu.be/assets/_core/php/examples/multiple_qform/intro.php. Ideally, you'd write the action binding code inside the constructor of DataGridExporter.

3) The way you instantiate your control inside your form should probably be
$this->btnCVS = new DataGridButton($this, $this->dtgPeople). The first parameter is the parent object, the second - the datagrid that you want exported.

4) You might want to rename your class to QDataGridExporter - anything that has to do with QCubed intentionally has a Q prefix, so that there are no class name conflicts.

grossini's picture
Offline
Joined: 01/05/2009

As you sugggested I created ticket #403 for QButtonBase class proposed enhancement-
Changed also name of class in QDataGridExporter to adhere Qcubed convention .
Other activity OOP related are in progress

Happy new year, Gianni

alex94040's picture
Offline
Joined: 11/06/2008

Super-awesome. Thanks a bunch, Gianni! Looking forward to working with you in the New Year! Happy Holidays!

grossini's picture
Offline
Joined: 01/05/2009

Hello Alex now in 2010 I hope to arrive (with your help) at top of stairs

Some step done, now the button (with image!!) is living but for cvs data the step appear for me too higher to climb.

Here is the actual result with a note where I am in trouble.

Thank's for the patience of my guide.

<?php
/*
I don't know if you want your CSV downloader to be an extension of a paginator class...
I was imagining a different architecture.
I thoght you would write a class like this for your plugin:

class DataGridExporter extens QButton {
     public __construct ($objParentObject, QDataGrid $objGrid, $objId) {
      ....
    }
}
What I mean here is that the plugin you'd write would be a special kind of button,
that can be rendered separately from the datagrid.
When created, that button would require a pointer to the QDataGrid that has to be exported.
When the button is clicked, the user gets to download the contents of the datagrid as a CSV file.
*/



class QDataGridExporter extends QButton {

    var
$objForm;
    var
$datagrid ;
    var
$datasource = array();

   
//public function __construct($objParentObject, $objgrid, $strControlId=null) {
     //         parent::__construct($objParentObject, $objgrid, $strControlId);
    
public function __construct($objParentObject$dtgobj) {
            
parent::__construct($objParentObject, $strControlId);


      
$this->Text = "download CVS";
      
$this->HtmlEntities = false;
      
$this->Text = '<img src="' . __VIRTUAL_DIRECTORY__ . __IMAGE_ASSETS__ . '/save_16.png">' . 'Download CVS';

      
$this->AddAction(new QClickEvent(), new QServerControlAction($this, 'buttonCVS_clicked'));

     
// now get data to export
      // print_r($dtgobj);

       //  I need here help on  how can I get datagrid column header and row from passed dtg object
      //$this->datagrid = new QDataGrid($dtgobj);
      
$this->datasource = $dtgobj;
      
       }

    function 
buttonCVS_clicked ($strFormId, $strControlId, $strParameter)
    {       
          
           if (
$this->Text == " clicked ")
            {
             
$this->Text = '<img src="' . __VIRTUAL_DIRECTORY__ . __IMAGE_ASSETS__ . '/save_16.png">' . 'Download CVS';
          }
          else
          {
            
               
QFirebug::log("test");
               
// flip info on buttom to see that click was ok
               
$this->Text = " clicked ";
       

              
//   $this->datagrid = new QPaginator($dtgobj);
                // echo $this->datasource;
              
$this->objForm->RenderBegin(false);
              
session_cache_limiter('must-revalidate');        // Blaine's fix for SSL & PHP Sessions
              
header("Pragma: hack"); // IE chokes on "no cache", so set to something, anything, else.
              
$ExpStr = "Expires: " . gmdate("D, d M Y H:i:s", time()) . " GMT";
              
header($ExpStr);

              
header("Content-type: text/csv");
              
header("Content-disposition: csv; filename=" . date("Y-m-d") .
               
"_datagrid_export.csv");

              
$this->datasource->ItemsPerPage = 2147483647;
              
$this->datasource->PageNumber = 1;
              
$this->datasource->DataBind();

             
$strToReturn ="header";
          
               print(
$this->GetCSVHeader());

             
$this->datasource = null;

          
//kill any JS caused by databind
          
QApplication::$JavaScriptArray = array();
          
QApplication::$JavaScriptArrayHighPriority = array();
          
$this->objForm->RenderEnd(false);
           exit();
       }
}              

   
  
//added by magia
   
public function GetCSVHeader()
    {
       
$strToReturn = '';

       
$arrNames = array();
        if (
$this->datasource->objColumnArray) foreach ($this->datasource->objColumnArray as $col)
        {
             
$arrNames[] = strip_tags($col->Name);
          }
        
//return $arrNames;


       
if ($this->datasource->objColumnArray)
        {
           
$strToReturn = implode('","', $arrNames);
           
$strToReturn = '"' . $strToReturn . '"' . "\n";
        }
        return
$strToReturn;

}

// snippet code in test
               
/*****************************
*  get column step...
****************************
// changed  public from private $objColumnArray in QdataGridBase.class line 247
//         // MISC
//         public $objColumnArray;
//
//  and also optionally changed public from private for function GeHeaderRowHtml()) to see it here
//              QFirebug::log( $this->datasource->GeHeaderRowHtml());

//this works !!!
                QFirebug::log($this->GetCSVHeader());


/*****************************
*  get data step...  
****************************
//  I need here help on  how can I get data row
//   (all row as resulted from filter - not only that in page)
//   from passed dtg object

// action done to study
// change public from private GetControlHtml())


// added to avoid error Control cannot be rendered until RenderBegin() has been called on the form.
// $this->objForm->RenderBegin(false); 

// result log display the html table (but only row in that page!!!) 
//    ******** it may be better investigate QPaginatedControl and DataBind()?????
// but this give error -  This control has already been rendered: c5           

               QFirebug::log($this->datasource->GetControlHtml());



       }
   
// the function I'll hope give expected result.......
    function GetCSVContent()
     {

        $this->objForm->RenderBegin(false);
       
        session_cache_limiter('must-revalidate');        // Blaine's fix for SSL & PHP Sessions
        header("Pragma: hack"); // IE chokes on "no cache", so set to something, anything, else.
        $ExpStr = "Expires: " . gmdate("D, d M Y H:i:s", time()) . " GMT";
        header($ExpStr);

        header("Content-type: text/csv");
        header("Content-disposition: csv; filename=" . date("Y-m-d") .
                "_datagrid_export.csv");

        $this->datasource->ItemsPerPage = 2147483647;
        $this->datasource->PageNumber = 1;
        $this->datasource->DataBind();

       $strToReturn ="header";
        if ($this->datasource)
            foreach ($this->datasource->objDataSource as $objObject)
            {
                //  $strToReturn .= "pippo";
                $strToReturn .= $this->GetCSVRow($objObject);
            }   
        return $strToReturn;
    }
*/  

}
?>

kukrik's picture
Offline
Joined: 05/12/2008

Welcome Grossini!

I do not know whether it is necessary to draw attention to it. Used in one place:

<?php

var $ objForm;
var $
datagrid;
var $
datasource = array ();
?>

Perhaps both should be used: whether the public ... ... or protected, for example:
<?php
public $ objForm;
public $
datagrid;
public $
datasource = array ();
?>

or
<?php
protected $ objForm;
protected $
datagrid;
protected $
datasource = array ();
?>

I have one simple observation, and perhaps premature ...

Good luck to you! We look forward to!

alex94040's picture
Offline
Joined: 11/06/2008

Grossini - Happy New Year!

I'm so thrilled to see you learning and making huge progress! You're almost there with this plugin - and I'm delighted to help you make the final few steps.

Ideas for you: instead of doing

$this->objForm->RenderBegin(false);
and the rest of the code in buttonCVS_clicked() handler, why don't you do this:

1) serialize() the entire contents of the datagrid's datasource into a variable. How?

$myVar = serialize($dataGridObject->DataSource);

2) Perform an HTTP POST to a brand new page - essentially, redirect the user to a new page. In your HTTP POST, set one variable to the $myVar that you serialized the datasource into.

3) In the code of that brand new page, push out all the headers that you already know you should set. Then, spit out a TSV file from that page!

4) Suggestions that kukrik made above are very valid - instead of PHP4's vars, you should use public/private/protected. Also, all methods in your class should declare their access level (public, private, or protected). That's good PHP5 practice.

How does all that sound? What do you think?

Offline
Joined: 03/31/2008

Sorry to respond to this so late, but have you seen the work done here?
http://www.qcodo.com/forums/forum.php/2/2103

alex94040's picture
Offline
Joined: 11/06/2008

Vexed - the link you provided doesn't quite work, it takes the user to the QCodo Forum homepage.

grossini's picture
Offline
Joined: 01/05/2009

Vexed, is correct that the post you refer, now is pointed with this link http://www.qcodo.com/forums/forum.php/2/2103 ?
Very interesting post in new restructured qcodo forum about CVS download, that I refer in first entry of this post.

Alex, thanks for your trace for serialize. I'll try to climb on this step.
What you think about going to new page, can you suggest the instruction to code...
? redirect.. ? I must put there a button to download cvs ?

alex94040's picture
Offline
Joined: 11/06/2008

On going to the new page: just do a QApplication::Redirect().

Instead of setting a POST variable like I suggested above, you can just serialize the datasource into $_SESSION (just remember to unset the variable after you read it - so that the session is not full of garbage afterwards :-))

On that restination PHP file, you don't show a form or anything at all - you would set the headers in a way that would cause the browser to throw a File/Save As prompt.

Offline
Joined: 03/31/2008

Sorry about the link, yes I did try to link to the same one you provided in the first post. Obviously you have read it then. :P

tronics's picture
Offline
Joined: 04/06/2008

This works well with the most recent qcubed version but the following has to be removed to avoid trashing your datagrid:

//$this->objForm->RenderBegin(false);
//$this->objForm->RenderEnd(false);

Cheers,
tronics