Applying Formulas and Functions in Excel Using PHP

zeroexp.dev ZeroonLeave a Comment on Applying Formulas and Functions in Excel Using PHP
Applying Formulas and Functions in Excel Using PHP

Learn how to apply formulas and functions in Excel using PHP and PhpSpreadsheet. Automate calculations, create dynamic reports, and generate structured data.

Introduction

Excel formulas and functions are essential for automating calculations, generating reports, and processing financial data. When working with PHP, automating Excel file creation with embedded formulas can save time and reduce errors.

With PhpSpreadsheet, PHP can:

  • Insert built-in Excel formulas dynamically
  • Perform calculations on large datasets
  • Automate financial, statistical, and logical functions
  • Generate structured reports with live calculations

This guide covers:

  • Installing PhpSpreadsheet
  • Applying basic and advanced Excel formulas
  • Automating calculations for reports
  • Saving and exporting Excel files with formulas

1. Installing PhpSpreadsheet for Excel Formula Processing

To handle Excel formulas in PHP, install PhpSpreadsheet via Composer:

composer require phpoffice/phpspreadsheet

Include PhpSpreadsheet in your PHP script:

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

Once installed, the application can generate Excel files with embedded formulas.

2. Creating an Excel File with Basic Formulas

To add basic calculations like SUM, AVERAGE, and MULTIPLICATION, define them as Excel formulas within the PHP script.

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'Product');
$sheet->setCellValue('B1', 'Price');
$sheet->setCellValue('C1', 'Quantity');
$sheet->setCellValue('D1', 'Total');

$sheet->setCellValue('A2', 'Laptop');
$sheet->setCellValue('B2', 800);
$sheet->setCellValue('C2', 2);
$sheet->setCellValue('D2', '=B2*C2'); // Multiplication formula

$sheet->setCellValue('A3', 'Phone');
$sheet->setCellValue('B3', 500);
$sheet->setCellValue('C3', 3);
$sheet->setCellValue('D3', '=B3*C3'); // Multiplication formula

$sheet->setCellValue('A5', 'Total Revenue:');
$sheet->setCellValue('D5', '=SUM(D2:D3)'); // SUM formula

$writer = new Xlsx($spreadsheet);
$writer->save('calculated_report.xlsx');

echo "Excel file with formulas created successfully.";

This script:

  • Applies multiplication for each product's total price
  • Calculates total revenue using the SUM formula
  • Automatically updates when opened in Excel

3. Applying Advanced Formulas in PHP-Generated Excel

More complex calculations can be performed using financial and statistical functions.

Example: Calculating Tax and Discounts

$sheet->setCellValue('A1', 'Product');
$sheet->setCellValue('B1', 'Price');
$sheet->setCellValue('C1', 'Quantity');
$sheet->setCellValue('D1', 'Subtotal');
$sheet->setCellValue('E1', 'Discount (10%)');
$sheet->setCellValue('F1', 'Tax (5%)');
$sheet->setCellValue('G1', 'Final Price');

$sheet->setCellValue('A2', 'TV');
$sheet->setCellValue('B2', 1000);
$sheet->setCellValue('C2', 2);
$sheet->setCellValue('D2', '=B2*C2'); // Subtotal
$sheet->setCellValue('E2', '=D2*0.10'); // Discount
$sheet->setCellValue('F2', '=D2*0.05'); // Tax
$sheet->setCellValue('G2', '=D2-E2+F2'); // Final Price Calculation

$writer->save('financial_report.xlsx');

This script automates:

  • Subtotal Calculation
  • Discount and Tax Computation
  • Final price adjustment

4. Using Conditional Functions in Excel (IF, VLOOKUP, COUNTIF)

Conditional formulas help automate data analysis in reports.

Example: Applying Conditional Logic with IF

$sheet->setCellValue('A1', 'Sales');
$sheet->setCellValue('B1', 'Status');
$sheet->setCellValue('A2', 1500);
$sheet->setCellValue('B2', '=IF(A2>1000, "Above Target", "Below Target")');

$writer->save('conditional_report.xlsx');

Example: Using COUNTIF for Data Analysis

$sheet->setCellValue('A1', 'Employee');
$sheet->setCellValue('B1', 'Department');

$sheet->setCellValue('A2', 'John');
$sheet->setCellValue('B2', 'HR');

$sheet->setCellValue('A3', 'Jane');
$sheet->setCellValue('B3', 'IT');

$sheet->setCellValue('A4', 'Mike');
$sheet->setCellValue('B4', 'HR');

$sheet->setCellValue('D1', 'Total HR Employees:');
$sheet->setCellValue('E1', '=COUNTIF(B2:B4, "HR")'); // Count occurrences

$writer->save('countif_report.xlsx');

5. Applying Date and Time Functions in Excel

To include automated timestamps and dynamic dates, use built-in date functions.

$sheet->setCellValue('A1', 'Generated Date');
$sheet->setCellValue('B1', '=TODAY()'); // Automatically sets today’s date

$sheet->setCellValue('A2', 'Current Time');
$sheet->setCellValue('B2', '=NOW()'); // Displays the current time

$writer->save('date_time_report.xlsx');

6. Saving and Exporting Excel Files with Formulas

To allow users to download the generated Excel file with formulas intact:

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="report.xlsx"');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');

This triggers an automatic download for the user.

7. Using PhpSpreadsheet to Preserve Formulas

By default, PhpSpreadsheet preserves formulas, and when the file is opened in Excel, the formulas recalculate automatically.

To ensure formulas are retained and not converted to values:

$writer = new Xlsx($spreadsheet);
$writer->setPreCalculateFormulas(false);
$writer->save('preserved_formulas.xlsx');

8. Automating Excel Report Generation with Formulas Using Cron Jobs

To schedule Excel file generation with formulas on a regular basis:

0 8 * * * php /var/www/html/generate_excel.php

This automatically runs the script every morning at 8 AM, ensuring reports are updated.

Best Practices for Using Formulas in PHP-Generated Excel

  • Always use Excel-compatible formulas
  • Preserve formulas instead of pre-calculating them when exporting
  • Apply conditional logic for automation and efficiency
  • Use financial and statistical functions to generate insightful reports
  • Automate report generation with cron jobs

Conclusion

With PhpSpreadsheet, PHP can automate Excel formulas, reducing manual calculations and enhancing report generation and financial processing.

This guide covered:

  • Basic and advanced formulas in Excel
  • Conditional logic, data analysis, and date functions
  • Exporting Excel files with preserved formulas
  • Automating Excel report generation using PHP

By implementing these techniques, PHP applications can generate structured, dynamic reports with real-time calculations.

Leave a Reply