README

XLSXLight

This is a light weight PHP Excel file writer

How do I get set up?

composer require jaffa80/xlsxlight

How To use

#!php
//Initialize Workbook
$xls = new Workbook();
$style = new Style();

Define number formats

following formats are already defined in style:

default : #,##0.00
currency : #,##0.00 "�"
datetime : dd/mm/yyyy hh:mm;@
date : dd/mm/yyyy;@
time : hh:mm:ss;@
percent : #,##0.00 %

You can customize or create new formats but it is recommended to use dot as decimal separator and comma as thousands separator, Excel is using system regional settings to format them properly then. You can force custom symbols to number by adding \ in front of the symbol but is is impossible to force decimal separator to be different. This can be done only by setting cell value as string (cant use the numbers for calculation then).

Add conditional formatting just like in Excel custom formatting

[green]0.00;[Red]-0.00

Use # for optional numbers and 0 for required numbers

#,##0.00

When using custom text or marks, put them in between quot marks\

#,##0.00 "�"

#!php
$style->setNumberFormat('default', '#,##0.00##;[Red]-#,##0.00##')
  ->setNumberFormat('currency', '# ##0,00 "EUR"')
  ->setNumberFormat('datetime', 'dd.mm.yyyy hh:mm:ss;@')
  ->setNumberFormat('date', 'dd/mm/yyyy;@')
  ->setNumberFormat('time', 'hh:mm:ss;@')
  ->setNumberFormat('percent', '0,00%;[Red]-0,00%');

Font styling

default Font is already predefined (Calibri size 11) You can Over write default font by adding font with the name default.

#!php
$style->setFont((new Font('default'))
      ->setSize(10)
      ->setColor('F0F0F0'));

Create new font

#!php
$style->setFont((new Font('label'))
      ->setSize(14)
      ->setColor('FFFFFF')
      ->setBold()
      ->setItalic()
      ->setUndeline()
      ->setStrikeThrow()
      //set font alignment: first horizontal, then vertical
      ->setAlignment('center bottom'));

Cell background fill

#!php
$style->setFill('gray', 'CCCCCC')
  ->setFIll('blue', '003399');

Border styles

thickness and color can be defined based on css logic

#!php
$style->setBorder('fullBottomThick', 'thin thin thin thick', '000000')
  ->setBorder('sideBorders, 'thin none', '000000')
  ->setBorder('fullGrid', 'thin', '000000');

Add style to workbook

#!php
$xls->setStyle($style);

Create a sheet and fill with data

Repeat for as many sheets required\ By default data is written directly to sheet, You can disable it by adding third parameter false\ example: new Sheet('Sheet Name', $Worbook, false)\ DirectWrite is disabled for each sheet separately

#!php
$sheet = (new Sheet('DirectWrite', $xml))

adjust default column width and row height

#!php
$sheet->setDefaultColWidth(120)
  ->setDefaultRowHeight(20)

adjust specific column width using range or single column letter

#!php
$sheet->setColumnWidth('B:C',140)
  ->setColumnWidth('A',80)

cell first parameter can be defined as A2 or as a range A2:C3 cells defined as range will be merged

#!php
$sheet->setCell((new Cell('B2:E3', 'XLSXLight Report Example'))
      ->setFont('title') // set pre defined font
      ->setFill('blue') // set pre defined fill
  );

in for loops you can use Workbook::getColumnLetter(4) to convert number to column letter

#!php
foreach(range(1,10) as $col){
  $sheet->setCell((new Cell(Workbook::getColumnLetter($col).'5', 'Label '.$col))
      ->setBorder('fullBorder')
      ->setFill('gray')
  );
}

by not defining cell value you can format the cell, but no value is set to the cell

#!php
$sheet->setCell((new Cell('B4'))
  -setBorder('fullGrid')
  ->setFill('gray')
);

To add an image to the sheet

#!php
$sheet->addImage((new Image('D2','Logo.png'))
    //setSize uses 2 parameters width and height in pixels
    //using auto as one of the values will calculate it.
    ->setSize('auto', 240)
)

Freeze panes on sheet

use freezePanes to freeze rows and columns from scrolling. This works exactly like in Excel, You just define the most top left corner that will remain scrolling.

Defining cell "B6" rows 1-5 and column A will be frozen in place!
Defining cell "A6" only rows 1-5 will be frozen
defining cell "B1" only column A will be frozen

#!php
$sheet->freezePanes(B6);

Output

To Open the file in Browser

#!php
header('Content-Disposition: attachment; filename="xlsxlight_sample.xlsx"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');
$xls->stdOut();

To save directly in server

#!php
$xls->save($filename);

Author

Janis Kummits janis.kummits@gmail.com

jaffa80 / xlsxlight

Maintainers

Details