Membuat Laporan PHP CodeIgniter dengan PHPExcel

Membuat Laporan PHP CodeIgniter dengan PHPExcel

Pernahkah sobat diminta membuat php report codeigniter dengan ouput Excel pastinya semuanya mengalami nah berikut ini merupakan contoh sedikit kasus yang pernah saya alami ketika membuat report excel yang sedikit komplek.

Keyword search: belajar php dasar,belajar php pemula,belajar php mysql,belajar php pdf,belajar php dasar pdf,belajar php online,belajar php dari dasar sampai mahir,belajar php dari nol,belajar php 7,cara belajar php,ebook belajar php,syntax belajar php,belajar phpmyadmin,belajar oop php,belajar bahasa pemrograman php,belajar membuat website dengan php,belajar coding php,belajar crud php,belajar mvc php,belajar array php,belajar function php,belajar syntax php,belajar api php,belajar php dasar pdf,belajar php mysql,belajar php dari dasar sampai mahir,belajar php w3schools,belajar html,contoh program php sederhana,concept of php,tutorial php

Pertama saya menggunakan phpreport salah satu thrid party dari phpexcel yang menurut saya sangat powerfull tapi saat berhadapan dengan sedikit kasus bentuk tampilan phpreport tidak bisa karena format templatenya sudah ditentukan dari sananya.

lansung saja ke pembahasan

#1 pertama kita panggil dahulu library phpexcel
$this->load->library('excel');

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
ini_set('error_reporting', E_STRICT);
require_once APPPATH."/third_party/phpreport/vendor/autoload.php";
class Excel extends PHPExcel{
public function __construct(){
   parent::__construct();
}
} 

#2 kedua melakukan draw phpexcel sebagai berikut

$this->load->model('developer/Devcp_model','company');
        $company_info = $this->company->get_info();
#COLOR
$color_gray = array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => 'BFBFBF')
)
);
$color_orange = array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => 'FFDA65')
)
);
$color_red = array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => 'FF0000')
)
);
#BORDER_THIN
$styleArray = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
$styleCenter = array(
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
)
);
//set active sheet
$this->excel->setActiveSheetIndex(0);
$this->excel->getActiveSheet()->setTitle("Laporan Buku Besar");
//SET PERUSAHAAN
$this->excel->getActiveSheet()->mergeCells('A1:H1');
$this->excel->getActiveSheet()->setCellValue('A1',isset($company_info['pt_name'])? $company_info['pt_name'] : '');
$this->excel->getActiveSheet()->getStyle("A1")->getFont()->setSize(14);
$this->excel->getActiveSheet()->getStyle("A1")->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle("A1:H1")->applyFromArray($styleCenter);
//SET PERIODE
$this->excel->getActiveSheet()->mergeCells('A2:H2');
$this->excel->getActiveSheet()->setCellValue('A2', 'Periode: '.$tgl_mulai.' s/d '.$tgl_akhir);
$this->excel->getActiveSheet()->getStyle("A2")->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle("A2")->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle("A2:H2")->applyFromArray($styleCenter);
//SET USER
$this->excel->getActiveSheet()->mergeCells('A3:H3');
$this->excel->getActiveSheet()->setCellValue('A3', 'User: '.$this->user->getName());
$this->excel->getActiveSheet()->getStyle("A3")->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle("A3")->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle("A3:H3")->applyFromArray($styleCenter);
#SET WIDTH
$this->excel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$this->excel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(20); $this->excel->getActiveSheet()->getStyle("A4:H4")->getFont()->setSize(10);
$this->excel->getActiveSheet()->getStyle("A4:H4")->getFont()->setBold(true);
$this->excel->getActiveSheet()->setCellValue('A4', "Tgl. Transaksi");
$this->excel->getActiveSheet()->setCellValue('B4', "Kode Akun");
$this->excel->getActiveSheet()->setCellValue('C4', "Nama Akun");
$this->excel->getActiveSheet()->setCellValue('D4', "Uraian");
$this->excel->getActiveSheet()->setCellValue('E4', "Debet");
$this->excel->getActiveSheet()->setCellValue('F4', "Kredit");
$this->excel->getActiveSheet()->setCellValue('G4', "Saldo Debet");
$this->excel->getActiveSheet()->setCellValue('H4', "Saldo Kredit");
#ROW
$endRow = 0;
$total_debet=0; $total_kredit=0;
if(isset($results) && count($results)>0){
$inc = 5;
foreach($results as $r){
$total_debet = $total_debet+$r->debet;
$total_kredit = $total_kredit+$r->kredit;
$coa_info = $this->coa->info(array('kode'=>$r->kode_akun));
$this->excel->getActiveSheet()->setCellValue('A'.$inc, $r->tanggal);
$this->excel->getActiveSheet()->setCellValue('B'.$inc, $coa_info['kode']);
$this->excel->getActiveSheet()->setCellValue('C'.$inc, $coa_info['nama']);
$this->excel->getActiveSheet()->setCellValue('D'.$inc, $r->uraian);
$this->excel->getActiveSheet()->getStyle('E'.$inc)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$this->excel->getActiveSheet()->setCellValueExplicit('E'.$inc, number_format($r->debet,0,"",","),PHPExcel_Cell_DataType::TYPE_STRING);
$this->excel->getActiveSheet()->getStyle('F'.$inc)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$this->excel->getActiveSheet()->setCellValueExplicit('F'.$inc, number_format($r->kredit,0,"",","),PHPExcel_Cell_DataType::TYPE_STRING);
$this->excel->getActiveSheet()->getStyle('G'.$inc)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
if(!empty($total_debet)){
$total_sisa_debet = abs($total_debet-$total_kredit);
$this->excel->getActiveSheet()->setCellValueExplicit('G'.$inc, number_format($total_sisa_debet,0,"",","),PHPExcel_Cell_DataType::TYPE_STRING);
}else{
$this->excel->getActiveSheet()->setCellValueExplicit('G'.$inc,number_format(0,0,"",","),PHPExcel_Cell_DataType::TYPE_STRING);
}
$this->excel->getActiveSheet()->getStyle('H'.$inc)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
if(!empty($total_kredit)){
$total_sisa_kredit = abs($total_debet-$total_kredit);
$this->excel->getActiveSheet()->setCellValueExplicit('H'.$inc, number_format($total_sisa_kredit,0,"",","),PHPExcel_Cell_DataType::TYPE_STRING);
}else{
$this->excel->getActiveSheet()->setCellValueExplicit('H'.$inc,number_format(0,0,"",","),PHPExcel_Cell_DataType::TYPE_STRING);
}
$inc++;
}
$endRow=$inc;
}
$this->excel->getActiveSheet()->mergeCells('A'.$endRow.':D'.$endRow);
$this->excel->getActiveSheet()->getStyle('A'.$endRow)->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle('A'.$endRow)->getFont()->setBold(true);
$this->excel->getActiveSheet()->setCellValue('A'.$endRow, 'TOTAL: ');
$this->excel->getActiveSheet()->getStyle('E'.$endRow.':H'.$endRow)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('E'.$endRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$this->excel->getActiveSheet()->setCellValueExplicit('E'.$endRow, number_format($total_debet,0,"",","),PHPExcel_Cell_DataType::TYPE_STRING);
$this->excel->getActiveSheet()->getStyle('F'.$endRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$this->excel->getActiveSheet()->setCellValueExplicit('F'.$endRow, number_format($total_kredit,0,"",","),PHPExcel_Cell_DataType::TYPE_STRING);
$this->excel->getActiveSheet()->getStyle('G'.$endRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
if(!empty($total_debet)){
$total_sisa_debet = abs($total_debet-$total_kredit);
$this->excel->getActiveSheet()->setCellValueExplicit('G'.$endRow, number_format($total_sisa_debet,0,"",","),PHPExcel_Cell_DataType::TYPE_STRING);
}else{
$this->excel->getActiveSheet()->setCellValueExplicit('G'.$endRow,number_format(0,0,"",","),PHPExcel_Cell_DataType::TYPE_STRING);
}
$this->excel->getActiveSheet()->getStyle('H'.$endRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
if(!empty($total_kredit)){
$total_sisa_kredit = abs($total_debet-$total_kredit);
$this->excel->getActiveSheet()->setCellValueExplicit('H'.$endRow, number_format($total_sisa_kredit,0,"",","),PHPExcel_Cell_DataType::TYPE_STRING);
}else{
$this->excel->getActiveSheet()->setCellValueExplicit('H'.$endRow,number_format(0,0,"",","),PHPExcel_Cell_DataType::TYPE_STRING);
}
$filename='report-buku-besar.xls';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0'); //no cache
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5'); 
$objWriter->save('php://output');

kasus ini saya gunakan untuk pembuatan aplikasi akunting untuk report buku besar. Jika ada pertanyaan silahkan tinggalkan dikomentar. thk