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.
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');