Export To Excel

Export to Excel mengambil data dari database yang ada.
pertama buat skrip dengan nama export.php sourcenya seperti berikut  :

<?php

$filename = "Task List";
header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
header("Content-Disposition: inline; filename=\"" . $filename . ".xls\""); //rubah bentuk jika ingin mengconvert ke bentuk lain tinggal ganti . (dotnya)
require ("xml_parsing.php");

//include "config_conn.php";
MYSQL_CONNECT("localhost", "root", "");
mysql_select_db("test");
$q = "select * nyoba";

$cb[1] = array("Data Coba");
$cb[2] = array("Tujuan", "Tujuan Description");
if (isset($_GET[Tujuan1])) {
    $q .= " where Tujuan_description='" . $_GET['Tujuan1'] . "'";
}
$qry = mysql_query($q);
$i = 1;
while ($r = mysql_fetch_array($qry)) {
    $val4[$i] = $r[tujuan];
    $val5[$i] = $r[Tujuan_description];
    $i++;
    $n = $i;
};

for ($i = 1; $i <= $n - 1; $i++) {
    $cb[] = array($val4[$i],
                  $val5[$i]);
}
echo json_encode($hasil);
$xls = new Excel_XML;
$xls->addArray($cb);
$xls->generateXML();
?>

Kemudian buat parsingan dengan nama xml_parsing.php sourcenya sebagai berikut  :

<?php
class Excel_XML
{
    private $header = "<?xml version=\"1.0\" encoding=\"UTF-8\"?\>
    <?mso-application progid=\"Excel.Sheet\"?\>
    <Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"
    xmlns:o=\"urn:schemas-microsoft-com:office:office\"
    xmlns:x=\"urn:schemas-microsoft-com:office:excel\"
    xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"
    xmlns:html=\"http://www.w3.org/TR/REC-html40\">
    <Styles>
        <Style ss:ID=\"Default\" ss:Name=\"Normal\">
        <Alignment ss:Vertical=\"Bottom\"/>
        <Borders/>
        <Font/>
        <Interior/>
        <NumberFormat/>
        <Protection/>
    </Style>
    <Style ss:ID=\"isi\">
        <Borders>
        <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>
        <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>
        <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>
        <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>
        </Borders>
    </Style>
    <Style ss:ID=\"judul\">
        <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>
        <Borders>
        <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>
        <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>
        <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>
        <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>
        </Borders>
        <Font x:Family=\"Swiss\" ss:Bold=\"1\"/>
    </Style>
    <Style ss:ID=\"header\">
        <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>
        <Borders>
        <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>
        <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>
        <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>
        <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>
        </Borders>
        <Font x:Family=\"Swiss\" ss:Bold=\"1\"/>
        <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>
        </Style>
    </Styles>";
    private $footer = "</Workbook>";
    private $lines = array ();
    private $worksheet_title = "Coba"; //JUdul Bawah pada Excell
    private function addRow ($array, $baris)
    {
        $cells = "";
        foreach ($array as $k => $v):
        $cells .= "<Cell".$baris."><Data ss:Type=\"String\">" . utf8_encode($v) . "</Data></Cell>\n";
        endforeach;
        $this->lines[] = "<Row>\n" . $cells . "</Row>\n";
    }
   
    public function addArray ($array)
    {
        foreach ($array as $k => $v):
        if ($k==1){
            $baris = " ss:MergeAcross=\"2\" ss:StyleID=\"judul\"";  //Nambah baris halaman Judul Di lebihi kalau mengisi
        }
        else if ($k==2){
            $baris = " ss:StyleID=\"header\"";
        }
        else {
            $baris = " ss:StyleID=\"isi\"";
        }
        $this->addRow ($v, $baris);
        endforeach;
    }
   
    public function setWorksheetTitle ($title)
    {
        $title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
        $title = substr ($title, 0, 31);
        $this->worksheet_title = $title;
    }
   
    function generateXML ()
    {
        echo stripslashes ($this->header);
        echo "\n<Worksheet ss:Name=\"" . $this->worksheet_title . "\">\n<Table>\n";
        //echo "<Column ss:Index=\"1\" ss:AutoFitWidth=\"0\" />\n";
        echo implode ("\n", $this->lines);
        echo "</Table>\n</Worksheet>\n";
        echo $this->footer;
    }
}
?>

Kemudian panggil localhost/export.php
Selanjutnya lihat hasilnya sperti berikut :

Selamat Mencoba.

Read More