How to Apply Borders to All Cells, Not a Range!

Question

I have got my Excel reader to work but there is an annoying problem where I can only find information on how to apply a border to a range of cells, which is useless for my application.

Users upload Excel sheets that can be viewed later on. So the cell range method won't work since the range changes.

Is there not a default parameter to set all cell styles etc?

Here is what I have:

require_once ROOT . '/libs/PHPExcel/IOFactory.php';
      $excel = PHPExcel_IOFactory::load('../public_html/uploads/' . $filename);
      PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
      $writer = PHPExcel_IOFactory::createWriter($excel, 'HTML');

      $writer->setUseInlineCSS(true);
      $styleArray = array(
          'borders' => array(
              'allborders' => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
              )
          )
      );

      $excel->getActiveSheet()->getStyle('A1:B1')->applyFromArray($styleArray);
      unset($styleArray);
      $writer->save('uploads/excel-sheet.html');
      header('Location: ' . WROOT . 'uploads/excel-sheet.html');

I don't work with php but logically thinking why all cells? You would make your workbook very heavy. Does php have any method to find the last cell in your data range? If yes then you can define your range and then apply the border to that range? — Siddharth Rout, Apr 30 '13 at 11:08

score 27 · Accepted Answer · answered Apr 30 '13 at 11:07

27

Set a default style that will apply to the whole workbook

$excel->getDefaultStyle()->applyFromArray($styleArray);

Though you should be able to read the range for any worksheet that has been loaded, and so set the style for that range

$excel->getActiveSheet()->getStyle(
    'A1:' . 
    $excel->getActiveSheet()->getHighestColumn() . 
    $excel->getActiveSheet()->getHighestRow()
)->applyFromArray($styleArray);

answered Apr 30 '13 at 11:07

Mark Baker

209,507
32
346
385

Thanks, that has worked for most cells but there are some that don't have a border applied. Any idea why this is? – imperium2335 Apr 30 '13 at 11:11
It suggests that those cells are explicitly formatted to border=none. Individual cells styles override range styles, which in turn override default styles – Mark Baker Apr 30 '13 at 11:11
2

@Siddharth Rout - there is, but `getHighestRow()` and `getHighestColumn()` aren't actually it :) Those functions return the highest column/row that contain something - but not necessarily data, it can include style information such as format masking of colour, or images that overlay the data area, or print breaks, or anything. There are equivalent methods, `getHighestDataRow()` and `getHighestDataColumn()` that return the highest references to cells that actually contain data; but they're less efficient to use because they actively check cell values – Mark Baker Apr 30 '13 at 11:19
Ah! the same as what `UsedRange.Rows` does in Excel. – Siddharth Rout Apr 30 '13 at 11:35
there is a function `$excel->getActiveSheet()->calculateWorksheetDimension()` – Alex78191 Nov 11 '21 at 13:23

score 0 · Answer 2 · answered Jun 20 '23 at 10:36

To apply border style to entire cell in sheet, you can do with this code

$objPHPExcel = new \PHPExcel();

$objPHPExcel->getActiveSheet()->getDefaultStyle()->applyFromArray(
    array(
        'borders' => array(
            'allborders' => array(
                'style' => \PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('rgb' => 'FFFFFF')
            )
        )
    )
);

or use this code

$objPHPExcel->getActiveSheet()->getDefaultStyle()
    ->getBorders()
    ->getTop()
    ->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN)
    ->setColor(new \PHPExcel_Style_Color('FFFFFF'));
$objPHPExcel->getActiveSheet()->getDefaultStyle()
    ->getBorders()
    ->getBottom()
    ->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN)
    ->setColor(new \PHPExcel_Style_Color('FFFFFF'));
$objPHPExcel->getActiveSheet()->getDefaultStyle()
    ->getBorders()
    ->getLeft()
    ->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN)
    ->setColor(new \PHPExcel_Style_Color('FFFFFF'));
$objPHPExcel->getActiveSheet()->getDefaultStyle()
    ->getBorders()
    ->getRight()
    ->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN)
    ->setColor(new \PHPExcel_Style_Color('FFFFFF'));

How to Apply Borders to All Cells, Not a Range!

2 Answers2