The following pages provide you with some widely used PhpSpreadsheet recipes. Note that these files do not provide full documentation on specific PhpSpreadsheet API functions, but are just a start. If you need a specific API function, see API Documentation.

E.g, You can set the page orientation and size of the worksheet Set the page orientation to A4. Other paper formats (e.g. US Letter) are not discussed in this document but in PhpSpreadsheet Discussed in the API documentation.

Set up spreadsheet metadata

PhpSpreadsheet provides an easy way to set a spreadsheet's metadata using document property accessors. Spreadsheet metadata can be used to find specific documents in file repositories or document management systems. For example, Microsoft Sharepoint uses document metadata to search for specific documents in its document list.

Here's how to set up spreadsheet metadata:

$spreadsheet->getProperties()
    ->setCreator("Maarten Balliauw")
    ->setLastModifiedBy("Maarten Balliauw")
    ->setTitle("Office 2007 XLSX Test Document")
    ->setSubject("Office 2007 XLSX Test Document")
    ->setDescription(
        "Test document for Office 2007 XLSX, generated using PHP classes."
    )
    ->setKeywords("office 2007 openxml php")
    ->setCategory("Test result file");

Set up the active sheet for the spreadsheet

The following line of code sets the active sheet index to the first sheet:

$spreadsheet->setActiveSheetIndex(0);

You can also set the active sheet by its name/title

$spreadsheet->setActiveSheetIndexByName('DataSheet')

Change the currently active sheet to the sheet named "DataSheet".

Write a date or time to a cell

In Excel, dates and times are stored as numeric values ​​and are used to calculate the number of days that have passed since 1900-01-01. For example, the date '2008-12-31' is represented as 39813. You can validate this date in Microsoft Office Excel by entering the date in a cell and then changing the number format to "General" so that the actual numeric value is displayed. Likewise, "3:15 AM" is represented as 0.135417.

PhpSpreadsheet uses UST (Universal Standard Time) date and time values, but does no internal conversion. Therefore, developers should ensure that the value passed to the date/time conversion function is UST.

Writing a date value in a cell consists of two lines of code. Choose the method that works best for you. Here are some examples:

// MySQL-like timestamp '2008-12-31' or date string
\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );

$spreadsheet->getActiveSheet()
    ->setCellValue('D1', '2008-12-31');

$spreadsheet->getActiveSheet()->getStyle('D1')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

// PHP-time (Unix time)
$time = gmmktime(0,0,0,12,31,2008); // int(1230681600)
$spreadsheet->getActiveSheet()
    ->setCellValue('D1', \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($time));
$spreadsheet->getActiveSheet()->getStyle('D1')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

// Excel-date/time
$spreadsheet->getActiveSheet()->setCellValue('D1', 39813)
$spreadsheet->getActiveSheet()->getStyle('D1')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

The methods above for entering a date all produce the same result. \PhpOffice\PhpSpreadsheet\Style\NumberFormat provides many predefined date formats.

The \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel() method can also be used with PHP DateTime objects.

Similarly, times (or date and time values) can be entered in the same way: just remember to use the appropriate format code.

Notice:

See the section "Using Value Binders to Facilitate Data Entry" to learn more about the AdvancedValueBinder used in the first example. Excel also works in a 1904 based calendar (the default for workbooks saved on a Mac). Usually, you don't have to worry about this when using PhpSpreadsheet.

Write formula to cell

In an Excel file, formulas are always stored as they appear in the English version of Microsoft Office Excel, and PhpSpreadsheet handles all formulas in this format internally. This means that the following rules hold:

• The decimal separator is . (period)
• The function parameter separator is , (comma)
• The matrix row separator is ; (semicolon)
• English function names must be used

Excel files are created regardless of which language version of Microsoft Office Excel is used.

When the user opens the final workbook, Microsoft Office Excel takes care of displaying the formulas according to the application language. The app takes care of the translation!

The following generation code line writes the formula =if (c4>500, "profit", "loss") into cell B8 Note that the formula must be = for PhpSpreadsheet to recognize this formula as the beginning.

$spreadsheet->getActiveSheet()->setCellValue('B8','=IF(C4>500,"profit","loss")');

The setCellValueExplicit() method should be used if you want to write a string starting with a character into the = cell.

$spreadsheet->getActiveSheet()
    ->setCellValueExplicit(
        'B8',
        '=IF(C4>500,"profit","loss")',
        \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
    );

The cell's formula can be read again with the following line of code:

$formula = $spreadsheet->getActiveSheet()->getCell('B8')->getValue();

If you need the calculated cell value, use the following code. this will be in in the computing engine Further explanation.

$value = $spreadsheet->getActiveSheet()->getCell('B8')->getCalculatedValue();

Regional settings for formulas

Some localization elements are already included in PhpSpreadsheet. You can set the locale by changing the settings. To set the locale to Russian, use:

$locale = 'ru';
$validLocale = \PhpOffice\PhpSpreadsheet\Settings::setLocale($locale);
if (!$validLocale) {
    echo 'Unable to set locale to '.$locale." - reverting to en_us<br />\n";
}

If there are no Russian files, the setLocale() method will return an error and the English setting will always be used.

After setting the locale, you can translate the formula from its internal English encoding.

$formula = $spreadsheet->getActiveSheet()->getCell('B8')->getValue();
$translatedFormula = \PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance()->_translateFormulaToLocale($formula);

You can also create formulas with function names and parameter separators appropriate for a defined locale; then translate it to English before setting cell values:

$formula = '=ДНЕЙ360(ДАТА(2010;2;5);ДАТА(2010;12;31);ИСТИНА)';
$internalFormula = \PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance()->translateFormulaToEnglish($formula);
$spreadsheet->getActiveSheet()->setCellValue('B8',$internalFormula);

Currently, formula conversion only converts function names, constants TRUE and FALSE, and function argument separators.

Currently, the following locale settings are supported:

Write a newline "\n" in a cell (ALT + "Enter")

In Microsoft Office Excel, you can wrap a line in a cell by pressing ALT + "Enter". When you do that, it automatically turns on Word Wrap for that cell.

Here's how to achieve this in PhpSpreadsheet:

$spreadsheet->getActiveSheet()->getCell('A1')->setValue("hello\nworld");
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);

tip

Read more about formatting cells with getStyle() elsewhere.

tip

When AdvancedValuebinder.php sees a newline in the string to be inserted into a cell, it automatically turns on Word Wrap for that cell. Just like Microsoft Office Excel. try this:

\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );

$spreadsheet->getActiveSheet()->getCell('A1')->setValue("hello\nworld");

Read more about AdvancedValueBinder.php elsewhere.

Explicitly set the data type of the cell

You can explicitly set a cell's data type using the cell's setValueExplicit method or the worksheet's setCellValueExplicit method. Here is an example:

$spreadsheet->getActiveSheet()->getCell('A1')
    ->setValueExplicit(
        '25',
        \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC
    );

Change the cell to a clickable URL

You can make the cell a clickable URL by setting the hyperlink property of the cell:

$spreadsheet->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('https://www.example.com');

If you want to make a hyperlink to another sheet/cell, use the following code:

$spreadsheet->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl("sheet://'Sheetname'!A1");

Set printer options for Excel files

Set the page orientation and size of the worksheet

The page orientation and size of the sheet can be set using the following lines of code:

$spreadsheet->getActiveSheet()->getPageSetup()
    ->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
$spreadsheet->getActiveSheet()->getPageSetup()
    ->setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4);

Note that there are other page settings available. For all possible options please refer to API Documentation.

Page Setup: Zoom Options

As shown, the page setup scaling options in PhpSpreadsheet are directly related to the scaling options in the Page Setup dialog.

As shown, the default values ​​in PhpSpreadsheet correspond to the default values ​​in MS Office Excel.

example

Here's how to fit a 1 page wide page of infinite width:

$spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(0);

As you can see, it is not necessary to call setFitToPage(TRUE) since setFitToWidth(...) and setFitToHeight(...) trigger this.

If you use setFitToWidth(), you should generally also specify setFitToHeight() explicitly as in the example. Note the dependence on the initial value.

Margins

To set margins for a worksheet, use the following code:

$spreadsheet->getActiveSheet()->getPageMargins()->setTop(1);
$spreadsheet->getActiveSheet()->getPageMargins()->setRight(0.75);
$spreadsheet->getActiveSheet()->getPageMargins()->setLeft(0.75);
$spreadsheet->getActiveSheet()->getPageMargins()->setBottom(1);

Note that margin values ​​are specified in inches.

Center the page horizontally/vertically

To center the page horizontally/vertically, you can use the following code:

$spreadsheet->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
$spreadsheet->getActiveSheet()->getPageSetup()->setVerticalCentered(false);

Set print headers and footers for a worksheet

The following lines of code can be used to set the print header and footer of a worksheet:

$spreadsheet->getActiveSheet()->getHeaderFooter()
    ->setOddHeader('&C&HPlease treat this document as confidential!');
$spreadsheet->getActiveSheet()->getHeaderFooter()
    ->setOddFooter('&L&B' . $spreadsheet->getProperties()->getTitle() . '&RPage &P of &N');

Replacement and formatting codes (starting with &) can be used in headers and footers. The codes do not have a required order.

The first occurrence of the following code turns the formatting on, and the second occurrence turns it off again:

• strikethrough
• superscript
• subscript

Superscript and subscript cannot be turned on at the same time. Whoever wins first is ignored and wins first.

Xlsx supports the following codes:

tip

The code table above can seem overwhelming the first time you try to figure out how to write a header or footer. Fortunately, there is an easier way. Let Microsoft Office Excel do the work for you. For example, create an xlsx file in Microsoft Office Excel where you can insert headers and footers as needed using the program's own interface. Save the file as test.xlsx. Now, take that file and use PhpSpreadsheet to read the values ​​like this:

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('test.xlsx');
$worksheet = $spreadsheet->getActiveSheet();

var_dump($worksheet->getHeaderFooter()->getOddFooter());
var_dump($worksheet->getHeaderFooter()->getEvenFooter());
var_dump($worksheet->getHeaderFooter()->getOddHeader());
var_dump($worksheet->getHeaderFooter()->getEvenHeader());

This reveals the code for even/odd headers and footers. Experienced users may find it easier to rename test.xlsx to test.zip, unzip and directly inspect the contents of the relevant xl/worksheets/sheetX.xml to find the code for the header/footer.

Image Tips

$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooterDrawing();
$drawing->setName('PhpSpreadsheet logo');
$drawing->setPath('./images/PhpSpreadsheet_logo.png');
$drawing->setHeight(36);
$spreadsheet->getActiveSheet()->getHeaderFooter()->addImage($drawing, \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooter::IMAGE_HEADER_LEFT);

Set the print interval on a row or column

To set the print interval, use the following code, which sets a line interval on line 10.

$spreadsheet->getActiveSheet()->setBreak('A10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW);

The following line of code sets a print break on column D:

$spreadsheet->getActiveSheet()->setBreak('D10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_COLUMN);

Show/hide gridlines when printing

To show/hide gridlines when printing, use the following code:

$spreadsheet->getActiveSheet()->setShowGridlines(true);

Set row/column to repeat at top/left

PhpSpreadsheet can repeat a specific row/cell at the top/left side of the page. The following code is an example of how to repeat rows 1 to 5 on each printed page of a specific worksheet:

$spreadsheet->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 5);

Specify the print area

To specify the print area of ​​the worksheet, use the following code:

$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('A1:E5');

It is also possible to have multiple print areas in a single worksheet:

$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('A1:E5,G4:M20');

shape

format cell

Cells can be formatted with font, border, padding, ... style information. For example, you can set the cell's foreground color to red, align it to the right, and set the border to black and a thick border style. Let's do this on cell B2:

$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getBorders()->getTop()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getBorders()->getBottom()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getBorders()->getLeft()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getBorders()->getRight()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getFill()->getStartColor()->setARGB('FFFF0000');

getStyle() also accepts a cell range as a parameter. For example, you can set a red background color on a range of cells:

$spreadsheet->getActiveSheet()->getStyle('B3:B7')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('FFFF0000');

The tip suggests using e.g. getStyle('A1:M500') to style many cells at once instead of styling the cells individually in a loop. This is much faster than iterating over the cells and styling them individually.

There is another way to set styles. The following code sets the style of the cell to bold, right-aligned, thin top border, and gradient fill:

$styleArray = [
    'font' => [
        'bold' => true,
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT,
    ],
    'borders' => [
        'top' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        ],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
        'rotation' => 90,
        'startColor' => [
            'argb' => 'FFA0A0A0',
        ],
        'endColor' => [
            'argb' => 'FFFFFFFF',
        ],
    ],
];

$spreadsheet->getActiveSheet()->getStyle('A3')->applyFromArray($styleArray);

or with a range of cells:

$spreadsheet->getActiveSheet()->getStyle('B3:B7')->applyFromArray($styleArray);

This alternative to using an array should be faster in terms of execution whenever you are setting multiple style properties. However, unless there are many different styles in the workbook, the difference is almost immeasurable.

number format

You usually want to format numbers in Excel. For example, you might want a thousand separator followed by a fixed number of decimals after the decimal separator. Or maybe you want some numbers to be zero-padded.

In Microsoft Office Excel, you may be familiar with the process of selecting a number format from the Format Cells dialog box. There are some predefined number formats, including some date formats. The dialog is designed in such a way that you do not need to interact with the underlying raw number format code unless you need to customize the number format.

In PhpSpreadsheet, you can also apply various predefined number formats. example:

$spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

This will format the number like 1587.2 so when you open the workbook in MS Office Excel it will show as 1,587.20. (Depending on the settings of decimal and thousands separators in Microsoft Office Excel, it may appear as 1.587,20)

You can achieve the exact same effect as above using:

$spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat()
    ->setFormatCode('#,##0.00');

In Microsoft Office Excel and PhpSpreadsheet, whenever you need some special custom number formatting, you have to interact with the raw number formatting code. example:

$spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat()
    ->setFormatCode('[Blue][>=3000]$#,##0;[Red][<0]$#,##0;$#,##0');

Another example is when you want to zero pad a number to a fixed length with leading zeros:

$spreadsheet->getActiveSheet()->getCell('A1')->setValue(19);
$spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat()
    ->setFormatCode('0000'); // will show as 0019 in Excel

Tip The rules for writing number formatting codes in Excel can be quite complicated. Sometimes, you know how to create a certain number format in Microsoft Office Excel, but you don't know what the underlying number format code looks like. How did you find it?

The reader that comes with PhpSpreadsheet comes to the rescue. Load a template workbook using e.g. Xlsx reader to display number format codes. Example how to read the number format code for cell A1:

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$spreadsheet = $reader->load('template.xlsx');
var_dump($spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat()->getFormatCode());

Advanced users can directly check number format codes faster by renaming template.xlsx to template.zip, unzipping and looking in xl/styles.xml for the relevant XML code that contains number format codes.

justify and wrap

Let's set the vertical alignment to the top of cells A1:D4

$spreadsheet->getActiveSheet()->getStyle('A1:D4')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);

Here's how to implement word wrapping:

$spreadsheet->getActiveSheet()->getStyle('A1:D4')
    ->getAlignment()->setWrapText(true);

Set the default style for the workbook

You can set the default style of the workbook. Let's set the default font to Arial size 8:

$spreadsheet->getDefaultStyle()->getFont()->setName('Arial');
$spreadsheet->getDefaultStyle()->getFont()->setSize(8);

style cell border

In PhpSpreadsheet, it is easy to apply various borders on rectangular selections. Here's how to apply a thick red border outline around cells B2:G8.

$styleArray = [
    'borders' => [
        'outline' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
            'color' => ['argb' => 'FFFF0000'],
        ],
    ],
];

$worksheet->getStyle('B2:G8')->applyFromArray($styleArray);

In Microsoft Office Excel, the above would correspond to selecting cells B2:G8, launching the Style dialog, selecting the thick red border and clicking the Outline border component.

Note that the border outline is applied to the rectangular selection B2:G8 ​​as a whole, not to each cell individually.

You can achieve any border effect by using only the 5 basic borders and working on one cell at a time:

• remain
• right
• optimal
• bottom
• diagonal

Like the example above, other shortcut borders are handy too. These are the available shortcut borders:

• all borders
• outline
• Inside
• vertical
• horizontal

The following image shows an overview of all border shortcuts:

If both allBorders and vertical are set, then we will have "overlapping" borders, and one of the components must win over the other with overlapping borders. In PhpSpreadsheet, from weakest border to strongest border, the list is as follows: allBorder, outline/inner, vertical/horizontal, left/right/top/bottom/diagonal.

Various effects can be achieved in a simple manner using this boundary hierarchy.

Valid style array keys applyFromArray()

The following table lists the valid array keys for the \PhpOffice\PhpSpreadsheet\Style\Style::applyFromArray() class. If the "Map to Property" column maps a key to a setter, the value provided for that key will be applied directly. If the "Map to Property" column maps a key to a getter, the value provided for that key will be used as another style array.

\ PhpOffice \ PhpSpreadsheet \ Style \ Style

\ PhpOffice \ PhpSpreadsheet \ Style \ Fill

\ PhpOffice \ PhpSpreadsheet \ Style \ Font

\ PhpOffice \ PhpSpreadsheet \ Style \ Borders

\ PhpOffice \ PhpSpreadsheet \ Style \ Border

\ PhpOffice \ PhpSpreadsheet \ Style \ Alignment

\ PhpOffice \ PhpSpreadsheet \ Style \ NumberFormat

\ PhpOffice \ PhpSpreadsheet \ Style \ Protection

Conditionally format cells

Cells can be formatted conditionally based on specific rules. For example, if a cell's value is less than zero, it can be set to red, and if its value is zero or greater, it can be set to green.

A conditional styling ruleset can be set for a cell using the following code:

$conditional1 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
$conditional1->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_LESSTHAN);
$conditional1->addCondition('0');
$conditional1->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
$conditional1->getStyle()->getFont()->setBold(true);

$conditional2 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$conditional2->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
$conditional2->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_GREATERTHANOREQUAL);
$conditional2->addCondition('0');
$conditional2->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_GREEN);
$conditional2->getStyle()->getFont()->setBold(true);

$conditionalStyles = $spreadsheet->getActiveSheet()->getStyle('B2')->getConditionalStyles();
$conditionalStyles[] = $conditional1;
$conditionalStyles[] = $conditional2;

$spreadsheet->getActiveSheet()->getStyle('B2')->setConditionalStyles($conditionalStyles);

If you want to copy the ruleset to other cells, you can copy the style object:

$spreadsheet->getActiveSheet()
    ->duplicateStyle(
        $spreadsheet->getActiveSheet()->getStyle('B2'),
        'B3:B7'
    );

Add comments to cells

To add a comment to a cell, use the following code. The following example adds a comment to cell E11:

$spreadsheet->getActiveSheet()
    ->getComment('E11')
    ->setAuthor('Mark Baker');
$commentRichText = $spreadsheet->getActiveSheet()
    ->getComment('E11')
    ->getText()->createTextRun('PhpSpreadsheet:');
$commentRichText->getFont()->setBold(true);
$spreadsheet->getActiveSheet()
    ->getComment('E11')
    ->getText()->createTextRun("\r\n");
$spreadsheet->getActiveSheet()
    ->getComment('E11')
    ->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');

Apply an autofilter to a range of cells

To apply an autofilter to a range of cells, use the following code:

$spreadsheet->getActiveSheet()->setAutoFilter('A1:C9');

Make sure you always include the full filter range! Excel does only support setting header rows, but this is not best practice.

Set up security on the spreadsheet

Excel offers 3 levels of "protection":

• Documentation: Allows you to set a password on the complete spreadsheet, which can only be changed when entered.
• Sheets: Provides additional security options: you can disable inserting rows on specific sheets, disable sorting, ...
• Cells: Provides options to lock/unlock cells and show/hide internal formulas.

Example for setting document security:

$spreadsheet->getSecurity()->setLockWindows(true);
$spreadsheet->getSecurity()->setLockStructure(true);
$spreadsheet->getSecurity()->setWorkbookPassword("PhpSpreadsheet");

Example for setting worksheet security:

$spreadsheet->getActiveSheet()
    ->getProtection()->setPassword('PhpSpreadsheet');
$spreadsheet->getActiveSheet()
    ->getProtection()->setSheet(true);
$spreadsheet->getActiveSheet()
    ->getProtection()->setSort(true);
$spreadsheet->getActiveSheet()
    ->getProtection()->setInsertRows(true);
$spreadsheet->getActiveSheet()
    ->getProtection()->setFormatCells(true);

Example for setting cell security:

$spreadsheet->getActiveSheet()->getStyle('B1')
    ->getProtection()
    ->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_UNPROTECTED);

If you need any sheet protection features, make sure to enable sheet protection! This can be done with the following code:

$spreadsheet->getActiveSheet()->getProtection()->setSheet(true);

Set up data validation on cells

Data validation is a powerful feature of Xlsx. It allows specifying input filters on data that can be inserted into specific cells. The filter can be a range (i.e. the values ​​must be between 0 and 10), a list (i.e. the values ​​must be picked from a list), ...

The following code only allows numbers between 10 and 20 to be entered in cell B3:

$validation = $spreadsheet->getActiveSheet()->getCell('B3')
    ->getDataValidation();
$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_WHOLE );
$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP );
$validation->setAllowBlank(true);
$validation->setShowInputMessage(true);
$validation->setShowErrorMessage(true);
$validation->setErrorTitle('Input error');
$validation->setError('Number is not allowed!');
$validation->setPromptTitle('Allowed input');
$validation->setPrompt('Only numbers between 10 and 20 are allowed.');
$validation->setFormula1(10);
$validation->setFormula2(20);

The following code allows only selected items from the data list to be entered into cell B5:

$validation = $spreadsheet->getActiveSheet()->getCell('B5')
    ->getDataValidation();
$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
$validation->setAllowBlank(false);
$validation->setShowInputMessage(true);
$validation->setShowErrorMessage(true);
$validation->setShowDropDown(true);
$validation->setErrorTitle('Input error');
$validation->setError('Value is not in list.');
$validation->setPromptTitle('Pick from list');
$validation->setPrompt('Please pick a value from the drop-down list.');
$validation->setFormula1('"Item A,Item B,Item C"');

When validating lists with the above data, be sure to put the list between and "," and separate items with commas (,).

It's important to remember that any string participating in an Excel formula can contain up to 255 characters (not bytes). This places a limit on the items that can be contained in the string "Item A, Item B, Item C". Therefore, it is usually best to type the item value directly in a cell area (such as A1: A3), and use $validation->setformula1 ('sheet! $a$1:$a$3') instead Another benefit is that the item value itself can contain commas, characters themselves.

If you need data validation on multiple cells, you can clone the ruleset:

$spreadsheet->getActiveSheet()->getCell('B8')->setDataValidation(clone $validation);

Set the width of the column

The width of the column can be set with the following code:

$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12);

If you want PhpSpreadsheet to perform automatic width calculation, use the following code. PhpSpreadsheet will approximate the column using the width of the widest column value.

$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

The amount of column width in PhpSpreadsheet does not correspond exactly to the measurements you might be used to in Microsoft Office Excel. Column widths are hard to deal with in Excel, and there are several ways to measure column widths.

1. Internal width in character units (e.g. 8.43 might be the width you are familiar with in Excel)
2. Full width in pixels (e.g. 64 pixels)
3. Full width in characters (eg 9.140625, value -1 means unset width)

PhpSpreadsheet always runs in "3. Full-width character units", which is actually the only value stored in any Excel file and therefore the most reliable measure. Unfortunately, Microsoft Office Excel doesn't give you that measure. Instead, metrics 1 and 2 are calculated by the application when the file is opened, and these values ​​are displayed in various dialogs and tooltips.

Character width units are the width of 0 (zero) glyphs in the workbook default font. Therefore, you can only compare column widths measured in characters if you have the same default workbook font in two different workbooks. If you have some Excel files and need to know the column widths in measure 3, you can use PhpSpreadsheet and echo the retrieved values.

show/hide columns

To set the column visibility of the worksheet, you can use the following code. The first row explicitly shows column C, and the second row hides column D.

$spreadsheet->getActiveSheet()->getColumnDimension('C')->setVisible(true);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setVisible(false);

group/outline a column

To group/outline a column, you can use the following code:

$spreadsheet->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);

You can also collapse columns. Note that you should also set the column to be invisible, otherwise the fold will not be visible in Excel 2007.

$spreadsheet->getActiveSheet()->getColumnDimension('E')->setCollapsed(true);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setVisible(false);

See the "Grouping/Outlining a Row" section for a complete example of collapsing.

You can instruct PhpSpreadsheet to add the summary to the right (default) or to the left. The following code adds the summary to the left:

$spreadsheet->getActiveSheet()->setShowSummaryRight(false);

Set the height of the row

The height of the row can be set with the following code:

$spreadsheet->getActiveSheet()->getRowDimension('10')->setRowHeight(100);

Excel measures row height in points, where 1 pt is 1/72 of an inch (or about 0.35mm). The default value is 12.75 points; and the range of allowed values ​​is between 0 and 409 pts, where 0 pts is the hidden row.

show/hide row

To set row visibility for a worksheet, you can use the following code. The example below hides row 10.

$spreadsheet->getActiveSheet()->getRowDimension('10')->setVisible(false);

Note that if you apply an active filter using an autofilter, when you save the file, it will overwrite any lines you manually hide or unhide within that autofilter range.

Group/Overview Lines

To group/outline the rows, you can use the following code:

$spreadsheet->getActiveSheet()->getRowDimension('5')->setOutlineLevel(1);

You can also collapse the row. Note that you should also set the row to be invisible, otherwise the fold will not be visible in Excel 2007.

$spreadsheet->getActiveSheet()->getRowDimension('5')->setCollapsed(true);
$spreadsheet->getActiveSheet()->getRowDimension('5')->setVisible(false);

Here's an example of collapsing rows 50 to 80:

for ($i = 51; $i <= 80; $i++) {
    $spreadsheet->getActiveSheet()->setCellValue('A' . $i, "FName $i");
    $spreadsheet->getActiveSheet()->setCellValue('B' . $i, "LName $i");
    $spreadsheet->getActiveSheet()->setCellValue('C' . $i, "PhoneNo $i");
    $spreadsheet->getActiveSheet()->setCellValue('D' . $i, "FaxNo $i");
    $spreadsheet->getActiveSheet()->setCellValue('E' . $i, true);
    $spreadsheet->getActiveSheet()->getRowDimension($i)->setOutlineLevel(1);
    $spreadsheet->getActiveSheet()->getRowDimension($i)->setVisible(false);
}

$spreadsheet->getActiveSheet()->getRowDimension(81)->setCollapsed(true);

You can instruct PhpSpreadsheet to add summaries below (default) or above the collapsible row. The following code adds the above summary:

$spreadsheet->getActiveSheet()->setShowSummaryBelow(false);

Merge/unmerge cells

If you want to display a large amount of data in a worksheet, you can combine two or more cells into a single cell. This can be done with the following code:

$spreadsheet->getActiveSheet()->mergeCells('A18:E22');

Merge can be removed using the unmergeCells method:

$spreadsheet->getActiveSheet()->unmergeCells('A18:E22');

Insert row/column

You can insert/delete rows/columns at specific positions. The following code inserts 2 lines before line 7:

$spreadsheet->getActiveSheet()->insertNewRowBefore(7, 2);

Add drawings to worksheets

Drawings are always represented as separate objects that can be added to worksheets. Therefore, you must first instantiate new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing and then assign a meaningful value to its property:

$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName('Logo');
$drawing->setDescription('Logo');
$drawing->setPath('./images/officelogo.jpg');
$drawing->setHeight(36);

To add the above graph to the worksheet, use the following code snippet. PhpSpreadsheet creates a link between the graph and the worksheet:

$drawing->setWorksheet($spreadsheet->getActiveSheet());

You can set many properties on drawings, here are some examples:

$drawing->setName('Paid');
$drawing->setDescription('Paid');
$drawing->setPath('./images/paid.png');
$drawing->setCoordinates('B15');
$drawing->setOffsetX(110);
$drawing->setRotation(25);
$drawing->getShadow()->setVisible(true);
$drawing->getShadow()->setDirection(45);

You can also add images created with GD functions without first saving them to disk as in-memory graphics.

//  Use GD to create an in-memory image
$gdImage = @imagecreatetruecolor(120, 20) or die('Cannot Initialize new GD image stream');
$textColor = imagecolorallocate($gdImage, 255, 255, 255);
imagestring($gdImage, 1, 5, 5,  'Created with PhpSpreadsheet', $textColor);

//  Add the In-Memory image to a worksheet
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing();
$drawing->setName('In-Memory image 1');
$drawing->setDescription('In-Memory image 1');
$drawing->setCoordinates('A1');
$drawing->setImageResource($gdImage);
$drawing->setRenderingFunction(
    \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::RENDERING_JPEG
);
$drawing->setMimeType(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT);
$drawing->setHeight(36);
$drawing->setWorksheet($spreadsheet->getActiveSheet());

Read image from worksheet

A common question is how to retrieve an image from a loaded workbook and save it to disk as a separate image file.

The following code extracts images from the currently active sheet and writes each image as a separate file.

$i = 0;
foreach ($spreadsheet->getActiveSheet()->getDrawingCollection() as $drawing) {
    if ($drawing instanceof \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing) {
        ob_start();
        call_user_func(
            $drawing->getRenderingFunction(),
            $drawing->getImageResource()
        );
        $imageContents = ob_get_contents();
        ob_end_clean();
        switch ($drawing->getMimeType()) {
            case \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_PNG :
                $extension = 'png';
                break;
            case \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_GIF:
                $extension = 'gif';
                break;
            case \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_JPEG :
                $extension = 'jpg';
                break;
        }
    } else {
        $zipReader = fopen($drawing->getPath(),'r');
        $imageContents = '';
        while (!feof($zipReader)) {
            $imageContents .= fread($zipReader,1024);
        }
        fclose($zipReader);
        $extension = $drawing->getExtension();
    }
    $myFileName = '00_Image_'.++$i.'.'.$extension;
    file_put_contents($myFileName,$imageContents);
}

Add rich text to cells

Rich text can be added to cells using the \PhpOffice\PhpSpreadsheet\RichText\RichText instance. Here is an example that creates the following rich text string:

Unless otherwise stated on the invoice, the invoice is due within thirty days of the end of the month.

$richText = new \PhpOffice\PhpSpreadsheet\RichText\RichText();
$richText->createText('This invoice is ');
$payable = $richText->createTextRun('payable within thirty days after the end of the month');
$payable->getFont()->setBold(true);
$payable->getFont()->setItalic(true);
$payable->getFont()->setColor( new \PhpOffice\PhpSpreadsheet\Style\Color( \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_DARKGREEN ) );
$richText->createText(', unless specified otherwise on the invoice.');
$spreadsheet->getActiveSheet()->getCell('A18')->setValue($richText);

define a named range

PhpSpreadsheet supports the definition of named ranges. They can be defined with the following code:

// Add some data
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->setCellValue('A1', 'Firstname:');
$spreadsheet->getActiveSheet()->setCellValue('A2', 'Lastname:');
$spreadsheet->getActiveSheet()->setCellValue('B1', 'Maarten');
$spreadsheet->getActiveSheet()->setCellValue('B2', 'Balliauw');

// Define named ranges
$spreadsheet->addNamedRange( new \PhpOffice\PhpSpreadsheet\NamedRange('PersonFN', $spreadsheet->getActiveSheet(), 'B1') );
$spreadsheet->addNamedRange( new \PhpOffice\PhpSpreadsheet\NamedRange('PersonLN', $spreadsheet->getActiveSheet(), 'B2') );

Optionally, a fourth parameter can be passed to define a local named range (i.e. only available on the current sheet). By default, named scopes are global.

Redirect output to client's Web browser

Sometimes, people do want to output files to the client's browser, especially when creating spreadsheets on the fly. There are some simple steps to do the following:

1. Create your PhpSpreadsheet spreadsheet
2. output the HTTP header for the type of document you want to output
3. Use \PhpOffice\PhpSpreadsheet\Writer\* of your choice, then save to 'php://output'

\PhpOffice\PhpSpreadsheet\Writer\Xlsx uses temporary storage php://output when writing. By default, temporary files are stored in the script's working directory. If there is no access, it will fall back to the OS's temporary file location.

Unauthorized viewing may not be safe! Depending on the configuration of your operating system, the temporary storage folder can be read by anyone using the same temporary storage folder. It is recommended not to use php://output when the document needs to be kept private.

HTTP headers

Example of script to redirect Excel 2007 file to client browser:

/* Here there will be some code where you create $spreadsheet */

// redirect output to client browser
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="myfile.xlsx"');
header('Cache-Control: max-age=0');

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');

Example of script to redirect Xls file to client browser:

/* Here there will be some code where you create $spreadsheet */

// redirect output to client browser
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="myfile.xls"');
header('Cache-Control: max-age=0');

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');

warn:

Make sure you don't include any echo statements or output anything other than an Excel file. At the beginning <? There should be no spaces before the PHP tag, but at the end? > There should be no newline after the tag (it can also be omitted to avoid problems) Make sure you save the script without a BOM (byte order mark) as this counts as echo output. The same applies to all included files. Failure to follow the above guidelines may result in a corrupt Excel file reaching the client browser, and/or PHP failing to set the header (resulting in a warning message).

set default column width

The default column width can be set with the following code:

$spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(12);

Set default row height

The default row height can be set with the following code:

$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);

Add GD Drawings to Worksheets

In some cases you may want to use GD to generate an in-memory image and add it to Spreadsheet without first saving this file to a temporary location.

Here's an example that generates an image in memory and adds it to the active sheet:

// Generate an image
$gdImage = @imagecreatetruecolor(120, 20) or die('Cannot Initialize new GD image stream');
$textColor = imagecolorallocate($gdImage, 255, 255, 255);
imagestring($gdImage, 1, 5, 5,  'Created with PhpSpreadsheet', $textColor);

// Add a drawing to the worksheet
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing();
$drawing->setName('Sample image');
$drawing->setDescription('Sample image');
$drawing->setImageResource($gdImage);
$drawing->setRenderingFunction(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::RENDERING_JPEG);
$drawing->setMimeType(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT);
$drawing->setHeight(36);
$drawing->setWorksheet($spreadsheet->getActiveSheet());

Set the worksheet zoom level

To set the zoom level of the worksheet, you can use the following code:

$spreadsheet->getActiveSheet()->getSheetView()->setZoomScale(75);

Note that the zoom level should be in the range 10-400.

Sheet tab color

Sometimes you want to set a color for sheet tabs. For example, you can have a red sheet tab:

$worksheet->getTabColor()->setRGB('FF0000');

Create worksheets in workbooks

If you need to create more sheets in the workbook, please follow the steps below:

$worksheet1 = $spreadsheet->createSheet();
$worksheet1->setTitle('Another sheet');

Think of its createSheet() as the "Insert Sheet" button in Excel. When you click the button, the new sheet is added to the existing sheet collection in the workbook.

Hidden sheet (Sheet state)

Use the following code to set the sheet to be hidden:

$spreadsheet->getActiveSheet()
    ->setSheetState(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN);

Sometimes, you may even want the worksheet to be "very hidden". The available sheet states are:

• \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VISIBLE
• \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN
• \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VERYHIDDEN

In Excel, the sheet state "Very Hidden" can only be set programmatically (for example, using a Visual Basic Macro). Such sheets cannot be made visible through the user interface.

Right-to-left worksheet

Worksheets can be set separately whether column A should start on the left or right. Leave the default value. Here's how to set columns from right to left.

// right-to-left worksheet
$spreadsheet->getActiveSheet()->setRightToLeft(true);