PHP Excel Customizations: Working with Formulas and Functions

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.

If you need a working introduction of PHP Excel implementation in XAMPP localhost, you can refer to this tutorial: http://www.devshed.com/c/a/PHP/PHP-Excel-Implementation-in-XAMPP-Localhost/

Configuring to output an Excel Spreadsheet with formulas

When you install the PHP Excel library in your Windows XAMPP localhost, you’ll notice that it includes a Tests folder. This is where you can see sample PHP scripts for outputting different types of files, particularly MS Excel spreadsheets.

Let’s configure a script to output an Excel spreadsheet containing formulas. The script will output the Excel-based formulas as an Excel 2002 spreadsheet. The default script installed by the library uses Excel 2007, so you will not need to change anything. Let’s use an older version of Excel, as it will not need more changes for the default Excel 2007, and the older versions of Excel can be opened with Excel 2007.

The script below shows the changes necessary to make it compatible with MS Excel 2002 workbook.

Step 1. Go to the PHP Excel folder in your XAMPP htdocs.

Step 2. Go to the Tests folder.

Step 3. Open 03formulas.php

Step 4. Configure set properties.

The default looks something like this:

echo date(‘H:i:s’) . " Set propertiesn";
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
        ->setLastModifiedBy("Maarten Balliauw")
        ->setTitle("Office 2007 XLSX Test Document")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");

You need change this to something that defines your own project, such as:

echo date(‘H:i:s’) . " Set propertiesn";
$objPHPExcel->getProperties()->setCreator("Codex M")
        ->setLastModifiedBy("Codex M")
        ->setTitle("Office 2002 XLS Test Document")
        ->setSubject("Office 2002 XLS Test Document")
        ->setDescription("Test document for Office 2002 XLS, generated using PHP classes.")
        ->setKeywords("office 2002 openxml php")
        ->setCategory("Test result file");

Step 5. Let’s look at what the output of the default script should look like under this section “Add some data; we will use some formulas here.” So you will not need to edit it at this time.

Step 6. Let’s change the default “Save Excel 2007 file” to “Save Excel 2002.” You will need to find these lines:

// Save Excel 2007 file
echo date(‘H:i:s’) . " Write to Excel2007 formatn";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007′);
$objWriter->save(str_replace(‘.php’, ‘.xlsx’, __FILE__));

Change that to:

// 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__));

Step 7. Save the PHP file.

Step 8. To finally create the MS Excel file, open the URL in the browser using this path: http://localhost/phpexcel/Tests/03formulas.php

You will then see the following confirmation text sent to the browser:

00:32:20 Create new PHPExcel object 00:32:21 Set properties 00:32:21 Add some data 00:32:21 Rename sheet 00:32:21 Write to Excel2002 format 00:32:22 Peak memory usage: 9 MB 00:32:22 Done writing file.

Step 9. Go back to the Tests folder and see the newly-created Excel file, containing formulas. You should see the new file named “03formulas.xls” created by the script.

Step 10. Open the 03formulas.xls file; you should see an output that looks like this:

 

Explanation of the PHP Script formula

The above screen shot shows the default output of the PHP script formula included in 03formulas.xls

The PHP script responsible for the above screen shot output is this:

// Add some data, we will use some formulas here
echo date(‘H:i:s’) . " Add some datan";
$objPHPExcel->getActiveSheet()->setCellValue(‘A5′, ‘Sum:’);
$objPHPExcel->getActiveSheet()->setCellValue(‘B1′, ‘Range 1′);
$objPHPExcel->getActiveSheet()->setCellValue(‘B2′, 2);
$objPHPExcel->getActiveSheet()->setCellValue(‘B3′, 8);
$objPHPExcel->getActiveSheet()->setCellValue(‘B4′, 10);
$objPHPExcel->getActiveSheet()->setCellValue(‘B5′, ‘=SUM(B2:B4)’);
$objPHPExcel->getActiveSheet()->setCellValue(‘C1′, ‘Range 2′);
$objPHPExcel->getActiveSheet()->setCellValue(‘C2′, 3);
$objPHPExcel->getActiveSheet()->setCellValue(‘C3′, 9);
$objPHPExcel->getActiveSheet()->setCellValue(‘C4′, 11);
$objPHPExcel->getActiveSheet()->setCellValue(‘C5′, ‘=SUM(C2:C4)’);
$objPHPExcel->getActiveSheet()->setCellValue(‘A7′, ‘Total of both ranges:’);
$objPHPExcel->getActiveSheet()->setCellValue(‘B7′, ‘=SUM(B5:C5)’);
$objPHPExcel->getActiveSheet()->setCellValue(‘A8′, ‘Minimum of both ranges:’);
$objPHPExcel->getActiveSheet()->setCellValue(‘B8′, ‘=MIN(B2:C5)’);

Examining the above script, you will learn that:

1. To assign a text or number to a cell in an Excel spreadsheet, the following formula is used:

$objPHPExcel->getActiveSheet()->setCellValue(‘A5′, ‘Sum:’);

Where:

  • A5 can be changed to any cell to which you would like the text to be outputted.
  • The same concept applies when outputting a number instead of a text, for example:

$objPHPExcel->getActiveSheet()->setCellValue(‘B2′, 2);

2. To add Excel formulas to a spreadsheet using PHP Excel, the following line is used:

$objPHPExcel->getActiveSheet()->setCellValue(‘B5′, ‘=SUM(B2:B4)’);

Where:

  • B5 is the cell where the formula output will be placed.
  • =SUM(B2:B4) is an Excel formula that will sum the values from cell B2 to B4.

Those two concepts above can be used to create more complex Excel formulas than you normally use in your Excel spreadsheet. All you need to do is:

  • Assign values and text to the Excel spreadsheet using the PHP declaration lines above.
  • Analyze the values using the common Excel formulas, and output the value into an assigned cell.

{mospagebreak title=Functions Implemented in PHP Excel}

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

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye