Styling cell borders with PhpSpreadsheet PHP

Question

I use PhpSpreadsheet to read o write in Excel files. I want to add to my excel a border style so I used this code:

<?php
    $fxls ='myfile.xlsx';
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fxls);
    $xls_data = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
    $sheet = $spreadsheet->getActiveSheet();

    $styleArray = array(
        'borders' => array(
            'outline' => array(
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
                'color' => array('argb' => 'FFFF0000'),
            ),
        ),
    );

    $sheet ->getStyle('B2:G8')->applyFromArray($styleArray);

    /* Generate the Excel File */
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="myNEWFile.xlsx"');
    header('Cache-Control: max-age=0');
    header('Cache-Control: max-age=1');
    header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
    header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT');
    header ('Cache-Control: cache, must-revalidate');
    header ('Pragma: public');
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;

I get no error but the excel file is created without border. What I miss !??

Answer 1

tl;dr

Beyond the style array way you can also do it in the method chaining way:

use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;

$spreadsheet
    ->getActiveSheet()
    ->getStyle('B2')
    ->getBorders()
    ->getOutline()
    ->setBorderStyle(Border::BORDER_THICK)
    ->setColor(new Color('FFFF0000'));

Blatantly copied from the official docs on cell formatting.

Available border patterns

The docs on cell formatting contains a list of available patterns and their keys used in the style array form. For the method chaining form just slap a get before the capitalized version of the key. These methods are all available under ->getActiveSheet()->getStyle('B2')->getBorders() just like in the example.

• On a single cell:
  • left: ->getLeft()
  • right: ->getRight()
  • top: ->getTop()
  • bottom: ->getBottom()
  • diagonal: ->getDiagonal()
• On an area:
  • allBorders: ->getAllBorders()
  • outline: ->getOutline()
  • inside: ->getInside()
  • vertical: ->getVertical()
  • horizontal: ->getHorizontal()

The patterns visualized (also from the docs):

Available border styles

Border::BORDER_DASHDOT
Border::BORDER_DASHDOTDOT
Border::BORDER_DASHED
Border::BORDER_DOTTED
Border::BORDER_DOUBLE
Border::BORDER_HAIR
Border::BORDER_MEDIUM
Border::BORDER_MEDIUMDASHDOT
Border::BORDER_MEDIUMDASHDOTDOT
Border::BORDER_MEDIUMDASHED
Border::BORDER_NONE
Border::BORDER_SLANTDASHDOT
Border::BORDER_THICK
Border::BORDER_THIN

Answer 2

$styleArray = array(
    'borders' => array(
        'outline' => array(
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
            'color' => array('argb' => 'FFFF0000'),
        ),
    ),
);

Replace by :

$styleArray = array(
    'borders' => array(
        'outline' => array(
            'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
            'color' => array('argb' => 'FFFF0000'),
        ),
    ),
);

See in line 169-203.

borderStyle has been add after the 1.0.0-beta2 release in 2017-11-26.

Before, borders configuration was still with style

Answer 3

You need reassign value to sheet:

$styleArray = array(
    'borders' => array(
        'outline' => array(
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
            'color' => array('argb' => 'FFFF0000'),
        ),
    ),
);

$sheet = $sheet ->getStyle('B2:G8')->applyFromArray($styleArray);