正文

markdownphpexcelnotes和代码片段(代码片段)

author  author  2022-12-14  677

关键词：

## 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("");
```

## Sort
```php
<?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**
```php
// 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  

```php
  $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
<?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:**
```php
$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**  
```php
$objPHPExcel->getProperties()->getCategory()
$objPHPExcel->getProperties()->getCompany()
$objPHPExcel->getProperties()->getManager()
```
**Custom Properties**
```php
$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);
```

# PHPExcel:Styles  


**Set default font**
```php
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')->setSize(10);
```

**Set fonts**
```php
<?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**
```php
$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
```php
$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
```php
$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
```php
$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
```php
$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);
```


[PHPExcel: Class Reference](http://www.contao-docs.org/docs/PHPExcel/html/class_p_h_p_excel___worksheet.html)
  
[PHPExcel: Documentation](https://github.com/PHPOffice/PHPExcel/tree/develop/Documentation)

- [Valid array keys for style applyFromArray](https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/11-Appendices.md#valid-array-keys-for-style-applyfromarray)




```
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
var_dump($sheetData);
```

```php
<?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);
```

**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);
```

52java编程思想——创建窗口和程序片程序片限制

...好的或者不友好的站点，我们想要一些可以安全地行动的代码。所以我们可能会注意到大量的限制：(1)一个程序片不能接触到本地的磁盘。这意味着不能在本查看详情

57java编程思想——创建窗口和程序片可视编程和beans

...程序片可视编程和Beans我们已看到Java对创建可重复使用的代码片工作而言是多么的有价值。“最大限度地可重复使用”的代码单元拥有类，因为它包含一个紧密结合在一起的单元特性（字段）和单元动作（方法），它们可以直接... 查看详情

2021-2022-1acm集训队每周程序设计竞赛题解(代码片段)

A-苹果派题意：你有a个苹果和p个苹果片你有a个苹果和p个苹果片你有a个苹果和p个苹果片1个苹果可以制作3个苹果片1个苹果可以制作3个苹果片1个苹果可以制作3个苹果片2个苹果片可以制作1个苹果派2个苹果片可以制作1个苹果... 查看详情

2021-2022-1acm集训队每周程序设计竞赛题解(代码片段)

pythonqtgui设计：qtableviewqlistviewqlistwidetqtablewidgetqtreewidget和qtreewidgetltem表格和树类（提升篇—1）(代码片(代

目录1、QTableView类2、QListView类3、QListWidet类4、QTableWidget类5、QTreeWidget和QTreeWidgetltem类表格与树解决的问题是如何在一个控件中有规律地呈现更多的数据。PyQt提供了两种控件类用于解决该问题，其中一种是表格结构的控件类&#x... 查看详情

pythonqtgui设计：qtableviewqlistviewqlistwidetqtablewidgetqtreewidget和qtreewidgetltem表格和树类（提升篇—1）(代码片(代

mha集群（gtid复制）和vip漂移(代码片段)

在上一片博客中，讲述了怎么去配置MHA架构！这片博客不再细说，只说明其中MySQL主从搭建，这里使用的是gtid加上半同步复制！步骤与上一片博客一样，不同之处在于MySQL主从的搭建！详细的gtid搭建过程https://www.cnblogs.com/wxzhe/p/1... 查看详情

hdu4785闵可夫斯基和exhaustedrobot(代码片段)

题目链接：http://acm.hdu.edu.cn/showproblem.php?pid=4785题意：一个房间（矩形），里面有一些家具（凸多边形），你有一个扫地机器（凸多边形），扫地机器可以扫地是它的第一个点，能扫地条件是机器完全在房间里面并且和家具没有... 查看详情

错误记录kotlin编译报错(notnullablevaluerequiredtocallan‘iterator()‘methodonfor-looprange)(代码片(代码片段)

...级为303030之后,Kotlin的编译检查变得更严格,之前不规范的代码需要逐个修改;将编译版本compileSdkVersion和目标版本targetSdkVersion由28修改为30;androidcompileSdkVe 查看详情

完成一个简单的时间片轮转多道程序内核代码

王康+原创作品转载请注明出处+《Linux内核分析》MOOC课程http://mooc.study.163.com/course/USTC-1000029000”分别是1存储程序计算机工作模型，cpu执行程序的基础流程；2函数调用堆栈：各种寄存器和存储主要是为了指令的传取值，通过eip,esp,... 查看详情

javascriptcloudwatch到awslambda到slackchannelalerts和charts。通过sns主题通过lambda函数将cloudwatch警报发布到slack通(代码片

查看详情

powershell使用powershell和核心服务淘汰sdltridion2013sp1发布目标。见http://tridion.stackexchange.com/questions/1(代码片

查看详情

powershell使用powershell和核心服务淘汰sdltridion2013sp1发布目标。见http://tridion.stackexchange.com/questions/1(代码片

查看详情

ct片居然可以这么玩：用头部ct断层扫描片复原三维头像(代码片段)

CT是现代医学影像的主力设备，寻常百姓并不陌生。通常，一张CT片由多张连续断层扫描的图像组成。在医生眼中，CT片展示了人体器官的形态和性质，是判断病人健康状况的重要依据。对于普通人而言&#... 查看详情

背水一战windows10(76)-控件（控件基类）:control-基础知识,焦点相关,运行时获取controltemplate和datatemplate中的元素(代码片(代码片段)

原文:背水一战Windows10(76)-控件（控件基类）:Control-基础知识,焦点相关,运行时获取ControlTemplate和DataTemplate中的元素[源码下载]背水一战Windows10(76)-控件（控件基类）:Control-基础知识,焦点相关,运行时获取ControlTemplate和DataTemplate中的... 查看详情

markdown放代码片(代码片段)

查看详情

2021-11-10：o时间插入删除和获取随机元素。实现randomizedset类：randomizedset()初始化randomizedset对象。boolinsert(in(代码片(代码片段)

2021-11-10：O(1)时间插入、删除和获取随机元素。实现RandomizedSet类：RandomizedSet()初始化RandomizedSet对象。boolinsert(intval)当元素val不存在时，向集合中插入该项，并返回true；否则，返回false。boolremove(intval)当元查看详情

requestsdependencywarning:urllib3(1.26.4)orchardet(4.0.0)doesn‘tmatchasupportedversion(代码片(代码片段)

python报错urllib3和chardet的版本不对点进报错信息，可以看到urllib3和charder需要的是什么版本按照这个版本在命令行中下载该版本。pipinstallurllib3==1.25.11pipinstallurllib3==3.0.1就可以了，亲测有效。查看详情