Eksport data MySQL ke Ms. Excel dengan PHP

Beberapa waktu yang lalu saya sempet jalan-jalan ke sini, maksud hati mau download appserv, dan rupanya ada artikel tentang eksport data MySQL ke Ms. Excel (XLS format) dengan PHP, dan rasanya ini akan sangat bermanfaat untuk (misalnya) mencetak laporan yang bisa diedit dengan mudah. Berikut kutipan kodenya :

//function for XLS
function xlsBOF() {
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}

function xlsEOF() {
echo pack("ss", 0x0A, 0x00);
return;
}

function xlsWriteNumber($Row, $Col, $Value) {
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}

function xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}

Kemudian kita tambahkan header agar output bisa didownload dalam bentuk XLS format, serta query ke database dan hasil query diletakkan pada cell-cell di spreadsheet. Kutipan kodenya :

// query database
$queabsdetail = "SELECT id, nama, nohp FROM tabel_user";

// eksekusi query
$exequeabsdetail = mysql_query($queabsdetail);

// read the result and insert into new array
while($res = mysql_fetch_array($exequeabsdetail)){
$data['id'][] = $res['id'];
$data['nama'][] = $res['nama'];
$data['nohp'][] = $res['nohp'];
} 

$jm = sizeof($data['id']);
// sending header
header("Pragma: public" );
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=fileexcel.xls " );
header("Content-Transfer-Encoding: binary " );
xlsBOF();
xlsWriteLabel(0,0,"Sample : Export To Excel" );
xlsWriteLabel(2,0,"Jumlah Data" );
xlsWriteLabel(2,1,$jm); xlsWriteLabel(4,1,"No" );

//menulis pada cell (baris 4 kolom 1)
xlsWriteLabel(4,2,"Nama" );
xlsWriteLabel(4,3,"No. Hp" );
$xlsRow = 5;

//posisi awal
for ($y=0;$y<$jm;$y++){ //perulangan untuk baca data
++$i;
xlsWriteNumber($xlsRow,1,"$i" );
xlsWriteLabel($xlsRow,2,$data['nama'][$y]);
xlsWriteLabel($xlsRow,3,$data['nohp'][$y]); 
$xlsRow++;
}
xlsEOF();
exit();

Semoga bermanfaat..


Warning: file_get_contents(https://graph.facebook.com/comments/?ids=http://fahmijafar.net/2007/02/14/eksport-data-mysql-ke-ms-excel-dengan-php/): failed to open stream: HTTP request failed! HTTP/1.1 400 Bad Request in /home/fahmij01/public_html/wp-content/plugins/facebook-like-and-comment/comments.php on line 17

Warning: Invalid argument supplied for foreach() in /home/fahmij01/public_html/wp-content/plugins/facebook-like-and-comment/comments.php on line 19