Add logo to excel sheet using php excel

Question

I am trying to add a logo image to the generated excel worksheet using the code below, but for some reason does nothing

$objReader = PHPExcel_IOFactory::createReader('Excel2007'); $objPHPExcel = $objReader->load("workbooks/" . $labref . "/" . $labref . ".xlsx"); $objPHPExcel->getActiveSheet(2); $objWorkSheet = $objPHPExcel->createSheet(); $objWorkSheet->setTitle("Sample Summary"); $dir1 = "exclusive_image"; $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName("name"); $objDrawing->setDescription("Description"); $objDrawing->setPath(base_url().'exclusive_image/nqcl.png'); $objDrawing->setCoordinates('A1'); $objDrawing->setWorksheet($objWorkSheet->getActiveSheet(2)); $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $objWriter->save("workbooks/" . $labref . "/" . $labref . ".xlsx"); 

Answer 1

Adding a logo is so simple, try the code below.

 $objPHPExcel = new PHPExcel(); $sheet = $objPHPExcel->setSheetIndexAndTitle(1, "YOUR_SHEET_TITLE"); // first sheet $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo'); $objDrawing->setDescription('Logo'); $logo = base_path() . '/images/logo.png'; // Provide path to your logo file $objDrawing->setPath($logo); $objDrawing->setOffsetX(8); // setOffsetX works properly $objDrawing->setOffsetY(300); //setOffsetY has no effect $objDrawing->setCoordinates('B1'); $objDrawing->setHeight(75); // logo height $objDrawing->setWorksheet($sheet); 

Answer 2

Using:

 <?php include 'DB.php'; error_reporting(E_ALL); ini_set('include_path', ini_get('include_path').';../Classes/'); include 'PHPExcel.php'; include 'PHPExcel/Writer/Excel2007.php'; $excel = new PHPExcel(); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="your_name.xls"'); header('Cache-Control: max-age=0'); $excel->setActiveSheetIndex(0) ->setCellValue('A1', 'Hello') ->setCellValue('B2', 'world!') ->setCellValue('C1', 'Hello') ->setCellValue('D2', 'world!'); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo'); $objDrawing->setDescription('Logo'); $logo = 'images/logo.png'; // Provide path to your logo file $objDrawing->setPath($logo); //setOffsetY has no effect $objDrawing->setCoordinates('E1'); $objDrawing->setHeight(200); // logo height $objDrawing->setWorksheet($excel->getActiveSheet()); // Do your stuff here writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5'); // This line will force the file to download $writer->save('php://output'); 

Answer 3

to try

 public function rep() { //$this->load->library('phpexcel'); $this->load->library('PHPExcel/IOFactory'); $objPHPExcel = new PHPExcel(); // Set document properties $objPHPExcel->getProperties()->setCreator("Maarten Balliauw") ->setLastModifiedBy(" dev@Eri ") ->setTitle("Laporan Barang") ->setSubject("Laporan Barang") ->setDescription("report using PHPEXCEL.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); // Create the worksheet $objPHPExcel->setActiveSheetIndex(0); //DATA $data = array(array('kode'=>'B001', 'Barang'=>'Dunhill', 'Kategori'=>'Rokok'), array('kode'=>'B002', 'Barang'=>'Marlboro', 'Kategori'=>'Rokok'), array('kode'=>'B003', 'Barang'=>'Pepsodent', 'Kategori'=>'Pasta Gigi'), array('kode'=>'B004', 'Barang'=>'Gillete Goal', 'Kategori'=>'Pisau Cukur')); ################################## HEADER TABLE ################################# $objPHPExcel->getActiveSheet()->setCellValue('A6', "Kode Barang") ->setCellValue('B6', "Barang") ->setCellValue('C6', "Kategori"); ################################## /HEADER TABLE ################################ $nox=count($data)+6; ############################### ROW VALUE TABLE ############################## $objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A7'); ############################### /ROW VALUE TABLE ############################# // 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('A6:C6')->getFont()->setBold(true); // Set fills $objPHPExcel->getActiveSheet()->getStyle('A6:C6')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A6:C6')->getFill()->getStartColor()->setARGB('FF808080'); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); // Set autofilter // Always include the complete filter range! // Excel does support setting only the caption // row, but that 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, "A6:C$nox"); // Set style for header row using alternative method $objPHPExcel->getActiveSheet()->getStyle('A6:C6')->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('./assets/images/photo.jpg'); $objDrawing->setCoordinates('A2'); $objDrawing->setHeight(50); $objDrawing->setWidth(50); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); ############################# HEADER TABLE ####################################### $objPHPExcel->getActiveSheet()->getStyle('A6:C1000')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('A6:C1000')->getFont()->setSize(12); ############################# /HEADER TABLE ###################################### // Merge cells ############################# PAGE TITLE ######################################### $objPHPExcel->getActiveSheet()->mergeCells('A2:C2'); $objPHPExcel->getActiveSheet()->setCellValue('A2', "Compeni"); $objPHPExcel->getActiveSheet()->getStyle('A2:C2')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('A2:C2')->getFont()->setSize(18); $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(22); $objPHPExcel->getActiveSheet()->getStyle('A2:C2')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A2:C2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); ############################# /PAGE TITLE ######################################## ############################# SUB PAGE TITLE ##################################### $objPHPExcel->getActiveSheet()->mergeCells('A3:C3'); $objPHPExcel->getActiveSheet()->setCellValue('A3',"Laporan Barang"); $objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getFont()->setName('Verdana'); $objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getFont()->setSize(14); $objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); ############################# /SUB PAGE TITLE #################################### ############################# DETAIL PAGE REPORT ##################################### $objPHPExcel->getActiveSheet()->mergeCells('A4:C4'); $objPHPExcel->getActiveSheet()->setCellValue('A4',"Per ".date("dFY").""); $objPHPExcel->getActiveSheet()->getStyle('A4:C4')->getFont()->setName('Verdana'); $objPHPExcel->getActiveSheet()->getStyle('A4:C4')->getFont()->setSize(12); $objPHPExcel->getActiveSheet()->getStyle('A4:C4')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A4:C4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); ############################# /DETAIL PAGE REPORT #################################### // Redirect output to a client web browser (Excel2007) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="test"'.date("dFY").'".xlsx"'); header('Cache-Control: max-age=0'); $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); $this->load->view('test'); exit; }