FastExcelWriter – Writing | FastExcelWriter 6.19.2

FastExcelWriter – Writing

Writing Row by Row vs Direct

writeIterable(iterable $rows, ?array $rowStyle = null) accepts arrays, generators and Traversable objects. Rows are consumed once, in iteration order; iterator keys are ignored. The method delegates each row to writeRow(), like writeRows(), without collecting the input. Each yielded row should be an array of cell values. An empty iterable writes nothing.

php

$rows = (function () {
    for ($i = 1; $i <= 100000; ++$i) {
        yield [$i, 'Item ' . $i];
    }
})();
$sheet->writeIterable($rows, ['font-size' => 11]);

Exceptions from the iterator propagate to the caller; rows already consumed are not rolled back. The existing writeRows(array $rowArray, ...) signature is unchanged.

There are two ways to write to a XLSX-file in the library - sequential (row by row) and direct. When you use "row by row" writing, cells are written to the file as soon as you move to the next row. And you can no longer write something into the cells of the previous rows. This allows you to write quickly and save memory.

Note: Any Sheet class writing functions use row-by-row way, while Area class functions use direct writing

php

$excel = Excel::create(['Sheet1']);
$sheet = $excel->sheet(); 
// now position of internal record pointer is "A1"

$sheet->writeCell('...'); 
// ^^^ write to A1 and move position of internal record pointer to "B1"

$sheet->writeCell('...'); 
// ^^^ position of internal record pointer is "C1"

$sheet->nextCell(); 
// ^^^ move position of internal record pointer to "D1"

$sheet->nextCell(); 
// ^^^ position of internal record pointer is "E1"

// we can write to any cell of the current row
$sheet->writeTo('G1', '...'); 
// ^^^ write value to G1 and move pointer to the H1

$sheet->writeTo('F1', '...'); 
// ^^^ we can write to previous cell of the current row

// function writeRow() always flush current row to the file and writes new values to the next row
$sheet->writeRow(['a', 'b', 'c']);
// ^^^ internal pointer moves to the next row 2

$sheet->cell('B2')->applyBgColor('#ccc');
// ^^^ you can change cells in the row 2 (this is the current row)

// but you can't write to the previous row
$sheet->cell('B1')->applyBgColor('#ccc');
// ^^^ ERROR! this code will throw an exception because B1 is in the previous row

You can write two-dimensional array

php

//Data will be written to cells in the range E5:I8
$data = [
    ['', 'Q1', 'Q2', 'Q3', 'Q4'],
    ['2020', 111, 222, 333, 444],
    ['2021', 110, 220, 330, 440],
    ['2022', 100, 200, 300, 400],
];
$sheet->writeArrayTo('E5', $data);

Direct Writing To Cells

With direct writing, you first declare a writing area (or multiple areas) and write to it. In this case, you can write to arbitrary cells within the area, they will all be stored in memory and written to a file when you call functions Sheet::writeAreas() or Excel::save(). This method is more flexible, but requires more memory and is not recommended for large files.

php

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

// Make write area from A1 to max column and max row
$area = $sheet->beginArea();

// write to the row 3
$area->setValue('C3', 'text 3');
// then write to the row 2
$area->setValue('B2', 'text 2');
// then write to the row 1
$area->setValue('A1', 'text 1');

// Close and write all areas
$sheet->writeAreas();

You can define any number of areas, and they can overlap

php

// Define are from D3 to max column and max row
$area1 = $sheet->beginArea('D3');

// make write area from B4 to F12
$area2 = $sheet->makeArea('B4:F12');
// you can define any number of areas and they can overlap
$area3 = $sheet->makeArea('C6:G18'); 

// the left column will be D and the first row of this area is 3
$area1->writeRow([100, 101, 102]);
$area2->writeRow([200, 201, 202]);

// you can write value to any cell of area...
$area1->writeTo('H3', 'text');
// ...but if you try to write to a cell outside the area it will throw an exception
$area1->writeTo('B2', 'text');

// Close and write all areas
$sheet->writeAreas();

You can write two-dimensional array

php

// Define are from D3 to max column and max row
$area = $sheet->beginArea('D3');
// But data will be written to cells from E5 to I8
$array = [
    ['', 'Q1', 'Q2', 'Q3', 'Q4'],
    ['2020', 111, 222, 333, 444],
    ['2021', 110, 220, 330, 440],
    ['2022', 100, 200, 300, 400],
];
$area->writeArrayTo('E5', $data);

Writing Cell Values

Usually, values is written sequentially, cell by cell, row by row. Writing to a cell moves the internal pointer to the next cell in the row, writing a row moves the pointer to the first cell of the next row.

php

use \avadim\FastExcelWriter\Excel;

// Create workbook
$excel = Excel::create();

// Get the sheet on which we will write data
$sheet = $excel->sheet();

// Write data to cell by cell (the first cell is A1)
// Write number to A1 and the pointer moves to the next cell (B1)
$sheet->writeCell(123);

// Write string to B1 (pointer in C1)
$sheet->writeCell('abc');

// Pointer moves to the next cell (D1) without value writing
$sheet->nextCell();

// Write to B3 and moves pointer to C3. The pointer can only move from left to right and top to bottom
$sheet->writeTo('B3', 'value');
$sheet->writeTo('A4', 'value');

// Now we will write value to B4 with styling
$style = [
    'format' => '#,##0.00',
    'font-color' => '#ff0000',
    'text-align' => 'center',
];
$sheet->writeCell(0.9, $style);

// this code will throw an exception because C3 is in the previous row
$sheet->writeTo('C3', 'value');

You can write the entire row at once

php

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

// Sheet::writeHeader(array header, ?array rowStyle)
// Sheet::writeRow(array row, ?array rowStyle)
// Sheet::nextRow()

// Write header values to the current row 
$sheet->writeHeader(['title1', 'title2']);

// Write header values to the current row and set format of columns A and B 
$sheet->writeHeader(['title1' => '@integer', 'title2' => '@date']);

$data = [
    [184, '2022-01-23'],
    [835, '1971-12-08'],
    [760, '1997-05-11'],
];

foreach ($data as $rowData) {
    $sheet->writeRow($rowData);
}

Also, you can define the top left cell for writing

php

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

// The first row is 3, all rows start in column B
$sheet->setTopLeftCell('B3');

// Write header values to the current row and set format of columns A and B 
$sheet->writeHeader(['title1' => '@integer', 'title2' => '@date'])->applyFontStyleBold();

$data = [
    [184, '2022-01-23'],
    [835, '1971-12-08'],
    [760, '1997-05-11'],
];

foreach ($data as $rowData) {
    $sheet->writeRow($rowData);
}

R1C1 Reference Style

By default, the library works with addresses of the R1C1 reference style. But you can change this behavior.

php

$sheet = $excel->sheet();

// Current row & column + 1 => B1
$sheet->writeTo('RC1', 'TEST 1');

// You can disable recognize R1C1 addresses
$excel->setR1C1(false);
// The value will be written to cell RC1 (column RC & row 1)
$sheet->writeTo('RC1', 'TEST 2');

Merging Cells

php

// Merge C4:E4, write value to merged cells
$sheet->writeTo('C4:E4', 'other value');

// Write value to the cell
$sheet->writeTo('D1', 'Title');
$sheet->writeRow(['...']);
$sheet->writeRow(['...']);
$sheet->writeRow(['...']);

// Merge cells range
$sheet->mergeCells('D1:F1');

Note: function mergeCells() does not write values or styles, it sets the properties of the sheet, so it can be called for previous rows when all the data has already been written.

Each time the mergeCells() method is called, a check is performed to see if the specified range overlaps with other merged cells. If the file being generated is large and there are many cells to be merged, this may slow down file generation. If you are sure that your cells to be merged do not overlap, you can disable the check to speed up file generation by passing -1 as the second argument.

php

$sheet->mergeCells('D1:F1', -1);
$sheet->mergeCells('D2:F2', -1);
$sheet->mergeCells('D3:F3', -1);

Cell Formats

You can use simple and advanced formats. Example of setting the format for each cell:

php

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

$sheet->writeCell(123456); // value 123456 as integer by default
$sheet->writeCell('123456'); // value '123456' as string by default
$sheet->writeCell(12.34); // float number 12.34
$sheet->writeCell(12.34, ['format' => '@money']); // money format
$sheet->writeCell(date('Y-m-d'), ['format' => '@date']); // date format
$sheet->writeCell(time(), ['format' => '@date']); // date format

You can also set the format for all column values.

php

$sheet = $excel->sheet();

// define column names and format
$header = [
    'created' => '@date',
    'product_id' => '@integer',
    'quantity' => '#,##0',
    'amount' => '#,##0.00',
    'description' => '@string',
    'tax' => '[$$]#,##0.00;[RED]-[$$]#,##0.00',
];
$data = [
    ['2015-01-01', 873, 1, 44.00, 'misc', '=D2*0.05'],
    ['2015-01-12', 324, 2, 88.00, 'none', '=D3*0.15'],
];

$sheet->writeHeader($header);
foreach($data as $row) {
    $sheet->writeRow($row );
}

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

Simple cell formats map to more advanced cell formats

simple formats	format code
@text	@
@string	@
@integer	0
@date	YYYY-MM-DD
@datetime	YYYY-MM-DD HH:MM:SS
@time	HH:MM:SS
@money	[$$]#,##0.00

Formulas

Formulas must start with '='. If you want to write the formula as a text, use a backslash. Setting the locale allows the use of national language function names. You can use both A1 and R1C1 notations in formulas.

You must comply with the following rules:

Formulas must start with '='
Decimal separator in float numbers is '.'
Function argument separator is ','
Matrix row separator is ';'

php

use \avadim\FastExcelWriter\Excel;

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

// Set Russian locale
$excel->setLocale('ru');

$headRow = [];

$sheet->writeRow([1, random_int(100, 999), '=RC[-1]*0.1']);
$sheet->writeRow([2, random_int(100, 999), '=RC[-1]*0.1']);
$sheet->writeRow([3, random_int(100, 999), '=RC[-1]*0.1']);

$totalRow = [
    'Total',
    '=SUM(B1:B3)', // English function name
    '=СУММ(C1:C3)', // You can use Russian function name because the locale is 'ru'
];

$sheet->writeRow($totalRow);

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

Setting the locale allows the use of national language function names.

php

$excel = Excel::create();
// Set Portuguese locale
$excel->setLocale('pt');
$sheet = $excel->getSheet();

$sheet->writeTo('A1', '=SE(FALSO,1.23+A4,4.56+B3)');

You can define formula for the specified column

php

$sheet->setColFormula('C', '=RC[-1]*0.1');

// We write values only to columns 'A' and 'B', formula to 'C' will be added automatically
$sheet->writeRow([100, 230]);
$sheet->writeRow([120, 560]);
$sheet->writeRow([130, 117]);

Important! The library cannot pre-calculate values of formulas. It save formulas as is, without pre-calculation. But when the saved file is opened in Excel, Excel recalculates all cells with formulas and show results. There's only one way to save pre-calculations: you do those calculations in your code and save them along with the formula

php

$a1 = 10;
$b1 = 30;
$a2 = 50;
$b2 = 70;
$sheet->writeRow([$a1, $b1]);
$sheet->writeRow([$a2, $b2]);

// formula and pre-calculated result
$a3 = ['=A1+A2', $a1 + $a2];
$b3 = ['=B1+B2', $b1 + $b2];
$sheet->writeRow([$a3, $b3]);

A pre-calculated result can be a number, a string, a boolean or an error value

php

$sheet->writeRow([
    ['=IF(A1>0, "PASS", "FAIL")', 'PASS'],  // string
    ['=A1=A2', false],                      // boolean
    ['=A1/0', '#DIV/0!'],                   // error
]);

Hyperlinks

You can insert URLs as active hyperlinks

php

// Write URL as simple string (not hyperlink)
$sheet->writeCell('https://google.com');

// Write URL as an active hyperlink
$sheet->writeCell('https://google.com', ['hyperlink' => true]);

// Write text with an active hyperlink
$sheet->writeCell('Google', ['hyperlink' => 'https://google.com']);

Write hyperlink using writeRow()

php

$rowValues = [
    'text',
    'http://google.com',
    123456,
];
$rowStyle = [];
$cellStyles = [
    [], // style for the first cell,
    ['hyperlink' => true], // 2nd cell
    [], // 3rd cell
];

$sheet->writeRow($rowValues, $rowStyle, $cellStyles);

Here is the other way

php

$cellStyles = [
    'B' => ['hyperlink' => true],
];

Internal hyperlinks to other sheets

php

$sheet->writeCell('Internal link', ['hyperlink' => "'Sheet 1'!C7"]);

// If the sheet name does not contain spaces, it can be written without quotes.
$sheet->writeCell('Internal link', ['hyperlink' => "Sheet1!C7"]);

External hyperlinks to other workbook

php

// You must use quotes even if the file and sheet names do not contain spaces.
$sheet->writeCell('Workbook link', ['hyperlink' => "'[other_file.xlsx]Sheet1'!C7"]);

For compatibility with phpSpreadsheet you can use this syntax

php

// if the name of the sheet does not contain spaces
$sheet->writeCell('Internal link', ['hyperlink' => "sheet://Sheet1!C7"]);

// If the name of the sheet is with spaces, then you need to use quotes
$sheet->writeCell('Internal link', ['hyperlink' => "sheet://'Sheet 1'!C7"]);

Using Rich Text

Writing rich text to a cell can be done using RichText instances. Here's an example, which creates the following rich text string:

img/rich-text.jpg

php

$richText = new \avadim\FastExcelWriter\RichText\RichText();
$richText->addText('ATTENTION!')->setBold();
$richText->addText(' The product is reserved for ');
$richText->addText('5 days')->setUnderline()->setColor('red');

$sheet->writeCell($richText);
$sheet->writeTo('B2', $richText);
$sheet->writeRow(['plain text', $richText]);

You can use the following functions to format rich text:

setBold()
setItalic()
setUnderline()
setFont($fontName)
setSize($size)
setColor($color)

Here's another way to do the same thing

php

$richText = new RichText('ATTENTION! ', 'The product is reserved for ', '5 days');

$richText->fragment(0)->setBold();
$richText->fragment(2)->setUnderline()->setColor('f00');

$sheet->writeCell($richText);

Also, you can use simple tags to format rich text

php

$richText = new \avadim\FastExcelWriter\RichText\RichText('<b>ATTENTION!</b> The product is reserved for <u><c=red>5 days</c></u>');
$sheet->writeTo('B2', $richText);

The following tags can be used:

short tag	full tag	example
<b>	<bold>	<b>bold text</b>
<i>	<italic>	<i>italic text</i>
<u>	<underline>	<u>underline text</u>
<f=fontName>	<font=fontName>	<f="Times New Roman">text<f>
<s=fontSize>	<size=fontSize>	<s=18>text with size 18</s>
<c=fontColor>	<color=fontColor>	<c="#f0b5d4">colored text</c>

Subscript and superscript

addText() and RichTextFragment accept literal, unescaped text. The library escapes XML special characters and encodes XLSX control characters when saving. setFont() also accepts the literal font name, including quotes and ampersands.

The constructor and addTaggedText() interpret supported tags and decode XML entities once. Use  to display a literal  in tagged text; unknown tags remain literal text. Use addText() for arbitrary input that must never be interpreted as markup.

php

$text = new RichText();
$text->addText('A & B, x < y')->setFont('Arial')->setSize(10.5);
$text->addText(' regular')->setBold(false)->setItalic(false)->setStrike(false)->removeUnderline();
$text->addTaggedText(' <b>A &amp; B</b>');

setBold($enabled = true), setItalic($enabled = true) and setStrike($enabled = true) explicitly enable or disable the corresponding property. removeUnderline() explicitly disables underline. These settings override inherited font properties and are available on both RichText and RichTextFragment. setSize(float $size) accepts positive finite sizes, including fractional points; <s=10.5> and <size=10.5> are supported too.

For strikethrough, use $text->addText('old')->setStrike() or <strike>old</strike> / <del>old</del>. These tags support nesting and can be combined with the other styles. setUnderline() and setUnderline(false) apply a single underline; setUnderline(true) applies a double underline, on both RichText and RichTextFragment.

Use  and  to format part of a cell as a subscript or superscript:

php

$sheet->writeRow([
    new RichText('H<sub>2</sub>O'),
    new RichText('m<sup>2</sup>'),
]);

The fluent API provides setSubscript(), setSuperscript() and setBaseline() on both RichText (the last added fragment) and RichTextFragment:

php

$text = new RichText();
$text->addText('H');
$text->addText('2')->setSubscript();
$text->addText('O');
$sheet->writeCell($text);

$text->fragment(1)->setSuperscript(); // Replace subscript with superscript
$text->fragment(1)->setBaseline();   // Restore normal text positioning

Each new plain fragment starts without a script setting. Closing  or  restores the previous setting, including in nested tags. Scripts can be combined with bold, italic, color and font size. Excel handles the script size and position; no manual size reduction is needed. Changes to fragments affect subsequent serialization; cells already written are not updated.

Streaming Mode And Memory

Row-by-row writing is streaming: as soon as you move to the next row, the current one is flushed into a temporary file of the sheet. Therefore the memory does not depend on the number of rows - in a measurement there was no growth at all between 50 000 and 500 000 written rows.

Everything that cannot be flushed together with the row stays in memory until Excel::save():

kept in memory until saving	about	notes
cells of an `Area`	0.3 KB per cell	flushed by `Sheet::writeAreas()` or `Excel::save()`
merged cells	0.5 KB per merge
hyperlinks	1 KB per link	max 65 530 per sheet (`Sheet::MAX_HYPERLINKS`)
notes	1 KB per note
data validation rules	0.6 KB per rule	one rule may cover a whole range
conditional formatting rules	0.5 KB per rule	one rule may cover a whole range
table of shared strings	0.5 KB per unique string	only with `'shared_string' => true`
charts	the whole definition of the chart	values passed as an array are kept too

Images are an exception: the content of an image is written to a temporary file at once, only its size and hash stay in memory, and identical images are stored in the file only once.

So a plain export of millions of rows costs almost nothing, but the same export where every row also gets a note, a hyperlink or its own merge does grow linearly. If you hit the memory limit, look at that column first: usually a single rule applied to a range replaces thousands of per-cell rules.

php

$conditional = Conditional::greaterThan(100, ['fill-color' => '#FFC7CE']);

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

// the same result, one rule
$sheet->addConditionalFormatting('C1:C100000', $conditional);

When You Need An Area

Row-by-row writing goes forward only - a row that is already flushed cannot be changed. Use Sheet::beginArea() / Sheet::makeArea() when you need to

write into a cell of a row that has already been written;
apply a style to cells of previous rows;
fill cells in an arbitrary order (for example, build a table of contents while the data is written).

The cells of an area live in memory until it is flushed, so keep areas as small as the task allows - a header block or a summary table, not the whole sheet.

Limits of Excel

Some limits of the format cannot be exceeded, a file that breaks them is not readable by Excel:

limit	value	behaviour of the library
characters in a cell	32 767	a longer value is truncated, as Excel does on input
hyperlinks on a sheet	65 530	adding one more throws an exception (`Sheet::MAX_HYPERLINKS`)
significant digits of a number	15	with `auto_convert_number` a longer numeric string is kept as text
rows / columns of a sheet	1 048 576 / XFD	an address beyond them is rejected

FastExcelWriter – Writing