Key Takeaways
- PhpSpreadsheet is a powerful PHP library for creating, editing, and styling Excel files programmatically.
- Automating spreadsheet generation saves time, reduces errors, and ensures consistency in reports.
- Proper setup of print layouts and margins ensures professional, ready-to-use reports for any audience.
Imagine this: every month, your Accounting team needs a detailed Transaction Log Report to reconcile accounts. The manual way—running queries, copying data into Excel, formatting cells, bolding headers, adjusting margins—takes time and invites errors.
The smarter way?
With PHP and PhpSpreadsheet, we can automate the process completely. A single button click fetches the data, creates a fully styled .xlsx file, sets print-ready layouts, and forces an instant download—ready for Excel, LibreOffice Calc, or even Google Sheets.
In this tutorial, I’ll walk you through exactly how I built this automated reporting solution from scratch. We’ll cover everything: creating a report, styling it, editing existing spreadsheets, preparing them for print, and testing the results so your monthly reports are always accurate, consistent, and effortless.
Prerequisites
Before we write a single line of code, we need to ensure our local development environment is set up. Here’s my setup:
1. The Local Server: XAMPP
I use XAMPP because it’s a free, easy-to-install package that gives me everything I need: the Apache web server and the PHP scripting engine.
- My Action: I always make sure the Apache service is running in my XAMPP Control Panel. If it’s not running, my browser won’t be able to execute any PHP code.
2. Dependency Management: Composer
Composer is PHP’s dependency manager, and it’s essential here. It’s how we install and manage the PhpSpreadsheet library without manually downloading dozens of files.
3. Creating Our Project Folder
I’m going to create a new folder inside my XAMPP web root directory (my htdocs folder) and call it excel-report. This keeps everything organized.
- My Path: C:\xampp\htdocs\excel-report\
4. Installing PhpSpreadsheet
This is the most critical step. We need to install the PhpSpreadsheet library inside our project folder.
First, make sure the required PHP extensions are enabled. In your php.ini file, confirm these lines are present and not commented out (no ; at the beginning):
extension=openssl extension=gd
Now, open your Command Prompt (CMD) or Terminal and navigate to your project folder:
cd C:\xampp\htdocs\excel-report
Run the Composer installation command:
composer require phpoffice/phpspreadsheet
This will download and install PhpSpreadsheet into your project, making it ready for use.
- What just happened! Composer downloaded the PhpSpreadsheet files and created a vendor folder and an autoload.php file.
- This single autoload.php file lets our PHP scripts use the entire library with just one line of code.
Step 1: Building the Simple UI (The Front-End)
I need a visual way to trigger the download so I can see my results right away. I’m going to create a simple HTML file called index.html. This is the user interface (UI).
File: C:\xampp\htdocs\excel-report\index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP Excel Report Generator</title>
<style>
body { font-family: Arial, sans-serif; text-align: center; padding-top: 50px; }
.button {
display: inline-block;
padding: 15px 30px;
font-size: 18px;
color: #fff;
background-color: #007bff;
border: none;
border-radius: 5px;
text-decoration: none;
cursor: pointer;
}
</style>
</head>
<body>
<h1>Monthly Transaction Log Generator</h1>
<p>Click the button below to instantly generate and download the required report.</p>
<a href="generate_report.php" class="button">Download Report Now</a>
</body>
</html>
Now, when I go to http://localhost/excel-report/index.html in my browser, I see that clean, clickable button. The link points directly to the PHP file we are about to create.
Step 2: Writing the PHP Report Logic (The Engine)
This is the most crucial part. Inside your project folder (C:\xampp\htdocs\excel-report), create a new file called generate_report.php.
This file will contain all the logic for creating and downloading the spreadsheet when you trigger it from your browser (the Download Report Now button).
Here’s the complete script, broken down into parts for clarity:
A. Load and Define
First, I load the library and define some dummy data. In a real application, I’d replace this array with data pulled directly from a MySQL database.
<?php
// 1. Load the PhpSpreadsheet library using Composer's autoloader
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// 2. Define the data I want to export
$reportTitle = 'Monthly Transaction Log - ' . date('F Y');
$data = [
// Header Row
['Transaction ID', 'Customer Name', 'Date', 'Amount ($)'],
// Data Rows (pulled from my hypothetical database)
['T001', 'Alice Johnson', '2025-09-01', 59.99],
['T002', 'Robert Smith', '2025-09-05', 125.00],
['T003', 'Carla Diaz', '2025-09-12', 39.50],
['T004', 'Mark Lee', '2025-09-20', 220.00],
];
// 3. Initialize the Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Transactions');
B. Populate and Style the Spreadsheet
Now, I’ll loop through the data and apply some professional styling, like bold headers and column auto-sizing.
// Set the main title and merge cells for a nice banner
$sheet->setCellValue('A1', $reportTitle);
$sheet->mergeCells('A1:D1'); // Merge across columns A to D
// Apply styling to the title
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
$sheet->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
// Start writing the actual data below the title (I'll start at row 3)
$startRow = 3;
// Loop through my data array and place values into the sheet
foreach ($data as $rowIndex => $rowData) {
$currentColumn = 'A';
foreach ($rowData as $cellData) {
$sheet->setCellValue($currentColumn . ($startRow + $rowIndex), $cellData);
$currentColumn++;
}
}
// Styling the Header Row (Row 3)
$headerStyle = [
'font' => ['bold' => true],
'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'color' => ['argb' => 'FFEBEBEB']],
];
$sheet->getStyle('A3:D3')->applyFromArray($headerStyle);
// Auto-size the columns so everything fits!
foreach (range('A', 'D') as $column) {
$sheet->getColumnDimension($column)->setAutoSize(true);
}
C. Force the Download
This is the crucial step for the UI experience. Instead of saving the file on the server, I use special HTTP headers to tell the browser: “Hey, this isn’t a webpage, it’s a file, and you need to download it.”
// 4. Send headers and download the file
$fileName = 'transaction_report_' . date('Ymd') . '.xlsx';
// 4a. Set the headers for file download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $fileName . '"');
header('Cache-Control: max-age=0'); // Ensures compatibility with older browsers
// 4b. Create the writer object and send the file
$writer = new Xlsx($spreadsheet);
$writer->save('php://output'); // This command writes the file contents to the HTTP response stream
exit; // Always stop execution after sending a file!
Step 3: Seeing What I Made (The Proof)
This is the most satisfying part, and it’s where you see your little UI button pay off!
- I make sure my Apache service is still running in XAMPP.
- I open my browser to http://localhost/excel-report/index.html.
- I click the “Download Report Now” button.
The browser immediately prompts me to download a file named transaction_report_[current date].xlsx.
Since I don’t have Microsoft Excel on my machine, I can open the file in Google Sheets. When I open that file, I see a perfectly formatted sheet:
- A bold, centered report title (Monthly Transaction Log – September 2025) spanning the top four columns.
- A header row (Transaction ID, Customer Name, etc.) that is bold and gray.
- All the data from my PHP array.
- The columns are automatically sized to fit the text—no manual adjusting needed.
This is exactly what I wanted. I’ve successfully automated a recurring task with just a few lines of PHP and a local XAMPP setup. This method is robust, professional, and entirely free of manual data entry errors.
Now go try it out and let me know what kind of reports you plan to automate.
Editing an Existing Spreadsheet in PHP
In real-world scenarios, you often need to update or repurpose an already-generated report instead of starting fresh. This is common when working with monthly logs, financial statements, or preformatted templates. Teams reviewing in the browser can edit spreadsheet online to check layout and formulas with teammates before saving a final copy.
For our demo, we’ll take the report we generated earlier (transaction_report_20250926.xlsx), rename it to existing_report.xlsx, and place it in our project folder (C:\xampp\htdocs\excel-report\). This file will act as the “template” we want to modify.
New PHP Script: edit_report.php
Now, let’s create a new PHP file named edit_report.php inside the same project folder. This script will load our existing spreadsheet, make some changes, and then save a new version of it.
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// 1. Load the existing spreadsheet
// Make sure "existing_report.xlsx" is in your project folder
try {
$spreadsheet = IOFactory::load('existing_report.xlsx');
} catch (\PhpOffice\PhpSpreadsheet\Reader\Exception $e) {
die('Error loading file: ' . $e->getMessage());
}
$sheet = $spreadsheet->getActiveSheet();
// 2. Perform Editing Operations
// Remove 3 rows starting from row 5
$sheet->removeRow(5, 3);
// Insert 2 blank rows before row 2 (for a new header section)
$sheet->insertNewRowBefore(2, 2);
// Update the top-left cell with the date of modification
$sheet->setCellValue('A1', 'Report Edited on: ' . date('Y-m-d'));
// 3. Save the modified spreadsheet
// Save as a new file so we don’t overwrite the original
$writer = new Xlsx($spreadsheet);
$newFileName = 'modified_report_' . date('Ymd') . '.xlsx';
$writer->save($newFileName);
echo "File successfully edited and saved as $newFileName!";
How It Works
- Loading the file – Instead of creating a blank Spreadsheet object, we load our renamed existing_report.xlsx file using IOFactory::load().
- Editing operations – We demonstrate three common modifications:
- Removing rows (e.g., deleting old data).
- Inserting new rows (e.g., for extra headers or notes).
- Updating a specific cell with the edit timestamp.
- Saving changes – The script creates a new file, modified_report_YYYYMMDD.xlsx, to ensure the original file remains untouched.
- To test, open your browser and visit:
http://localhost/excel-report/edit_report.php
If everything is set up correctly, you’ll see a success message and a new file (e.g., modified_report_20250926.xlsx) will appear in your project folder.
With this, you’ve now expanded your toolkit: you can both create new reports from scratch and edit existing ones — a core requirement in real reporting systems.
Preparing Spreadsheets for Printing
If you want to print or export your report as a PDF later, you’ll want your spreadsheet to look professional on paper. Luckily, PhpSpreadsheet lets us define print settings directly.
Add this block to your generate_report.php script, right before the download section (where we send the file to the browser with headers).
// --- PRINTING SETUP SECTION --- use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup; // Set page orientation to Landscape $sheet->getPageSetup()->setOrientation(PageSetup::ORIENTATION_LANDSCAPE); // Set paper size to A4 $sheet->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4); // Define margins (in inches) $sheet->getPageMargins()->setTop(1.0); $sheet->getPageMargins()->setRight(0.5); $sheet->getPageMargins()->setLeft(0.5); $sheet->getPageMargins()->setBottom(1.0); // Fit all columns to one page wide $sheet->getPageSetup()->setFitToWidth(1); $sheet->getPageSetup()->setFitToHeight(0); // --- END OF PRINTING SETUP SECTION —
My completed file looks like this:
<?php
// 1. Load the PhpSpreadsheet library using Composer's autoloader
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup; // <-- move here
// 2. Define the data I want to export
$reportTitle = 'Monthly Transaction Log - ' . date('F Y');
$data = [
// Header Row
['Transaction ID', 'Customer Name', 'Date', 'Amount ($)'],
// Data Rows (dummy data for now)
['T001', 'Alice Johnson', '2025-09-01', 59.99],
['T002', 'Robert Smith', '2025-09-05', 125.00],
['T003', 'Carla Diaz', '2025-09-12', 39.50],
['T004', 'Mark Lee', '2025-09-20', 220.00],
];
// 3. Initialize the Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Transactions');
// Set the main title and merge cells for a nice banner
$sheet->setCellValue('A1', $reportTitle);
$sheet->mergeCells('A1:D1'); // Merge across columns A to D
// Apply styling to the title
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
$sheet->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
// Start writing the actual data below the title (I'll start at row 3)
$startRow = 3;
foreach ($data as $rowIndex => $rowData) {
$currentColumn = 'A';
foreach ($rowData as $cellData) {
$sheet->setCellValue($currentColumn . ($startRow + $rowIndex), $cellData);
$currentColumn++;
}
}
// Styling the Header Row (Row 3)
$headerStyle = [
'font' => ['bold' => true],
'fill' => [
'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
'color' => ['argb' => 'FFEBEBEB']
],
];
$sheet->getStyle('A3:D3')->applyFromArray($headerStyle);
// Auto-size the columns
foreach (range('A', 'D') as $column) {
$sheet->getColumnDimension($column)->setAutoSize(true);
}
// --- PRINTING SETUP SECTION ---
// Place this BEFORE headers and writer
$sheet->getPageSetup()->setOrientation(PageSetup::ORIENTATION_LANDSCAPE);
$sheet->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);
$sheet->getPageMargins()->setTop(1.0);
$sheet->getPageMargins()->setRight(0.5);
$sheet->getPageMargins()->setLeft(0.5);
$sheet->getPageMargins()->setBottom(1.0);
$sheet->getPageSetup()->setFitToWidth(1);
$sheet->getPageSetup()->setFitToHeight(0);
// --- END OF PRINTING SETUP SECTION ---
// 4. Send headers and download the file
$fileName = 'transaction_report_' . date('Ymd') . '.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $fileName . '"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
How to Test It
- Start Apache in XAMPP and open your report page in the browser (where you trigger the download). This URL I mean: http://localhost/excel-report/generate_report.php
- Download the file again — e.g., transaction_report_20250926.xlsx.
- Since I don’t have Excel, like I mentioned earlier, I’ll use Google Sheets.
- After uploading, I’ll go to File → Print (or Ctrl+P) — this will show you a live print preview, just like Excel’s Print Preview.
- You should see the sheet in landscape orientation.
- The paper size is A4.
- Margins are set as defined.
- All columns fit neatly on one page width.
Now your report isn’t just styled for the screen but also optimized for professional printing.
Wrapping Up!
We did it! In this tutorial, we built a professional tool that saves time, eliminates errors, and produces beautiful reports on demand.
By following this guide, you’ve mastered the core concepts of PhpSpreadsheet. You now have the skills to:
- Create new .xlsx files from scratch with custom data and styling.
- Edit existing files, allowing you to update templates or modify old reports programmatically.
- Style your reports with professional formatting, including merged cells, bold headers, and automatic column sizing.
- Prepare your spreadsheets for printing by setting the orientation, paper size, and margins—a step that makes your reports truly ready for any audience.
If you have any questions, let me know in the comments below.
Frequently Asked Questions
Q1. What is the difference between PHPExcel and PhpSpreadsheet?
PhpSpreadsheet is the modern, actively maintained successor to the deprecated PHPExcel library. While PHPExcel is no longer supported and can have compatibility issues with newer PHP versions, PhpSpreadsheet is a future-proof solution that offers better performance and continues to receive updates. Our guide uses PhpSpreadsheet for all functions, from creating to editing.
Q2. Do I need Microsoft Excel to use this PHP-based reporting solution?
No, you do not need Microsoft Excel. The PHP script generates a standard .xlsx file that can be opened by any modern spreadsheet software. You can open and view the report on free platforms like Google Sheets or LibreOffice Calc, ensuring your reports are accessible to everyone, regardless of what software they have installed.
Q3. How can I automate a report to run on a schedule instead of on a button click?
To automate a report on a schedule, you would use a cron job (on Linux servers) or a Task Scheduler (on Windows). These tools can be configured to run your PHP script at a specific time each day or month. Instead of forcing a download, the script would be modified to save the file directly to the server, and then you could email it or store it for later access.
Shahzeb Ahmed
Shahzeb is a Digital Marketer with a Software Engineering background, works as a Community Manager — PHP Community at Cloudways. He is growth ambitious and aims to learn & share information about PHP & Laravel Development through practice and experimentation. He loves to travel and explore new ideas whenever he finds time. Get in touch with him at [email protected]
