PHP Excel Customizations: Working 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.