Cara menggunakan phpexcel top align

Mungkin bagi newbi-newbi termasuk saya sering bingung jika berhadapan dengan reporting atau membuat laporan di PHP. Baik itu laporan dalam bentuk PDF atau EXCEL.

Saya sendiri pernah mencoba 3 (tiga) library untuk membuat laporan dalam bentuk PDF (bisa dilihat disni). Untuk laporan atau reporting dalam bentuk excel ada satu library PHP yang sangat mengagumkan, yaitu PHPExcel.

Sebelumnya saya biasanya memakai cara biasa untuk membuat laporan dalam excel, cara ini termasuk cara yang paling mudah bagi saya:

Code:

<?php

// isi $excel akan bernilai true apabila ditemukan parameter get dengan nama 'excel'

$excel = isset($_GET['excel']);

if($excel):

 // buat nama file unique untuk di download

 $filename = 'export-'.date('YmdHis'
;

 // dengan perintah di bawah ini akan memunculkan dialog download di browser anda

 header("Content-type: application/x-msdownload");

 // perintah di bawah untuk menentukan nama file yang akan di download

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

else:

 // tag header di html disembuyikan apabila sedang convert html to excel

?>

<html>

<head>

<style type="text/css">

/* setting format tabel */

table {

 font-family: Verdana;

 font-size: 8pt;

 border-width: 1px;

 border-style: solid;

 border-color: #ccd2d2;

 border-collapse: collapse;

 background-color: #f9f9f9;

}

th {

 color: #f00;

 font-size: 8pt;

 text-transform: uppercase;

 text-align: center;

 padding: 0.5em;

 border-width: 1px;

 border-style: solid;

 border-color: #000;

 border-collapse: collapse;

 background-color: #000;

}

td {

 padding: 0.1em;

 color: #272727;

 vertical-align: top;

 border-width: 1px;

 border-style: solid;

 border-color: #000;

 border-collapse: collapse;

 font-size: 8pt;

}

</style>

<title>Membuat Report Excel Dari HTML Dengan PHP</title>

</head>

<body>

<?php endif; ?>

<?php if(!$excel): ?>

<!-- Tombol di bawah dan penutup html hanya akan dipanggil apabila dalam kondisi melihat data,

 tapi ketika menampilkan file excel tombol ini disembunyikan -->

<div align="left" style="margin-top:15px">

 <input type="button">

</div>

<br>

<?php endif; ?>

<?php

//setting koneksi anda

$server = "localhost";

$username = "root";

$password = "";

$db = "excel";

$tbl = "customer";



$koneksi = mysql_connect($server,$username,$password);

mysql_select_db($db, $koneksi) or die("Cannot connect to database..");



// create tabel dengan php

echo "<div align='left'><img src='http://smartcoderszone.com/wp-content/uploads/2010/09/generate-excel-report-with-php-and-mysql2.jpg' width='100' height='150'><div><br><br><br><br><br>";

echo "<p> </p>";

echo "<div align='left' style='color='#ff0000'><strong>DATA PEGAWAI</strong>";

echo "<table border='1'>";

echo " <tr bgcolor='#cccccc'>";

echo " <th>NO</th>";

echo " <th>company_name</th>";

echo " <th>email</th>";

echo " <th>city</th>";

echo " </tr>";



 $str = " SELECT * FROM ".$db.".".$tbl.

 " order by ID desc ";

 $query = mysql_query($str);

 if($query && mysql_num_rows($query) > 0){

 $no = $posisi+1;

 while($row = mysql_fetch_object($query)){

 //if($bgcolor=='#f1f1f1'
{$bgcolor='#ffffff';}

 //else{$bgcolor='#f1f1f1';}

 echo "<tr bgcolor=$bgcolor>";

 echo " <td align='center' height='18'><strong>$no</strong></td>";

 echo " <td>{$row->company_name}?</td>";

 echo " <td>{$row->email}?</td>";

 echo " <td>{$row->city}?</td>";

 $no++;

 }

 }

 //echo $str;

echo " </tr>";

echo "</table>";

echo "</div>";

?>

</body>

</html>

Cara yang lebih rumit tapi lebih bisa di custom, sebelum saya menemukan phpexcel saya sering pakai cara ini

dan sepertinya cara ini sering digunakan karena kebanyakan tutorial di internet memakai cara ini:

Code:

<?php



// nama file



$namaFile = "report.xls";



// Function penanda awal file (Begin Of File) Excel



function xlsBOF() {

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

return;

}



// Function penanda akhir file (End Of File) Excel



function xlsEOF() {

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

return;

}



// Function untuk menulis data (angka) ke cell excel



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

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

echo pack("d", $Value);

return;

}



// Function untuk menulis data (text) ke cell excel



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

$L = strlen($Value);

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

echo $Value;

return;

}



// header file 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 untuk nama file

header("Content-Disposition: attachment;

 filename=".$namaFile."");



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



// memanggil function penanda awal file excel

xlsBOF();



// ------ membuat kolom pada excel --- //



// mengisi pada cell A1 (baris ke-0, kolom ke-0)

xlsWriteLabel(0,0,"NO");



// mengisi pada cell A2 (baris ke-0, kolom ke-1)

xlsWriteLabel(0,1,"NIP");



// mengisi pada cell A3 (baris ke-0, kolom ke-2)

xlsWriteLabel(0,2,"NAMA MAHASISWA");



// mengisi pada cell A4 (baris ke-0, kolom ke-3)

xlsWriteLabel(0,3,"EMAIL");



// mengisi pada cell A5 (baris ke-0, kolom ke-4)

xlsWriteLabel(0,4,"KELAMAMIN");



// -------- menampilkan data --------- //



// koneksi ke mysql



mysql_connect("localhost", "root", "");

mysql_select_db("schoolv2");



// query menampilkan semua data



$query = "SELECT * FROM datasiswa";

$hasil = mysql_query($query);



// nilai awal untuk baris cell

$noBarisCell = 1;



// nilai awal untuk nomor urut data

$noData = 1;



while ($data = mysql_fetch_array($hasil))

{

 // menampilkan no. urut data

 xlsWriteNumber($noBarisCell,0,$noData);



// menampilkan data nim

 xlsWriteLabel($noBarisCell,1,$data['nip']);



// menampilkan data nama mahasiswa

 xlsWriteLabel($noBarisCell,2,$data['nama']);



// menampilkan data nilai

 xlsWriteNumber($noBarisCell,3,$data['email']);



// menentukan status kelulusan

 if ($data['kelamin'] >= 60) $status = "LULUS";

 else $status = "TIDAK LULUS";



// menampilkan status kelulusan

 xlsWriteLabel($noBarisCell,4,$status);



// increment untuk no. baris cell dan no. urut data

 $noBarisCell++;

 $noData++;

}



// memanggil function penanda akhir file excel

xlsEOF();

exit();



?>

Setelah dituntut untuk membuat reporting / laporan yang lebih wonderfull saya merasa kedua cara diatas kurang cocok.
Kendala saya saat itu adalah bagaimana menambahkan kop atau header dan logo/image atau gambar dalam excel.
Akhirnya saya searching di google.com untuk menemukan cara yang lebih bagus dan saya menemukan library phpexcel.
berikut ini script phpexcel dan screenshoot nya yang pernah saya buat. ohy saya juga menambahkan fitur filter dan sorting di excel yang saya buat. kelebihan phpexcel yang lain adalah kita bisa menyimpannya sebagai file excel 2007.

Lanjut di bawah gan

Code:

<?php

/**

 * PHPExcel

 *

 * Copyright (C) 2006 - 2012 PHPExcel

 *

 * This library is free software; you can redistribute it and/or

 * modify it under the terms of the GNU Lesser General Public

 * License as published by the Free Software Foundation; either

 * version 2.1 of the License, or (at your option) any later version.

 *

 * This library is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU

 * Lesser General Public License for more details.

 *

 * You should have received a copy of the GNU Lesser General Public

 * License along with this library; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

 *

 * @category PHPExcel

 * @package PHPExcel

 * @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)

 * @license [url]http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt[/url] LGPL

 * @version 1.7.7, 2012-05-19

 */



$tahun = $_POST['tahun'];

/** Error reporting */

error_reporting(E_ALL);



date_default_timezone_set('Europe/London'
;



/** Include PHPExcel */

require_once '../Classes/PHPExcel.php';



// Create new PHPExcel object

$objPHPExcel = new PHPExcel();



// Set document properties

$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")

 ->setLastModifiedBy("Data Siswa SMPN 5 ********")

 ->setTitle("Data Siswa SMPN 5 ********")

 ->setSubject("Data Siswa SMPN 5 ********")

 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")

 ->setKeywords("office 2007 openxml php")

 ->setCategory("Test result file");



// Create the worksheet

$objPHPExcel->setActiveSheetIndex(0);



$objPHPExcel->getActiveSheet()->setCellValue('A7', "No")

 ->setCellValue('B7', "NIS")

 ->setCellValue('C7', "NISN")

 ->setCellValue('D7', "Nama")

 ->setCellValue('E7', "J.Kelamin")

 ->setCellValue('F7', "Tmp&Tgl;.Lahir")

 ->setCellValue('G7', "Alamat")

 ->setCellValue('H7', "No.Telp/HP")

 ->setCellValue('I7', "Agama")

 ->setCellValue('J7', "No.Test")

 ->setCellValue('K7', "Kelas")

 ->setCellValue('L7', "Nama Ayah")

 ->setCellValue('M7', "Pendapatan Ayah");



$server = "localhost";

$username = "root";

$password = "";

$db = "schoolv2";



$koneksi = mysql_connect($server,$username,$password);

mysql_select_db($db, $koneksi) or die("Cannot connect to database..");



$SQL = mysql_query("SELECT datasiswa.nisn,nis,namalengkap,jeniskelamin, tempatlahirsiswa,tgllahirsiswa,agamasiswa,nomortest, alamattinggalsiswa,notelpsiswa,nohpsiswa,datanoabsen.tingkat,namaayah,pendapatanayah

 FROM schoolv2.datasiswa,schoolv2.datanoabsen

 WHERE datasiswa.idsiswa=datanoabsen.idsiswa AND datanoabsen.tahun='$tahun' ORDER BY nis");



$totJML = mysql_num_rows($SQL);



$dataArray= array();

$no=0;

while($row = mysql_fetch_array($SQL, MYSQL_ASSOC)){

 $no++;

 $row_array['no'] = $no;

 $row_array['nis'] = $row['nis'];

 $row_array['nisn'] = $row['nisn'];

 $row_array['namalengkap'] = $row['namalengkap'];

 $row_array['jeniskelamin'] = $row['jeniskelamin'];

 $row_array['ttl'] = $row['tempatlahirsiswa'].", ".$row['tgllahirsiswa'];

 $row_array['alamattinggalsiswa'] = $row['alamattinggalsiswa'];

 $row_array['notelpsiswa'] = $row['nohpsiswa'];

 $row_array['agamasiswa'] = $row['agamasiswa'];

 $row_array['nomortest'] = $row['nomortest'];

 $row_array['tingkat'] = $row['tingkat'];

 $row_array['namaayah'] = $row['namaayah'];

 $row_array['pendapatanayah'] = $row['pendapatanayah'];

 array_push($dataArray,$row_array);

}

$nox=$no+7;

$objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A8'
;



// Set page orientation and size

$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);

$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LEGAL);

$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.75);

$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.75);

$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75);

$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.75);

$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage; &P of &N'
;



// Set title row bold;

$objPHPExcel->getActiveSheet()->getStyle('A7:M7'
->getFont()->setBold(true);

// Set fills

$objPHPExcel->getActiveSheet()->getStyle('A7:M7'
->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

$objPHPExcel->getActiveSheet()->getStyle('A7:M7'
->getFill()->getStartColor()->setARGB('FF808080'
;



$objPHPExcel->getActiveSheet()->getColumnDimension('D'
->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('F'
->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('A'
->setWidth(4.43);

$objPHPExcel->getActiveSheet()->getColumnDimension('B'
->setWidth(6.29);

$objPHPExcel->getActiveSheet()->getColumnDimension('C'
->setWidth(11.14);

$objPHPExcel->getActiveSheet()->getColumnDimension('D'
->setWidth(21);

$objPHPExcel->getActiveSheet()->getColumnDimension('E'
->setWidth(9.14);

$objPHPExcel->getActiveSheet()->getColumnDimension('F'
->setWidth(16.14);

$objPHPExcel->getActiveSheet()->getColumnDimension('G'
->setWidth(23);

$objPHPExcel->getActiveSheet()->getColumnDimension('H'
->setWidth(11);

$objPHPExcel->getActiveSheet()->getColumnDimension('I'
->setWidth(6.86);

$objPHPExcel->getActiveSheet()->getColumnDimension('J'
->setWidth(7.43);

$objPHPExcel->getActiveSheet()->getColumnDimension('K'
->setWidth(6.29);

$objPHPExcel->getActiveSheet()->getColumnDimension('L'
->setWidth(15.29);

$objPHPExcel->getActiveSheet()->getColumnDimension('M'
->setWidth(11.1);



// Set autofilter

 // Always include the complete filter range!

 // Excel does support setting only the caption

 // row, but that's not a best practise...

$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());



// Set active sheet index to the first sheet, so Excel opens this as the first sheet

$objPHPExcel->setActiveSheetIndex(0);



$sharedStyle1 = new PHPExcel_Style();

$sharedStyle2 = new PHPExcel_Style();



$sharedStyle1->applyFromArray(

 array('borders' => array(

 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),

 'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),

 'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM),

 'left' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)

 ),

 ));



$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A7:M$nox");



// Set style for header row using alternative method

$objPHPExcel->getActiveSheet()->getStyle('A7:M7'
->applyFromArray(

 array(

 'font' => array(

 'bold' => true

 ),

 'alignment' => array(

 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,

 ),

 'borders' => array(

 'top' => array(

 'style' => PHPExcel_Style_Border::BORDER_THIN

 )

 ),

 'fill' => array(

 'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,

 'rotation' => 90,

 'startcolor' => array(

 'argb' => 'FFA0A0A0'

 ),

 'endcolor' => array(

 'argb' => 'FFFFFFFF'

 )

 )

 )

);



// Add a drawing to the worksheet

$objDrawing = new PHPExcel_Worksheet_Drawing();

$objDrawing->setName('Logo'
;

$objDrawing->setDescription('Logo'
;

$objDrawing->setPath('../images/logo2.png'
;

$objDrawing->setCoordinates('B2'
;

$objDrawing->setHeight(120);

$objDrawing->setWidth(120);

$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());



$objPHPExcel->getActiveSheet()->getStyle('A7:M1000'
->getFont()->setName('Arial'
;

$objPHPExcel->getActiveSheet()->getStyle('A7:M1000'
->getFont()->setSize(7);



// Merge cells

$objPHPExcel->getActiveSheet()->mergeCells('D2:M2'
;

$objPHPExcel->getActiveSheet()->setCellValue('D2', "PEMERINTAH KOTA ********");

$objPHPExcel->getActiveSheet()->mergeCells('D3:M3'
;

$objPHPExcel->getActiveSheet()->setCellValue('D3', "DINAS PENDIDIKAN");

$objPHPExcel->getActiveSheet()->mergeCells('D4:M4'
;

$objPHPExcel->getActiveSheet()->setCellValue('D4', "SMPN 5 ********");

$objPHPExcel->getActiveSheet()->mergeCells('D5:M5'
;

$objPHPExcel->getActiveSheet()->setCellValue('D5', "Jl.WR.Supratman 12, Telp. 482713 ********");

$objPHPExcel->getActiveSheet()->mergeCells('D6:M6'
;

$objPHPExcel->getActiveSheet()->setCellValue('D6', "REKAPITULASI DATA SISWA TAHUN $tahun");

$objPHPExcel->getActiveSheet()->getStyle('D2:M6'
->getFont()->setName('Arial'
;

$objPHPExcel->getActiveSheet()->getStyle('D2:M5'
->getFont()->setSize(18);

$objPHPExcel->getActiveSheet()->getStyle('D6'
->getFont()->setSize(22);

$objPHPExcel->getActiveSheet()->getStyle('D2:M6'
->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->getStyle('A2:M6'
->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);



// Redirect output to a clients web browser (Excel2007)

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
;

header('Content-Disposition: attachment;filename="dataSiswa"'.date("d-F-Y").'".xlsx"'
;

header('Cache-Control: max-age=0'
;



$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'
;

$objWriter->save('php://output'
;

exit;



// Save Excel 2007 file

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'
;

$objWriter->save(str_replace('.php', '.xlsx', __FILE__));