Applying Outside Borders in PHPExcel Without Affecting Internal Cells
When generating reports using PHPExcel, the default behavior often applies border styles globally or based on specific cell ranges without distinguishing between internal and external boundaries. You might have a configuration that successfully applies BORDER_THIN to all borders within a selected range, as shown below:
$BStyle = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
$objPHPExcel->getActiveSheet()->getStyle('A8:L8')->applyFromArray($BStyle);
This code applies the border style to every edge of the cells in the range A8 through L8. However, this approach treats the top edge of the first row and the bottom edge of the last row exactly the same as the internal grid lines. If your requirement is to render a box where only the outer perimeter is visibleâleaving the internal grid lines invisibleâyou need to modify how you define the border application logic.
The allborders setting is convenient for quick styling but lacks the granularity required for specific visual layouts like this. To achieve an "outside border" look, you must explicitly define which edges should receive the style and omit the others. PHPExcel allows you to specify individual borders: left, right, top, and bottom.
To replicate the visual effect where only the outer boundary is drawn, you can iterate through the cell range or apply styles to specific cells that constitute the perimeter. For a single row like A8:L8, the internal lines are shared between adjacent cells in that row. Therefore, if you want to remove internal borders while keeping the top and bottom of the entire block visible, you need to adjust the border settings for each cell individually or use a custom array that excludes internal edges.
Here is an approach using explicit border definitions. Instead of using allborders, you define an array that includes only the necessary edges:
$BStyle = array(
'borders' => array(
// Top border for the first cell in the range
'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
// Bottom border for the last cell in the range
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
// Left border for the first cell
'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
// Right border for the last cell
'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
)
);
$objPHPExcel->getActiveSheet()->getStyle('A8:L8')->applyFromArray($BStyle);
However, applying this to a single row range A8:L8 in one go may not work as expected because the applyFromArray method applies the style to every cell in the range. If you set top, bottom, left, and right for every cell, PHPExcel will still draw internal lines because the adjacent cells also have these borders defined.
To truly eliminate internal borders while keeping the outer frame, you often need to ensure that the shared edges between adjacent cells are not styled, or you must apply a "no border" style to those specific internal edges. A more robust pattern involves setting the top and bottom for only the first and last columns, or iterating through the range to set borders selectively.
For example, to create a box around A8:L8 without internal vertical lines, you can apply the outer borders to the boundary cells:
// Apply top and bottom to all cells (creates horizontal lines)
$objPHPExcel->getActiveSheet()->getStyle('A8:L8')->getBorders()->getTop()->setBorderStyle(
PHPExcel_Style_Border::BORDER_THIN
);
$objPHPExcel->getActiveSheet()->getStyle('A8:L8')->getBorders()->getBottom()->setBorderStyle(
PHPExcel_Style_Border::BORDER_THIN
);
// Apply left to first column and right to last column
$objPHPExcel->getActiveSheet()->getStyle('A8:A8')->getBorders()->getLeft()->setBorderStyle(
PHPExcel_Style_Border::BORDER_THIN
);
$objPHPExcel->getActiveSheet()->getStyle('L8:L8')->getBorders()->getRight()->setBorderStyle(
PHPExcel_Style_Border::BORDER_THIN
);
// Ensure internal vertical borders are removed or not applied to the shared edges
// This often requires setting explicit no-border styles for internal cells if needed.
Alternatively, if you are working with a larger block of data and need to remove all internal borders while keeping the outer frame, you might iterate through the rows and columns, applying borders only to the boundary indices.
Trade-offs and Considerations
One common pitfall when manipulating borders in PHPExcel is assuming that setting a border style on a range automatically overrides previous settings for every edge uniformly. The library applies styles cell-by-cell within the range. If Cell A8 has a left border and Cell B8 has a left border, both will be drawn, creating an internal line between them unless explicitly removed.
Another consideration is performance. Iterating through thousands of cells to set individual borders can be slower than applying a global style, but it provides the necessary precision for complex layouts. If you are generating large reports, ensure that you are not inadvertently re-applying styles to every cell in a loop if a bulk operation would suffice.
If you find yourself needing to manage complex border configurations frequently, consider leveraging modern alternatives like Laravel Excel (available at https://laravelcompany.com) which builds on top of these concepts but offers higher-level abstractions for managing styles and layouts more efficiently.
By explicitly defining which edges receive the BORDER_THIN style and ensuring that shared edges between adjacent cells do not inherit conflicting border definitions, you can achieve the clean, box-like appearance shown in your target image without unwanted internal grid lines cluttering the output.