Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function table2Excel($company,$sheetTitle,$data,$fieldCaption){
- /* $sheetTitle -> judul worksheet excel
- * $data -> dataset array dari hasil 'SELECT' query
- * $fieldCaption -> data array untuk header kolom
- */
- if (count($data)>0) {
- $objPHPExcel = new PHPExcel();
- // set properties
- $objPHPExcel->getProperties()
- ->setCreator("Export to Excel Generator")
- ->setLastModifiedBy("SYSTEM")
- ->setTitle("Office 2007 XLSX - Export to Excel")
- ->setSubject("Office 2007 XLSX - Export to Excel")
- ->setDescription($sheetTitle)
- ->setKeywords("")
- ->setCategory("Export to Excel");
- $objset = $objPHPExcel->setActiveSheetIndex(0);
- $objget = $objPHPExcel->getActiveSheet();
- $objget->setTitle($sheetTitle); //sheet title
- $val = $fieldCaption;
- array_unshift($val, 'No');
- // style for column header
- $headerStyle = array(
- 'alignment'=>array(
- 'horizontal' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
- ),
- 'fill'=>array( // background style
- 'type'=>PHPExcel_Style_Fill::FILL_SOLID,
- 'color'=>array('rgb'=>'92d050')
- ),
- 'font'=>array( // font style
- 'color'=>array('rgb'=>'000000'),
- 'bold'=>true,
- 'size'=>12
- ),
- 'borders'=>array( // border style
- 'top' => array(
- 'style' => PHPExcel_Style_Border::BORDER_THIN,
- 'color' => array('argb' => 'FF000000'),
- ),
- 'left' => array(
- 'style' => PHPExcel_Style_Border::BORDER_THIN,
- 'color' => array('argb' => 'FF000000'),
- ),
- 'right' => array(
- 'style' => PHPExcel_Style_Border::BORDER_THIN,
- 'color' => array('argb' => 'FF000000'),
- ),
- 'inside' => array(
- 'style' => PHPExcel_Style_Border::BORDER_THIN,
- 'color' => array('argb' => 'FF000000'),
- ),
- 'bottom' => array(
- 'style' => PHPExcel_Style_Border::BORDER_DOUBLE,
- 'color' => array('argb' => 'FF000000'),
- ),
- )
- );
- $cells = array(
- 'borders'=>array(
- 'allborders'=>array(
- 'style'=>PHPExcel_Style_Border::BORDER_THIN,
- 'color'=>array('rgb'=>'000000')
- )
- )
- );
- $companyStyle = array(
- 'font' => array(
- 'color' => array('rgb'=>'1BB635'),
- 'bold' => true,
- 'size' => 15
- )
- );
- $sheetTitleStyle = array(
- 'font' => array(
- 'color' => array('rgb'=>'4C4D4C'),
- 'bold' => true,
- 'size' => 12
- )
- );
- $date = date('d-m-Y H:i:s');
- $objset->setCellValue('A1',ucwords($company));
- $objset->setCellValue('A2',ucwords($sheetTitle));
- $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($companyStyle);
- $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($sheetTitleStyle);
- $objPHPExcel->getActiveSheet()->mergeCells('A1:C1');
- $objPHPExcel->getActiveSheet()->mergeCells('A2:C2');
- // set kolom mulai dari A
- $cols = 'A';
- for ($i=0; $i < count($val); $i++) {
- // set kolom header dengan value dari $val=$fieldCaption
- $objset->setCellValue($cols.'5',ucwords($val[$i]));
- // set lebar kolom (lebar otomatis)
- $objPHPExcel->getActiveSheet()->getColumnDimension($cols)->setAutoSize(true);
- // set style kolom header dari array $headerStyle
- $objPHPExcel->getActiveSheet()->getStyle($cols.'5')->applyFromArray($headerStyle);
- $cols++;
- }
- $baris = 6;
- $no = 1;
- $key = array_keys($data[0]);
- for ($x=0; $x < count($data); $x++) {
- // set kolom mulai dari A
- $cols = 'A';
- for ($i=0; $i < count($val); $i++) {
- // set cell dengan data dari variabel $data. diLooping sebanyak isi $fieldCaption
- if ($cols=='A') {
- $objset->setCellValue($cols.$baris, $no);
- // set number format
- $objPHPExcel->getActiveSheet()->getStyle($cols.$baris)->getNumberFormat()->setFormatCode('0');
- } else {
- $objset->setCellValue($cols.$baris, $data[$x][$key[$i-1]]);
- }
- // set cell style dari array $cells
- $objPHPExcel->getActiveSheet()->getStyle($cols.$baris)->applyFromArray($cells);
- $cols++;
- }
- $baris++;
- $no++;
- }
- $objset->setCellValue('A'.$baris+=1,ucwords('Dibuat pada: '.$date));
- $objPHPExcel->getActiveSheet()->mergeCells('A'.$baris.':C'.$baris);
- $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);
- $objPHPExcel->setActiveSheetIndex(0);
- $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
- ob_start();
- $objWriter->save('php://output');
- $xlsData = ob_get_contents();
- ob_end_clean();
- $response = array(
- 'status' => 'oke',
- 'file' => "data:application/vnd.ms-excel;base64,".base64_encode($xlsData)
- );
- return $response;
- } else {
- return json_encode(array('msg'=>'Tidak bisa export'));
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment