Create Excel Files With Header And Footer In PHP Using PHPSpreadSheet

With the simple setOddHeader() and setOddFooter() functions in PhpSpreadsheet, set a detailed document information at the top and at the bottom respectively. Short information such as the name of the document, company name, document title, page number data, or any other key specifics needed can be added. Some of the basic font design styles can also be applied.

Requirements:

Composer
PHP 7.2 or newer

Step 1.

Setup dependencies.

composer.json

{
    "require": {
        "phpoffice/phpspreadsheet": "^1.3"
    }
}

Step 2.

Install phpspreadsheet.

command line

$ composer install

Step 3.

Create a new PHP file, and start coding.

create-xlsx-files-with-header-and-footer.php

<?php

// Autoload dependencies
require 'vendor/autoload.php';

// Import the core class of PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;

// Import the Xlsx writer class
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Retrieve the current active worksheet
$sheet = $spreadsheet->getActiveSheet();

// Set cell A1 with the "Hello World !" string value
$sheet->setCellValue('A1', 'Hello World !');

// Set the document header and center it by '&C'
$sheet->getHeaderFooter()->setOddHeader('&CHeader of the Document');

// Set the document header and center it by '&C' and make it bold by '&B'
// $sheet->getHeaderFooter()->setOddHeader('&C&BHeader of the Document');

/**
 * Set the document footer on the left by '&L', and on the right by '&R',
 * with '&P' for the current page, and '&N' for the total pages.
 */
$sheet->getHeaderFooter()->setOddFooter('&LFooter of the Document&RPage &P of &N');

// Write a new .xlsx file
$writer = new Xlsx($spreadsheet);

// Save the new .xlsx file
$writer->save('create-xlsx-files-with-header-and-footer.xlsx');

Test.

Run the following codes.

command line

$ php create-xlsx-files-with-header-and-footer.php

Result.

Open the generated file create-xlsx-files-with-header-and-footer.xlsx and print preview.

PHP

// Set the document header and center it by '&C'
$sheet->getHeaderFooter()->setOddHeader('&CHeader of the Document');

PHP

/*
 * Set the document footer on the left by '&L', and on the right by '&R',
 * with '&P' for the current page, and '&N' for the total pages.
 */
$sheet->getHeaderFooter()->setOddFooter('&LFooter of the Document&RPage &P of &N');

Test Again

Try to set the header in bold font weight.

PHP

// Set the document header and center it by '&C' and make it bold by '&B'
$sheet->getHeaderFooter()->setOddHeader('&C&BHeader of the Document');

References:

How to install PhpSpreadsheetOn this site

Creating a spreadsheetphpspreadsheet.readthedocs.io

Reading and writing to filephpspreadsheet.readthedocs.io

PhpSpreadsheet Recipesphpspreadsheet.readthedocs.io