Export PHP To Excel With PHPExcel II (Font)

Sikarang kita bikin export PHP ke Excel lagi. Bedanyo dari sebelumnya (export PHP Excel sederhana) adalah menambah style font nya. Yuk, buat filenya, sekarang namanya excelb.php. Nanti yang file yang dibuat excelb.xls.

Sama seperti sebelumnya. Tulis kode seperti ini di excelb.php:

error_reporting(E_ALL);
ini_set('include_path', ini_get('include_path').';PHPExcel/');
include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
$objPHPExcel = new PHPExcel();
$fonta= new PHPExcel_Style_Font();
$fonta->setName('Arial');
$fonta->setSize(12);
$objPHPExcel->getProperties()->setCreator("Creator");
$objPHPExcel->getProperties()->setLastModifiedBy("Modificator");
$objPHPExcel->getProperties()->setTitle("Title");
$objPHPExcel->getProperties()->setSubject("Subject");
$objPHPExcel->getProperties()->setDescription("Description");
$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);
$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);
$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);

Nah setelah itu bikin font style nya seperti ginian:

//Untuk mempertebal tulisan (Bold)
$objPHPExcel->getActiveSheet(0)->getStyle('A2')->getFont()->setBold(true);
//Untuk jenis tulisan dan ukurannya
$objPHPExcel->getActiveSheet(0)->getStyle('B3')->getFont()->setName('Tahoma')->setSize(14);
//Untuk warna tulisan
$objPHPExcel->getActiveSheet(0)->getStyle('C4')->getFont()->getColor()->setRGB('000099');
//Untuk ukuran tulisan dan nambah garis bawah (underline)
$objPHPExcel->getActiveSheet(0)->getStyle('D5')->getFont()->setSize(16)->setUnderline(true);
//Ini gabungan style semua
$objPHPExcel->getActiveSheet(0)->getStyle('E6')->getFont()->setName('Arial')->setSize(18)->setBold(true)->setUnderline(true)->getColor()->setRGB('999900');

Sikarang, tulis di setiap cell nya...

$objPHPExcel->getActiveSheet(0)->SetCellValue('A2', 'Convert');
$objPHPExcel->getActiveSheet(0)->SetCellValue('B3', 'PHP To');
$objPHPExcel->getActiveSheet(0)->SetCellValue('C4', 'Excel');
$objPHPExcel->getActiveSheet(0)->SetCellValue('D5', 'With');
$objPHPExcel->getActiveSheet(0)->SetCellValue('E6', 'PHPExcel');

Bisa juga dibalik. Nulis kode buat isi cell nya terlebih dahulu lalu kode untuk stylenya... Hasilnya sama aja.

Lha terus bikin kode untuk convert:

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="ExcelB.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');  
$objWriter->save('php://output');
exit;

Gimpink bingitz kan... Sekian dulu, nanti nyambung lagi dengan yang laen... Makaciiii...

Read more »

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;

Read more »