Adding a logo to an excel sheet using php excel

php codeigniter phpexcel

26,533

Solution 1

Adding logo is so simple, try below code.

$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);

Solution 2

Use:

<?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');

Solution 3

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's 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("d-F-Y")."");
        $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's web browser (Excel2007)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="test"'.date("d-F-Y").'".xlsx"');
        header('Cache-Control: max-age=0');

        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        $this->load->view('test');
        exit;

    }

26,533

Author by

Alphy

I like programming

Updated on July 05, 2022

Comments

Alphy almost 2 years

I am trying to add a logo image on a generated excel worksheet using the code listing below but for some reason it 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");