PHP Excel: Working with Workbook and PDF Files

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: