From data export to report: styles, auto-width, frozen headers, filters, and multiple sheets

aVadim • 08.10.2026 13:36 •

Most PHP developers eventually build an “export to Excel” feature. The first version usually looks the same: loop over the data, call writeRow(), and you’re done. Open the file, though, and it looks like a CSV dump: no header, default column widths, and amounts without thousands separators. A manager scrolls down, loses sight of the headings, and asks: “What’s in the fourth column?”

In this article, we’ll turn a raw array into a report you can send to management. We’ll use avadim/fast-excel-writer, a streaming XLSX writer written in pure PHP. All examples have been checked against version 6.17. We’ll cover individual techniques first, then bring them together in a complete runnable script.

Our running example is a monthly sales report. Here’s the starting point — just the data:

php
use avadim\FastExcelWriter\Excel;

// In a real project, $rows comes from a SQL query:
// [date, sales rep, product, quantity, price, amount]
$excel = Excel::create(['Sales']);
$sheet = $excel->sheet();

foreach ($rows as $row) {
    $sheet->writeRow($row);
}

$excel->save('sales.xlsx');

It works, but it doesn’t look useful yet. Let’s improve it.

Step 1. Style and write the table header in one call

A style in FastExcelWriter is an ordinary array of options for fonts, fills, borders, and alignment. The second argument to writeRow() accepts this array and applies it to the row being written:

php
$headerStyle = [
    'font-style' => 'bold',
    'font-color' => '#ffffff',
    'fill-color' => '#4472c4',   // brand blue
    'text-align' => 'center',
    'border'     => 'thin',
    'height'     => 24,
];

$sheet->writeRow(['Date', 'Sales rep', 'Product', 'Qty', 'Price', 'Amount'], $headerStyle);

There’s also a dedicated writeHeader() method. It writes the header row and sets column formats and styles for subsequent rows:

php
$sheet->writeHeader([
    // <heading> => [<column style/format>]
    'Date'     => ['format' => '@date',    'width' => 12],
    'Sales rep' => ['format' => '@string',  'width' => 16],
    'Product'    => ['width' => 'auto'],
    'Qty'   => ['format' => '@integer', 'width' => 9],
    'Price'     => ['format' => '#,##0.00', 'width' => 12],
    'Amount'    => ['format' => '#,##0.00', 'width' => 14],
], $headerStyle);

One call gives us a styled header and column formats. Subsequent writeRow() calls can use plain data; the formatting is applied automatically. You can also style a row with a chain of apply...() methods:

php
$sheet->writeRow(['Date', 'Sales rep', 'Product', 'Qty', 'Price', 'Amount'])
    ->applyFontStyleBold()
    ->applyFillColor('#4472c4')
    ->applyFontColor('#ffffff')
    ->applyTextAlign('center')
    ->applyBorder('thin')
    ->applyRowHeight(24);

This chain styles the current row but doesn’t set column formats. For those, use writeHeader() with column settings or separate setColFormats() calls. Call apply...() methods immediately after writing, while the row is still buffered and before moving to the next row.

Step 2. Column widths and row heights

Default widths are a common reason exports look untidy. Set widths individually, as a list, or automatically:

php
$sheet->setColWidth('C', 40); // one column

// Set several at once: explicit widths and auto-width
$sheet->setColWidths(['A' => 12, 'B' => 16, 'C' => 'auto', 'D' => 9, 'E' => 12, 'F' => 14]);

// Auto-width with a lower bound: fit the content, but at least 20
$sheet->setColMinWidth('C', 20);
$sheet->setColWidthAuto('C');

Auto-width has two caveats. First, enable it before writing data: widths are calculated as rows are output, and the library doesn’t reread rows that have already been flushed. Second, the calculation is approximate. It accounts for text, formatting, and font settings, but doesn’t measure how Excel actually renders the cells. Explicit widths are more reliable for predictable content such as dates and amounts; reserve auto for text columns.

Set row heights when writing ('height' => 24 in the row style) or immediately afterward with applyRowHeight(24). Settings that target particular rows, such as setRowHeight(2, 24) and setRowHeights([1 => 30, 2 => 24]), are easiest to define in advance. They also work for the current buffered row, but not for rows already flushed to the temporary file.

Step 3. Two lines that save users time

If you remember only one snippet from this article, make it this one:

php
$sheet->setFreeze('A3');    // freeze everything above row 3
$sheet->setAutoFilter(2);   // filter on row 2 (the header row)

setFreeze('A3') freezes the rows above the specified cell ('B3' would also freeze column A to the left). The header stays visible as users scroll through hundreds of rows. setAutoFilter(2) adds Excel’s standard filter dropdowns to the header, so a manager can filter sales by employee without asking you for “the same report, but only for Smith.” Both settings are worksheet properties and can be applied at any point before save().

This layout assumes that row 1 contains the report title, row 2 contains the table header, and data starts in row 3. When you pass only a row number, the filter covers the range from the header to the end of the populated sheet. If there’s a totals row at the bottom, specify an exact range that excludes it:

php
$lastDataRow = count($rows) + 2;
$sheet->setAutoFilter('A2:F' . $lastDataRow);

Step 4. Format the data: numbers, alternating fills, and a report title

Number formats

We already set column formats through writeHeader(). If you build the header another way, use separate calls:

php
$sheet->setColFormat('E', '#,##0.00'); // price with thousands separators
// Or set all at once, starting at column A (null means default format)
$sheet->setColFormats(['@date', null, null, '@integer', '#,##0.00', '#,##0.00']);

Alongside standard Excel format codes, the library provides named formats: @date, @datetime, @integer, @money, and @string. The appearance of @money depends on the workbook’s locale and format settings. This report uses the explicit #,##0.00 format for amounts without a currency symbol. Set column formats before writing the data.

Alternating row fills

The library has no dedicated “banded rows” option, but there are two straightforward ways to get the effect. First, alternate the fill in the writing loop:

php
$i = 0;
foreach ($rows as $row) {
    $sheet->writeRow($row, ($i++ % 2) ? ['fill-color' => '#eef3fb'] : []);
}

Second, use formula-based conditional formatting, with one rule for the entire range:

php
use avadim\FastExcelWriter\Conditional\Conditional;

$zebra = Conditional::expression('MOD(ROW(),2)=0', ['fill-color' => '#eef3fb']);
$sheet->addConditionalFormatting('A3:F' . (count($rows) + 2), $zebra);

A report title above the table

The report title looks better when it spans the full table width. Write it first, before the header and data. Calling writeTo() with a range writes the value and merges the cells in one operation:

php
$sheet->writeTo('A1:F1', 'Sales report — June 2026')
    ->applyFontStyleBold()
    ->applyFontSize(14)
    ->applyTextCenter();

To merge cells that have already been written, use mergeCells('A1:F1'). This works even for rows flushed to disk: it adds merge metadata rather than rewriting cell values. Avoid writing different values into cells that you intend to merge; the merged range displays the value from its top-left cell.

Range borders

Use withRange() to add a border around a block of cells. Run this snippet immediately after writeHeader() for row 2, before writing the data:

php
use avadim\FastExcelWriter\Style\Style;

$sheet->withRange('A2:F2')
    ->applyOuterBorder(Style::BORDER_MEDIUM)
    ->applyInnerBorder(Style::BORDER_THIN);

For data rows, 'border' => 'thin' in the row style is often enough — it adds a border to each cell as it is written.

withRange() cannot style an arbitrary range from earlier in the sheet. Its rows must still be buffered or lie ahead of the current writing position. That’s why we style the header border before moving to the next row.

Step 5. Multiple sheets: summary and details

A useful report starts with totals and puts the details on a second sheet. Create sheets up front with Excel::create() or add them with makeSheet():

php
$excel = Excel::create(['Summary']);       // workbook with a Summary sheet
$details = $excel->makeSheet('Sales');  // second sheet
// ... populate Sales as shown above ...
$lastDataRow = count($rows) + 2;

// Summary sheet: formulas can reference other sheets
$summary = $excel->getSheet('Summary');
$summary->writeRow(
    ['Total, RUB', "=SUM('Sales'!F3:F" . $lastDataRow . ')'],
    null,
    ['B' => ['format' => '#,##0.00']]
);

// Which sheet opens first
$excel->setActiveSheet('Summary');

The library doesn’t evaluate formulas itself — Excel calculates them when the file opens. The formula includes only the data range: summing the entire F:F column would also include the totals row on the Sales sheet and double the result. The third argument to writeRow() sets styles for individual cells; here, only column B gets the amount format.

Complete example

Let’s put everything together, from an array to a finished report:

php
<?php
require __DIR__ . '/vendor/autoload.php';

use avadim\FastExcelWriter\Excel;

// Data (normally from a database)
$rows = [
    ['2026-06-01', 'Smith',  'Alpha 15 laptop', 2, 78900.00, 157800.00],
    ['2026-06-01', 'Johnson', 'ProView monitor',  5, 21450.00, 107250.00],
    // ...
];

$excel = Excel::create(['Summary']);

// ---------- Sales sheet ----------
$sheet = $excel->makeSheet('Sales');

// Report title: merged cells A1:F1
$sheet->writeTo('A1:F1', 'Sales report — June 2026')
    ->applyFontStyleBold()
    ->applyFontSize(14)
    ->applyTextCenter()
    ->applyRowHeight(30);

// Product names can be long: auto-width ('auto' below), but at least 20
$sheet->setColMinWidth('C', 20);

// Table header, column formats, and widths in one call
$headerStyle = [
    'font-style' => 'bold',
    'font-color' => '#ffffff',
    'fill-color' => '#4472c4',
    'text-align' => 'center',
    'border'     => 'thin',
    'height'     => 24,
];
$sheet->writeHeader([
    'Date'     => ['format' => '@date',    'width' => 12],
    'Sales rep' => ['format' => '@string',  'width' => 16],
    'Product'    => ['width' => 'auto'],
    'Qty'   => ['format' => '@integer', 'width' => 9],
    'Price'     => ['format' => '#,##0.00', 'width' => 12],
    'Amount'    => ['format' => '#,##0.00', 'width' => 14],
], $headerStyle);

// Data: borders and alternating fills
$i = 0;
foreach ($rows as $row) {
    $rowStyle = ['border' => 'thin'];
    if ($i++ % 2) {
        $rowStyle['fill-color'] = '#eef3fb';
    }
    $sheet->writeRow($row, $rowStyle);
}

// Totals row
$lastDataRow = count($rows) + 2; // row 1 is the title; row 2 is the table header
$sheet->writeRow(
    ['Total', null, null, null, null, '=SUM(F3:F' . $lastDataRow . ')'],
    ['font-style' => 'bold', 'fill-color' => '#d9e2f3', 'border' => 'thin']
);

// Frozen headers and a filter that excludes the totals row
$sheet->setFreeze('A3');
$sheet->setAutoFilter('A2:F' . $lastDataRow);

// ---------- Summary sheet ----------
$summary = $excel->getSheet('Summary');
$summary->setColWidths(['A' => 24, 'B' => 16]);
$summary->writeRow(['Detail rows', count($rows)]);
$summary->writeRow(
    ['Total, RUB', "=SUM('Sales'!F3:F" . $lastDataRow . ')'],
    null,
    ['B' => ['format' => '#,##0.00']]
);

$excel->setActiveSheet('Summary');
$excel->save('sales-report.xlsx');

The result has a title row, frozen headers, filters, number formats, alternating row fills, totals, and a summary sheet. This example assumes a nonempty dataset. If your query returns no rows, display a separate message instead of generating formulas with the range F3:F2.

Streaming means writing forward

FastExcelWriter is a streaming library: processed rows are flushed to a temporary file, while the current row stays in a buffer for a short time. The library doesn’t keep the entire worksheet in memory, so you can’t return to a row once it has been flushed. In practice:

  • Pass row styles to writeRow() or apply them through apply...() immediately after writing, before moving to the next row.
  • Set setRowHeight() and setRowStyle() in advance or while the target row is still buffered. Trying to change a flushed row throws the exception “Row number must be greater than written rows.”
  • Enable auto-width before writing data.
  • You can call mergeCells() for earlier rows. For complex headers, beginArea() and makeArea() provide areas with random access to buffered data. They don’t restore access to flushed rows, and their memory usage grows with the contents of the area.

Reports usually follow a top-to-bottom layout, so this writing order suits tabular exports. Total memory use still depends on your code: the example’s $rows array is held entirely in memory. For large datasets, feed rows incrementally, reuse styles, and remember that auto-width caches text measurements. Fixed widths give more predictable resource usage. FastExcelWriter creates new XLSX files; reading and editing existing workbooks requires a different tool.

Takeaways

  • Report formatting combines header and data styles, column widths, number formats, frozen rows, and an exact filter range.
  • Define styles as option arrays or chains of apply...() methods while the target row is still buffered.
  • Alternate fill-color in the loop or apply one Conditional::expression() rule to a range.
  • Excel calculates the summary sheet’s formulas. Limit them to the source data range so totals aren’t counted twice.

Install the library:

bash
composer require avadim/fast-excel-writer:6.17.0

Documentation and runnable examples (in demo/) are in the version 6.17 repository. Basic styles and streaming behavior were checked against Sheet.php and Style.php.

#fast-excel-writer #php #excel

Comments (0)