Home arrow PHP arrow Page 2 - PHP Excel Customizations: Working with Formulas and Functions

Functions Implemented in PHP Excel - PHP

PHP Excel is one of the most important developer's tools when working with MS Excel applications in PHP. This tutorial will start to explore some of PHP Excel's basic features. The most important one enables you to work with formulas and functions.

  1. PHP Excel Customizations: Working with Formulas and Functions
  2. Functions Implemented
By: Codex-M
Rating: starstarstarstarstar / 3
September 07, 2010

print this article



It is important to know the full list of functions that can be used with PHP Excel. To do this, follow the steps below:

1. Go to the Tests folder inside your XAMPP PHP Excel library.

2. Find this PHP file: 13calculation.php

3.  The script contains a long list of functions commonly used in MS Excel. To clearly see the output, letís render the script in the browser using the following URL: http://localhost/phpexcel/Tests/13calculation.php

4. Once you see the output, view the source code of the page. You will then see the complete list of functions implemented in PHP Excel:

5. To see how those functions work, as well as the test values, letís output 13calculation.php as an Excel 2002 file. To do this, add the following lines to the 13calculation.php script in between // Calculated data and // Echo memory peak usage:

// Rename sheet
echo date('H:i:s') . " Rename sheetn";

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

// Save Excel 2002 file
echo date('H:i:s') . " Write to Excel2002 formatn";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', __FILE__));

So it will look like this (inside the red box are the new scripts above, which are added to output contents as an Excel file):

6. Run the URL in the browser again to finally create the Excel file: http://localhost/phpexcel/Tests/13calculation.php

7. Go to the Tests folder. You will see a newly-created Excel file named 13calculation.xls

8. Open 13calculation.xls. You will see different Excel functions in action.

Integration Techniques with Existing PHP Web Application

Now that you have a pretty clear idea of how to implement formulas and functions in MS Excel using PHP Excel, you might think of incorporating this feature into your existing PHP web application.

The most obvious implementation is when your PHP web application involves numerical and statistical calculations, and you are using PHP Excel to render the calculations and output it as an Excel file.

Letís assume your existing application still does not have PHP Excel installed, and you are just outputting the numerical results to the web browser for your users to see.

To integrate PHP Excel using formulas and functions, go through the following steps: 

Step 1: Open your PHP file in your favorite PHP editor.

Step 2: Suppose you have the following script structure:

//Step1. Receive form inputs here (POST or GET)
//Step2. Validate form inputs here
//Step3. Assign any values to a PHP variable
//Step4. Compute preliminary PHP computations
//Step5. Output the computation results to the browser
//Step6. Complete

Step 3: You will then integrate the PHP Excel script, particularly the basic script discussed in 03formulas.php as follows (this assumes you have uploaded and configured correctly the PHP Excel library in your web server).

//Step1. Accept form inputs here (POST or GET)
//Step2. Validate form inputs here
//Step3. Assign any values to a PHP variable
//Compute preliminary PHP computations - OPTIONAL

//Start of PHP Excel integration

//Step4. Require php excel
/** PHPExcel */
require_once '../Classes/PHPExcel.php';

//Step5. Put the rest of the PHP Excel scripts here
//Step6. Render the calculation, results, etc as MS Excel file

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