Functions Implemented in PHP Excel - PHP

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";
$objPHPExcel->getActiveSheet()->setTitle('13calculation');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

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

<?php
//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).

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