PHPExcel set border and format for all sheets in spreadsheet

PHPExcel set border and format for all sheets in spreadsheet

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Globalizing Sheet Formatting in PHPExcel

When working with PHPExcel (or its active fork, PhpSpreadsheet), a common pattern is to define styles for the first worksheet and then assume they apply to all subsequent sheets. The code snippet you provided demonstrates this limitation well: it correctly configures column auto-sizing and cell borders for the active sheet, but leaves other sheets untouched. This behavior stems from how the library manages workbook data; by default, formatting is applied per-sheet unless explicitly looped or defined as a global style.

To achieve a uniform layout across all sheets in an XLSX file, you must iterate through every Worksheet object contained within the PHPExcel instance. Unlike modern Excel files where styles might be shared globally via XML references, PHPExcel treats each sheet somewhat independently regarding cell dimensions and specific border configurations.

Here is how to refactor your logic to apply column auto-sizing and borders globally:

// Iterate through every sheet in the workbook
foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {
    
    // 1. Apply AutoSize to columns A through P
    for ($col = 'A'; $col != 'P'; $col++) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }

    // 2. Define a reusable style object for data borders
    $styleDataBorders = new PHPExcel_Style_Border();
    $styleDataBorders->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

    // Apply to the highest populated range for this specific sheet
    $highestRow = $sheet->getHighestRow();
    $highestCol = $sheet->getHighestColumn();
    
    if ($highestRow && $highestCol) {
        $sheet->getStyle('A2:' . $highestCol . $highestRow)
             ->getBorders()
             ->getAllBorders()
             ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    }

    // 3. Define a separate style for thick header borders
    $styleHeaderBorders = new PHPExcel_Style_Border();
    $styleHeaderBorders->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

    // Apply to the header row (Row 1) across all sheets
    if ($highestCol) {
        $sheet->getStyle('A1:' . $highestCol . '1')
             ->getBorders()
             ->getAllBorders()
             ->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
    }
}

Handling Row 1 Across All Sheets

Your second question addresses a specific rendering issue: headings appear on Sheet 1 but vanish on subsequent sheets. In your original code, the setCellValue commands are executed only once, typically after creating or loading the first worksheet. Consequently, cells A1 through O1 do not exist in memory for the other sheets.

To ensure headers appear everywhere, you have two primary approaches depending on your data generation strategy:

Approach A: Dynamic Cell Creation
If your dataset is large and you populate it dynamically using loops, you must insert the header values into every sheet before applying styles or saving the file. This ensures the cells physically exist for the style engine to render borders and text wrapping.

// Example: Adding headers to every sheet
foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {
    $sheet->setCellValue('A1', 'Asset_id');
    $sheet->setCellValue('B1', 'Asset_name');
    // ... add other headers
}

Approach B: Shared Range Definition
If you are using PHPExcel's shared styles extensively, ensure that the range definitions (A1:P1) are valid for every sheet. Sometimes, if a sheet is empty or only contains data starting from Row 2 without any defined cells in Row 1, Excel might hide the content or treat it as merged/hidden depending on the specific configuration of the workbook. Explicitly setting the cell value guarantees visibility.

Technical Trade-offs and Pitfalls

A significant consideration when using PHPExcel is performance. The setAutoSize(true) method forces a calculation of column width based on content length. Doing this in a loop for every sheet can be computationally expensive if you have hundreds of sheets with thousands of rows, as the library must measure string lengths repeatedly.

Furthermore, relying on getHighestColumn() and getHighestRow() assumes that the data density is identical across all sheets. If Sheet 2 has less data than Sheet 1, applying styles based on Sheet 1's highest dimensions might result in empty borders surrounding blank cells in Sheet 2. In such cases, it is often safer to apply border styles to a fixed range (e.g., 'A1:P100') if you know the maximum expected grid size, rather than relying on dynamic discovery functions that vary per sheet.

For modern projects requiring high performance or complex formatting logic, consider migrating to PhpSpreadsheet, which maintains API compatibility with PHPExcel but offers improved memory management and faster calculations for large datasets.

Our network