PHPExcel Multi-Page PDF Issue When Including Images

Topics: Developer Forum, Project Management Forum, User Forum
Jun 16, 2010 at 4:04 PM

Hello,

I've had great success so far with PHPExcel 1.7.2, but I ran into an issue. I can export a 2 page Excel or Excel2007 that has data on one page, and an image on the other with no issues. But when I change the output to PDF, I get an empty file. Here is my code:

 

require_once(drupal_get_path('module', 'excel_export') .'/classes/PHPExcel.php');
require_once(drupal_get_path('module', 'excel_export') .'/classes/PHPExcel/IOFactory.php');

class ExcelExport {

  //Class variables
  var $excel;
  var $columns;
  var $data;
  var $title;
  var $export_type;
  var $active_sheet;
  var $writer_type;
  var $writer;

  /**
  * put your comment there...
  *
  * @param mixed $key
  * @return ExcelExport
  */
  function __construct($export_type = '', $filename = '') {
    $this->excel = new PHPExcel();
    if($export_type == '') {
      $this->export_type = variable_get('default_export_type', 'xls');
    } else {
      $this->export_type = $export_type;
    }
    if($title == '') {
      $this->title = 'Report';
    } else {
      $this->title = $title;
    }
    if($this->export_type == 'xlsx') {
      header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      header('Content-Disposition: attachment;filename="'.$filename.'.xlsx"');
      header('Cache-Control: max-age=0');
      $this->writer_type = 'Excel2007';
    } else if ($this->export_type == 'pdf') {
      header('Content-Type: application/pdf');
      header('Content-Disposition: attachment;filename="'.$filename.'.pdf"');
      header('Cache-Control: max-age=0');
      $this->writer_type = 'PDF';
    } else {
      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
      header('Cache-Control: max-age=0');
      $this->writer_type = 'Excel5';
    }
    $this->writer = PHPExcel_IOFactory::createWriter($this->excel, $this->writer_type);
    $this->active_sheet = $this->excel->setActiveSheetIndex(0);
  }

  /**
  * This adds a worksheet that is of tabular data.
  *
  * @param mixed $columns
  * @param mixed $data
  * @param mixed $title
  * @param mixed $styled
  * @param mixed $sheet
  */
  function add_table($columns = NULL, $data = NULL, $title = '', $styled = true, $sheet = 0) {
    //Create a new sheet
    $this->create_new_sheet($sheet, $title);

    $col = 0;
    $row = 1;
    //This adds a title row to the worksheet
    if($styled) {
      $letters = range('A', 'Z');
      $this->active_sheet->setCellValueByColumnAndRow($col, $row, $title);
      //The 'cells_to_merge' variable looks something like 'A1:J1' as you would see in Excel
      $cells_to_merge = $letters[0].$row.':'.$letters[count($columns)-1].$row;
      $this->active_sheet->mergeCells($cells_to_merge);
      $style_array = array(
        'font' => array('bold' => true),
        'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
      );
      $this->active_sheet->getStyleByColumnAndRow($col, $row)->applyFromArray($style_array);
      $row++;
    }

    $col = 0;
    //This adds the header
    foreach($columns as $column) {
      if($styled) {
        $this->active_sheet->setCellValueByColumnAndRow($col, $row, $column);
        $style_array = array(
          'font' => array('bold' => true),
          'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
        );
        $this->active_sheet->getStyleByColumnAndRow($col, $row)->applyFromArray($style_array);
        $this->active_sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
      } else {
        $this->active_sheet->setCellValueByColumnAndRow($col, $row, $column);
      }
      $col++;
    }

    $col = 0;
    $row++;
    //This adds the data
    foreach($data as $rows) {
      foreach($rows as $column) {
        $this->active_sheet->setCellValueByColumnAndRow($col, $row, $column);
        $col++;
      }
      $col = 0;
      $row++;
    }
  }

  /**
  * This adds a worksheet that contains an image
  *
  * @param mixed $title
  * @param mixed $image_name
  * @param mixed $image_description
  * @param mixed $image_location
  * @param mixed $image_coordinates
  * @param mixed $sheet
  */
  function add_image($title = '', $image_name = '', $image_description = '', $image_location = '', $image_coordinates = 'A1', $sheet = 0) {
    //Create a new sheet
    $this->create_new_sheet($sheet, $title);

    $image = new PHPExcel_Worksheet_Drawing();
    $image->setName($image_name);
    $image->setDescription($image_description);
    $image->setPath($image_location);
    $image->setCoordinates($image_coordinates);
    $image->setWorksheet($this->active_sheet);
  }

  /**
  * This function starts the export that pushes the data to the user as a download
  *
  */
  function download() {
    $this->active_sheet = $this->excel->setActiveSheetIndex(0);
    if($this->export_type == 'pdf') {
      $this->active_sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
      $this->active_sheet->getPageSetup()->setFitToWidth(1);
      $this->active_sheet->getPageSetup()->setFitToHeight(1);
      $this->writer->writeAllSheets();
    }
    $this->writer->save('php://output');
    exit;
  }

  /**
  * By default, a workbook has a single worksheet, to which we can append more worksheets.
  * To dynamically add worksheets, we need to keep track of how many worksheets we have, and
  * make sure that we make the lastest addition, the active worksheet.
  */
  function create_new_sheet($sheet, $title) {
    if($this->excel->getSheetCount()-1 < $sheet) {
      $this->excel->createSheet($sheet);
    }
    $this->active_sheet = $this->excel->setActiveSheetIndex($sheet);
    $this->active_sheet->setTitle($title);
  }

  /**
  * Default destruct function
  *
  */
  function __destruct() {

  }
}

To run this code I used:

$excel = new ExcelExport('pdf', 'Task Type Report');
  $excel->add_table($header, $rows, 'Task Type Report Data', true, 0);
  if($chart_image_name != '') {
    $excel->add_image('Task Type Report Graph', 'Graph', 'Task Type Report', $image_location, 'A1', 1);
  }
  $excel->download();

Please advise.

Coordinator
Jun 23, 2010 at 2:02 PM
Edited Jun 23, 2010 at 2:03 PM

- Have you tried storing the PDF on disk somewhere?

- 1.7.3c fixes a lot, can you try with the latest version?

Jun 23, 2010 at 2:31 PM
maartenba wrote:

- Have you tried storing the PDF on disk somewhere?

- 1.7.3c fixes a lot, can you try with the latest version?

Hello Maarten,

I am using the snapshot I downloaded from Friday (which I assume is 1.7.3c, since the version number is listed as "@version    ##VERSION##, ##DATE##"), and the PDF output still doesn't work. When I receive the download it is less than 200 bytes in size, and Adobe complains that it is corrupt.

I haven't tried storing the file on the server file system since that functionality is not appropriate for the system we are building. Would saving to disk effect whether the export works or not?

 

Here is my updated code:

require_once(drupal_get_path('module', 'excel_export') .'/classes/PHPExcel.php');
require_once(drupal_get_path('module', 'excel_export') .'/classes/PHPExcel/IOFactory.php');

class ExcelExport {

  //Class variables
  var $excel;
  var $columns;
  var $data;
  var $title;
  var $export_type;
  var $active_sheet;
  var $writer_type;
  var $writer;
  var $default_row_height;

  /**
  * put your comment there...
  *
  * @param mixed $key
  * @return ExcelExport
  */
  function __construct($export_type = '', $filename = '') {
    $this->excel = new PHPExcel();
    if($export_type == '') {
      $this->export_type = variable_get('default_export_type', 'xls');
    } else {
      $this->export_type = $export_type;
    }
    if($title == '') {
      $this->title = 'Report';
    } else {
      $this->title = $title;
    }
    $this->default_row_height = 15;
    if($this->export_type == 'xlsx') {
      header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      header('Content-Disposition: attachment;filename="'.$filename.'.xlsx"');
      header('Cache-Control: max-age=0');
      $this->writer_type = 'Excel2007';
    } else if ($this->export_type == 'pdf') {
      header('Content-Type: application/pdf');
      header('Content-Disposition: attachment;filename="'.$filename.'.pdf"');
      header('Cache-Control: max-age=0');
      $this->writer_type = 'PDF';
    } else {
      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
      header('Cache-Control: max-age=0');
      $this->writer_type = 'Excel5';
    }
    $this->writer = PHPExcel_IOFactory::createWriter($this->excel, $this->writer_type);
    $this->active_sheet = $this->excel->setActiveSheetIndex(0);
    $this->active_sheet->getDefaultRowDimension()->setRowHeight($this->default_row_height);
  }

  /**
  * This adds a worksheet that is of tabular data.
  *
  * @param mixed $columns
  * @param mixed $data
  * @param mixed $title
  * @param mixed $styled
  * @param mixed $sheet
  */
  function add_table($columns = NULL, $data = NULL, $title = '', $styled = true, $sheet = 0) {
    //Create a new sheet
    $this->create_new_sheet($sheet, $title);

    $col = 0;
    $row = 1;
    //This adds a title row to the worksheet
    if($styled) {
      $letters = range('A', 'Z');
      $this->active_sheet->setCellValueByColumnAndRow($col, $row, $title);
      //The 'cells_to_merge' variable looks something like 'A1:J1' as you would see in Excel
      $cells_to_merge = $letters[0].$row.':'.$letters[count($columns)-1].$row;
      $this->active_sheet->mergeCells($cells_to_merge);
      $style_array = array(
        'font' => array('bold' => true),
        'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
      );
      $this->active_sheet->getStyleByColumnAndRow($col, $row)->applyFromArray($style_array);
      $row++;
    }

    $col = 0;
    //This adds the header
    foreach($columns as $column) {
      if($styled) {
        $this->active_sheet->setCellValueByColumnAndRow($col, $row, $column);
        $style_array = array(
          'font' => array('bold' => true),
          'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
        );
        $this->active_sheet->getStyleByColumnAndRow($col, $row)->applyFromArray($style_array);
        $this->active_sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
      } else {
        $this->active_sheet->setCellValueByColumnAndRow($col, $row, $column);
      }
      $col++;
    }

    $col = 0;
    $row++;
    //This adds the data
    foreach($data as $rows) {
      foreach($rows as $column) {
        $this->active_sheet->setCellValueByColumnAndRow($col, $row, $column);
        $col++;
      }
      $col = 0;
      $row++;
    }
  }

  /**
  * This adds a worksheet that contains an image
  *
  * @param mixed $title
  * @param mixed $image_name
  * @param mixed $image_description
  * @param mixed $image_location
  * @param mixed $image_coordinates
  * @param mixed $sheet
  */
  function add_image($title = '', $image_name = '', $image_description = '', $image_location = '', $image_coordinates = 'A1', $sheet = 0) {
    //Create a new sheet
    $this->create_new_sheet($sheet, $title);

    $image = new PHPExcel_Worksheet_Drawing();
    $image->setName($image_name);
    $image->setDescription($image_description);
    $image->setPath($image_location);
    $image->setCoordinates($image_coordinates);
    $image->setWorksheet($this->active_sheet);
  }

  /**
  * put your comment there...
  *
  * @param mixed $image_name
  * @param mixed $image_description
  * @param mixed $image_location
  * @param mixed $image_coordinates
  * @param mixed $columns
  * @param mixed $data
  * @param mixed $title
  * @param mixed $styled
  * @param mixed $sheet
  */
  function add_table_and_image($image_name = '', $image_description = '', $image_location = '', $image_coordinates = 'A1', $columns = NULL, $data = NULL, $title = '', $styled = true, $sheet = 0) {
    //Create a new sheet
    $this->create_new_sheet($sheet, $title);

    if(file_exists($image_location)) {
      $image = new PHPExcel_Worksheet_Drawing();
      $image->setName($image_name);
      $image->setDescription($image_description);
      $image->setPath($image_location);
      $image->setCoordinates($image_coordinates);
      $image->setWorksheet($this->active_sheet);

      list($width, $height, $type, $attr) = getimagesize($image_location);

      //Convert the height in pixels to points, so that you can calculate
      //how many rows down the data should start on.
      $row = ((($height * 72) / 96) / $this->default_row_height) + 2;
    } else {
      $row = 1;
    }
    $col = 0;

    //This adds a title row to the worksheet
    if($styled) {
      $letters = range('A', 'Z');
      $this->active_sheet->setCellValueByColumnAndRow($col, $row, $title);
      //The 'cells_to_merge' variable looks something like 'A1:J1' as you would see in Excel
      $cells_to_merge = $letters[0].$row.':'.$letters[count($columns)-1].$row;
      $this->active_sheet->mergeCells($cells_to_merge);
      $style_array = array(
        'font' => array('bold' => true),
        'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
      );
      $this->active_sheet->getStyleByColumnAndRow($col, $row)->applyFromArray($style_array);
      $row++;
    }

    $col = 0;
    //This adds the header
    foreach($columns as $column) {
      $this->active_sheet->setCellValueByColumnAndRow($col, $row, $column);
      if($styled) {
        $style_array = array(
          'font' => array('bold' => true),
          'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER),
          'borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN,))
        );
        $this->active_sheet->getStyleByColumnAndRow($col, $row)->applyFromArray($style_array);
        $this->active_sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
      }
      $col++;
    }

    $col = 0;
    $row++;
    //This adds the data
    foreach($data as $rows) {
      foreach($rows as $column) {
        $this->active_sheet->setCellValueByColumnAndRow($col, $row, $column);
        if($styled) {
          $style_array = array(
            'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT),
            'borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN,))
          );
          $this->active_sheet->getStyleByColumnAndRow($col, $row)->applyFromArray($style_array);
          $this->active_sheet->getRowDimension($row)->setRowHeight($this->default_row_height);
          $this->active_sheet->getStyleByColumnAndRow($col, $row)->getAlignment()->setWrapText(true);
        }
        $col++;
      }
      $col = 0;
      $row++;
    }
  }

  /**
  * This function starts the export that pushes the data to the user as a download
  *
  */
  function download() {
    $this->active_sheet = $this->excel->setActiveSheetIndex(0);
    $this->active_sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    $this->active_sheet->getPageSetup()->setFitToWidth(1);
    $this->active_sheet->getPageSetup()->setFitToHeight(1);
    if($this->export_type == 'pdf') {
      $this->writer->writeAllSheets();
    }
    $this->writer->save('php://output');
    //exit;
  }

  /**
  * By default, a workbook has a single worksheet, to which we can append more worksheets.
  * To dynamically add worksheets, we need to keep track of how many worksheets we have, and
  * make sure that we make the lastest addition, the active worksheet.
  */
  function create_new_sheet($sheet, $title) {
    if($this->excel->getSheetCount()-1 < $sheet) {
      $this->excel->createSheet($sheet);
    }
    $this->active_sheet = $this->excel->setActiveSheetIndex($sheet);
    $this->active_sheet->setTitle(substr($title, 0, 31));
  }

  /**
  * This is a useful helper function to remove the image(s) used in the report.
  * The function would be useful in an environment where you must remove a temporary
  * image exported by another process. (e.g. Fusion Charts)
  *
  * @param mixed $image_location
  */
  function delete_image($image_location) {
    if(file_exists($image_location)) {
      unlink($image_location);
    }
  }

  /**
  * Nullify the large data elements, to ensure that memory leaks are kept to a minimum, if any.
  */
  function __destruct() {
    $this->data = null;
    $this->writer = null;
    $this->excel = null;
  }
}

Jun 30, 2010 at 2:11 PM

It's been almost two weeks, and still no resolution. Any ideas?

Coordinator
Jul 1, 2010 at 8:31 AM

Just tested it, works fine! There is one PP notice ($title is undefined in the __construct method), otherwise I do have output and a valid PDF file.
I just copied your code, added the following:

$columns = array('Name', 'Company');
$data = array();
for ($i = 0; $i < 500; $i++) {
    $data[] = array('Name' . $i, 'Company' . $i);
}

$export = new ExcelExport('pdf', '01.pdf');
$export->add_image('Koala', 'Koala', 'Koala', 'images\phpexcel_logo.gif');
$export->add_table($columns, $data, 'Customers');
$export->download();

When executing this, I executed it from the command line using:

php export.php > export.pdf

This resulted in export.pdf on disk, valid PDF file. Can you verify this?

PS: I will be on a vacation for the next two weeks.