DOCSEARCHbyAlgolia
Submit Docs

PhpSpreadsheet

repository·master·Indexed 11 days ago

https://github.com/phpoffice/phpspreadsheet

A powerful PHP library for reading and writing spreadsheet files, including support for XLSX, XLS, and ODS formats. It features a stack-based calculation engine, cell caching for memory management, and support for PDF export via mpdf, dompdf, or tcpdf.

Tokens
127.3K
Snippets
287
Records
373
Agent score
95%

What's inside PhpSpreadsheet

  1. Supported file formats for reading

    master

    PhpSpreadsheet supports reading a variety of spreadsheet and text-based file formats. Note that feature support varies depending on the reader used. Before implementing a specific format, consult the features cross reference to ensure the specific spreadsheet features you need (like formulas, styling, or charts) are supported by the reader for that format.

    Supported reading formats include:

    • Xls: Microsoft Excel™ Binary (BIFF5 and BIFF8).
    • Xml: Microsoft Excel™ 2003 SpreadsheetML (zipped XML).
    • Xlsx: Microsoft Office Open XML SpreadsheetML (zipped XML, standard in Excel 2007+).
    • Ods: Open Document Format (ODF/OASIS), used by OpenOffice.org and StarCalc.
    • Slk: Microsoft Multiplan Symbolic Link Interchange (SYLK).
    • Gnumeric: Gnumeric spreadsheet format (gzip-compressed XML).
    • Csv: Comma Separated Values (plain text).
    • Html: HyperText Markup Language (.html or .htm).

  2. Avoid memory exhaustion by using `rangeToArray()` instead of `toArray()`

    master

    The toArray() method uses getHighestRow() and getHighestColumn() to determine the range. In many Excel files, features like Data Validation or Conditional Formatting can artificially inflate these values (e.g., claiming the highest row is 1,048,576). Calling toArray() on such a sheet can create a massive array filled with null values, leading to PHP memory exhaustion.

    To prevent this, use rangeToArray() to specify a precise range of cells. This limits the size of the resulting array and improves iteration speed.

  4. How PhpSpreadsheet handles date parameters in formulas

    master

    When passing values into Excel functions that expect dates (like DATEDIF, YEAR, or NETWORKDAYS), PhpSpreadsheet identifies the type based on the PHP data type:

    • Integer: Treated as a PHP/Unix timestamp.
    • Float (Real number): Treated as an Excel date/timestamp.
    • PHP DateTime object: Treated as a DateTime object.
    • String: Converted to a DateTime object using server locale settings.

    Warning: Avoid using strings for dates in formulas. String parsing is dependent on server locale (e.g., '07/08/2008' might be July 8th in the US but August 7th in the UK) and can lead to unpredictable results or #VALUE errors if the format is unrecognized.

  5. Use Readers and Writers to handle file persistence

    master

    The Spreadsheet class itself does not handle reading from or writing to files (disk or database). Instead, you must use Reader and Writer objects.

    PhpSpreadsheet includes built-in support for various formats, such as Open XML (Excel 2007). If you need to support a custom format, you can implement the following interfaces in your own classes:

    • \PhpOffice\PhpSpreadsheet\Reader\IReader for reading files.
    • \PhpOffice\PhpSpreadsheet\Writer\IWriter for writing files.

  6. Use Simple Filters to match specific values

    master

    Simple Filters allow you to select specific values from a column (similar to checkboxes in Excel).

    • Behavior: Multiple rules are joined by an OR condition.
    • Standard Filter: Excel considers only EQUAL tests as standard filters.
    • Blanks: To filter for blank cells, use an empty string '' with the AUTOFILTER_COLUMN_RULE_EQUAL rule.
    • Case Sensitivity: String comparisons in filters are case-insensitive.
    // Set the filter type to standard filter
$columnFilter->setFilterType(
    \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER
);

// Create rules for 'France' OR 'Germany'
$columnFilter->createRule()
    ->setRule(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
        'France'
    );

$columnFilter->createRule()
    ->setRule(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
        'Germany'
    );

// To match blanks:
$columnFilter->createRule()
    ->setRule(\PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL, '');

  8. Understand the order of evaluating multiple conditional rules

    master

    Conditional rules are stored in an array ($conditionalStyles), and their order in this array determines the evaluation order.

    • MS Excel Behavior: Excel checks conditions in the order they are defined. If multiple rules match, Excel applies non-conflicting styles from each (e.g., one rule sets font color, another sets fill color). If rules have conflicting formatting (e.g., both set different fill colors), the first matching rule wins.
    • Other Spreadsheet Programs: Some programs may stop processing once the first match is found.

    Tip: When rules might overlap (e.g., a rule for 'between -2 and 2' and a rule for 'exactly 0'), place the most specific rule first to ensure it is evaluated before more general rules.

  9. How sparkline groups work

    master

    Sparklines in PhpSpreadsheet are organized into SparklineGroup objects. Every sparkline within a single group shares the same formatting, such as type, colors, markers, and axis behavior.

    You should build a group explicitly in two scenarios:

    1. When you want multiple sparklines to share identical formatting.
    2. When you need to customize specific formatting options (like colors or markers) for a set of sparklines.

    To add a group to a worksheet, use $worksheet->addSparklineGroup($group).

    use PhpOffice\PhpSpreadsheet\Worksheet\Sparkline\SparklineGroup;
use PhpOffice\PhpSpreadsheet\Worksheet\Sparkline\SparklineType;

$group = new SparklineGroup();
$group->setType(SparklineType::Column)
    ->setDisplayMarkers(true)
    ->setDisplayHigh(true)
    ->setColorSeries('FF00B050')
    ->createSparkline('G3', 'Sheet1!B3:F3')
    ->createSparkline('G4', 'Sheet1!B4:F4');

$worksheet->addSparklineGroup($group);

  10. Reduce memory usage with Cell Caching

    master

    PhpSpreadsheet consumes significant memory (approx. 1k to 1.6k per cell). To handle large workbooks without exhausting available memory, you can use Cell Caching. This mechanism moves cell objects out of primary memory into an external storage system like disk, APCu, Memcache, or Redis.

    To enable cell caching, you must provide a PSR-16 compliant cache implementation using \PhpOffice\PhpSpreadsheet\Settings::setCache().

    Important constraints:

    • A separate cache is maintained for each individual worksheet.
    • You cannot change the cache configuration once you have started reading a workbook or have created your first worksheet.
    • Cell caching reduces memory usage but introduces a performance cost when accessing cell data.
    $cache = new MyCustomPsr16Implementation();

\PhpOffice\PhpSpreadsheet\Settings::setCache($cache);

  12. Define composite format masks with multiple sections

    master

    Custom number formats can consist of up to four sections separated by semicolons (;). These sections define how different types of values are displayed in a specific order:

    1. Positive values
    2. Negative values
    3. Zero values
    4. Text

    Section Rules:

    • One section: Applies to all numbers.
    • Two sections: First applies to positive and zero; second applies to negative.
    • Three sections: First for positive, second for negative, third for zero.
    • Four sections: Fourth applies only to non-numeric (text) values.
    • Skipping sections: If you want to skip a section (e.g., to hide zero values), you must still include the semicolon (;) to maintain the correct position in the sequence.
    /* Example: Positive;Negative;Zero;Text */
0.00;-0.00;0;"Text Value"