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:
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.
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.
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:
//Require once the IOFactory.php, this is a required line to make the loading of Excel workbook successful
//Load Excel Workbook using PHPExcel_IOFactory::load command
//Prepare the 1st Excel worksheet (named as Sheet1) in the workbook for editing
//Change the content of Cell A1 in Sheet1
//Prepare the 2nd Excel worksheet (named as Sheet2) in the workbook for editing
//Change the content of Cell A1 in Sheet2
//Prepare the 3rd Excel worksheet (named as Sheet3) in the workbook for editing
//Change the content of Cell A1 in Sheet3
//Now that all cell contents are edited, you are ready to save it as Excel 2002
//This is the file name: write.xls
// Echo memory peak usage
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:
blog comments powered by Disqus