Skip to content

Instantly share code, notes, and snippets.

@r-sal

r-sal/PHPExcel_Basics.md

Last active March 6, 2024 19:21
Show Gist options
  • Star 83 You must be signed in to star a gist
  • Fork 50 You must be signed in to fork a gist
  • Save r-sal/4313500 to your computer and use it in GitHub Desktop.
Save r-sal/4313500 to your computer and use it in GitHub Desktop.
Download ZIP
PHPExcel Notes and code snippets
Raw
PHPExcel_a.md
Raw
PHPExcel_Basics.md

Basics

Creating a new PHPExcel Object.

    $this->PHPExcel = new PHPExcel();

Working with sheets

Creating a new sheet:

    $this->activeSheet = $this->PHPExcel->createSheet();

Getting the active Sheet:

    $this->activeSheet = $this->PHPExcel->getActiveSheet();

Setting the active sheet:

    $this->PHPExcel->setActiveSheetIndex(2);

Renaming a worksheet:

    $this->activeSheet->setTitle($title);

Writing to cells

Text can be added to a cell using setCellValue($colRow, $data)
$colRow - The column and row to write to (i.e. 'A2')
$data - The data to write

    $this->activeSheet
        ->setCellValue($colRow, $data);       
        
    $this->activeSheet
        ->setCellValue("B1", $data) 
        ->setCellValue("B2", $data); 
        ->setCellValue("B5", $data); 

    $this->activeSheet->setCellValueByColumnAndRow($column, $row, $data);  

    $this->activeSheet->setCellValueExplicit($coord, $value, $dataType);  
    $this->activeSheet->setCellValueExplicitByColumnAndRow($col, $row, $value, $dataType);

Writing from arrays

A 2-dimensional array can be written to the current sheet usng fromArray($twoDimArray)

  • $twoDimArray - the 2D array to be written
  • $useWhenNull - what to use if there is a null value
  • $topLeftCorner - where the top left corner should be.
    $this->activeSheet->fromArray($sheet);  
    $this->activeSheet->fromArray($sheet, "", $colRow);

Or the array can be written manually by looping through the array and calling setCellValue

    foreach($rows as $row => $columns) {
        foreach($columns as $column => $data) {
            $this->activeSheet->setCellValue($column.$row, $data);
        }
    }

Formatting Cells

Setting column width
A single column:

    $this->activeSheet
        ->getColumnDimension($colString)
        ->setWidth($width);

Default width for all columns on a sheet:

    $this->activeSheet
        ->getDefaultColumnDimension()
        ->setWidth($width);

Auto size

    $this->activeSheet
        ->getColumnDimension("A")
        ->setAutoSize(true);

Setting row height
A single row:

Default row height for an entire sheet:

    $this->activeSheet
        ->getDefaultRowDimension()
        ->setRowHeight($height);

Styling Cells


    
    $this->activeSheet
        ->getStyle("B1")
        ->getAlignment()
        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        
    $styleArray = array(
        'font' => array(
            'bold' => true,
        )
    );
    
    $this->activeSheet
        ->getStyle("B1:F1")
        ->applyFromArray(array("font" => array( "bold" => true)));

->getStyle("D1:D20")->getAlignment()->setWrapText(true);

Setting default styles for the active sheet

    $this->activeSheet
        ->getDefaultStyle()
        ->applyFromArray($this->defaultStyle);

Setting file properties

$this->PHPExcel->getProperties()->setCreator("");
$this->PHPExcel->getProperties()->setLastModifiedBy("");
$this->PHPExcel->getProperties()->setTitle("");
$this->PHPExcel->getProperties()->setSubject("");
$this->PHPExcel->getProperties()->setDescription("..");
$this->PHPExcel->getProperties()->setKeywords("");
$this->PHPExcel->getProperties()->setCategory("");
Raw
PHPExcel_featDem.md 
<?php

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
    					 ->setLastModifiedBy("Maarten Balliauw");


// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Invoice');
$objPHPExcel->getActiveSheet()->setCellValue('D1', PHPExcel_Shared_Date::PHPToExcel( gmmktime(0,0,0,date('m'),date('d'),date('Y')) ));
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);
$objPHPExcel->getActiveSheet()->setCellValue('E1', '#12566');

$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Product Id');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Description');
$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Price');
$objPHPExcel->getActiveSheet()->setCellValue('D3', 'Amount');
$objPHPExcel->getActiveSheet()->setCellValue('E3', 'Total');

$objPHPExcel->getActiveSheet()->setCellValue('A4', '1001');
$objPHPExcel->getActiveSheet()->setCellValue('B4', 'PHP for dummies');
$objPHPExcel->getActiveSheet()->setCellValue('C4', '20');
$objPHPExcel->getActiveSheet()->setCellValue('D4', '1');
$objPHPExcel->getActiveSheet()->setCellValue('E4', '=IF(D4<>"",C4*D4,"")');

$objPHPExcel->getActiveSheet()->setCellValue('A5', '1012');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'OpenXML for dummies');
$objPHPExcel->getActiveSheet()->setCellValue('C5', '22');
$objPHPExcel->getActiveSheet()->setCellValue('D5', '2');
$objPHPExcel->getActiveSheet()->setCellValue('E5', '=IF(D5<>"",C5*D5,"")');

$objPHPExcel->getActiveSheet()->setCellValue('E6', '=IF(D6<>"",C6*D6,"")');
$objPHPExcel->getActiveSheet()->setCellValue('E7', '=IF(D7<>"",C7*D7,"")');
$objPHPExcel->getActiveSheet()->setCellValue('E8', '=IF(D8<>"",C8*D8,"")');
$objPHPExcel->getActiveSheet()->setCellValue('E9', '=IF(D9<>"",C9*D9,"")');

$objPHPExcel->getActiveSheet()->setCellValue('D11', 'Total excl.:');
$objPHPExcel->getActiveSheet()->setCellValue('E11', '=SUM(E4:E9)');

$objPHPExcel->getActiveSheet()->setCellValue('D12', 'VAT:');
$objPHPExcel->getActiveSheet()->setCellValue('E12', '=E11*0.21');

$objPHPExcel->getActiveSheet()->setCellValue('D13', 'Total incl.:');
$objPHPExcel->getActiveSheet()->setCellValue('E13', '=E11+E12');

// Add comment
$objPHPExcel->getActiveSheet()->getComment('E11')->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');

$objPHPExcel->getActiveSheet()->getComment('E12')->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('Total amount of VAT on the current invoice.');

$objPHPExcel->getActiveSheet()->getComment('E13')->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('Total amount o...uding VAT.');
$objPHPExcel->getActiveSheet()->getComment('E13')->setWidth('100pt');
$objPHPExcel->getActiveSheet()->getComment('E13')->setHeight('100pt');
$objPHPExcel->getActiveSheet()->getComment('E13')->setMarginLeft('150pt');
$objPHPExcel->getActiveSheet()->getComment('E13')->getFillColor()->setRGB('EEEEEE');


// Add rich-text string
$objRichText = new PHPExcel_RichText();
$objRichText->createText('This invoice is ');

$objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );

$objRichText->createText(', unless specified otherwise on the invoice.');

$objPHPExcel->getActiveSheet()->getCell('A18')->setValue($objRichText);

// Merge cells
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
$objPHPExcel->getActiveSheet()->mergeCells('A28:B28');		// Just to test...
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');	// Just to test...

// Protect cells
// Needs to be set to true in order to enable any worksheet protection!
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);	
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');

// Set cell number formats
$objPHPExcel->getActiveSheet()->getStyle('E4:E13')
    ->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);

// Set column widths
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);

// Set fonts
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

$objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);

// Set alignments
$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()
    ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('B5')->getAlignment()->setShrinkToFit(true);

// Set thin black border outline around column
$styleThinBlackBorderOutline = array(
	'borders' => array(
		'outline' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN,
			'color' => array('argb' => 'FF000000'),
		),
	),
);
$objPHPExcel->getActiveSheet()->getStyle('A4:E10')
    ->applyFromArray($styleThinBlackBorderOutline);


// Set thick brown border outline around "Total"
$styleThickBrownBorderOutline = array(
	'borders' => array(
		'outline' => array(
			'style' => PHPExcel_Style_Border::BORDER_THICK,
			'color' => array('argb' => 'FF993300'),
		),
	),
);
$objPHPExcel->getActiveSheet()->getStyle('D13:E13')
    ->applyFromArray($styleThickBrownBorderOutline);

// Set fills
$objPHPExcel->getActiveSheet()->getStyle('A1:E1')
    ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:E1')
    ->getFill()->getStartColor()->setARGB('FF808080');

// Set style for header row using alternative method
$objPHPExcel->getActiveSheet()->getStyle('A3:E3')->applyFromArray(
	array(
		'font'    => array(
				'bold'      => true
			),
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
			),
			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				)
			),
			'fill' => array(
	 			'type'       => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
	  			'rotation'   => 90,
	 			'startcolor' => array(
	 				'argb' => 'FFA0A0A0'
	 			),
	 			'endcolor'   => array(
	 				'argb' => 'FFFFFFFF'
	 			)
	 		)
		)
);

$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray(
		array(
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
			),
			'borders' => array(
				'left'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				)
			)
		)
);

$objPHPExcel->getActiveSheet()->getStyle('B3')->applyFromArray(
		array(
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
			)
		)
);

$objPHPExcel->getActiveSheet()->getStyle('E3')->applyFromArray(
		array(
			'borders' => array(
				'right'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				)
			)
		)
);

// Unprotect a cell
$objPHPExcel->getActiveSheet()
    ->getStyle('B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

// Add a hyperlink to the sheet
$objPHPExcel->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website');
$objPHPExcel->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

$objPHPExcel->getActiveSheet()->setCellValue('E27', 'Terms and conditions');
$objPHPExcel->getActiveSheet()->getCell('E27')->getHyperlink()->setUrl("sheet://'Terms and conditions'!A1");
$objPHPExcel->getActiveSheet()->getCell('E27')->getHyperlink()->setTooltip('Review terms and conditions');
$objPHPExcel->getActiveSheet()->getStyle('E27')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

// Add a drawing to the worksheet
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/officelogo.jpg');
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

// Add a drawing to the worksheet
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Paid');
$objDrawing->setDescription('Paid');
$objDrawing->setPath('./images/paid.png');
$objDrawing->setCoordinates('B15');
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

// Add a drawing to the worksheet
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('PHPExcel logo');
$objDrawing->setDescription('PHPExcel logo');
$objDrawing->setPath('./images/phpexcel_logo.gif');
$objDrawing->setHeight(36);
$objDrawing->setCoordinates('D24');
$objDrawing->setOffsetX(10);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

// Play around with inserting and removing rows and columns
$objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10);
$objPHPExcel->getActiveSheet()->removeRow(6, 10);
$objPHPExcel->getActiveSheet()->insertNewColumnBefore('E', 5);
$objPHPExcel->getActiveSheet()->removeColumn('E', 5);

// Set header and footer. When no different headers for odd/even are used, odd header is assumed.
$objPHPExcel->getActiveSheet()->getHeaderFooter()
    ->setOddHeader('&L&BInvoice&RPrinted on &D');
$objPHPExcel->getActiveSheet()->getHeaderFooter()
    ->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

// Set page orientation and size
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

// Rename first worksheet
$objPHPExcel->getActiveSheet()->setTitle('Invoice');


// Create a new worksheet, after the default sheet
$objPHPExcel->createSheet();

// Llorem ipsum...
$sLloremIpsum = 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Vivamus eget ante. Sed cursus nunc semper tortor. Aliquam luctus purus non elit. Fusce vel elit commodo sapien dignissim dignissim. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Curabitur accumsan magna sed massa. Nullam bibendum quam ac ipsum. Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Proin augue. Praesent malesuada justo sed orci. Pellentesque lacus ligula, sodales quis, ultricies a, ultricies vitae, elit. Sed luctus consectetuer dolor. Vivamus vel sem ut nisi sodales accumsan. Nunc et felis. Suspendisse semper viverra odio. Morbi at odio. Integer a orci a purus venenatis molestie. Nam mattis. Praesent rhoncus, nisi vel mattis auctor, neque nisi faucibus sem, non dapibus elit pede ac nisl. Cras turpis.';

// Add some data to the second sheet, resembling some different data types
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Terms and conditions');
$objPHPExcel->getActiveSheet()->setCellValue('A3', $sLloremIpsum);
$objPHPExcel->getActiveSheet()->setCellValue('A4', $sLloremIpsum);
$objPHPExcel->getActiveSheet()->setCellValue('A5', $sLloremIpsum);
$objPHPExcel->getActiveSheet()->setCellValue('A6', $sLloremIpsum);

// Set the worksheet tab color
$objPHPExcel->getActiveSheet()->getTabColor()->setARGB('FF0094FF');

// Set alignments
$objPHPExcel->getActiveSheet()->getStyle('A3:A6')->getAlignment()->setWrapText(true);

// Set column widths
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(80);

// Set fonts
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);

$objPHPExcel->getActiveSheet()->getStyle('A3:A6')->getFont()->setSize(8);

// Add a drawing to the worksheet
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Terms and conditions');
$objDrawing->setDescription('Terms and conditions');
$objDrawing->setPath('./images/termsconditions.jpg');
$objDrawing->setCoordinates('B14');
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

// Set page orientation and size
$objPHPExcel->getActiveSheet()->getPageSetup()
    ->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()
    ->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

// Rename second worksheet
$objPHPExcel->getActiveSheet()->setTitle('Terms and conditions');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
Raw
PHPExcel_Misc.md

Sort

<?php
  $objPHPExcel->getActiveSheet()->toArray();
	
	// Rename worksheet
	$objPHPExcel->getActiveSheet()->setTitle('Datatypes');  
	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
	$objPHPExcel->setActiveSheetIndex(0);
	
	$objPHPExcel->getActiveSheet()->setShowGridLines(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

Named Ranges

// Define named ranges
$objPHPExcel->addNamedRange( new PHPExcel_NamedRange('PersonName', $objPHPExcel->getActiveSheet(), 'B1') );
// Rename named ranges
$objPHPExcel->getNamedRange('PersonName')->setName('PersonFN');
// Add some data to the sheet
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Firstname:')
                              ->setCellValue('B1', '=PersonFN');
// Resolve range
$objPHPExcel->getActiveSheet()->getCell('B1')->getCalculatedValue()l

Date/Time

  • PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2 //2012-12-18
  • PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4 //3:06:11
  • PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME //18/12/12 3:06
  $dateTimeNow = time();
  
  $sheet = $objPHPExcel->getActiveSheet();
  $sheet->setCellValue('A1', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));
  
  $sheet->getStyle('A1')
        ->getNumberFormat()
        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);  //2012-12-18

Iterator

<?php
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("05featuredemo.xlsx");

echo date('H:i:s') , " Iterate worksheets" , EOL;
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
	echo 'Worksheet - ' , $worksheet->getTitle() , EOL;

	foreach ($worksheet->getRowIterator() as $row) {
		echo '    Row number - ' , $row->getRowIndex() , EOL;

		$cellIterator = $row->getCellIterator();
		$cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
		foreach ($cellIterator as $cell) {
			if (!is_null($cell)) {
				echo '        Cell - ' , $cell->getCoordinate() , ' - ' , $cell->getCalculatedValue() , EOL;
			}
		}
	}
}

Doc Properties

Core Properties:

$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
							 ->setLastModifiedBy("Maarten Balliauw")
							 ->setTitle("Office 2007 XLSX Test Document")
							 ->setSubject("Office 2007 XLSX Test Document")
							 ->setDescription("Tasses.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Test result file");
							 
$objPHPExcel->getProperties()->getCreator()
$objPHPExcel->getProperties()->getCreated()
$objPHPExcel->getProperties()->getLastModifiedBy()
$objPHPExcel->getProperties()->getModified()
$objPHPExcel->getProperties()->getTitle()
$objPHPExcel->getProperties()->getSubject()
$objPHPExcel->getProperties()->getDescription()
$objPHPExcel->getProperties()->getKeywords()

Extended (Application) Properties

$objPHPExcel->getProperties()->getCategory()
$objPHPExcel->getProperties()->getCompany()
$objPHPExcel->getProperties()->getManager()

Custom Properties

$customProperties = $objPHPExcel->getProperties()->getCustomProperties();
foreach($customProperties as $customProperty) {
	$propertyValue = $objPHPExcel->getProperties()->getCustomPropertyValue($customProperty);
	$propertyType = $objPHPExcel->getProperties()->getCustomPropertyType($customProperty);
	
	echo '    ' , $customProperty , ' - (' , $propertyType , ') - ';
	if ($propertyType == PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE) {
		echo date('d-M-Y H:i:s',$propertyValue) , EOL;
	} elseif ($propertyType == PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN) {
		echo (($propertyValue) ? 'TRUE' : 'FALSE') , EOL;
	} else {
		echo $propertyValue , EOL;
	}
}

# Reading Files

```php
<?php
	$objReader = PHPExcel_IOFactory::createReader('Excel2007');
	$objPHPExcel = $objReader->load("templates/template_1.xlsx");

	$data = array(array('title' => 'Excel for dummies', 'price'=> 17.99, 'quantity'	=> 2),
				  array('title' => 'PHP for dummies', 'price'=> 15.99, 'quantity' => 1),
				  array('title' => 'Inside OOP', 'price'=> 12.95, 'quantity' => 1));

	$baseRow = 4;
	foreach($data as $r => $dataRow) {
		$row = $baseRow + $r;
		$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);
	
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $r+1)
		                              ->setCellValue('B'.$row, $dataRow['title'])
		                              ->setCellValue('C'.$row, $dataRow['price'])
		                              ->setCellValue('D'.$row, $dataRow['quantity'])
		                              ->setCellValue('E'.$row, '=C'.$row.'*D'.$row);
	}
	$objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);
Raw
PHPExcel_Styles.md

PHPExcel:Styles

Set default font

$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')->setSize(10);

Set fonts

<?php
$sheet->getStyle('B1')->getFont()->setName('Candara');
$sheet->getStyle('B1')->getFont()->setSize(20);
$sheet->getStyle('B1')->getFont()->setBold(true);
$sheet->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$sheet->getStyle('D1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$sheet->getStyle('D13')->getFont()->setBold(true);

Set alignments

$sheet->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$sheet->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$sheet->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$sheet->getStyle('B5')->getAlignment()->setShrinkToFit(true);

Add rich-text

$objRichText = new PHPExcel_RichText();
$objRichText->createText('This invoice is ');

$objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );

$objRichText->createText(', unless specified otherwise on the invoice.');
$objPHPExcel->getActiveSheet()->getCell('A18')->setValue($objRichText);

Comments

$sheet->getComment('E13')->setAuthor('PHPExcel');
$objCommentRichText = $sheet->getComment('E13')->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$sheet->getComment('E13')->getText()->createTextRun("\r\n");
$sheet->getComment('E13')->getText()->createTextRun('some text....');
$sheet->getComment('E13')->setWidth('100pt');
$sheet->getComment('E13')->setHeight('100pt');
$sheet->getComment('E13')->setMarginLeft('150pt');
$sheet->getComment('E13')->getFillColor()->setRGB('EEEEEE');

Shared Styles

$sharedStyle = new PHPExcel_Style();
$sharedStyle1->applyFromArray(
  array('fill'  => array(
  	                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                    'color'	=> array('argb' => 'FFCCFFCC')
                  ),
        'borders' => array(
                    'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                    'right'  => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
                    )
    ));
    
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle, "A1:T100");

Duplicate Style

$style = new PHPExcel_Style();
$style->getFont()->setSize(20);
$coord = PHPExcel_Cell::stringFromColumnIndex($col) . $row;
$worksheet->setCellValue($coord, $str);
// Copy the style to that cell
$worksheet->duplicateStyle($style, $coord);
Raw
PHPExcel_to-sort.md

Merge & Un-Merge cells

$objPHPExcel->getActiveSheet()->mergeCells('A28:B28');      
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');

Add a hyperlink to the sheet

$objPHPExcel->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website');
$objPHPExcel->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

tab color

$objPHPExcel->getActiveSheet()->getTabColor()->setARGB('FF0094FF');

inserting and removing rows and columns

$objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10);
$objPHPExcel->getActiveSheet()->removeRow(6, 10);
$objPHPExcel->getActiveSheet()->insertNewColumnBefore('E', 5);
$objPHPExcel->getActiveSheet()->removeColumn('E', 5);

Set page orientation and size

$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
@Solihin123
Copy link

I have a proble, for sum of column I ,J,K,L.
My code is :<?php

mysql_query("SET NAMES 'utf8'");
mysql_query('SET character_set_connection=utf8');
mysql_query('SET character_set_client=utf8');
mysql_query('SET character_set_results=utf8');

$tahun = $_POST['tahun'];
/** Error reporting */
error_reporting(E_ALL);

date_default_timezone_set('Asia/Jakarta');

/** Include PHPExcel */
require_once 'Classes/PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
->setLastModifiedBy("Data Siswa SMPN 5 ")
->setTitle("Data Siswa SMPN 5 ***")
->setSubject("Data Siswa SMPN 5 ***")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");

// Create the worksheet
$objPHPExcel->setActiveSheetIndex(0);

$objPHPExcel->getActiveSheet()->setCellValue('A4', "NO")
->setCellValue('B4', "SALES")
->setCellValue('C4', "NO.PO")
->setCellValue('D4', "CUSTOMER")
->setCellValue('E4', "NO.INV")
->setCellValue('F4', "DATE")
->setCellValue('G4', "NO.URUT F.PAJAK")
->setCellValue('H4', "FAKTUR PAJAK")
->setCellValue('I4', "DPP")
->setCellValue('J4', "PPN")
->setCellValue('K4', "TOTAL")
->setCellValue('L4', "PPH 23")
->setCellValue('M4', "NET");

include("Includes/db.php");

$SQL =
mysql_query("SELECT no_inv_manual,nm_sales,no_inv,pajak,nm_cust,no_po,no_order,disc1,disc4,faktur_pajak,
sum(hrg_jual * qty_terima_cust) as tot_hrg_jual,Sum(hrg_jual * qty_terima_cust) as tot_jual,
DATE_FORMAT(tgl_po,'%d-%m-%Y') as Xtgl_po,DATE_FORMAT(tgl_inv,'%d-%m-%Y') as Xtgl_inv,DATE_FORMAT(tgl_dn,'%d-%m-%Y') as Xtgl_dn,DATE_FORMAT(tgl_fak_beli,'%d-%m-%Y') as Xtgl_fak_beli from dt_order
where not no_inv = '' AND tgl_inv >= '2016-05-01' and tgl_inv <= '2016-05-21' group by no_inv ");

$totJML = mysql_num_rows($SQL);

$dataArray= array();
$no=0;
while($row = mysql_fetch_array($SQL)){
$no++;

        if(substr($row['faktur_pajak'],0,3) == '070' or substr($row['faktur_pajak'],0,3) == '030' ){
            $Xpajak = 0;
        } else {
            $Xpajak = 10/100 * $row['tot_jual'];
        }

$tot_jual_ppn = $Xpajak + $row['tot_jual'];

        if(substr($row['faktur_pajak'],0,3) == '030'  ){
           $pph_23 = 1.5 / 100 * $row['tot_jual'];    
        } else {
           $pph_23 = 0;
        }

$tot_net = $tot_jual_ppn - $pph_23;

$row_array['no'] = $no;
$row_array['nm_sales'] = $row['nm_sales'];
$row_array['no_po'] = $row['no_po'];
$row_array['nm_cust'] = $row['nm_cust'];
$row_array['no_inv'] = $row['no_inv'];
$row_array['Xtgl_inv'] = $row['Xtgl_inv'];
$row_array['Xurut'] = substr($row['faktur_pajak'],8);
$row_array['faktur_pajak'] = $row['faktur_pajak'];
$row_array['dpp'] = $row['tot_jual'];
$row_array['ppn'] = number_format($Xpajak);
$row_array['total'] = number_format($tot_jual_ppn);
$row_array['pph_23'] = number_format($pph_23);
$row_array['total_net'] = number_format($tot_net);
$row_array['total_all'] = $row['tot_jual'];
array_push($dataArray,$row_array);

}

$nox=$no+4;

// $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A4:M$nox");

/// $objPHPExcel->getActiveSheet()->setCellValue($row_array['total_all'], 'Total excl.:');
$objPHPExcel->getActiveSheet()->setCellValue('I22', '=SUM(I5:I20)');

$objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A5');

// Set page orientation and size
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LEGAL);
$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.75);
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.75);
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75);
$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.75);
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

// Set title row bold;
$objPHPExcel->getActiveSheet()->getStyle('A4:M4')->getFont()->setBold(true);
// Set fills
$objPHPExcel->getActiveSheet()->getStyle('A4:M4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A4:M4')->getFill()->getStartColor()->setARGB('FF808080');

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);

// Set autofilter
// Always include the complete filter range!
// Excel does support setting only the caption
// row, but that's not a best practise...
//$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

$sharedStyle1 = new PHPExcel_Style();
$sharedStyle2 = new PHPExcel_Style();

$sharedStyle1->applyFromArray(
array('borders' => array(
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM),
'left' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
),
));

$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A4:M$nox");

// Set style for header row using alternative method
$objPHPExcel->getActiveSheet()->getStyle('A4:M4')->applyFromArray(
array(
'font' => array(
'bold' => true
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
),
'borders' => array(
'top' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
),
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
'rotation' => 90,
'startcolor' => array(
'argb' => 'FFA0A0A0'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
)
);

// Add a drawing to the worksheet
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('images/R-3.gif');
$objDrawing->setCoordinates('B2');
$objDrawing->setHeight(50);
$objDrawing->setWidth(50);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objPHPExcel->getActiveSheet()->getStyle('A4:M1000')->getFont()->setName('Calibri');
$objPHPExcel->getActiveSheet()->getStyle('A4:M1000')->getFont()->setSize(11);

// Merge cells
$objPHPExcel->getActiveSheet()->mergeCells('D2:M2');
$objPHPExcel->getActiveSheet()->setCellValue('D2', "LAPORAN PENJUALAN $tahun");
$objPHPExcel->getActiveSheet()->getStyle('D2:M2')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('D2:M2')->getFont()->setSize(18);
$objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setSize(22);
$objPHPExcel->getActiveSheet()->getStyle('D2:M2')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A2:M2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="lap.penjualan"'.date("d-F-Y").'".xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;

// Save Excel 2007 file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', FILE));
?>

Please help me send answer to my email.

@denism300
Copy link

I have a problem with adding a hyperlink to the sheet.
I do exactly as the manual, but the tooltip can not be inserted.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment