How to Get the Option to Use Existing Name Range Inexcel Workbook Instead of Properating Again
Recipes
The following pages offer you some widely-used PhpSpreadsheet recipes. Delight note that these exercise Non offer complete documentation on specific PhpSpreadsheet API functions, simply merely a crash-land to go you started. If you need specific API functions, please refer to the API documentation.
For example, setting a worksheet'southward folio orientation and size covers setting a page orientation to A4. Other paper formats, like United states of america Letter of the alphabet, are non covered in this document, but in the PhpSpreadsheet API documentation.
PhpSpreadsheet allows an easy manner to set a spreadsheet's metadata, using certificate property accessors. Spreadsheet metadata can exist useful for finding a specific certificate in a file repository or a document management organisation. For example Microsoft Sharepoint uses document metadata to search for a specific certificate in its document lists.
Setting spreadsheet metadata is done every bit follows:
$spreadsheet->getProperties() ->setCreator("Maarten Balliauw") ->setLastModifiedBy("Maarten Balliauw") ->setTitle("Role 2007 XLSX Exam 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 event file"); Setting a spreadsheet'south agile sheet
The following line of code sets the agile sheet index to the kickoff canvass:
$spreadsheet->setActiveSheetIndex(0); You can as well set up the active canvas past its name/title
$spreadsheet->setActiveSheetIndexByName('DataSheet') will change the currently active sheet to the worksheet called "DataSheet".
Write a appointment or time into a cell
In Excel, dates and Times are stored as numeric values counting the number of days elapsed since 1900-01-01. For example, the date '2008-12-31' is represented as 39813. You can verify this in Microsoft Office Excel by entering that date in a cell and afterwards irresolute the number format to 'General' then the true numeric value is revealed. Also, 'iii:15 AM' is represented every bit 0.135417.
PhpSpreadsheet works with UST (Universal Standard Time) date and Time values, but does no internal conversions; so it is upwards to the developer to ensure that values passed to the date/fourth dimension conversion functions are UST.
Writing a date value in a cell consists of 2 lines of code. Select the method that suits you the best. Here are some examples:
// MySQL-similar timestamp '2008-12-31' or appointment string \PhpOffice\PhpSpreadsheet\Cell\Prison 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 fourth dimension) $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-engagement/time $spreadsheet->getActiveSheet()->setCellValue('D1', 39813) $spreadsheet->getActiveSheet()->getStyle('D1') ->getNumberFormat() ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH); The above methods for inbound a date all yield the aforementioned consequence. \PhpOffice\PhpSpreadsheet\Style\NumberFormat provides a lot of pre-defined date formats.
The \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel() method volition likewise work with a PHP DateTime object.
Similarly, times (or date and fourth dimension values) tin can exist entered in the same fashion: merely call up to use an appropriate format code.
Note:
See section "Using value binders to facilitate data entry" to acquire more than most the AdvancedValueBinder used in the first example. Excel tin can also operate in a 1904-based calendar (default for workbooks saved on Mac). Usually, y'all practice not have to worry about this when using PhpSpreadsheet.
Write a formula into a cell
Inside the Excel file, formulas are always stored as they would appear in an English version of Microsoft Office Excel, and PhpSpreadsheet handles all formulae internally in this format. This means that the following rules hold:
- Decimal separator is
.(period) - Function argument separator is
,(comma) - Matrix row separator is
;(semicolon) - English language function names must be used
This is regardless of which linguistic communication version of Microsoft Part Excel may have been used to create the Excel file.
When the final workbook is opened by the user, Microsoft Office Excel will take care of displaying the formula according the applications language. Translation is taken care of by the application!
The following line of code writes the formula =IF(C4>500,"profit","loss") into the cell B8. Annotation that the formula must start with = to make PhpSpreadsheet recognise this every bit a formula.
$spreadsheet->getActiveSheet()->setCellValue('B8','=IF(C4>500,"turn a profit","loss")'); If you desire to write a string start with an = grapheme to a cell, then you lot should use the setCellValueExplicit() method.
$spreadsheet->getActiveSheet() ->setCellValueExplicit( 'B8', '=IF(C4>500,"profit","loss")', \PhpOffice\PhpSpreadsheet\Prison cell\DataType::TYPE_STRING ); A cell's formula tin can be read once again using the post-obit line of code:
$formula = $spreadsheet->getActiveSheet()->getCell('B8')->getValue(); If you need the calculated value of a cell, use the following code. This is further explained in the calculation engine.
$value = $spreadsheet->getActiveSheet()->getCell('B8')->getCalculatedValue(); Locale Settings for Formulae
Some localisation elements accept been included in PhpSpreadsheet. Y'all can set a locale by changing the settings. To set up the locale to Russian y'all would utilise:
$locale = 'ru'; $validLocale = \PhpOffice\PhpSpreadsheet\Settings::setLocale($locale); if (!$validLocale) { repeat 'Unable to prepare locale to '.$locale." - reverting to en_us<br />\n"; } If Russian language files aren't available, the setLocale() method will render an error, and English settings will be used throughout.
One time you have prepare a locale, y'all can interpret a formula from its internal English coding.
$formula = $spreadsheet->getActiveSheet()->getCell('B8')->getValue(); $translatedFormula = \PhpOffice\PhpSpreadsheet\Adding\Calculation::getInstance()->_translateFormulaToLocale($formula); You tin also create a formula using the office names and statement separators appropriate to the defined locale; then translate it to English before setting the cell value:
$formula = '=ДНЕЙ360(ДАТА(2010;2;5);ДАТА(2010;12;31);ИСТИНА)'; $internalFormula = \PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance()->translateFormulaToEnglish($formula); $spreadsheet->getActiveSheet()->setCellValue('B8',$internalFormula); Currently, formula translation but translates the function names, the constants TRUE and FALSE, and the function argument separators. Jail cell addressing using R1C1 formatting is not supported.
At present, the following locale settings are supported:
| Language | Locale Lawmaking | |
|---|---|---|
| Czech | Ceština | cs |
| Danish | Dansk | da |
| German | Deutsch | de |
| Castilian | Español | es |
| Finnish | Suomi | fi |
| French | Français | fr |
| Hungarian | Magyar | hu |
| Italian | Italiano | it |
| Dutch | Nederlands | nl |
| Norwegian | Norsk Bokmål | nb |
| Smoothen | Jezyk polski | pl |
| Portuguese | Português | pt |
| Brazilian Portuguese | Português Brasileiro | pt_br |
| Russian | русский язык | ru |
| Swedish | Svenska | sv |
| Turkish | Türkçe | tr |
Write a newline grapheme "\n" in a cell (ALT+"Enter")
In Microsoft Part Excel y'all become a line break in a cell by hitting ALT+"Enter". When you practise that, it automatically turns on "wrap text" for the cell.
Hither is how to attain this in PhpSpreadsheet:
$spreadsheet->getActiveSheet()->getCell('A1')->setValue("hello\nworld"); $spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true); Tip
Read more nigh formatting cells using getStyle() elsewhere.
Tip
AdvancedValuebinder.php automatically turns on "wrap text" for the jail cell when information technology sees a newline character in a string that you are inserting in a cell. Just like Microsoft Part Excel. Try this:
\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() ); $spreadsheet->getActiveSheet()->getCell('A1')->setValue("how-do-you-do\nworld"); Read more about AdvancedValueBinder.php elsewhere.
Explicitly set a cell's datatype
You can set a jail cell'due south datatype explicitly by using the prison cell's setValueExplicit method, or the setCellValueExplicit method of a worksheet. Here's an example:
$spreadsheet->getActiveSheet()->getCell('A1') ->setValueExplicit( '25', \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC ); Alter a jail cell into a clickable URL
Y'all can make a prison cell a clickable URL by setting its hyperlink holding:
$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 some other worksheet/jail cell, apply the following code:
$spreadsheet->getActiveSheet()->setCellValue('E26', 'world wide web.phpexcel.cyberspace'); $spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl("sail://'Sheetname'!A1"); Setting Printer Options for Excel files
Setting a worksheet's page orientation and size
Setting a worksheet's folio orientation and size can be done 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); Notation that in that location are boosted page settings available. Please refer to the API documentation for all possible options.
The default papersize is initially PAPERSIZE_LETTER. However, this default tin be changed for new sheets with the post-obit call:
\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::setPaperSizeDefault( \PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4 ); The default orientation is ORIENTATION_DEFAULT, which will exist treated as Portrait in Excel. Still, this default can be inverse for new sheets with the following call:
\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::setOrientationDefault( \PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE ); Page Setup: Scaling options
The page setup scaling options in PhpSpreadsheet relate straight to the scaling options in the "Page Setup" dialog as shown in the illustration.
Default values in PhpSpreadsheet correspond to default values in MS Office Excel as shown in illustration
| method | initial value | calling method volition trigger | Note |
|---|---|---|---|
| setFitToPage(...) | Faux | - | |
| setScale(...) | 100 | setFitToPage(Simulated) | |
| setFitToWidth(...) | one | setFitToPage(True) | value 0 ways do-not-fit-to-width |
| setFitToHeight(...) | one | setFitToPage(TRUE) | value 0 ways practice-not-fit-to-tiptop |
Instance
Here is how to fit to 1 page wide by infinite pages tall:
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(i); $spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(0); As you can run into, it is not necessary to telephone call setFitToPage(TRUE) since setFitToWidth(...) and setFitToHeight(...) triggers this.
If yous utilise setFitToWidth() you should in general also specify setFitToHeight() explicitly like in the case. Be conscientious relying on the initial values.
Page margins
To set folio margins for a worksheet, apply this code:
$spreadsheet->getActiveSheet()->getPageMargins()->setTop(1); $spreadsheet->getActiveSheet()->getPageMargins()->setRight(0.75); $spreadsheet->getActiveSheet()->getPageMargins()->setLeft(0.75); $spreadsheet->getActiveSheet()->getPageMargins()->setBottom(one); Notation that the margin values are specified in inches.
Center a page horizontally/vertically
To middle a page horizontally/vertically, you can apply the post-obit code:
$spreadsheet->getActiveSheet()->getPageSetup()->setHorizontalCentered(true); $spreadsheet->getActiveSheet()->getPageSetup()->setVerticalCentered(false); Setting the print header and footer of a worksheet
Setting a worksheet's print header and footer can be done using the post-obit lines of code:
$spreadsheet->getActiveSheet()->getHeaderFooter() ->setOddHeader('&C&HPlease treat this document as confidential!'); $spreadsheet->getActiveSheet()->getHeaderFooter() ->setOddFooter('&50&B' . $spreadsheet->getProperties()->getTitle() . '&RPage &P of &Due north'); Substitution and formatting codes (starting with &) can be used inside headers and footers. At that place is no required order in which these codes must appear.
The showtime occurrence of the post-obit codes turns the formatting ON, the second occurrence turns it OFF again:
- Strikethrough
- Superscript
- Subscript
Superscript and subscript cannot both be ON at aforementioned time. Whichever comes first wins and the other is ignored, while the first is ON.
The following codes are supported by Xlsx:
| Lawmaking | Significant |
|---|---|
&L | Code for "left section" (in that location are three header / footer locations, "left", "center", and "right"). When ii or more occurrences of this section marker exist, the contents from all markers are concatenated, in the order of appearance, and placed into the left section. |
&P | Code for "electric current page #" |
&Due north | Lawmaking for "full pages" |
&font size | Code for "text font size", where font size is a font size in points. |
&K | Code for "text font color" - RGB Color is specified equally RRGGBB Theme Color is specifed as TTSNN where TT is the theme colour Id, Due south is either "+" or "-" of the tint/shade value, NN is the tint/shade value. |
&S | Code for "text strikethrough" on / off |
&10 | Code for "text super script" on / off |
&Y | Code for "text subscript" on / off |
&C | Code for "center department". When two or more occurrences of this department marker exist, the contents from all markers are concatenated, in the social club of appearance, and placed into the center section. |
&D | Lawmaking for "engagement" |
&T | Code for "time" |
&1000 | Code for "film equally background" - Please make sure to add the image to the header/footer (see Tip for picture) |
&U | Code for "text single underline" |
&E | Code for "double underline" |
&R | Code for "right section". When two or more occurrences of this section marker exist, the contents from all markers are concatenated, in the social club of advent, and placed into the right section. |
&Z | Code for "this workbook's file path" |
&F | Lawmaking for "this workbook's file name" |
&A | Lawmaking for "sheet tab name" |
&+ | Code for add to page # |
&- | Lawmaking for subtract from page # |
&"font name,font type" | Lawmaking for "text font name" and "text font type", where font proper name and font type are strings specifying the proper name and type of the font, separated by a comma. When a hyphen appears in font name, it means "none specified". Both of font proper noun and font type can be localized values. |
&"-,Assuming" | Lawmaking for "bold font manner" |
&B | Code for "bold font style" |
&"-,Regular" | Code for "regular font fashion" |
&"-,Italic" | Lawmaking for "italic font style" |
&I | Lawmaking for "italic font style" |
&"-,Assuming Italic" | Lawmaking for "assuming italic font way" |
&O | Code for "outline style" |
&H | Code for "shadow style" |
Tip
The above table of codes may seem overwhelming offset fourth dimension y'all are trying to figure out how to write some header or footer. Luckily, there is an easier way. Let Microsoft Office Excel do the work for you.For example, create in Microsoft Part Excel an xlsx file where you lot insert the header and footer equally desired using the programs ain interface. Relieve file every bit test.xlsx. Now, take that file and read off the values using PhpSpreadsheet as follows:
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('examination.xlsx'); $worksheet = $spreadsheet->getActiveSheet(); var_dump($worksheet->getHeaderFooter()->getOddFooter()); var_dump($worksheet->getHeaderFooter()->getEvenFooter()); var_dump($worksheet->getHeaderFooter()->getOddHeader()); var_dump($worksheet->getHeaderFooter()->getEvenHeader()); That reveals the codes for the even/odd header and footer. Experienced users may observe it easier to rename test.xlsx to exam.zip, unzip it, and inspect directly the contents of the relevant xl/worksheets/sheetX.xml to notice the codes for header/footer.
Tip for picture
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooterDrawing(); $drawing->setName('PhpSpreadsheet logo'); $drawing->setPath('./images/PhpSpreadsheet_logo.png'); $drawing->setHeight(36); $spreadsheet->getActiveSheet()->getHeaderFooter()->addImage($cartoon, \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooter::IMAGE_HEADER_LEFT); Setting press breaks on a row or cavalcade
To prepare a print suspension, apply the post-obit code, which sets a row suspension on row ten.
$spreadsheet->getActiveSheet()->setBreak('A10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW); The following line of code sets a impress suspension on column D:
$spreadsheet->getActiveSheet()->setBreak('D10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_COLUMN); Testify/hide gridlines when press
To show/hide gridlines when printing, use the following code:
$spreadsheet->getActiveSheet()->setPrintGridlines(true); Setting rows/columns to echo at top/left
PhpSpreadsheet can repeat specific rows/cells at top/left of a folio. The following code is an case of how to echo row one to 5 on each printed folio of a specific worksheet:
$spreadsheet->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(one, 5); Specify press expanse
To specify a worksheet's printing expanse, use the following code:
$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('A1:E5'); In that location can too be multiple printing areas in a single worksheet:
$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('A1:E5,G4:M20'); Styles
Formatting cells
A jail cell can be formatted with font, edge, fill, ... style data. For example, 1 can fix the foreground color of a cell to red, aligned to the right, and the edge to blackness and thick border style. Permit's practice that on cell B2:
$spreadsheet->getActiveSheet()->getStyle('B2') ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Fashion\Colour::COLOR_RED); $spreadsheet->getActiveSheet()->getStyle('B2') ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Fashion\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\Mode\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\Manner\Edge::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 instance, y'all 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'); Tip It is recommended to manner 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 culling fashion to ready styles. The following lawmaking sets a cell's style to font bold, alignment right, superlative border thin and a gradient fill:
$styleArray = [ 'font' => [ 'bold' => true, ], 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Way\Alignment::HORIZONTAL_RIGHT, ], 'borders' => [ 'peak' => [ 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, ], ], 'fill up' => [ '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 culling method using arrays should be faster in terms of execution whenever you are setting more than one style property. But the deviation may barely exist measurable unless you lot accept many different styles in your workbook.
You can perform the reverse function, exporting a Style every bit an assortment, as follows:
$styleArray = $spreadsheet->getActiveSheet()->getStyle('A3')->exportArray(); Number formats
You oft want to format numbers in Excel. For case you may want a thousands separator plus a fixed number of decimals later the decimal separator. Or perhaps yous want some numbers to be zippo-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 and so yous don't have to interact with the underlying raw number format code unless you need a custom number format.
In PhpSpreadsheet, you tin can also apply diverse predefined number formats. Example:
$spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat() ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); This volition format a number east.1000. 1587.two so it shows upwardly every bit 1,587.xx when you open the workbook in MS Office Excel. (Depending on settings for decimal and thousands separators in Microsoft Function Excel it may prove up as 1.587,20)
You can achieve exactly the same equally the to a higher place by using this:
$spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat() ->setFormatCode('#,##0.00'); In Microsoft Role Excel, as well as in PhpSpreadsheet, you will have to interact with raw number format codes whenever you need some special custom number format. Example:
$spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat() ->setFormatCode('[Blue][>=3000]$#,##0;[Scarlet][<0]$#,##0;$#,##0'); Another example is when yous want numbers zero-padded with leading zeros to a fixed length:
$spreadsheet->getActiveSheet()->getCell('A1')->setValue(19); $spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat() ->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 y'all discover it?
The readers shipped with PhpSpreadsheet come to the rescue. Load your template workbook using eastward.one thousand. Xlsx reader to reveal the number format code. Example how read a 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 may notice it faster to inspect the number format code directly by renaming template.xlsx to template.zip, unzipping, and looking for the relevant slice of XML code belongings the number format code in twoscore/styles.xml.
Alignment and wrap text
Let's prepare vertical alignment to the top for cells A1:D4
$spreadsheet->getActiveSheet()->getStyle('A1:D4') ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP); Here is how to achieve wrap text:
$spreadsheet->getActiveSheet()->getStyle('A1:D4') ->getAlignment()->setWrapText(truthful); Setting the default fashion of a workbook
It is possible to fix the default style of a workbook. Let'south set the default font to Arial size eight:
$spreadsheet->getDefaultStyle()->getFont()->setName('Arial'); $spreadsheet->getDefaultStyle()->getFont()->setSize(viii); Styling cell borders
In PhpSpreadsheet it is piece of cake to utilise various borders on a rectangular selection. Hither is how to apply a thick red border outline around cells B2:G8.
$styleArray = [ 'borders' => [ 'outline' => [ 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Edge::BORDER_THICK, 'colour' => ['argb' => 'FFFF0000'], ], ], ]; $worksheet->getStyle('B2:G8')->applyFromArray($styleArray); In Microsoft Office Excel, the above performance would correspond to selecting the cells B2:G8, launching the style dialog, choosing a thick reddish border, and clicking on the "Outline" edge component.
Note that the border outline is applied to the rectangular selection B2:G8 as a whole, not on each cell individually.
You can attain any edge effect by using just the 5 basic borders and operating on a single cell at a time:
- left
- right
- top
- bottom
- diagonal
Additional shortcut borders come up in handy like in the instance above. These are the shortcut borders bachelor:
- allBorders
- outline
- inside
- vertical
- horizontal
An overview of all edge shortcuts can be seen in the following image:
If you simultaneously set due east.1000. allBorders and vertical, then nosotros have "overlapping" borders, and one of the components has to win over the other where at that place is border overlap. In PhpSpreadsheet, from weakest to strongest borders, the list is as follows: allBorders, outline/inside, vertical/horizontal, left/right/acme/bottom/diagonal.
This border hierarchy tin can be utilized to attain various effects in an like shooting fish in a barrel manner.
Valid array keys for manner applyFromArray()
The following table lists the valid array keys for \PhpOffice\PhpSpreadsheet\Mode\Style::applyFromArray() classes. If the "Maps to property" column maps a fundamental to a setter, the value provided for that key will exist applied straight. If the "Maps to property" column maps a fundamental to a getter, the value provided for that key will be applied as some other manner array.
\PhpOffice\PhpSpreadsheet\Style\Style
| Assortment primal | Maps to holding |
|---|---|
| alignment | setAlignment() |
| borders | setBorders() |
| fill | setFill() |
| font | setFont() |
| numberFormat | setNumberFormat() |
| protection | setProtection() |
| quotePrefix | setQuotePrefix() |
\PhpOffice\PhpSpreadsheet\Manner\Alignment
| Assortment key | Maps to belongings |
|---|---|
| horizontal | setHorizontal() |
| indent | setIndent() |
| readOrder | setReadOrder() |
| shrinkToFit | setShrinkToFit() |
| textRotation | setTextRotation() |
| vertical | setVertical() |
| wrapText | setWrapText() |
\PhpOffice\PhpSpreadsheet\Fashion\Edge
| Array primal | Maps to holding |
|---|---|
| borderStyle | setBorderStyle() |
| color | setColor() |
\PhpOffice\PhpSpreadsheet\Fashion\Borders
| Array cardinal | Maps to property |
|---|---|
| allBorders | setLeft(); setRight(); setTop(); setBottom() |
| bottom | setBottom() |
| diagonal | setDiagonal() |
| diagonalDirection | setDiagonalDirection() |
| left | setLeft() |
| right | setRight() |
| top | setTop() |
\PhpOffice\PhpSpreadsheet\Style\Color
| Assortment central | Maps to property |
|---|---|
| argb | setARGB() |
\PhpOffice\PhpSpreadsheet\Style\Fill
| Array key | Maps to holding |
|---|---|
| color | getStartColor() |
| endColor | getEndColor() |
| fillType | setFillType() |
| rotation | setRotation() |
| startColor | getStartColor() |
\PhpOffice\PhpSpreadsheet\Style\Font
| Array central | Maps to holding |
|---|---|
| bold | setBold() |
| colour | getColor() |
| italic | setItalic() |
| name | setName() |
| size | setSize() |
| strikethrough | setStrikethrough() |
| subscript | setSubscript() |
| superscript | setSuperscript() |
| underline | setUnderline() |
\PhpOffice\PhpSpreadsheet\Way\NumberFormat
| Array key | Maps to property |
|---|---|
| formatCode | setFormatCode() |
\PhpOffice\PhpSpreadsheet\Style\Protection
| Array key | Maps to property |
|---|---|
| locked | setLocked() |
| hidden | setHidden() |
Conditional formatting a cell
A prison cell tin be formatted conditionally, based on a specific rule. For example, 1 can set the foreground colour of a cell to ruby-red if its value is below nada, and to green if its value is nothing or more.
1 can set a conditional mode ruleset to a cell using the following code:
$conditional1 = new \PhpOffice\PhpSpreadsheet\Style\Conditional(); $conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS); $conditional1->setOperatorType(\PhpOffice\PhpSpreadsheet\Manner\Conditional::OPERATOR_LESSTHAN); $conditional1->addCondition('0'); $conditional1->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Way\Colour::COLOR_RED); $conditional1->getStyle()->getFont()->setBold(true); $conditional2 = new \PhpOffice\PhpSpreadsheet\Style\Provisional(); $conditional2->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Provisional::CONDITION_CELLIS); $conditional2->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Provisional::OPERATOR_GREATERTHANOREQUAL); $conditional2->addCondition('0'); $conditional2->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Manner\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 lot can duplicate the style object:
$spreadsheet->getActiveSheet() ->duplicateStyle( $spreadsheet->getActiveSheet()->getStyle('B2'), 'B3:B7' ); More detailed documentation of the Conditional Formatting options and rules, and the use of Wizards to aid create them, can exist constitute in a defended section of the documentation.
DataBar of Conditional formatting
The nuts are the aforementioned equally conditional formatting. Additional DataBar object to provisional formatting.
For example, the following code will result in the conditional formatting shown in the prototype.
$conditional = new Provisional(); $conditional->setConditionType(Conditional::CONDITION_DATABAR); $conditional->setDataBar(new ConditionalDataBar()); $conditional->getDataBar() ->setMinimumConditionalFormatValueObject(new ConditionalFormatValueObject('num', '2')) ->setMaximumConditionalFormatValueObject(new ConditionalFormatValueObject('max')) ->setColor('FFFF555A'); $ext = $conditional ->getDataBar() ->setConditionalFormattingRuleExt(new ConditionalFormattingRuleExtension()) ->getConditionalFormattingRuleExt(); $ext->setCfRule('dataBar'); $ext->setSqref('A1:A5'); // target CellCoordinates $ext->setDataBarExt(new ConditionalDataBarExtension()); $ext->getDataBarExt() ->setMinimumConditionalFormatValueObject(new ConditionalFormatValueObject('num', 'two')) ->setMaximumConditionalFormatValueObject(new ConditionalFormatValueObject('autoMax')) ->setMinLength(0) ->setMaxLength(100) ->setBorder(true) ->setDirection('rightToLeft') ->setNegativeBarBorderColorSameAsPositive(false) ->setBorderColor('FFFF555A') ->setNegativeFillColor('FFFF0000') ->setNegativeBorderColor('FFFF0000') ->setAxisColor('FF000000'); 
To add a comment to a jail cell, use the following code. The example beneath adds a comment to cell E11:
$spreadsheet->getActiveSheet() ->getComment('E11') ->setAuthor('Mark Bakery'); $commentRichText = $spreadsheet->getActiveSheet() ->getComment('E11') ->getText()->createTextRun('PhpSpreadsheet:'); $commentRichText->getFont()->setBold(true); $spreadsheet->getActiveSheet() ->getComment('E11') ->getText()->createTextRun("\r\northward"); $spreadsheet->getActiveSheet() ->getComment('E11') ->getText()->createTextRun('Total amount on the current invoice, excluding VAT.'); 
To add a comment with groundwork prototype to a prison cell, utilise the following code:
$sheet = $spreadsheet->getActiveSheet(); $sheet->setCellValue('B5', 'Gibli Chromo'); // Add png image to comment background $drawing = new Cartoon(); $drawing->setName('Gibli Chromo'); $drawing->setPath('/tmp/gibli_chromo.png'); $annotate = $sheet->getComment('B5'); $comment->setBackgroundImage($drawing); // Set the size of the comment equal to the size of the epitome $comment->setSizeAsBackgroundImage(); 
Apply autofilter to a range of cells
To employ an autofilter to a range of cells, use the post-obit code:
$spreadsheet->getActiveSheet()->setAutoFilter('A1:C9'); Make sure that yous e'er include the complete filter range! Excel does support setting simply the captionrow, only that's not a best practice...
Setting security on a spreadsheet
Excel offers three levels of "protection":
- Document: allows you to set a password on a complete spreadsheet, allowing changes to be made merely when that countersign is entered.
- Worksheet: offers other security options: y'all can disallow inserting rows on a specific canvas, disallow sorting, ...
- Cell: offers the option to lock/unlock a cell as well as prove/hibernate the internal formula.
Make certain you enable worksheet protection if y'all need any of the worksheet or cell protection features! This can be washed using the following code:
$spreadsheet->getActiveSheet()->getProtection()->setSheet(true); Certificate
An instance on setting certificate security:
$security = $spreadsheet->getSecurity(); $security->setLockWindows(true); $security->setLockStructure(true); $security->setWorkbookPassword("PhpSpreadsheet"); Annotation that there are additional methods setLockRevision and setRevisionsPassword which use but to change tracking and history for shared workbooks.
Worksheet
An instance on setting worksheet security:
$protection = $spreadsheet->getActiveSheet()->getProtection(); $protection->setPassword('PhpSpreadsheet'); $protection->setSheet(true); $protection->setSort(truthful); $protection->setInsertRows(truthful); $protection->setFormatCells(true); If writing Xlsx files you lot can specify the algorithm used to hash the password before calling setPassword() like so:
$protection = $spreadsheet->getActiveSheet()->getProtection(); $protection->setAlgorithm(Protection::ALGORITHM_SHA_512); $protection->setSpinCount(20000); $protection->setPassword('PhpSpreadsheet'); The salt should not exist set manually and volition be automatically generated when setting a new countersign.
Cell
An case on setting cell security:
$spreadsheet->getActiveSheet()->getStyle('B1') ->getProtection() ->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_UNPROTECTED); Reading protected spreadsheet
Spreadsheets that are protected equally described above can always be read by PhpSpreadsheet. There is no need to know the password or do anything special in guild to read a protected file.
However if you need to implement a password verification mechanism, you can use the post-obit helper method:
$protection = $spreadsheet->getActiveSheet()->getProtection(); $allowed = $protection->verify('my password'); if ($allowed) { doSomething(); } else { throw new Exception('Incorrect password'); } If you demand to completely forbid reading a file by any tool, including PhpSpreadsheet, then y'all are looking for "encryption", not "protection".
Setting data validation on a prison cell
Data validation is a powerful feature of Xlsx. It allows to specify an input filter on the data that can be inserted in a specific cell. This filter can be a range (i.e. value must be between 0 and ten), a listing (i.eastward. value must be picked from a list), ...
The post-obit piece of code but allows numbers betwixt x and twenty to be entered in cell B3:
$validation = $spreadsheet->getActiveSheet()->getCell('B3') ->getDataValidation(); $validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_WHOLE ); $validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Prison cell\DataValidation::STYLE_STOP ); $validation->setAllowBlank(truthful); $validation->setShowInputMessage(truthful); $validation->setShowErrorMessage(true); $validation->setErrorTitle('Input fault'); $validation->setError('Number is not allowed!'); $validation->setPromptTitle('Immune input'); $validation->setPrompt('But numbers betwixt 10 and xx are immune.'); $validation->setFormula1(10); $validation->setFormula2(20); The following slice of code only allows an item picked from a list of information to be entered in cell B5:
$validation = $spreadsheet->getActiveSheet()->getCell('B5') ->getDataValidation(); $validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST ); $validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Prison cell\DataValidation::STYLE_INFORMATION ); $validation->setAllowBlank(imitation); $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 selection a value from the drop-downwardly list.'); $validation->setFormula1('"Item A,Item B,Item C"'); When using a data validation list like above, make certain you put the listing between " and " and that you split the items with a comma (,).
It is important to remember that whatsoever string participating in an Excel formula is allowed to exist maximum 255 characters (not bytes). This sets a limit on how many items yous can have in the string "Particular A,Particular B,Detail C". Therefore information technology is normally a ameliorate idea to type the item values straight in some cell range, say A1:A3, and instead employ, say, $validation->setFormula1('\'Canvas title\'!$A$1:$A$three'). Another benefit is that the particular values themselves tin can incorporate the comma , character itself.
If yous need data validation on multiple cells, one tin clone the ruleset:
$spreadsheet->getActiveSheet()->getCell('B8')->setDataValidation(clone $validation); Alternatively, one tin can apply the validation to a range of cells:
$validation->setSqref('B5:B1048576'); Setting a column'southward width
A column's width can be set using the following code:
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12); If you desire to set a column width using a unlike unit of measurement of measure out, and so you tin practise then by telling PhpSpreadsheet what UoM the width value that you are setting is measured in. Valid units are pt (points), px (pixels), pc (pica), in (inches), cm (centimeters) and mm (millimeters).
Setting the column width to -1 tells MS Excel to display the column using its default width.
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(120, 'pt'); If yous want PhpSpreadsheet to perform an automatic width adding, use the following code. PhpSpreadsheet will approximate the column width to the width of the widest value displayed in that cavalcade.
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(truthful); 
The measure out for column width in PhpSpreadsheet does not stand for exactly to the measure you may be used to in Microsoft Part Excel. Cavalcade widths are difficult to deal with in Excel, and there are several measures for the cavalcade width.
- Inner width in character units (e.g. 8.43 this is probably what you are familiar with in Excel)
- Full width in pixels (e.g. 64 pixels)
- Full width in character units (due east.g. 9.140625, value -1 indicates unset width)
PhpSpreadsheet always operates with "3. Full width in character units" which is in fact the only value that is stored in whatsoever Excel file, hence the most reliable measure. Unfortunately, Microsoft Role Excel does not present you with this mensurate. Instead measures 1 and ii are computed by the application when the file is opened and these values are presented in various dialogues and tool tips.
The character width unit is the width of a 0 (cypher) glyph in the workbooks default font. Therefore column widths measured in character units in two different workbooks can only be compared if they have the aforementioned default workbook font.If y'all have some Excel file and need to know the column widths in measure 3, you tin can read the Excel file with PhpSpreadsheet and echo the retrieved values.
To fix a worksheet'south column visibility, y'all can use the following lawmaking. The first line explicitly shows the column C, the second line hides column D.
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setVisible(truthful); $spreadsheet->getActiveSheet()->getColumnDimension('D')->setVisible(faux); Grouping/outline a column
To group/outline a cavalcade, you tin use the following code:
$spreadsheet->getActiveSheet()->getColumnDimension('East')->setOutlineLevel(one); You lot tin also collapse the cavalcade. Notation that you lot should also set the column invisible, otherwise the collapse will not exist visible in Excel 2007.
$spreadsheet->getActiveSheet()->getColumnDimension('East')->setCollapsed(true); $spreadsheet->getActiveSheet()->getColumnDimension('Eastward')->setVisible(false); Please refer to the department "group/outline a row" for a complete example on collapsing.
You tin can instruct PhpSpreadsheet to add a summary to the right (default), or to the left. The post-obit lawmaking adds the summary to the left:
$spreadsheet->getActiveSheet()->setShowSummaryRight(fake); Setting a row'south peak
A row'southward height tin can be set using the following code:
$spreadsheet->getActiveSheet()->getRowDimension('10')->setRowHeight(100); Excel measures row height in points, where one pt is 1/72 of an inch (or about 0.35mm). The default value is 12.75 pts; and the permitted range of values is between 0 and 409 pts, where 0 pts is a hidden row.
If you lot want to set a row height using a different unit of mensurate, so you lot can do so by telling PhpSpreadsheet what UoM the height value that yous are setting is measured in. Valid units are pt (points), px (pixels), pc (pica), in (inches), cm (centimeters) and mm (millimeters).
$spreadsheet->getActiveSheet()->getRowDimension('10')->setRowHeight(100, 'pt'); Setting the row acme to -1 tells MS Excel to display the column using its default height, which is based on the character font size.
If yous have wrapped text in a cell, then the -1 default will but set the row peak to display a single line of that wrapped text. If you lot need to calculate the actual pinnacle for the row, then count the lines that should be displayed (count the \n and add ane); then suit for the font. The adjustment for Calibri 11 is approximately 14.5; for Calibri 12 15.9, etc.
$spreadsheet->getActiveSheet()->getRowDimension(1)->setRowHeight( 14.v * (substr_count($sheet->getCell('A1')->getValue(), "\north") + 1) ); To prepare a worksheet''s row visibility, you can use the post-obit code. The following case hides row number x.
$spreadsheet->getActiveSheet()->getRowDimension('x')->setVisible(fake); Note that if yous utilise active filters using an AutoFilter, so this volition override whatsoever rows that you hibernate or unhide manually within that AutoFilter range if you save the file.
Group/outline a row
To group/outline a row, you tin use the following code:
$spreadsheet->getActiveSheet()->getRowDimension('five')->setOutlineLevel(1); You can as well plummet the row. Note that you should as well set the row invisible, otherwise the collapse will non be visible in Excel 2007.
$spreadsheet->getActiveSheet()->getRowDimension('five')->setCollapsed(true); $spreadsheet->getActiveSheet()->getRowDimension('five')->setVisible(imitation); Here's an example which collapses rows 50 to 80:
for ($i = 51; $i <= fourscore; $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('Due east' . $i, true); $spreadsheet->getActiveSheet()->getRowDimension($i)->setOutlineLevel(1); $spreadsheet->getActiveSheet()->getRowDimension($i)->setVisible(false); } $spreadsheet->getActiveSheet()->getRowDimension(81)->setCollapsed(true); Y'all can instruct PhpSpreadsheet to add a summary below the collapsible rows (default), or above. The following code adds the summary above:
$spreadsheet->getActiveSheet()->setShowSummaryBelow(false); Merge/unmerge cells
If yous accept a big piece of information you lot desire to display in a worksheet, you tin can merge two or more cells together, to become one cell. This can be done using the following code:
$spreadsheet->getActiveSheet()->mergeCells('A18:E22'); Removing a merge can be done using the unmergeCells method:
$spreadsheet->getActiveSheet()->unmergeCells('A18:E22'); Inserting rows/columns
You tin can insert/remove rows/columns at a specific position. The following code inserts 2 new rows, right earlier row 7:
$spreadsheet->getActiveSheet()->insertNewRowBefore(7, 2); Add a drawing to a worksheet
A drawing is always represented equally a split object, which can be added to a worksheet. Therefore, y'all must first instantiate a new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing, and assign its backdrop a meaningful value:
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); $drawing->setName('Logo'); $drawing->setDescription('Logo'); $cartoon->setPath('./images/officelogo.jpg'); $drawing->setHeight(36); To add the to a higher place drawing to the worksheet, use the following snippet of code. PhpSpreadsheet creates the link between the drawing and the worksheet:
$drawing->setWorksheet($spreadsheet->getActiveSheet()); You tin can set numerous properties on a cartoon, here are some examples:
$drawing->setName('Paid'); $cartoon->setDescription('Paid'); $drawing->setPath('./images/paid.png'); $drawing->setCoordinates('B15'); $drawing->setOffsetX(110); $cartoon->setRotation(25); $drawing->getShadow()->setVisible(truthful); $drawing->getShadow()->setDirection(45); You tin can as well add together images created using GD functions without needing to save them to disk first equally In-Memory drawings.
// Use GD to create an in-memory image $gdImage = @imagecreatetruecolor(120, 20) or dice('Cannot Initialize new GD prototype stream'); $textColor = imagecolorallocate($gdImage, 255, 255, 255); imagestring($gdImage, 1, v, 5, 'Created with PhpSpreadsheet', $textColor); // Add together the In-Retentiveness image to a worksheet $cartoon = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing(); $drawing->setName('In-Retentiveness image 1'); $cartoon->setDescription('In-Retentivity image i'); $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()); Reading Images from a worksheet
A commonly asked question is how to retrieve the images from a workbook that has been loaded, and relieve them every bit individual image files to disk.
The following code extracts images from the current active worksheet, and writes each as a dissever file.
apply PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing; $i = 0; foreach ($spreadsheet->getActiveSheet()->getDrawingCollection() as $cartoon) { if ($drawing instanceof MemoryDrawing) { ob_start(); call_user_func( $cartoon->getRenderingFunction(), $drawing->getImageResource() ); $imageContents = ob_get_contents(); ob_end_clean(); switch ($drawing->getMimeType()) { case MemoryDrawing::MIMETYPE_PNG : $extension = 'png'; break; instance MemoryDrawing::MIMETYPE_GIF: $extension = 'gif'; break; instance MemoryDrawing::MIMETYPE_JPEG : $extension = 'jpg'; intermission; } } else { if ($drawing->getPath()) { // Bank check if the source is a URL or a file path if ($drawing->getIsURL()) { $imageContents = file_get_contents($drawing->getPath()); $filePath = tempnam(sys_get_temp_dir(), 'Drawing'); file_put_contents($filePath , $imageContents); $mimeType = mime_content_type($filePath); // You lot could use the below to find the extension from mime type. // https://gist.github.com/alexcorvi/df8faecb59e86bee93411f6a7967df2c#gistcomment-2722664 $extension = File::mime2ext($mimeType); unlink($filePath); } 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 a jail cell
Adding rich text to a prison cell tin exist done using \PhpOffice\PhpSpreadsheet\RichText\RichText instances. Hither''s an example, which creates the post-obit rich text string:
This invoice is payable inside thirty days later the terminate of the month unless specified otherwise on the invoice.
$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\Fashion\Colour( \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_DARKGREEN ) ); $richText->createText(', unless specified otherwise on the invoice.'); $spreadsheet->getActiveSheet()->getCell('A18')->setValue($richText); Ascertain a named range
PhpSpreadsheet supports the definition of named ranges. These can be divers using 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(), '$B$1')); $spreadsheet->addNamedRange( new \PhpOffice\PhpSpreadsheet\NamedRange('PersonLN', $spreadsheet->getActiveSheet(), '$B$ii')); Optionally, a fourth parameter can be passed defining the named range local (i.e. only usable on the current worksheet). Named ranges are global by default.
Define a named formula
In addition to named ranges, PhpSpreadsheet also supports the definition of named formulae. These can be defined using the following code:
// Add some data $spreadsheet->setActiveSheetIndex(0); $worksheet = $spreadsheet->getActiveSheet(); $worksheet ->setCellValue('A1', 'Production') ->setCellValue('B1', 'Quantity') ->setCellValue('C1', 'Unit Price') ->setCellValue('D1', 'Toll') ->setCellValue('E1', 'VAT') ->setCellValue('F1', 'Full'); // Ascertain named formula $spreadsheet->addNamedFormula( new \PhpOffice\PhpSpreadsheet\NamedFormula('GERMAN_VAT_RATE', $worksheet, '=sixteen.0%')); $spreadsheet->addNamedFormula( new \PhpOffice\PhpSpreadsheet\NamedFormula('CALCULATED_PRICE', $worksheet, '=$B1*$C1')); $spreadsheet->addNamedFormula( new \PhpOffice\PhpSpreadsheet\NamedFormula('GERMAN_VAT', $worksheet, '=$D1*GERMAN_VAT_RATE')); $spreadsheet->addNamedFormula( new \PhpOffice\PhpSpreadsheet\NamedFormula('TOTAL_INCLUDING_VAT', $worksheet, '=$D1+$E1')); $worksheet ->setCellValue('A2', 'Avant-garde Web Application Architecture') ->setCellValue('B2', ii) ->setCellValue('C2', 23.0) ->setCellValue('D2', '=CALCULATED_PRICE') ->setCellValue('E2', '=GERMAN_VAT') ->setCellValue('F2', '=TOTAL_INCLUDING_VAT'); $spreadsheet->getActiveSheet() ->setCellValue('A3', 'Object Design Style Guide') ->setCellValue('B3', 5) ->setCellValue('C3', 12.0) ->setCellValue('D3', '=CALCULATED_PRICE') ->setCellValue('E3', '=GERMAN_VAT') ->setCellValue('F3', '=TOTAL_INCLUDING_VAT'); $spreadsheet->getActiveSheet() ->setCellValue('A4', 'PHP For the Web') ->setCellValue('B4', 3) ->setCellValue('C4', ten.0) ->setCellValue('D4', '=CALCULATED_PRICE') ->setCellValue('E4', '=GERMAN_VAT') ->setCellValue('F4', '=TOTAL_INCLUDING_VAT'); // Utilize a relative named range to provide the totals for rows 2-4 $spreadsheet->addNamedRange( new \PhpOffice\PhpSpreadsheet\NamedRange('COLUMN_TOTAL', $worksheet, '=A$2:A$four') ); $spreadsheet->getActiveSheet() ->setCellValue('B6', '=SUBTOTAL(109,COLUMN_TOTAL)') ->setCellValue('D6', '=SUBTOTAL(109,COLUMN_TOTAL)') ->setCellValue('E6', '=SUBTOTAL(109,COLUMN_TOTAL)') ->setCellValue('F6', '=SUBTOTAL(109,COLUMN_TOTAL)'); As with named ranges, an optional fourth parameter can be passed defining the named formula scope every bit local (i.e. only usable on the specified worksheet). Otherwise, named formulae are global past default.
Redirect output to a client'due south web browser
Sometimes, 1 really wants to output a file to a client''south browser, especially when creating spreadsheets on-the-fly. There are some piece of cake steps that can exist followed to do this:
- Create your PhpSpreadsheet spreadsheet
- Output HTTP headers for the type of document you lot wish to output
- Use the
\PhpOffice\PhpSpreadsheet\Author\*of your pick, and salve to'php://output'
\PhpOffice\PhpSpreadsheet\Writer\Xlsx uses temporary storage when writing to php://output. By default, temporary files are stored in the script'southward working directory. When there is no access, it falls back to the operating arrangement's temporary files location.
This may non be safe for unauthorized viewing! Depending on the configuration of your operating organization, temporary storage can exist read by anyone using the same temporary storage binder. When confidentiality of your document is needed, it is recommended not to use php://output.
Example of a script redirecting an Excel 2007 file to the client'southward browser:
/* Here there will be some code where you create $spreadsheet */ // redirect output to customer browser header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="myfile.xlsx"'); header('Enshroud-Control: max-age=0'); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output'); Example of a script redirecting an Xls file to the client's browser:
/* Here at that place will be some code where yous create $spreadsheet */ // redirect output to client browser header('Content-Blazon: awarding/vnd.ms-excel'); header('Content-Disposition: zipper;filename="myfile.xls"'); header('Cache-Control: max-age=0'); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls'); $writer->save('php://output'); Caution:
Make sure not to include any repeat statements or output any other contents than the Excel file. In that location should be no whitespace before the opening <?php tag and at virtually one line break later the closing ?> tag (which can also exist omitted to avoid problems). Make sure that your script is saved without a BOM (Byte-order marking) because this counts equally echoing output. The same things use to all included files. Failing to follow the above guidelines may result in corrupt Excel files arriving at the client browser, and/or that headers cannot exist set past PHP (resulting in warning messages).
Setting the default cavalcade width
Default cavalcade width can exist set using the post-obit code:
$spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(12); Excel measures column width in its ain proprietary units, based on the number of characters that will be displayed in the default font.
If y'all want to set up the default column width using a different unit of measure, then you lot can exercise so past telling PhpSpreadsheet what UoM the width value that yous are setting is measured in. Valid units are pt (points), px (pixels), pc (pica), in (inches), cm (centimeters) and mm (millimeters).
$spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(400, 'pt'); and PhpSpreadsheet volition handle the internal conversion.
Setting the default row height
Default row height can be set using the following code:
$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15); Excel measures row height in points, where 1 pt is 1/72 of an inch (or virtually 0.35mm). The default value is 12.75 pts; and the permitted range of values is betwixt 0 and 409 pts, where 0 pts is a subconscious row.
If you want to set a row height using a different unit of measure, so you can do so by telling PhpSpreadsheet what UoM the superlative value that you are setting is measured in. Valid units are pt (points), px (pixels), pc (pica), in (inches), cm (centimeters) and mm (millimeters).
$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(100, 'pt'); Add a GD drawing to a worksheet
In that location might exist a state of affairs where you want to generate an in-retention image using GD and add it to a Spreadsheet without commencement having to save this file to a temporary location.
Here''southward an example which generates an paradigm in memory and adds it to the active worksheet:
// Generate an prototype $gdImage = @imagecreatetruecolor(120, 20) or die('Cannot Initialize new GD image stream'); $textColor = imagecolorallocate($gdImage, 255, 255, 255); imagestring($gdImage, one, 5, v, 'Created with PhpSpreadsheet', $textColor); // Add a drawing to the worksheet $cartoon = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing(); $drawing->setName('Sample image'); $drawing->setDescription('Sample image'); $drawing->setImageResource($gdImage); $cartoon->setRenderingFunction(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::RENDERING_JPEG); $drawing->setMimeType(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT); $cartoon->setHeight(36); $drawing->setWorksheet($spreadsheet->getActiveSheet()); Setting worksheet zoom level
To set a worksheet's zoom level, the following code can be used:
$spreadsheet->getActiveSheet()->getSheetView()->setZoomScale(75); Note that zoom level should be in range 10 - 400.
Sheet tab color
Sometimes you lot want to set a color for sheet tab. For example y'all can accept a red sheet tab:
$worksheet->getTabColor()->setRGB('FF0000'); Creating worksheets in a workbook
If you need to create more than worksheets in the workbook, here is how:
$worksheet1 = $spreadsheet->createSheet(); $worksheet1->setTitle('Another canvas'); Think of createSheet() as the "Insert sheet" button in Excel. When you hit that push button a new sheet is appended to the existing collection of worksheets in the workbook.
Set a worksheet to be hidden using this code:
$spreadsheet->getActiveSheet() ->setSheetState(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN); Sometimes you may even want the worksheet to be "very hidden". The bachelor sheet states are :
-
\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VISIBLE -
\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN -
\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VERYHIDDEN
In Excel the sail state "very hidden" tin merely be set programmatically, due east.grand. with Visual Basic Macro. It is non possible to make such a sheet visible via the user interface.
Right-to-left worksheet
Worksheets can be prepare individually whether column A should commencement at left or right side. Default is left. Here is how to prepare columns from right-to-left.
// right-to-left worksheet $spreadsheet->getActiveSheet()->setRightToLeft(true); Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/
0 Response to "How to Get the Option to Use Existing Name Range Inexcel Workbook Instead of Properating Again"
Post a Comment