PHPExcel set border and format for all sheets in spreadsheet

Question

I'm currently trying to set all borders for my spreadsheet, also formatting such as autosize.

My code below is working, for sheet 1. All other sheets inside the spreadsheet are completely untouched. I've been trying to get it to work with all other sheets inside this spreadsheet but with no luck.

Any ideas on how i can global set the formatting so that all sheets have borders and autosize? The layout of all sheets in this spreadsheet are all the same. I'm exporting to XLSX file.

Cheers,

/**autosize*/
for ($col = 'A'; $col != 'P'; $col++) {
    $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
}


/** Borders for all data */
   $objPHPExcel->getActiveSheet()->getStyle(
    'A2:' . 
    $objPHPExcel->getActiveSheet()->getHighestColumn() . 
    $objPHPExcel->getActiveSheet()->getHighestRow()
)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);



/** Borders for heading */
   $objPHPExcel->getActiveSheet()->getStyle(
    'A1:O1'
)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

EXTRA QUESTION: I've currently set headings. The headings do appear on sheet 1, but do not appear in any other sheets.. is it possible to show the headings in all sheets? The headings are set in Row 1. and the results are from Row 2 down.

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Asset_id');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Asset_name');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Asset_type');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'Asset_make');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'Asset_model');

Mark Baker · Accepted Answer · 2013-10-16T08:36:55.630

43

You can set a default style for the entire workbook (all worksheets):

$objPHPExcel->getDefaultStyle()
    ->getBorders()
    ->getTop()
        ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getDefaultStyle()
    ->getBorders()
    ->getBottom()
        ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getDefaultStyle()
    ->getBorders()
    ->getLeft()
        ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getDefaultStyle()
    ->getBorders()
    ->getRight()
        ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

or

  $styleArray = array(
      'borders' => array(
          'allborders' => array(
              'style' => PHPExcel_Style_Border::BORDER_THIN
          )
      )
  );
$objPHPExcel->getDefaultStyle()->applyFromArray($styleArray);

And this can be used for all style properties, not just borders.

But column autosizing is structural rather than stylistic, and has to be set for each column on each worksheet individually.

EDIT

Note that default workbook style only applies to Excel5 Writer

edited Oct 16 '13 at 08:36

answered Oct 16 '13 at 08:10

Mark Baker

209,507
32
346
385

I got an error for option 1: Fatal error: Uncaught exception 'Exception' with message 'Can only get pseudo-border for supervisor.' in C:\wamp\www\site\Excel\Classes\PHPExcel\Style\Borders.php on line 384 ( ! ) Exception: Can only get pseudo-border for supervisor. in C:\wamp\www\site\Excel\Classes\PHPExcel\Style\Borders.php on line 384 – Tuzki Oct 16 '13 at 08:20
My bad, you have to set individual borders, or use the applyFromArray() method to set allborders – Mark Baker Oct 16 '13 at 08:39
I've decided to use Excel5 and its now working: option 1, HOWEVER is there any way I can limit the amount of borders drawn like from my code above, from A2? – Tuzki Oct 16 '13 at 09:06
There isn't any way you can limit it if you set a default; because by its nature a default applies to everything unless you specify otherwise... a workbook default automatically applies to all cells in all worksheets. But you can over-ride the default for individual cells or cell ranges – Mark Baker Oct 16 '13 at 09:16
One more question, I've posted another edit in the original question. Thanks – Tuzki Oct 16 '13 at 09:24
There is no automatic way of setting headings in all sheets automatically; though you could copy content and style from one sheet to the next as you create each new sheet, or clone the sheet with only the headings and add the clone to the workbook rather than creating a new sheet – Mark Baker Oct 16 '13 at 09:28

score 1 · Answer 2 · edited May 01 '16 at 22:15

1

for ($s=65; $s<=90; $s++) {
    //echo chr($s);
    $objPHPExcel->getActiveSheet()->getColumnDimension(chr($s))->setAutoSize(true);
}

edited May 01 '16 at 22:15

Misa Lazovic

2,805
10
32
38

answered May 01 '16 at 06:19

Japarradog

11
4

ascii capital letter offsets. – TARKUS Jun 22 '16 at 00:13

score 0 · Answer 3 · answered May 21 '16 at 00:19

0

To answer your extra question:

You can set which rows should be repeated on every page using:

$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 5);

Now, row 1, 2, 3, 4 and 5 will be repeated.

answered May 21 '16 at 00:19

Philip

2,888
2
24
36

score 0 · Answer 4 · answered Feb 23 '22 at 16:04

In 2022 (phpoffice/phpspreadsheet v1.22.0), based on latest changes, the solution for applying a style to entire worksheet is:

$worksheet->getParent()->getDefaultStyle()->applyFromArray([
    'font' => [
        'name' => $pValue->getFont()->getName(),
        'size' => $pValue->getFont()->getSize(),
    ],
]);

for more info, refer to this link

based on documentation, now we have:

Worksheet::setDefaultStyle()

// Before
$worksheet->setDefaultStyle($value);

// After
$worksheet->getParent()->getDefaultStyle()->applyFromArray([
    'font' => [
        'name' => $pValue->getFont()->getName(),
        'size' => $pValue->getFont()->getSize(),
    ],
]);

PHPExcel set border and format for all sheets in spreadsheet

4 Answers4

Linked