Create Xlsx Files With Cell Border Styles

| August 6, 2020 | 2,357 views | PhpSpreadsheet

Establish boundaries on an a certain area of a spreadsheet when creating an xlsx file in PhpSpreadsheet. Surround a cell or a range of cells without difficulty, set a thick or thin borderline at the top, bottom, left, or right side of a cell/s, also a diagonal line is possible.

Just for a little nicer result presentation, the PhpSpreadsheet tutorial Create Xlsx Files With Text Aligned Horizontally will be used here.

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 B2 with the "Top left" string value
$sheet->setCellValue('B2', 'Top left');

// Set cell B6 with the "Lower left" string value
$sheet->setCellValue('B6', 'Lower left');

// Set cell F2 with the "Top right" string value
$sheet->setCellValue('F2', 'Top right');

// Set cell F6 with the "Lower right" string value
$sheet->setCellValue('F6', 'Lower right');

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

// Prepare style array container
$styleArray = [
    'borders' => [
        'outline' => [
        ],
    ],
];

// Set border color to light blue
$styleArray['borders']['outline']['color']['argb'] = 'FF538dd5';

// Set border style to thick
$styleArray['borders']['outline']['borderStyle'] = \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK;

// Apply border style around the area of B2 to F6
$sheet->getStyle('B2:F6')->applyFromArray($styleArray);

// Set border color to dark red
$styleArray['borders']['outline']['color']['argb'] = 'FFc00000';

// Set border style to thin
$styleArray['borders']['outline']['borderStyle'] = \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN;

// Apply border style to D4
$sheet->getStyle('D4')->applyFromArray($styleArray);

// Set B2 to F6 horizontal alignment to center
$sheet->getStyle('B2:F6')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

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

// Save the new .xlsx file
$writer->save('create-xlsx-files-with-cell-border-styles.xlsx');

Test.

Run the following codes.

$ php create-xlsx-files-with-cell-border-styles.php

Result.

Open the generated file create-xlsx-files-with-cell-border-styles.xlsx.

// Set cell B2 with the "Top left" string value
$sheet->setCellValue('B2', 'Top left');

// Set cell B6 with the "Lower left" string value
$sheet->setCellValue('B6', 'Lower left');

// Set cell F2 with the "Top right" string value
$sheet->setCellValue('F2', 'Top right');

// Set cell F6 with the "Lower right" string value
$sheet->setCellValue('F6', 'Lower right');

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

// Prepare style array container
$styleArray = [
    'borders' => [
        'outline' => [
        ],
    ],
];

// Set border color to light blue
$styleArray['borders']['outline']['color']['argb'] = 'FF538dd5';

// Set border style to thick
$styleArray['borders']['outline']['borderStyle'] = \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK;

// Apply border style around the area of B2 to F6
$sheet->getStyle('B2:F6')->applyFromArray($styleArray);

// Set border color to dark red
$styleArray['borders']['outline']['color']['argb'] = 'FFc00000';

// Set border style to thin
$styleArray['borders']['outline']['borderStyle'] = \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN;

// Apply border style to D4
$sheet->getStyle('D4')->applyFromArray($styleArray);

// Set B2 to F6 horizontal alignment to center
$sheet->getStyle('B2:F6')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

// Prepare style array container
$styleArray = [
    'borders' => [
        'outline' => [
        ],
    ],
];

// Set border color to light blue
$styleArray['borders']['outline']['color']['argb'] = 'FF538dd5';

// Set border style to thick
$styleArray['borders']['outline']['borderStyle'] = \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK;

// Apply border style around the area of B2 to F6
$sheet->getStyle('B2:F6')->applyFromArray($styleArray);

// Prepare style array container
$styleArray = [
    'borders' => [
        'outline' => [
        ],
    ],
];

// Set border color to dark red
$styleArray['borders']['outline']['color']['argb'] = 'FFc00000';

// Set border style to thin
$styleArray['borders']['outline']['borderStyle'] = \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN;

// Apply border style to D4
$sheet->getStyle('D4')->applyFromArray($styleArray);

References:

0 Comments

Leave a Reply

Your email address will not be published.