Tuesday, July 6, 2010

script mengubah data mysql ke file excel (php)

//function untuk menulis ke file excel (langsung dicopy aja...)
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;
}


//koneksi ke database
mysql_connect('localhost', 'root', '');
mysql_select_db('kepegawaian');

// query untuk select database
$queabsdetail = "SELECT idKaryawan, namaKaryawan, passwordKaryawan FROM karyawan";

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

// memasukkan hasil query ke dalam array
while($res = mysql_fetch_array($exequeabsdetail)){
$data['idKaryawan'][] = $res['idKaryawan'];
$data['namaKaryawan'][] = $res['namaKaryawan'];
$data['passwordKaryawan'][] = $res['passwordKaryawan'];
} 
//mengethui jumlah record
$jm = sizeof($data['idKaryawan']);

// sending header(ini yang paling penting... untuk mendownload file dan mengubah ke excel)
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,"Contoh :mengubah data mysql ke excel" );
xlsWriteLabel(2,0,"Jumlah Record" );
xlsWriteLabel(2,1,$jm); xlsWriteLabel(4,1,"No" );

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

//posisi awal menulis data (baris 5 kolom 1)
for ($y=0;$y<=$jm;$y++){ //perulangan untuk baca data
++$i;
xlsWriteNumber($xlsRow,1,"$i" );
xlsWriteLabel($xlsRow,2,$data['idKaryawan'][$y]);
xlsWriteLabel($xlsRow,3,$data['namaKaryawan'][$y]);
xlsWriteLabel($xlsRow,4,$data['passwordKaryawan'][$y]);
$xlsRow++;
}
xlsEOF();
exit();


No comments:

Post a Comment