Export Datagrid to xls (or csv), pdf, and/or html on the fly

thread: 36 messages  |  last: a year ago  |  started: tuesday, july 10, 2007, 7:21 am pdt


#1  |  mmaidana (Mallorca) Spain
Tuesday, July 10, 2007, 7:21 AM PDT

HI!!

I was looking at the forum if some of you guys, know, or did something that could allow us to export a datagrid to the following formats:

xls, csv, pdf, htm

I have developed an app, entire with qcodo, and is working perfectly, but now I need to print out some reports, (on the fly) in different formats... So it would be great if some of you could send me to the good path to get what I'm looking for...

I have tryed, fpdf, dompdf, and a pear package for xls, but with no success...

Can anyone help me?


Thanks in advance

Marc

#2  |  VexedPanda (Calgary, AB) Canada
Tuesday, July 10, 2007, 8:32 AM PDT

I'm actually in the middle of a “Download as CSV” enhancement for DataGrids. I'll put it up in downloads when it's complete, but the essence is:
1) Add a download link somewhere on the datagrid (I use 2 paginators, so I have the count at the bottom, and the download link at the top).
2) On click for that link, do your CSV export. I ripped the existing Get*RowHtml functions and modified them to spit out the data in CSV instead.

Here's where I am right now:

<?
    public function lnkDownload_Click($strFormId, $strControlId, $strParameter) 
    {
session_cache_limiter('must-revalidate');    // force a "no cache" effect
        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->ItemsPerPage = 2147483647;
        $this->DataBind();
        
        $this->PrintCSVHeader();

        if ($this->objDataSource)
            foreach ($this->objDataSource as $objObject)
                $this->PrintCSVRow($objObject);
        
        $this->objDataSource = null;

        exit();
    }
    
    protected function PrintCSVHeader()
    {
        $strToReturn = '';

        $arrNames = array();
        foreach($this->objColumnArray as $col)
            $arrNames[] = $col->Name;

        if ($this->objColumnArray) 
        {
            $strToReturn = implode('","', $arrNames);
            $strToReturn = '"' . $strToReturn . '"' . "\n";
        }
        print $strToReturn;
    }
    
    protected function PrintCSVRow($objObject)
    {
        // Iterate through the Columns
        $strColumnsHtml = '';
        $arrColumnText = array();
        foreach ($this->objColumnArray as $objColumn) {
            try {
                $strHtml = $this->ParseColumnHtml($objColumn, $objObject);
                
                if ($objColumn->HtmlEntities)
                    $strHtml = QApplication::HtmlEntities($strHtml);
                
                // For IE
                if (QApplication::IsBrowser(QBrowserType::InternetExplorer) &&
                        ($strHtml == ''))
                    $strHtml = '&nbsp;';
            } catch (QCallerException $objExc) {
                $objExc->IncrementOffset();
                throw $objExc;
            }
            $arrColumnText[] = $strHtml;
        }
        
        $strColumnsHtml = implode('","', $arrColumnText);
        $strColumnsHtml = '"' . $strColumnsHtml . '"'. "\n";
        
        print $strColumnsHtml;        
    }
?>
.bc
#3  |  VexedPanda (Calgary, AB) Canada
Tuesday, July 10, 2007, 9:38 AM PDT

Yeah, I pretty much stuck with that. Just throwing in some strip_tags, killing the &amp;nbsp; for IE, and right before exit() doing:

<?
//kill any JS caused by databind so it isn't printed to our CSV
QApplication::$JavaScriptArray = array();
QApplication::$JavaScriptArrayHighPriority = array();
?>
.bc
#4  |  mmaidana (Mallorca) Spain
Tuesday, July 10, 2007, 11:58 PM PDT

HI vexed!!!
Thanks for your answer...


The code above I have to put it on the qdatagrid.class.php right?

Do you have a suggestion where to create the link?

Thanks in advance

marc

#5  |  mmaidana (Mallorca) Spain
Wednesday, July 11, 2007, 2:55 AM PDT

HI again!

I have the code above at the Qdatagridbase.class.php

and I add also this to the constructor of this class:

      //csv
    $this->btnCsvDownload = new QImageButton($this);
    $this->btnCsvDownload->ImageUrl = 'assets/images/ver_xls.gif';
    $this->btnCsvDownload->CssClass = 'activeIcon';
    $this->btnCsvDownload->AddAction(new QClickEvent(), new QServerControlAction($this,'lnkDownload_Click'));

But, when I click on that button, it gives me the csv but with the following error:

Control cannot be rendered until RenderBegin() has been called on the form.


Exception Type:   QCallerException 

Rendered Page:   Click here to view contents able to be rendered 

Source File:   g:\xampp\xampp\htdocs\mediweb\sam\includes\data_classes\Common.class.php     Line:   889 


Line 884:                } else{
Line 885:                    return '<input type="checkbox" disabled>'.$chkSelected->Enabled=false;
Line 886:                }
Line 887:    
Line 888:            } else {
Line 889:                return $chkSelected->Render(false);
Line 890:            }
Line 891:        }
Line 892:    
Line 893:    

It seems that I get this error cause I have this function that allow me to check a user to edit it...

    public static function chkSelected_Render($objParentObject,$blnEditMode,$Idobj, $dtgBind,$Idedited = null) {
        $strControlId = 'chkSelected' . $Idobj;
        // Let's see if the Checkbox exists already
        $chkSelected = $objParentObject->GetControl($strControlId);

        if (!$chkSelected) {
            $chkSelected = new QCheckBox($dtgBind, $strControlId);
            $chkSelected->AddAction(new QClickEvent(), new QJavaScriptAction('isChecked(this.checked);'));
        }


        if($blnEditMode)
        {
            if ($Idedited == $Idobj)
            {
                return '<img src="assets/images/checked_out.png" border="0">'.$chkSelected->Visible=false;
            } else{
                return '<input type="checkbox" disabled>'.$chkSelected->Enabled=false;
            }

        } else {
            return $chkSelected->Render(false);
        }
    }

Any Ideas on how to solve it?

Thanks

#6  |  VexedPanda (Calgary, AB) Canada
Wednesday, July 11, 2007, 8:45 AM PDT

Ack, I have the same issue on datagrids with controls.

I haven't tried it, but it's possible that we could call RenderBegin(false) and RenderEnd(false) within the _Click method...

#7  |  VexedPanda (Calgary, AB) Canada
Wednesday, July 11, 2007, 8:50 AM PDT

Ok, I had to make those functions public, but it does seem to have worked.

The other thing to note is that you're going to have to find a way of cleaning up the output from columns that use HTML. I currently just strip any HTML, but this causes it to lose the state of the checkbox as well, making the Select column in the CSV blank.

#8  |  mmaidana (Mallorca) Spain
Thursday, July 12, 2007, 4:28 AM PDT

mmmm....
OK, I'm not too familiar with qcodo as you (neither php, I asume ) hehehe...
So could please tell me....

1 -  How I should Call the RenderBegin(false) inside the _click Method?. Cause when I try to put it: i get this error:

Fatal error:  Call to protected method QFormBase::RenderBegin() from context 'QDataGridBase' in g:\xampp\xampp\htdocs\mediweb\sam\includes\qcodo\_core\qform\QDataGridBase.class.php on line 710<br />


2- How do you strip the html??

thanks in advance

#9  |  VexedPanda (Calgary, AB) Canada
Thursday, July 12, 2007, 7:54 AM PDT

1) You'll need to edit your QFormBase.inc.php file to change the RenderBegin and RenderEnd functions to public instead of protected.

2) Use strip_tags in the appropriate place to affect PrintCSVRow's output.

#10  |  mmaidana (Mallorca) Spain
Friday, July 13, 2007, 12:58 AM PDT

HI again!!!

It worked with the RenderBegin and End public functions!!

But, I still have the problem, after I clicked on the download link, every ajax on the page doesn't work....

I think you're stuck there also?

Any Ideas?



THanks again!!



Copyright © 2005 - 2013, Quasidea Development, LLC
This open-source framework for PHP is released under the terms of The MIT License.