Home arrow PHP arrow PHP Excel: Working with Workbook and PDF Files

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.

  1. PHP Excel: Working with Workbook and PDF Files
  2. Saving the Excel Workbook as a PDF File using PHP Excel
By: Codex-M
Rating: starstarstarstarstar / 8
September 15, 2010

print this article



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:

/** Error reporting- very useful for troubleshooting PHP related errors */

//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

// 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:

>>> More PHP Articles          >>> More By Codex-M

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: