Setting the default column width

Default column width can be set using the following code:

$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(12);

Setting the default row height

Default row height can be set using the following code:

$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);

Add a GD drawing to a worksheet

There might be a situation where you want to generate an in-memory image using GD and add it to a PHPExcel worksheet without first having to save this file to a temporary location.

Here’s an example which generates an image in memory and adds it to the active worksheet:

// Generate an image

$gdImage = @imagecreatetruecolor(120, 20) or die('Cannot Initialize new GD image stream');

$textColor = imagecolorallocate($gdImage, 255, 255, 255);

imagestring($gdImage, 1, 5, 5, 'Created with PHPExcel', $textColor);

// Add a drawing to the worksheet

$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();

$objDrawing->setName('Sample image');

$objDrawing->setDescription('Sample image');

$objDrawing->setImageResource($gdImage);

$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);

$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);

$objDrawing->setHeight(36);

$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

Setting worksheet zoom level

To set a worksheet’s zoom level, the following code can be used:

$objPHPExcel->getActiveSheet()->getSheetView()->setZoomScale(75);

Note that zoom level should be in range 10 – 400.

Sheet tab color

Sometimes you want to set a color for sheet tab. For example you can have a red sheet tab:

$objWorksheet->getTabColor()->setRGB('FF0000');

Creating worksheets in a workbook

If you need to create more worksheets in the workbook, here is how:

$objWorksheet1 = $objPHPExcel->createSheet();

$objWorksheet1->setTitle('Another sheet');

Think of createSheet() as the "Insert sheet" button in Excel. When you hit that button a new sheet is appended to the existing collection of worksheets in the workbook.

Hidden worksheets (Sheet states)

Set a worksheet to be hidden using this code:

$objPHPExcel->getActiveSheet()

->setSheetState(PHPExcel_Worksheet::SHEETSTATE_HIDDEN);

Sometimes you may even want the worksheet to be “very hidden”. The available sheet states are :

PHPExcel_Worksheet::SHEETSTATE_VISIBLE

PHPExcel_Worksheet::SHEETSTATE_HIDDEN

PHPExcel_Worksheet::SHEETSTATE_VERYHIDDEN

In Excel the sheet state “very hidden” can only be set programmatically, e.g. with Visual Basic Macro. It is not possible to make such a sheet visible via the user interface.

Right-to-left worksheet

Worksheets can be set individually whether column ‘A’ should start at left or right side. Default is left. Here is how to set columns from right-to-left.

// right-to-left worksheet

$objPHPExcel->getActiveSheet()

->setRightToLeft(true);

Performing formula calculations

Date: 2016-03-03; view: 1005