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 :









01.//function for XLS

02.function xlsBOF() {

03.echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);

04.return;

05.}

06.

07.function xlsEOF() {

08.echo pack("ss", 0x0A, 0x00);

09.return;

10.}

11.

12.function xlsWriteNumber($Row, $Col, $Value) {

13.echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);

14.echo pack("d", $Value);

15.return;

16.}

17.

18.function xlsWriteLabel($Row, $Col, $Value ) {

19.$L = strlen($Value);

20.echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);

21.echo $Value;

22.return;

23.}



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 :



01.// query database

02.$queabsdetail = "SELECT id, nama, nohp FROM tabel_user";

03.

04.// eksekusi query

05.$exequeabsdetail = mysql_query($queabsdetail);

06.

07.// read the result and insert into new array

08.while($res = mysql_fetch_array($exequeabsdetail)){

09.$data['id'][] = $res['id'];

10.$data['nama'][] = $res['nama'];

11.$data['nohp'][] = $res['nohp'];

12.}

13.

14.$jm = sizeof($data['id']);

15.// sending header

16.header("Pragma: public" );

17.header("Expires: 0" );

18.header("Cache-Control: must-revalidate, post-check=0, pre-check=0" );

19.header("Content-Type: application/force-download" );

20.header("Content-Type: application/octet-stream" );

21.header("Content-Type: application/download" );;

22.header("Content-Disposition: attachment;filename=fileexcel.xls " );

23.header("Content-Transfer-Encoding: binary " );

24.xlsBOF();

25.xlsWriteLabel(0,0,"Sample : Export To Excel" );

26.xlsWriteLabel(2,0,"Jumlah Data" );

27.xlsWriteLabel(2,1,$jm); xlsWriteLabel(4,1,"No" );

28.

29.//menulis pada cell (baris 4 kolom 1)

30.xlsWriteLabel(4,2,"Nama" );

31.xlsWriteLabel(4,3,"No. Hp" );

32.$xlsRow = 5;

33.

34.//posisi awal

35.for ($y=0;$y<$jm;$y++){ //perulangan untuk baca data

36.++$i;

37.xlsWriteNumber($xlsRow,1,"$i" );

38.xlsWriteLabel($xlsRow,2,$data['nama'][$y]);

39.xlsWriteLabel($xlsRow,3,$data['nohp'][$y]);

40.$xlsRow++;

41.}

42.xlsEOF();

43.exit();



Semoga bermanfaat..

Share on FacebookPlus on Google+

SUBSCRIBE TO OUR NEWSLETTER