DOCSEARCHbyAlgolia
Submit Docs

FastExcelWriter

repository·master·Indexed 43 days ago

https://github.com/avadim483/fast-excel-writer

A high-performance, lightweight PHP library for writing XLSX spreadsheets. Optimized for speed and minimal memory consumption, it is designed for generating very large Excel files (100K+ rows) and is compatible with MS Excel (2007+), LibreOffice, and OpenOffice. Supports UTF-8, multiple worksheets, formulas, cell/row/column styling, conditional formatting, images, and charts. Requires PHP 7.4+.

Tokens
47.5K
Snippets
146
Records
196
Agent score
53%

What's inside FastExcelWriter

  1. FastExcelWriter Overview and Features

    master

    FastExcelWriter is a lightweight, pure PHP XLSX spreadsheet writer designed for high performance and minimal memory usage. It is optimized for writing large datasets (e.g., 100K+ rows).

    Key Features:

    • Format Support: Creates XLSX files compatible with MS Excel (2007+), LibreOffice, and OpenOffice.
    • Data Handling: Supports UTF-8 input, multiple worksheets, formulas, active hyperlinks, and data validations.
    • Formatting: Supports cell/row/column styling (colors, borders, fonts), currency/date/numeric formats, and conditional formatting.
    • Layout: Supports row height, column width (including auto-width), page margins, and page sizes.
    • Advanced Elements: Supports adding notes, images, and multiple charts.
    • Security: Supports workbook and sheet protection (with or without passwords).

  2. FastExcelWriter API Overview

    master
    The avadim\FastExcelWriter namespace contains the core classes for generating Excel files. The API is structured around a central Excel class that manages the workbook, Sheet objects for individual worksheets, and specialized classes for advanced features like RichText, Charts, DataValidation, Conditional formatting, and Style management.

  3. Handle numeric string conversion in version 5.8

    master

    Starting from version 5.8, writeCell() treats numeric strings strictly as strings.

    Behavior Change:

    • Before 5.8: writeCell('12345') would be written as a number.
    • Version 5.8+: writeCell('12345') is written as a string.

    Backward Compatibility: To maintain the old behavior where numeric strings are automatically converted to numbers, use the auto_convert_number option when creating a workbook.

    $excel = Excel::create(['Sheet1'], ['auto_convert_number' => true]);
$sheet = $excel->sheet();
$sheet->writeCell('12345'); // String '12345' will be automatically converted to a number

  4. Understand Shared Strings vs Inline Strings

    master

    FastExcelWriter allows you to choose how strings are stored in the XLSX file.

    Inline Strings (Default)

    Strings are written directly into the sheets.

    • Pros: Memory usage does not depend on the amount of data (streaming mode).
    • Cons: Larger uncompressed XML size.

    Shared Strings ('shared_string' => true)

    Strings are stored in a separate sharedStrings.xml table, and cells refer to them by index.

    • Pros: Smaller uncompressed XML size; Excel opens the file faster.
    • Cons: Requires keeping a table of all unique strings in memory (approx. 0.5 KB per unique string). This is the only part of the writing process that is not streamed.

    Rule of thumb: Keep the default (inline) for large exports. Use shared_string only when values come from a limited dictionary (e.g., statuses, categories, names) that repeat frequently, or if the consumer specifically requires it.

    // Enable shared strings
$excel = Excel::create([], ['shared_string' => true]);
// OR
$excel = Excel::create();
$excel->setSharedString();

  5. FastExcelWriter vs PhpSpreadsheet

    master

    While PhpSpreadsheet is a feature-rich library supporting many formats, FastExcelWriter is specialized for high-performance XLSX writing.

    Key advantages of FastExcelWriter:

    • Speed: Approximately 7–9x faster than PhpSpreadsheet.
    • Memory Efficiency: Uses 8–10x less memory (often maintaining a constant low memory footprint even as data grows).
    • Scalability: Designed to handle massive datasets (100K+ rows) efficiently.

    Note: FastExcelWriter only supports writing XLSX files.

  6. Choose between Row-by-Row and Direct Writing

    master

    FastExcelWriter offers two writing modes depending on your needs for memory efficiency versus flexibility:

    1. Row-by-Row (Sequential) Writing: Uses the Sheet class. Cells are flushed to a temporary file as soon as you move to the next row. This is highly memory-efficient and suitable for millions of rows, but you cannot write to or style cells in a previous row once it has been flushed.

    2. Direct Writing (Area-based): Uses the Area class. You define one or more writing areas. Data is stored in memory and only written to the file when calling Sheet::writeAreas() or Excel::save(). This allows writing to arbitrary cells in any order but consumes more memory.

    Rule of thumb: Use Row-by-Row for large data exports. Use Areas only when you need to fill cells in an arbitrary order or modify previous rows.

    // Row-by-Row (Sequential)
$sheet = $excel->sheet();
$sheet->writeRow(['a', 'b', 'c']); // Flushes row 1
// $sheet->cell('A1')->applyBgColor('#ccc'); // ERROR: Cannot access previous row

// Direct Writing (Area)
$area = $sheet->beginArea();
$area->setValue('C3', 'text 3');
$area->setValue('A1', 'text 1'); // Allowed because it's in the area
$sheet->writeAreas();

  7. Handle multi-line text and text wrapping

    master

    To create line breaks within a cell, use the LF character (\n) in a string.

    Automatic Wrapping: FastExcelWriter automatically enables text-wrap for any string or rich text containing \n.

    Manual Control:

    • To disable automatic wrapping for a specific row: writeRow(['Line 1\nLine 2'], ['text-wrap' => false]).
    • To disable automatic wrapping globally for the entire workbook:
      • Use Excel::create([], ['auto_wrap_text' => false]) during creation.
      • Or call $excel->setAutoWrapText(false) on the workbook instance.

    Note: In PHP, \n is only interpreted as a line break in double-quoted strings ("Line 1\nLine 2"). In single-quoted strings, it is treated as literal text.

    // Automatic wrap (enabled by default for strings with \n)
$sheet->writeRow(["Line 1\nLine 2"]);

// Explicitly disabling wrap
$sheet->writeRow(["Line 1\nLine 2"], ['text-wrap' => false]);

// Global disable
$excel->setAutoWrapText(false);

  8. Define styles using arrays or the Style class

    master

    You can define styles for FastExcelWriter using either a simple associative array or the fluent Excel::newStyle() class.

    Using an array: Provide keys like format, font-color, text-align, fill-color, width, and text-wrap.

    Using the Style class: Use Excel::newStyle() to chain methods like setFillGradient(), setFormat(), setFontColor(), and setTextAlign(). When using the class, you must call ->toArray() to pass the style to writing methods.

    // Array approach
$style = [
    'format' => '#,##0.00',
    'font-color' => '#ff0000',
    'text-align' => 'center',
];
$sheet->writeCell(0.9, $style);

// Class approach
$style = Excel::newStyle()
    ->setFillGradient('#fff000', '#fff')
    ->setFormat('#,##0.00')
    ->setFontColor('#ff0000')
    ->setTextAlign(Style::TEXT_ALIGN_CENTER)
;
$sheet->writeCell(0.9, $style->toArray());

  9. Use the Conditional class for conditional formatting

    master

    The \avadim\FastExcelWriter\Conditional\Conditional class allows you to apply conditional formatting rules to Excel cells. You can create various types of rules such as value comparisons, text matching, color scales, and data bars. Most rules are implemented as static factory methods that return a Conditional instance.

    // Example of creating a conditional rule (conceptually)
$conditional = Conditional::greaterThan(10, ['font' => ['bold' => true]]);

  10. Manage Memory and Streaming Mode

    master

    To keep memory usage low, use Row-by-Row writing. This flushes rows to a temporary file immediately.

    Memory Consumption Warnings: While row-by-row writing is efficient, certain features are kept in memory until Excel::save() is called. If you have millions of rows, avoid applying these per-cell to prevent linear memory growth:

    • Areas: ~0.3 KB per cell.
    • Merged Cells: ~0.5 KB per merge.
    • Hyperlinks: ~1 KB per link (Max 65,530 per sheet).
    • Notes/Validation/Conditional Formatting: ~0.5-1 KB per rule.
    • Shared Strings: ~0.5 KB per unique string (if shared_string is enabled).

    Optimization Tip: Instead of applying conditional formatting to every single cell in a loop, apply one rule to the entire range at once:

    // BAD: 100,000 rules in memory
foreach ($rows as $i => $row) {
    $sheet->writeRow($row);
    $sheet->addConditionalFormatting('C' . ($i + 1), $conditional);
}

// GOOD: One rule for the whole range
$sheet->addConditionalFormatting('C1:C100000', $conditional);

  11. Create a simple chart in FastExcelWriter

    master

    To create a basic chart, use Chart::make() with a chart type, a title, and a data series array. The data series array uses the cell containing the series name as the key and the range of data as the value. After creating the chart, use $sheet->addChart('RANGE', $chart) to place it in the worksheet.

    Example of a Column Chart:

    // Define data series for chart
// key - cell with name of data series
// value - range with data series
$dataSeries = [
    'B1' => 'B2:B5', 
    'C1' => 'c2:c5', 
    'D1' => 'd2:d5',
];

$chartTitle = 'Bar Chart';

// Create chart
$chart = Chart::make(Chart::TYPE_COLUMN, $chartTitle, $dataSeries)
    ->setCategoryAxisLabels('A2:A5')
    ->setLegendPosition(Legend::POSITION_TOPRIGHT);

// Add the chart to the worksheet at a specific range
$sheet->addChart('A7:H20', $chart);
    $dataSeries = [
    'B1' => 'B2:B5', 
    'C1' => 'c2:c5', 
    'D1' => 'd2:d5',
];

$chartTitle = 'Bar Chart';

$chart = Chart::make(Chart::TYPE_COLUMN, $chartTitle, $dataSeries)
    ->setCategoryAxisLabels('A2:A5')
    ->setLegendPosition(Legend::POSITION_TOPRIGHT);

$sheet->addChart('A7:H20', $chart);

  12. Reference ranges from another sheet in validation rules

    master

    You can create validation rules that refer to data located on a different worksheet. The library automatically handles the necessary Excel x14 extension list formatting if it detects a ! in the formula.

    Example: Creating a dropdown in the 'Main' sheet that pulls values from the 'Lists' sheet.

    $excel = Excel::create(['Main', 'Lists']);
$lists = $excel->getSheet('Lists');
$lists->writeRow(['red']);
$lists->writeRow(['green']);

$sheet = $excel->getSheet('Main');
// Reference the range on the 'Lists' sheet
$sheet->addDataValidation('A2:A100', DataValidation::dropDown('=Lists!$A$1:$A$2'));