Home Random Page


CATEGORIES:

BiologyChemistryConstructionCultureEcologyEconomyElectronicsFinanceGeographyHistoryInformaticsLawMathematicsMechanicsMedicineOtherPedagogyPhilosophyPhysicsPolicyPsychologySociologySportTourism






Setting printing breaks on a row or column

To set a print break, use the following code, which sets a row break on row 10.

$objPHPExcel->getActiveSheet()->setBreak( 'A10' , PHPExcel_Worksheet::BREAK_ROW );

 

The following line of code sets a print break on column D:

$objPHPExcel->getActiveSheet()->setBreak( 'D10' , PHPExcel_Worksheet::BREAK_COLUMN );

4.6.15. Show/hide gridlines when printing

To show/hide gridlines when printing, use the following code:

$objPHPExcel->getActiveSheet()->setShowGridlines(true);

4.6.16. Setting rows/columns to repeat at top/left

PHPExcel can repeat specific rows/cells at top/left of a page. The following code is an example of how to repeat row 1 to 5 on each printed page of a specific worksheet:

$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 5);

Specify printing area

To specify a worksheet’s printing area, use the following code:

$objPHPExcel->getActiveSheet()->getPageSetup()->setPrintArea('A1:E5');

 

There can also be multiple printing areas in a single worksheet:

$objPHPExcel->getActiveSheet()->getPageSetup()->setPrintArea('A1:E5,G4:M20');

Formatting cells

A cell can be formatted with font, border, fill, … style information. For example, one can set the foreground colour of a cell to red, aligned to the right, and the border to black and thick border style. Let’s do that on cell B2:

 

$objPHPExcel->getActiveSheet()->getStyle('B2')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);

 

$objPHPExcel->getActiveSheet()->getStyle('B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

 

$objPHPExcel->getActiveSheet()->getStyle('B2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

$objPHPExcel->getActiveSheet()->getStyle('B2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

$objPHPExcel->getActiveSheet()->getStyle('B2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

$objPHPExcel->getActiveSheet()->getStyle('B2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

 

$objPHPExcel->getActiveSheet()->getStyle('B2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

$objPHPExcel->getActiveSheet()->getStyle('B2')->getFill()->getStartColor()->setARGB('FFFF0000');

 

Starting with PHPExcel 1.7.0 getStyle() also accepts a cell range as a parameter. For example, you can set a red background color on a range of cells:

 

$objPHPExcel->getActiveSheet()->getStyle('B3:B7')->getFill()

->setFillType(PHPExcel_Style_Fill::FILL_SOLID)

->getStartColor()->setARGB('FFFF0000');

 

 

i Tip
It is recommended to style many cells at once, using e.g. getStyle('A1:M500'), rather than styling the cells individually in a loop. This is much faster compared to looping through cells and styling them individually.



 

There is also an alternative manner to set styles. The following code sets a cell’s style to font bold, alignment right, top border thin and a gradient fill:

 

$styleArray = 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($styleArray);

 

Or with a range of cells:

 

$objPHPExcel->getActiveSheet()->getStyle('B3:B7')->applyFromArray($styleArray);

 

This alternative method using arrays should be faster in terms of execution whenever you are setting more than one style property. But the difference may barely be measurable unless you have many different styles in your workbook.

 

 

i Prior to PHPExcel 1.7.0 duplicateStyleArray() was the recommended method for styling a cell range, but this method has now been deprecated since getStyle() has started to accept a cell range.

 

Number formats

You often want to format numbers in Excel. For example you may want a thousands separator plus a fixed number of decimals after the decimal separator. Or perhaps you want some numbers to be zero-padded.

 

In Microsoft Office Excel you may be familiar with selecting a number format from the "Format Cells" dialog. Here there are some predefined number formats available including some for dates. The dialog is designed in a way so you don't have to interact with the underlying raw number format code unless you need a custom number format.

 

In PHPExcel, you can also apply various predefined number formats. Example:

$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()

->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

 

This will format a number e.g. 1587.2 so it shows up as 1,587.20 when you open the workbook in MS Office Excel. (Depending on settings for decimal and thousands separators in Microsoft Office Excel it may show up as 1.587,20)

 

You can achieve exactly the same as the above by using this:

$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()

->setFormatCode('#,##0.00');

 

In Microsoft Office Excel, as well as in PHPExcel, you will have to interact with raw number format codes whenever you need some special custom number format. Example:

$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()

->setFormatCode('[Blue][>=3000]$#,##0;[Red][<0]$#,##0;$#,##0');

 

Another example is when you want numbers zero-padded with leading zeros to a fixed length:

$objPHPExcel->getActiveSheet()->getCell('A1')->setValue(19);

$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()

->setFormatCode('0000'); // will show as 0019 in Excel

 

 

Tip
The rules for composing a number format code in Excel can be rather complicated. Sometimes you know how to create some number format in Microsoft Office Excel, but don't know what the underlying number format code looks like. How do you find it?

The readers shipped with PHPExcel come to the rescue. Load your template workbook using e.g. Excel2007 reader to reveal the number format code. Example how read a number format code for cell A1:

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load('template.xlsx');
var_dump($objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()

->getFormatCode());


Advanced users may find it faster to inspect the number format code directly by renaming template.xlsx to template.zip, unzipping, and looking for the relevant piece of XML code holding the number format code in xl/styles.xml.


Date: 2016-03-03; view: 781


<== previous page | next page ==>
Setting the print header and footer of a worksheet | Setting data validation on a cell
doclecture.net - lectures - 2014-2019 year. Copyright infringement or personal data (0.002 sec.)