Export Excel Files With Images Using PHP Excel Library

Php
May 07 2015
19086 Views

Export Excel Files With Images Using PHP Excel Library

Download at github

Introduction:

PHPExcel library to provide an “Export to Excel” function in a website. So that the user can export the data into an Excel 2007/2013 file. We can draw the images inside the excel column. PHP Excel library provides this feature. It's easy to use.

Download the PHP Excel Library:

Before you working on this library, download it. You will get PHPExcel 1.7.7 version file. Click here to download.

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.

Html Code: index.php

Display the records in webpage by using bellow code. Html code block in index.php file

<!-- Page Content -->
    <div class="container">
        <div class="row">
            <div class="col-lg-12">

                <table class="table table-striped table-bordered">
                    <caption>Excel
                        <a href="javascript:void(0)" id="xlscreation">From Ajax</a>
                        <a href="direct.php" >From Direct</a>
                    </caption>
                   <thead>
                      <tr>
                        <th>Brand Icon</th>
                        <th>Comapany</th>
                        <th>Rank</th>
                        <th>Link</th>
                      </tr>
                   </thead>
                   <tbody>
                    <?php
                        $reportdetails = report_details(1);
                        foreach($reportdetails as $value){
                    ?>
                      <tr>
                        <td><?php echo $value['BrandIcon']; ?></td>
                        <td><?php echo $value['Comapany']; ?></td>
                        <td><?php echo $value['Rank']; ?></td>
                        <td><?php echo $value['Link']; ?></td>
                      </tr>
                    <?php } ?>
                   </tbody>
                </table>
            </div>
        </div>
    </div>
    <!-- /.container -->

Static Data Array: functions.php

Static data array for demo. You can load that array data from database.

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

    $reportdetails = array(
        array('BrandIcon' => $imagePath . "facebook.png",'Comapany' => "facebook",'Rank' => "2",'Link' => "http://www.facebook.com"),
        array('BrandIcon' => $imagePath . "googleplus.png",'Comapany' => "googleplus",'Rank' => "1",'Link' => "http://www.googleplus.com"),
        array('BrandIcon' => $imagePath . "twitter.png",'Comapany' => "twitter",'Rank' => "3",'Link' => "http://www.twitter.com"),
        array('BrandIcon' => $imagePath . "linkedin.png",'Comapany' => "linkedin",'Rank' => "8",'Link' => "http://www.linkedin.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 From Direct Request: functions.php

Export data to excelsheet from direct request use this function

/**
* 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);
    $presentDate = date('YmdHis');
    $fileName = "report_" . $rand . "_" . $presentDate . ".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();
}

Export Data From Ajax Request: functions.php

If you want export data from ajax request use this function,

/**
* Create excel by from ajax request
*/
function xlscreation_ajax() {

    $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++;
    }

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $saveExcelToLocalFile = saveExcelToLocalFile($objWriter);
    $response = array(
         'success' => true,
         'filename' => $saveExcelToLocalFile['filename'],
         'url' => $saveExcelToLocalFile['filePath']
     );
    echo json_encode($response);
    die();
}

Finally Take Look On functions.php File:

<?php

function my_constants(){
    $url = 'http://' . $_SERVER['HTTP_HOST'] . "/phpexcel_gd/";
    $path = $_SERVER['DOCUMENT_ROOT'] . '/phpexcel_gd/';
    define('SITEURL', $url);
    define('SITEPATH', str_replace('\\', '/', $path));
}

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

    $reportdetails = array(
        array('BrandIcon' => $imagePath . "facebook.png",'Comapany' => "facebook",'Rank' => "2",'Link' => "http://www.facebook.com"),
        array('BrandIcon' => $imagePath . "googleplus.png",'Comapany' => "googleplus",'Rank' => "1",'Link' => "http://www.googleplus.com"),
        array('BrandIcon' => $imagePath . "twitter.png",'Comapany' => "twitter",'Rank' => "3",'Link' => "http://www.twitter.com"),
        array('BrandIcon' => $imagePath . "linkedin.png",'Comapany' => "linkedin",'Rank' => "8",'Link' => "http://www.linkedin.com"),
    );
    return $reportdetails;

}
/**
* 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);
    $presentDate = date('YmdHis');
    $fileName = "report_" . $rand . "_" . $presentDate . ".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();
}

/**
* Create excel by from ajax request
*/
function xlscreation_ajax() {

    $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++;
    }

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $saveExcelToLocalFile = saveExcelToLocalFile($objWriter);
    $response = array(
         'success' => true,
         'filename' => $saveExcelToLocalFile['filename'],
         'url' => $saveExcelToLocalFile['filePath']
     );
    echo json_encode($response);
    die();
}

function saveExcelToLocalFile($objWriter) {

    $rand = rand(1234, 9898);
    $presentDate = date('YmdHis');
    $fileName = "report_" . $rand . "_" . $presentDate . ".xlsx";

    // make sure you have permission to write to directory
    $filePath = SITEPATH . 'reports/' . $fileName;
    $objWriter->save($filePath);
    $data = array(
        'filename' => $fileName,
        'filePath' => $filePath
    );
    return $data;

}

?>

Concusion:

Writting images in Excel Sheet using PHPExcel with XLSX and XLS format with 2007 and 2005 reader are just simply like above codes. If you have any issue or thoughts regarding the functionality just comment below. I hope this article would be helpful for few people. 

Leave a Reply