Export data in excel files with images using PHP Excel Library

09
10Export data in excel files with images using PHP Excel Library

Today in this tutorial, We explain about how to export a excel files with images using php excel library. PHP excel provides a well file manipulations. We will see how to use a PHPExcel library to provide an “Export to Excel” function in a web app so that the user can export the data into an Excel 2007/2013 file for further analysis. Before you working on this library, download it. You will get PHPExcel 1.7.7 version file. Click here to download.

Export data with image in excel using phpexcel

Export data using phpexcel

Preparation:

To use PHPExcel, we must have PHP version above 5.2.0. There are also 3 PHP extensions to be enabled: php_zip (which is essential to operate Office 2007 formats), php_xml and php_gd2 (optional, but required for exact column width auto-calculation).

Html Design:

Here I used bootstrap framework to display records nicely in webpage. Explained export option in two ways. a. Download trough ajax b. Download using PHP.


   
      
         
            Export Excel :
            
            
         
         

         
            
               
                  
                     Brand Icon
                     Comapany
                     Rank
                     Link

Static Data Array: functions.php

For demo, I used static array of data. You can also load array data from database.

function report_details($display = null) {
    
    if($display){
        $imagePath = SITEURL . "images/";
    } else {
        $imagePath = SITEPATH . "images/";
    }    

    $reportdetails = array(
        array('BrandIcon' => $imagePath . "googleplus.png",'Comapany' => "Google Plus",'Rank' => "1",'Link' => "https://www.googleplus.com"),
        array('BrandIcon' => $imagePath . "github.png",'Comapany' => "Github",'Rank' => "3",'Link' => "https://github.com/"),
        array('BrandIcon' => $imagePath . "bootstrap.png",'Comapany' => "Bootstrap",'Rank' => "4",'Link' => "http://getbootstrap.com/"),
        array('BrandIcon' => $imagePath . "so-icon.png",'Comapany' => "Stack Overflow",'Rank' => "3",'Link' => "http://stackoverflow.com/"),
    );
    return $reportdetails;

}

Draw Image In Excelsheet:

To draw image in excelsheet, use bellow code



    $objDrawing = new PHPExcel_Worksheet_Drawing();    //create object for Worksheet drawing

    $objDrawing->setName('Customer Signature');        //set name to image

    $objDrawing->setDescription('Customer Signature'); //set description to image

    $signature = $reportdetails[$rowCount][$value];    //Path to signature .jpg file
    $objDrawing->setPath($signature);

    $objDrawing->setOffsetX(25);                       //setOffsetX works properly
    $objDrawing->setOffsetY(10);                       //setOffsetY works properly

    $objDrawing->setCoordinates($column.$cell);        //set image to cell

    $objDrawing->setWidth(32);                 //set width, height
    $objDrawing->setHeight(32);  
                         
    $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());  //save

Export data directly : functions.php

This function will teach you how to export the data directly to the excel sheet.



    /**
    * Create excel by from direct request
    */
    function xlscreation_direct() {

        $reportdetails = report_details();

        require_once SITEPATH . 'PHPExcel/Classes/PHPExcel.php';

         $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()
                ->setCreator("user")
                ->setLastModifiedBy("user")
                ->setTitle("Office 2007 XLSX Test Document")
                ->setSubject("Office 2007 XLSX Test Document")
                ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("Test result file");

        // Set the active Excel worksheet to sheet 0
        $objPHPExcel->setActiveSheetIndex(0);

        // Initialise the Excel row number
        $rowCount = 0;

        // Sheet cells
        $cell_definition = array(
            'A' => 'BrandIcon',
            'B' => 'Comapany',
            'C' => 'Rank',
            'D' => 'Link'
        );

        // Build headers
        foreach( $cell_definition as $column => $value )
        {
            $objPHPExcel->getActiveSheet()->getColumnDimension("{$column}")->setAutoSize(true);
            $objPHPExcel->getActiveSheet()->setCellValue( "{$column}1", $value );
        }

        // Build cells
        while( $rowCount < count($reportdetails) ){
            $cell = $rowCount + 2;
            foreach( $cell_definition as $column => $value ) {

                $objPHPExcel->getActiveSheet()->getRowDimension($rowCount + 2)->setRowHeight(35);
                
                switch ($value) {
                    case 'BrandIcon':
                        if (file_exists($reportdetails[$rowCount][$value])) {
                            $objDrawing = new PHPExcel_Worksheet_Drawing();
                            $objDrawing->setName('Customer Signature');
                            $objDrawing->setDescription('Customer Signature');
                            //Path to signature .jpg file
                            $signature = $reportdetails[$rowCount][$value];    
                            $objDrawing->setPath($signature);
                            $objDrawing->setOffsetX(25);                     //setOffsetX works properly
                            $objDrawing->setOffsetY(10);                     //setOffsetY works properly
                            $objDrawing->setCoordinates($column.$cell);             //set image to cell
                            $objDrawing->setWidth(32);  
                            $objDrawing->setHeight(32);                     //signature height  
                            $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());  //save
                        } else {
                            $objPHPExcel->getActiveSheet()->setCellValue($column.$cell, "Image not found" );
                        }
                        break;
                    case 'Link':
                        //set the value of the cell
                        $objPHPExcel->getActiveSheet()->SetCellValue($column.$cell, $reportdetails[$rowCount][$value]);
                        //change the data type of the cell
                        $objPHPExcel->getActiveSheet()->getCell($column.$cell)->setDataType(PHPExcel_Cell_DataType::TYPE_STRING2);
                        ///now set the link
                        $objPHPExcel->getActiveSheet()->getCell($column.$cell)->getHyperlink()->setUrl(strip_tags($reportdetails[$rowCount][$value]));
                        break;

                    default:
                        $objPHPExcel->getActiveSheet()->setCellValue($column.$cell, $reportdetails[$rowCount][$value] );
                        break;
                }

            }
                
            $rowCount++;
        }

        $rand = rand(1234, 9898);
  
        $fileName = "" . $rand . "_" .time() . ".xlsx";

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$fileName.'"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        die();
    }