Create Excel Files Specifying The Printing Area In PHP Using PHPSpreadSheet

| July 26, 2020 | 1,436 views | PhpSpreadsheet

Define print area when creating xlsx file in PhpSpreadsheet by setting specific cell row and column, this will give control in which particular part of the spreadsheet should only be printed.

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 cell A11 with the "Hello World Again !" string value
$sheet->setCellValue('A11', 'Hello World Again !');

// Set cell D15 with the "End here !" string value
$sheet->setCellValue('D15', 'End here !');

// Set the printing area from A1 to D15
$sheet->getPageSetup()->setPrintArea('A1:D15');

// Set the printing area from A1 to B10 and A11 to D15
// $sheet->getPageSetup()->setPrintArea('A1:B10,A11:D15');

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

// Save the new .xlsx file
$writer->save('create-xlsx-files-specifying-the-printing-area.xlsx');

Test.

Run the following codes.

$ php create-xlsx-files-specifying-the-printing-area.php

Result.

Open the generated file create-xlsx-files-specifying-the-printing-area.xlsx.

Click View.

Click Page Break Preview.

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

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

// Set cell D15 with the "End here !" string value
$sheet->setCellValue('D15', 'End here !');

// Set the printing area from A1 to D15
$sheet->getPageSetup()->setPrintArea('A1:D15');

Test Again.

Add two printing areas.

// Set the printing area from A1 to B10 and A11 to D15
$sheet->getPageSetup()->setPrintArea('A1:B10,A11:D15');

References:

0 Comments

Leave a Reply

Your email address will not be published.