FastExcelWriter

FastExcelWriter is a part of the FastExcelPhp Project which consists of

FastExcelWriter - to create Excel spreadsheets
FastExcelReader - to read Excel spreadsheets
FastExcelTemplator - to generate Excel spreadsheets from XLSX templates
FastExcelLaravel - special Laravel edition

Introduction

This library is designed to be lightweight, super-fast and requires minimal memory usage.

FastExcelWriter creates Excel compatible spreadsheets in XLSX format (Office 2007+), 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

Jump To:

Installation

Use composer to install FastExcelWriter into your project:

composer require avadim/fast-excel-writer

Changes In Version 4

Now the library works even faster
Added a fluent interface for applying styles.
New methods and code refactoring

Changes In Version 5

The general news is Chart support

Usage

You can find usage examples below or in /demo folder

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->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
    ->setColOptions('A', ['format' => '@date', 'width' => 12])
    // column letter in lower case
    ->setColOptions('b', ['format' => '@text', 'width' => 24])
    // column can be specified by number
    ->setColOptions(3, ['format' => '0.00', 'width' => 15, 'color' => '#090'])
;

// The third way - all options in multilevel array (first level keys point to columns)
$sheet
    ->setColOptions([
        '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()


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

$sheet1->writeCell('Text to B1')->addNote('This is a note for B1');
$sheet1->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
$sheet1->addNote('E4:F8', "This note\nwill added to E4");

// You can split text into multiple lines
$sheet1->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


$sheet1->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'
];
$sheet1->writeCell('Text to B1')->addNote('This is a note for B1', $noteStyle);

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

Also, you can use rich text in notes

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

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

Adding Images

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

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

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

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