FastExcelWriter

Lightweight, powerful and very fast XLSX Spreadsheet Writer in pure PHP. This library is designed to be superfast and requires minimal memory usage.

FastExcelWriter creates spreadsheets in XLSX format, compatible with MS Excel (Office 2007+), LibreOffice, OpenOffice and others, with many features supported:

• Takes UTF-8 encoded input
• Multiple worksheets
• Supports currency/date/numeric cell formatting, formulas and active hyperlinks
• Supports most styling options for cells, rows, columns – colors, borders, fonts, etc.
• You can set the height of the rows and the width of the columns (including auto width calculation)
• You can add formulas, notes and images in you XLSX-files
• Supports workbook and sheet protection with/without passwords
• Supports page settings – page margins, page size
• Inserting multiple charts
• Supports data validations and conditional formatting

Jump To:

• Quick Start
  • Installation
  • Simple Example
  • Advanced Example
  • Adding Notes
  • Adding Images
• Workbook
  • Workbook settings
  • Sets metadata of workbook
  • Set Directory For Temporary Files
  • Helpers methods
• Sheets
  • Create, select and remove sheet
  • Sheet settings
  • Row's settings
  • Column's settings
  • Automatic column widths
  • Group/outline rows and columns
  • Define Named Ranges
  • Freeze Panes and Autofilter
  • Setting Active Sheet and Cells
  • Page Setup
  • Print Settings
  • Print Header and Footer
• Writing
  • Writing Row by Row vs Direct
  • Direct Writing To Cells
  • Writing Cell Values
  • Merging Cells
  • Cell Formats
  • Formulas
  • Hyperlinks
  • Using Rich Text
• Styles
  • Style Properties As Array
  • Class Style for Defining Style Properties
  • Cell Styles
  • Row Styles
  • Column Styles
  • Other Columns Options
  • Apply Styles (The Fluent Interface)
  • Apply Borders
  • Apply Fonts
  • Apply Colors
  • Apply Text Styles
• Charts
  • Simple usage
  • Combo charts
  • Multiple charts
  • Chart types
  • Useful Chart Methods
• Protection of workbook and sheets
  • Workbook protection
  • Sheet protection
  • Cells locking/unlocking
• Data validation
  • Simple usage
  • Define filters
  • Check type of value
  • Custom filters
  • All Data Validation settings
  • More than 64K validation rules
• Conditional Formatting
  • Simple usage
  • General cell value check
  • Expressions
  • Gradient fill depending on values (colorScale)
  • Data strip inside a cell (dataBar)
• API Reference
  • Excel class
  • Sheet class
  • RichText class
  • Chart class
  • DataValidation class
  • Conditional class
  • Style class

Quick Start

You can find usage examples below or in /demo folder

Installation

Use composer to install FastExcelWriter into your project:

composer require avadim/fast-excel-writer

Simple Example

use \avadim\FastExcelWriter\Excel;

$data = [
    ['2003-12-31', 'James', '220'],
    ['2003-8-23', 'Mike', '153.5'],
    ['2003-06-01', 'John', '34.12'],
];

$excel = Excel::create(['Sheet1']);
$sheet = $excel->sheet();

// Write heads
$sheet->writeRow(['Date', 'Name', 'Amount']);

// Write data
foreach($data as $rowData) {
    $rowOptions = [
        'height' => 20,
    ];
    $sheet->writeRow($rowData, $rowOptions);
}

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

Also, you can download generated file to client (send to browser)

$excel = Excel::create();
$sheet = $excel->sheet();

$sheet->writeCell(12345); // write integer
$sheet->writeCell(123.45); // write float
$sheet->writeCell('12345'); // write string
$sheet->writeCell(true); // write boolean value
$sheet->writeCell(fn() => $sheet->getCurrentCell()); // write result of function

$excel->download('download.xlsx');

Advanced Example

use \avadim\FastExcelWriter\Excel;

$head = ['Date', 'Name', 'Amount'];
$data = [
    ['2003-12-31', 'James', '220'],
    ['2003-8-23', 'Mike', '153.5'],
    ['2003-06-01', 'John', '34.12'],
];
$headStyle = [
    'font' => [
        'style' => 'bold'
    ],
    'text-align' => 'center',
    'vertical-align' => 'center',
    'border' => 'thin',
    'height' => 24,
];

$excel = Excel::create(['Sheet1']);
$sheet = $excel->sheet();

// Write the head row (sets style via array)
$sheet->writeHeader($head, $headStyle);

// The same result with new fluent interface
$sheet->writeHeader($head)
    ->applyFontStyleBold()
    ->applyTextAlign('center', 'center')
    ->applyBorder(Style::BORDER_STYLE_THIN)
    ->applyRowHeight(24);

// Sets columns options - format and width (the first way)
$sheet
    ->setColFormats(['@date', '@text', '0.00'])
    ->setColWidths([12, 14, 5]);

// The seconds way to set columns options
$sheet
    // column and options
    ->setColDataStyle('A', ['format' => '@date', 'width' => 12])
    // column letter in lower case
    ->setColDataStyle('b', ['format' => '@text', 'width' => 24])
    // column can be specified by number
    ->setColDataStyle(3, ['format' => '0.00', 'width' => 15, 'color' => '#090'])
;

// The third way - all options in multilevel array (first level keys point to columns)
$sheet
    ->setColDataStyle([
        'A' => ['format' => '@date', 'width' => 12],
        'B' => ['format' => '@text', 'width' => 24],
        'C' => ['format' => '0.00', 'width' => 15, 'color' => '#090'],
    ]);

$rowNum = 1;
foreach($data as $rowData) {
    $rowOptions = [
        'height' => 20,
    ];
    if ($rowNum % 2) {
        $rowOptions['fill-color'] = '#eee';
    }
    $sheet->writeRow($rowData, $rowOptions);
}

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

Adding Notes

There are currently two types of comments in Excel - comments and notes (see The difference between threaded comments and notes). Notes are old style comments in Excel (text on a light yellow background). You can add notes to any cells using method addNote()

$sheet->writeCell('Text to A1');
$sheet->addNote('A1', 'This is a note for cell A1');

$sheet->writeCell('Text to B1')->addNote('This is a note for B1');
$sheet->writeTo('C4', 'Text to C4')->addNote('Note for C1');

// If you specify a range of cells, then the note will be added to the left top cell
$sheet->addNote('E4:F8', "This note\nwill added to E4");

// You can split text into multiple lines
$sheet->addNote('D7', "Line 1\nLine 2");

You can change some note options. Allowed options of a note are:

• width - default value is '96pt'
• height - default value is '55.5pt'
• fill_color - default value is '#FFFFE1'
• show - default value is false

$sheet->addNote('A1', 'This is a note for cell A1', 
    ['width' => '200pt', 'height' => '100pt', 'fill_color' => '#ffcccc']);

// Parameters "width" and "height" can be numeric, by default these values are in points
// The "fill_color" parameter can be shortened
$noteStyle = [
    'width' => 200, // equivalent to '200pt'
    'height' => 100, // equivalent to '100pt'
    'fill_color' => 'fcc', // equivalent to '#ffcccc'
];
$sheet->writeCell('Text to B1')->addNote('This is a note for B1', $noteStyle);

// This note is visible when the Excel workbook is displayed
$sheet->addNote('C8', 'This note is always visible', ['show' => true]);

Also, you can use rich text in notes

$richText = new \avadim\FastExcelWriter\RichText\RichText('here is <c=f00>red</c> and <c=00f>blue</c> text');
$sheet->addNote('C8', $richText);

For more information on using rich text, see here: Using Rich Text

Adding Images

You can insert image to sheet from local file, URL or image string in base64

$sheet->addImage($cell, $imageFile, $imageStyle);

// Insert an image to the cell A1 from local path
$sheet->addImage('A1', 'path/to/file');

// Insert an image to the cell A1 from URL
$sheet->addImage('A1', 'https://site.com/image.jpg');

// Insert an image to the cell A1 from base64 string
$sheet->addImage('A1', 'data:image/jpeg;base64,/9j/4AAQ...');

// Insert an image to the cell B2 and set with to 150 pixels (height will change proportionally)
$sheet->addImage('B2', 'path/to/file', ['width' => 150]);

// Set height to 150 pixels (with will change proportionally)
$sheet->addImage('C3', 'path/to/file', ['height' => 150]);

// Set size in pixels
$sheet->addImage('D4', 'path/to/file', ['width' => 150, 'height' => 150]);

// Add hyperlink to the image
$sheet->addImage('D4', 'path/to/file', ['width' => 150, 'height' => 150, 'hyperlink' => 'https://www.google.com/']);

Available keys of image style:

• 'width' -- width of image
• 'height' -- height of image
• 'hyperlink' -- URL of hyperlink
• 'x' -- offset in pixels relative to the left border of the cell
• 'y' -- offset in pixels relative to the top border of the cell

IMPORTANT: in MS Excel, value 'x' cannot be greater than the column width of the parent cell, and value 'y' cannot be greater than the row height

FastExcelWriter vs PhpSpreadsheet

PhpSpreadsheet is a perfect library with wonderful features for reading and writing many document formats. FastExcelWriter can only write and only in XLSX format, but does it very fast and with minimal memory usage.

FastExcelWriter:

• 7-9 times faster
• uses less memory by 8-10 times
• supports writing huge 100K+ row spreadsheets

Benchmark of PhpSpreadsheet (generation without styles)

Rows x Cols	Time	Memory
1000 x 5	0.98 sec	2,048 Kb
1000 x 25	4.68 sec	14,336 Kb
5000 x 25	23.19 sec	77,824 Kb
10000 x 50	105.8 sec	256,000 Kb

Benchmark of FastExcelWriter (generation without styles)

Rows x Cols	Time	Memory
1000 x 5	0.19 sec	2,048 Kb
1000 x 25	1.36 sec	2,048 Kb
5000 x 25	3.61 sec	2,048 Kb
10000 x 50	13.02 sec	2,048 Kb

Do you want to support FastExcelWriter?

if you find this package useful you can support and donate to me for a cup of coffee:

• USDT (TRC20) TSsUFvJehQBJCKeYgNNR1cpswY6JZnbZK7
• USDT (ERC20) 0x5244519D65035aF868a010C2f68a086F473FC82b
• ETH 0x5244519D65035aF868a010C2f68a086F473FC82b

Or just give me a star on GitHub :)