Create Excel Files With Printing Breaks Settings In PHP Using PHPSpreadSheet

| July 19, 2020 | 1,727 views | PhpSpreadsheet

To make sure the contents of the xlsx file is on it’s intended page, the function setBreak() can be used when coding with PhpSpreadsheet. This will make sure the data will start on which page it is set to begin with, leaving no doubt that the xlsx file generated will be printed correctly.

Requirements:

  • Composer
  • PHP 7.2 or newer

Step 1.

Setup dependencies.

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

Step 2.

Install phpspreadsheet.

$ composer install

Step 3.

Create a new PHP file, and start coding.

<?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 a row break on 'A10'
$sheet->setBreak('A10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW);

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

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

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

// Save the new .xlsx file
$writer->save('create-xlsx-files-with-printing-breaks-settings.xlsx');

Test.

Run the following codes.

$ php create-xlsx-files-with-printing-breaks-settings.php

Result.

Open the generated file create-xlsx-files-with-printing-breaks-settings.xlsx.

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

// Set a row break on 'A10'
$sheet->setBreak('A10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW);

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

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

Notes.

On the default view, the printing breaks settings are unnoticieable.

Click on View -> Page Break Preview to see the printing breaks settings.

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

// Set a row break on 'A10'
$sheet->setBreak('A10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW);

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

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

References:

0 Comments

Leave a Reply

Your email address will not be published.