POUWIEL|COM

JeroenPouwiel

Export a query from Oracle via PHP to Excel

The easiest thing you will find on the net for spooling a resultset from Oracle to an Excel format via php, will probably be that you need to add:

header("Pragma: public");
header("Content-Type:  application/vnd.ms-excel");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=oracle_databases_" . $cdate . ".xls ");
header("Content-Transfer-Encoding: binary ");

to your php/html page. But what if you’d want to have an autofilter on your collumns…?

Then do the following:
make sure the top part of your script consists of the following:

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="https://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="test6_files/filelist.xml">
<link rel=Edit-Time-Data href="test6_files/editdata.mso">
<link rel=OLE-Object-Data href="test6_files/oledata.mso">
<!--[if gte mso 9]><xml>
 <o:DocumentProperties>
 </o:DocumentProperties>
 <o:OfficeDocumentSettings>
  <o:RelyOnVML/>
  <o:AllowPNG/>
  <o:TargetScreenSize>1024x768%lt;/o:TargetScreenSize>
 </o:OfficeDocumentSettings>
</xml><![endif]-->

And for the collumn-headers, add this as formatting:

<th x:autofilter="all"  x:autofilterrange="$A$5:$G$709">HEADING 1</th>
<th x:autofilter="all">HEADING 2</th>
<th x:autofilter="all">HEADING 3</th>
<th x:autofilter="all">HEADING 4</th>

P.s.: autofilterrange is the complete area where you want the autofilter to be active.
speaks for itself, but hey…

Comments are closed.

Categories