Saving the Excel Workbook as a PDF File using PHP Excel - PHP

It is also possible to save this as a PDF file. You can do it for one specific worksheet or all of the Excel worksheets in a workbook using PHP Excel.

By default, if not specified by the developer, PHP Excel will print only the active worksheet (one worksheet only) in PDF.

To save an Excel work as a pdf, you need to write it to a PDF file using this line:

$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'PDF');

To save all of the worksheets in the Excel workbook, you should include this line in your script:

$objWriter->writeAllSheets();

To assign a file name to the newly written PDF file, you should use a line like this:

$objWriter->save('write.pdf');

Suppose that, instead of saving as an Excel 2002 file in the previous example (write.xls), you want to save this as a PDF file (and name it write.pdf). To do this, you will need to define a specific write command (what type of file you are going to create) to PHP Excel. Then you must define what worksheet you need to include in the PDF File (a specific one or all of the worksheets in the workbook), and finally give the filename of your PDF file.

This is how the script should look. This PHP script will load an existing MS Excel workbook and then print all of the worksheets in a single PDF.

<?php
/** This is the same script as loading an Excel workbook to PHP Excel in the previous section*/
error_reporting(E_ALL);
require_once '../Classes/PHPExcel/IOFactory.php';
$objPHPexcel = PHPExcel_IOFactory::load('testing.xls');
$objWorksheet = $objPHPexcel->setActiveSheetIndex(0);
$objWorksheet->getCell('A1')->setValue('Jack and Jill went up the hill to fetch a pail of water.');
$objWorksheet = $objPHPexcel->setActiveSheetIndex(1);
$objWorksheet->getCell('A1')->setValue('Twinkle twinkle little star, how I wonder what you are?');
$objWorksheet = $objPHPexcel->setActiveSheetIndex(2);
$objWorksheet->getCell('A1')->setValue('Rain rain go away, Come again another day.');

//Now that the script completes the editing of the loaded MS Excel workbook; you can then write it as a PDF file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'PDF');

//You need to include and print to PDF the entire worksheets contained in the workbook
$objWriter->writeAllSheets();

//You need to assign a filename to the PDF file (write.pdf for example)
$objWriter->save('write.pdf');

// Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MBrn";
// Echo done
echo date('H:i:s') . " Done writing files.rn";
?>

Okay, now try running the script in the web browser (example): http://localhost/phpexcel/Tests/loadexcelfilesaveaspdf.php

And then go to the PHP Excel Tests folder. You will see a newly created pdf file named write.pdf.

When you open the pdf file, you should see something that looks like this:

You might have noticed that there are gridlines in the PDF. If you need to remove the gridlines when outputting to PDF document, you can declare:

setShowGridlines(false)

So the revised script will be as follows (take note of the added setShowGridlines(false) in the script):

<?php
//Revised script, prevent the PDF output to show gridlines
error_reporting(E_ALL);
require_once '../Classes/PHPExcel/IOFactory.php';
$objPHPexcel = PHPExcel_IOFactory::load('testing.xls');
$objWorksheet = $objPHPexcel->setActiveSheetIndex(0) ->setShowGridlines(false);
$objWorksheet->getCell('A1')->setValue('Jack and Jill went up the hill to fetch a pail of water.');
$objWorksheet = $objPHPexcel->setActiveSheetIndex(1) ->setShowGridlines(false);
$objWorksheet->getCell('A1')->setValue('Twinkle twinkle little star, how I wonder what you are?');
$objWorksheet = $objPHPexcel->setActiveSheetIndex(2)->setShowGridlines(false);
$objWorksheet->getCell('A1')->setValue('Rain rain go away, Come again another day.');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'PDF');
$objWriter->writeAllSheets();
$objWriter->save('write.pdf');
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MBrn";
echo date('H:i:s') . " Done writing files.rn";
?>

Troubleshooting Tips in PHP Excel

There are times when you can run into trouble implementing the PHP Excel class in your web applications.

For example, if you get the following error:

Notice: Undefined variable: objPHPExcel in C:xampphtdocsphpexcelTestsloadexcelfilesaveaspdf.php on line 6

Fatal error: Call to a member function setActiveSheetIndex() on a non-object in C:xampphtdocsphpexcelTestsloadexcelfilesaveaspdf.php on line 6

What causes this problem? When you are following the PHP Excel developer documentation, you might have noticed that if you use this line, for example, from the documentation:

$objPHPExcel->getActiveSheet()->setShowGridlines(true);

The variable $objPHPExcel uses an upper case E for Excel -- however, if you have used this same variable, but in this format: $objPHPexcel; your script throws an error, because PHP variable names are case sensitive.

Fortunately, there is a way to prevent this problem. In all of your script variables, make sure you maintain the correct case assignment. So if you've started to use: $objPHPexcel; all of your script should use $objPHPexcel and NOT $objPHPExcel.