PHP Excel: Working with Workbook and PDF Files

If you use the PHP Excel class, you know that it is a class designed to let you write to and read from Excel files. This article will take the mystery out of manipulating MS Excel files with this class, so you can edit their contents and save them in two different forms.

There are times when your PHP web application using the PHP Excel class will use an actual MS Excel file in the implementation process. For example, say your web application typically accepts an MS Excel file from a user as an input (via a file upload on the web form, for example).

In this case, you need PHP Excel to load the Excel document and complete some tasks. These tasks can be one of the following:

  • Editing the contents of the Excel worksheet contained in that workbook.
  • Saving the edited workbook file as another Excel file.
  • Saving the Excel workbook as a PDF file (either a selected sheet or all of the worksheets in the workbook).

In this tutorial, you will learn how to use PHP Excel to execute the above sample tasks so that you can integrate it easily in your web application.

Loading Excel Workbook using the PHP Excel Class: an Example

Suppose you have an existing Excel workbook named testing.xls (using Excel 2002), which has three default worksheets with the following contents:

Sheet1 on Cell A1: The quick brown fox jumps over the lazy dog.
Sheet2 on Cell A1: Mary had a little lamb.
Sheet3 on Cell A1: London Bridge is falling down.

You can download the sample workbook here: http://www.php-developer.org/wp-content/uploads/tutorials/testing.xls

Now suppose that you want to use the PHP Excel class to edit the content of these worksheets, too:

Sheet1 on Cell A1: Jack and Jill went up the hill to fetch a pail of water.
Sheet2 on Cell A1: Twinkle twinkle little star, how I wonder what you are?
Sheet3 on Cell A1: Rain rain go away, Come again another day.
 
And finally, when the editing is complete, you need to save the file as another Excel file (using the “Save As” command).

To load an Excel file to your existing web application, follow the steps below. The example assumes you are implementing PHP Excel in an XAMPP local web server environment, but things would be pretty similar in an actual Apache web hosting environment.

Step 1. Create a PHP File and save it under the Tests folder in your XAMPP PHP Excel directory.

For example, if your Windows path for PHP Excel installation using XAMPP is: C:xampphtdocsphpexcel

The Tests folder path will be: C:xampphtdocsphpexcelTests

Suppose you will create a PHP script called loadexcelfileedit.php; the path of this script will be: C:xampphtdocsphpexcelTestsloadexcelfileedit.php

Step 2. The actual PHP scripts are as follows:

<?php
/** Error reporting- very useful for troubleshooting PHP related errors */
error_reporting(E_ALL);

//Require once the IOFactory.php, this is a required line to make the loading of Excel workbook successful
require_once ‘../Classes/PHPExcel/IOFactory.php';

//Load Excel Workbook using PHPExcel_IOFactory::load command
//The name of the Excel workbook to be loaded is testing.xls
//In order for the script to successfully read testing.xls workbook, you need to
//place testing.xls file inside the Tests folder, so that the path of this
//workbook will be: C:xampphtdocsphpexcelTeststesting.xls
$objPHPexcel = PHPExcel_IOFactory::load(‘testing.xls’);

//Prepare the 1st Excel worksheet (named as Sheet1) in the workbook for editing
$objWorksheet = $objPHPexcel->setActiveSheetIndex(0);

//Change the content of Cell A1 in Sheet1
$objWorksheet->getCell(‘A1′)->setValue(‘Jack and Jill went up the hill to fetch a pail of water.’);

//Prepare the 2nd Excel worksheet (named as Sheet2) in the workbook for editing
$objWorksheet = $objPHPexcel->setActiveSheetIndex(1);

//Change the content of Cell A1 in Sheet2
$objWorksheet->getCell(‘A1′)->setValue(‘Twinkle twinkle little star, how I wonder what you are?’);

//Prepare the 3rd Excel worksheet (named as Sheet3) in the workbook for editing
$objWorksheet = $objPHPexcel->setActiveSheetIndex(2);

//Change the content of Cell A1 in Sheet3
$objWorksheet->getCell(‘A1′)->setValue(‘Rain rain go away, Come again another day.’);

//Now that all cell contents are edited, you are ready to save it as Excel 2002
$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, ‘Excel5′);

//This is the file name: write.xls
$objWriter->save(‘write.xls’);

// 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";
?>

Step 3. To run this script so that the Excel file (testing.xls) will be edited, run this script in the web browser: http://localhost/phpexcel/Tests/loadexcelfileedit.php

If the script is successful, you will see a message like this:

12:40:35 Peak memory usage: 10.75 MB 12:40:35 Done writing files.

Step 4. Now go to the Tests folder. A new file named write.xls is created. When you open the file, you will see that the contents have changed:

{mospagebreak title=Saving the Excel Workbook as a PDF File using PHP Excel}

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.

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort