Export as Excel
Mon, 11/16/2009 - 19:33
This is a quick and dirty export to Excel XLS.
Just add the code to qdatagrid.class.php and you can use
$this->dtgDatagrid->ExportAsXLS("filename.xls");to export your datagrid into an XLS file.
if there is enough interest, I could make this a plugin.
Here you go:
public function ExportAsXLS($strFilename) {
header( "content-type: text/xml" );
header(sprintf('Content-Disposition: attachment; filename="%s"', $strFilename));
print "<?xml version=\"1.0\"?>\n";
print "<?mso-application progid=\"Excel.Sheet\"?>\n";
print '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"';
print ' xmlns:o="urn:schemas-microsoft-com:office:office"';
print ' xmlns:x="urn:schemas-microsoft-com:office:excel"';
print ' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"';
print ' xmlns:html="http://www.w3.org/TR/REC-html40">';
print ' <DocumentProperties';
print ' xmlns="urn:schemas-microsoft-com:office:office">';
/*
* print ' <Author></Author>';
print ' <LastAuthor></LastAuthor>';
print ' <Created>2005-08-02T04:06:26Z</Created>';
print ' <LastSaved>2005-08-02T04:30:11Z</LastSaved>';
print ' <Company></Company>';
print ' <Version></Version>';
*/
print ' </DocumentProperties>';
print ' <ExcelWorkbook';
print ' xmlns="urn:schemas-microsoft-com:office:excel">';
print ' <WindowHeight>8535</WindowHeight>';
print ' <WindowWidth>12345</WindowWidth>';
print ' <WindowTopX>480</WindowTopX>';
print ' <WindowTopY>90</WindowTopY>';
print ' <ProtectStructure>False</ProtectStructure>';
print ' <ProtectWindows>False</ProtectWindows>';
print ' </ExcelWorkbook>';
print ' <Styles>';
print ' <Style ss:ID="Default" ss:Name="Normal">';
print ' <Alignment ss:Vertical="Bottom"/>';
print ' <Borders/>';
print ' <Font/>';
print ' <Interior/>';
print ' <NumberFormat/>';
print ' <Protection/>';
print ' </Style>';
print ' <Style ss:ID="s21" ss:Name="Hyperlink">';
print ' <Font ss:Color="#0000FF" ss:Underline="Single"/>';
print ' </Style>';
print ' <Style ss:ID="s23">';
print ' <Font x:Family="Swiss" ss:Bold="1"/>';
print ' </Style>';
print ' </Styles>';
print ' <Worksheet ss:Name="Names">';
print ' <Table ss:ExpandedColumnCount="' . (sizeof($this->objColumnArray) + 1) . '"';
print ' ss:ExpandedRowCount="' . (count( $this->DataSource ) + 1) . '"';
print ' x:FullColumns="1" x:FullRows="1">';
print $this->GetHeaderRowXls();
if ($this->objDataSource) {
foreach ($this->objDataSource as $objItem) {
print $this->GetDataGridRowXls($objItem);
}
}
print '</Table>';
print ' <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">';
print ' <Print>';
print ' <ValidPrinterInfo/>';
print ' <HorizontalResolution>300</HorizontalResolution>';
print ' <VerticalResolution>300</VerticalResolution>';
print ' </Print>';
print ' <Selected/>';
print ' <Panes>';
print ' <Pane>';
print ' <Number>3</Number>';
print ' <ActiveRow>1</ActiveRow>';
print ' </Pane>';
print ' </Panes>';
print ' <ProtectObjects>False</ProtectObjects>';
print ' <ProtectScenarios>False</ProtectScenarios>';
print ' </WorksheetOptions>';
print ' </Worksheet>';
print ' </Workbook>';
exit();
}
protected function GetHeaderRowXls() {
$strToReturn = sprintf('<Row ss:StyleID="s23">');
$intColumnIndex = 0;
if ($this->objColumnArray) foreach ($this->objColumnArray as $objColumn) {
$strToReturn .= sprintf('<Cell><Data ss:Type="String">%s</Data></Cell>', $objColumn->Name);
$intColumnIndex++;
}
$strToReturn .= '</Row>';
return $strToReturn;
}
protected function GetDataGridRowXls($objObject) {
// Get the Default Style
// Iterate through the Columns
$strColumnsHtml = '';
foreach ($this->objColumnArray as $objColumn) {
try {
$strHtml = $this->ParseColumnHtml($objColumn, $objObject);
if ($objColumn->HtmlEntities)
$strHtml = QApplication::HtmlEntities($strHtml);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
$strColumnsHtml .= sprintf('<Cell><Data ss:Type="String">%s</Data></Cell>', $strHtml);
}
$strToReturn = sprintf('<Row>%s</Row>', $strColumnsHtml);
return $strToReturn;
}
Just so I'm clear, this only exports the currently displayed page / filter, right?
If you want to extend it to also grab output from all pages, you can take inspiration from this:
http://www.qcodo.com/forums/forum.php/2/2103
Hopefully, one day I'll package that up as a plugin.
Correct VexedPanda, this exports the current datagrid to xls.
And just like you, I ripped the existing functions to spit out XLS fields.
Maybe we should make a combined control "DataGridExporter" or something that can do CSV, XLS, PDF, etc...
K.
Sounds like a great idea. :)
I'm not sure I've got much time right now to work on it, but if you come out with anything, I'll be in line to review it. :)
I'd be interested in something like this, too. A plugin with an QDataGridExplorter object that takes a datagrid, and makes a CSV/XLS file out of it. That'd make a HOT plugin :)
You might want to look into http://phpexcel.codeplex.com/
The guy has made a complete system that can import and export excel files.
The xml type you describe can only be imported but the exporting can be done to CSV, XLS, XLSX, PDF, HTML. Although I haven't tested PDF and HTML myself.
This might be handy ... no? There License is: GNU Library General Public License (LGPL)
OK, I'm not too familiar with qcodo as you (neither php, I asume )
So could please tell me how I should Call the RenderBegin(false) inside the _click Method?. Cause when I try to put it: i get error and How do you strip the html??
Hello,
This is great but does not work for me in Exel 2010.
Has anyone an Excel export simple and effective as this one running?
I also want to get all the current settings exported.
Thanks so much.
Best,
tronics
With some adaption this worked very well!!!
http://qcu.be/content/add-export-csv-datagrid
It would be good to look http://www.nicblue.com/gridnic/, seems to be easy to adapt for QCubed. Recently I found this link, have not yet reached a closer look and explore. How does that sound?
Thank you, for the tip. That grid is really useful - would make a cool plugin for qcubed.
I managed the direct export from qcubed datagrid, which has the advantage that you do not have to configure again for the export.
However this solution pointed out by you is perfect to use in many fields.
You guys might already be aware of this, but just in case: we do have the QDataGridExporterButton plugin (download), which worked pretty well for me.