A cell can be formatted with font, border, fill, … style information. For example, one can set the foreground colour of a cell to red, aligned to the right, and the border to black and thick border style. Let’s do that on cell B2:
i Tip It is recommended to style many cells at once, using e.g. getStyle('A1:M500'), rather than styling the cells individually in a loop. This is much faster compared to looping through cells and styling them individually.
There is also an alternative manner to set styles. The following code sets a cell’s style to font bold, alignment right, top border thin and a gradient fill:
This alternative method using arrays should be faster in terms of execution whenever you are setting more than one style property. But the difference may barely be measurable unless you have many different styles in your workbook.
i Prior to PHPExcel 1.7.0 duplicateStyleArray() was the recommended method for styling a cell range, but this method has now been deprecated since getStyle() has started to accept a cell range.
You often want to format numbers in Excel. For example you may want a thousands separator plus a fixed number of decimals after the decimal separator. Or perhaps you want some numbers to be zero-padded.
In Microsoft Office Excel you may be familiar with selecting a number format from the "Format Cells" dialog. Here there are some predefined number formats available including some for dates. The dialog is designed in a way so you don't have to interact with the underlying raw number format code unless you need a custom number format.
In PHPExcel, you can also apply various predefined number formats. Example:
This will format a number e.g. 1587.2 so it shows up as 1,587.20 when you open the workbook in MS Office Excel. (Depending on settings for decimal and thousands separators in Microsoft Office Excel it may show up as 1.587,20)
You can achieve exactly the same as the above by using this:
->setFormatCode('0000'); // will show as 0019 in Excel
Tip The rules for composing a number format code in Excel can be rather complicated. Sometimes you know how to create some number format in Microsoft Office Excel, but don't know what the underlying number format code looks like. How do you find it?
The readers shipped with PHPExcel come to the rescue. Load your template workbook using e.g. Excel2007 reader to reveal the number format code. Example how read a number format code for cell A1:
Advanced users may find it faster to inspect the number format code directly by renaming template.xlsx to template.zip, unzipping, and looking for the relevant piece of XML code holding the number format code in xl/styles.xml.