Export PHP To Excel With PHPExcel

Untuk mengubah ato mengconvert ato menyulap (hehehe) kode PHP ke MS Excel, banyak caranya. Salah satunya dan yang paling mudah dengan bantuan plugin PHPExcel. Sebelumnya download dulu file PHPExcel disini (https://phpexcel.codeplex.com/releases/view/119187). Setelah itu extract file tersebut.

Misalken, kita buat folder exc di htdocs. Taruh file PHPExcel.php dan folder PHPExcel. Dan kita buat file php nya: excel.php

Tulis kode di bawah ini (buat masukin/include/manggil class PHPExcel) di file excel.php:

error_reporting(E_ALL);ini_set('include_path', ini_get('include_path').';PHPExcel/'); 

include 'PHPExcel.php'; 

include 'PHPExcel/Writer/Excel2007.php';

Terusss, tulis lagi kode di bawah ini, untuk manggil class PHPExcel dan bikin font style globalnya:

$objPHPExcel = new PHPExcel(); 

$fonta= new PHPExcel_Style_Font();  

$fonta->setName('Arial');$fonta->setSize(12);  

//yang bikin dokumen ini 

$objPHPExcel->getProperties()->setCreator("Creator"); 

//yang ngubah paling terakhir 

$objPHPExcel->getProperties()->setLastModifiedBy("Modificator"); 

//judul 

$objPHPExcel->getProperties()->setTitle("Title"); 

//subjeknyo 

$objPHPExcel->getProperties()->setSubject("Subject"); 

//deskripsi 

$objPHPExcel->getProperties()->setDescription("Description");

Sekarang, bikin nomer sheet nya (jadi bisa bikin beberapa sheet). Misalnyo sheet 0. Dan atur page setupnya:

$objPHPExcel->setActiveSheetIndex(0); 

$sheet=$objPHPExcel->getActiveSheet(); 

$margin=$sheet->getPageMargins(); 

$mleft= 1.5/2.54; 

$mright= 0.6/2.54; 

$mtop= 4.5/2.54; 

$mbottom= 1.9/2.54; 

$margin->setTop($mtop);  

$margin->setBottom($mbottom); 

$margin->setLeft($mleft); 

$margin->setRight($mright);

Atur page setupnya kalo kepengen lebih lengkap. Contohnye halaman itu landscape ato potrait dll:

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

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

$objPHPExcel->getActiveSheet(0)->getPageSetup()->setFitToPage(true); 

$objPHPExcel->getActiveSheet(0)->getPageSetup()->setFitToWidth(1); 

$objPHPExcel->getActiveSheet(0)->getPageSetup()->setFitToHeight(0);

Atur lebarnya column. Misalken column A lebarnya 10 px, dst:

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

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

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

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

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

Tulis susuatu di column A:

$objPHPExcel->getActiveSheet(0)->SetCellValue('A2', 'Percobaan');

Lanjutkan!!! Ke column berikutnya:

$objPHPExcel->getActiveSheet(0)->SetCellValue('B3', 'PHP To'); 

$objPHPExcel->getActiveSheet(0)->SetCellValue('C4', 'Excel'); 

$objPHPExcel->getActiveSheet(0)->SetCellValue('D5', 'Dengan'); 

$objPHPExcel->getActiveSheet(0)->SetCellValue('E6', 'PHPExcel');

Setelah itu semua, mari kita bikin agar saat kita manggil localhost/exc/excel.php agar langsung dapet file download Excel.xls:

header('Content-Type: application/vnd.ms-excel'); 

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

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

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

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

exit;

Posted in: PHPExcel, Tutorial