PHPExcel Cheat Sheet.md

PHPExcel Cheat Sheet

Documentation

Snippets

Install.

composer require phpoffice/phpexcel

Create a new Excel file.

$PHPExcel = new PHPExcel();

Loading an existing Excel file.

$filename = './example1.xlsx';

$PHPExcel = PHPExcel_IOFactory::load($filename);

Set file properties.

$file_creator = 'Jose Robinson';
$doc_title = 'Test file';
$doc_description = 'Test file for example purpose.';

$PHPExcel->getProperties()->setCreator($file_creator);
$PHPExcel->getProperties()->setLastModifiedBy($file_creator);
$PHPExcel->getProperties()->setTitle($doc_title);
$PHPExcel->getProperties()->setSubject($doc_title);
$PHPExcel->getProperties()->setDescription($doc_description);

Define and get active worksheet.

Define the active worksheet, starting from 0 (zero):

$PHPExcel->setActiveSheetIndex(0);

Get the active worksheet:

$sheet = $PHPExcel->getActiveSheet();

Create a new worksheet.

$sheet2 = $PHPExcel->createSheet();

Rename a worksheet.

$sheet->setTitle('Report');

Set width and height for cells.

Set width:

$sheet->getColumnDimension('A')->setWidth(50); 

Set auto size for columns:

$sheet->getColumnDimension("A")->setAutoSize(true);

Set height:

$sheet->getRowDimension(1)->setRowHeight(35);

Set font styles.

$sheet->getStyle('A1')->getFont()
    ->applyFromArray([
        'name' => 'Arial',
        'bold' => true,
        'italic' => false,
        'underline' => PHPExcel_Style_Font::UNDERLINE_DOUBLE,
        'strike' => false,
        'color' => ['rgb' => '808080']
    ]);

Set background color.

$sheet->getStyle('A1')->getFill()
    ->applyFromArray([
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'startcolor' => ['rgb' => 'FF0000'],
    ]);

Set borders.

$sheet->getStyle('A1')->getBorders()
    ->applyFromArray([
        'bottom' => [
            'style' => PHPExcel_Style_Border::BORDER_DASHDOT,
            'color' => ['rgb' => '808080']
        ],
        'top' => [
            'style' => PHPExcel_Style_Border::BORDER_DASHDOT,
            'color' => ['rgb' => '808080']
        ],
        
        /* Or: */
        'allborders' => [
            'style' => PHPExcel_Style_Border::BORDER_DASHDOT,
            'color' => ['rgb' => '808080']
        ],
    ]);

Reference: PHPExcel_Style_Border.

Set Column Format

$sheet->getStyle('A1')
      ->getNumberFormat()
      ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);

Format code can also be a string:

->setFormatCode('0%')
->setFormatCode('yyyy-mm-dd')

Reference: PHPExcel Format Code

Merge cells.

$sheet->mergeCells('A1:C1');

Unmerge cells:

$sheet->unmergeCells('A1:C1');

Set alignment.

$sheet->getStyle('A1')->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
    ->setVertical(PHPExcel_Style_Alignment::VERTICAL_BOTTOM); 

// For horizontal align:
// HORIZONTAL_GENERAL, HORIZONTAL_LEFT, HORIZONTAL_RIGHT, HORIZONTAL_CENTER, HORIZONTAL_CENTER_CONTINUOUS, HORIZONTAL_JUSTIFY

// For vertical align:    
// VERTICAL_BOTTOM, VERTICAL_TOP, VERTICAL_CENTER, VERTICAL_JUSTIFY

Write on cells.

$sheet->setCellValue('A1', 'Lorem Ipsum');

Defining data type for the cell:

$sheet->setCellValueExplicit('A1', '0029', PHPExcel_Cell_DataType::TYPE_STRING);

Reference: PHPExcel_Cell_DataType.

Add a formula to a cell:

$sheet->setCellValue("A1", "=SUM(A1:A4)");

Add images

$PHPExcel_Drawing = new PHPExcel_Worksheet_Drawing();
$PHPExcel_Drawing->setWorksheet($sheet);
$PHPExcel_Drawing->setPath('/absolute/path/to/image.jpg');
$PHPExcel_Drawing->setCoordinates('A1');
$PHPExcel_Drawing->setOffsetX(1);
$PHPExcel_Drawing->setOffsetY(1);

Create the Excel file.

$filename = dirname(__FILE__) . '/Report.xlsx';

$PHPExcel_Writer = new PHPExcel_Writer_Excel2007($PHPExcel);
$objWriter->save($filename);

Output to the browser as a download.

$filename = 'Report.xlsx';

header('Content-Description: Download');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="'. $filename .'"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Connection: close');

$PHPExcel_Writer = new PHPExcel_Writer_Excel2007($PHPExcel);
$PHPExcel_Writer->save('php://output');