Setting data validation on a cell

Data validation is a powerful feature of Excel2007. It allows to specify an input filter on the data that can be inserted in a specific cell. This filter can be a range (i.e. value must be between 0 and 10), a list (i.e. value must be picked from a list), …

The following piece of code only allows numbers between 10 and 20 to be entered in cell B3:

$objValidation = $objPHPExcel->getActiveSheet()->getCell('B3')

->getDataValidation();

$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE );

$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );

$objValidation->setAllowBlank(true);

$objValidation->setShowInputMessage(true);

$objValidation->setShowErrorMessage(true);

$objValidation->setErrorTitle('Input error');

$objValidation->setError('Number is not allowed!');

$objValidation->setPromptTitle('Allowed input');

$objValidation->setPrompt('Only numbers between 10 and 20 are allowed.');

$objValidation->setFormula1(10);

$objValidation->setFormula2(20);

The following piece of code only allows an item picked from a list of data to be entered in cell B3:

$objValidation = $objPHPExcel->getActiveSheet()->getCell('B5')

->getDataValidation();

$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );

$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );

$objValidation->setAllowBlank(false);

$objValidation->setShowInputMessage(true);

$objValidation->setShowErrorMessage(true);

$objValidation->setShowDropDown(true);

$objValidation->setErrorTitle('Input error');

$objValidation->setError('Value is not in list.');

$objValidation->setPromptTitle('Pick from list');

$objValidation->setPrompt('Please pick a value from the drop-down list.');

$objValidation->setFormula1('"Item A,Item B,Item C"');

i When using a data validation list like above, make sure you put the list between " and " and that you split the items with a comma (,).

i It is important to remember that any string participating in an Excel formula is allowed to be maximum 255 characters (not bytes). This sets a limit on how many items you can have in the string "Item A,Item B,Item C". Therefore it is normally a better idea to type the item values directly in some cell range, say A1:A3, and instead use, say, $objValidation->setFormula1('Sheet!$A$1:$A$3');. Another benefit is that the item values themselves can contain the comma ‘,’ character itself.

If you need data validation on multiple cells, one can clone the ruleset:

$objPHPExcel->getActiveSheet()->getCell('B8')->setDataValidation(clone $objValidation);

4.6.28. Setting a column’s width

A column’s width can be set using the following code:

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);

If you want PHPExcel to perform an automatic width calculation, use the following code. PHPExcel will approximate the column with to the width of the widest column value.

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

The measure for column width in PHPExcel does not correspond exactly to the measure you may be used to in Microsoft Office Excel. Column widths are difficult to deal with in Excel, and there are several measures for the column width.

1) Inner width in character units (e.g. 8.43 this is probably what you are familiar with in Excel)

2) Full width in pixels (e.g. 64 pixels)

3) Full width in character units (e.g. 9.140625, value -1 indicates unset width)

PHPExcel always operates with 3) "Full width in character units" which is in fact the only value that is stored in any Excel file, hence the most reliable measure. Unfortunately, Microsoft Office Excel does not present you with this measure. Instead measures 1) and 2) are computed by the application when the file is opened and these values are presented in various dialogues and tool tips.

The character width unit is the width of a '0' (zero) glyph in the workbooks default font. Therefore column widths measured in character units in two different workbooks can only be compared if they have the same default workbook font.

If you have some Excel file and need to know the column widths in measure 3), you can read the Excel file with PHPExcel and echo the retrieved values.

4.6.29. Show/hide a column

To set a worksheet’s column visibility, you can use the following code. The first line explicitly shows the column C, the second line hides column D.

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);

4.6.30. Group/outline a column

To group/outline a column, you can use the following code:

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);

You can also collapse the column. Note that you should also set the column invisible, otherwise the collapse will not be visible in Excel 2007.

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setCollapsed(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);

Please refer to the part “group/outline a row” for a complete example on collapsing.

You can instruct PHPExcel to add a summary to the right (default), or to the left. The following code adds the summary to the left:

$objPHPExcel->getActiveSheet()->setShowSummaryRight(false);

4.6.31. Setting a row’s height

A row’s height can be set using the following code:

$objPHPExcel->getActiveSheet()->getRowDimension('10')->setRowHeight(100);

4.6.32. Show/hide a row

To set a worksheet’s row visibility, you can use the following code. The following example hides row number 10.

$objPHPExcel->getActiveSheet()->getRowDimension('10')->setVisible(false);

4.6.33. Group/outline a row

To group/outline a row, you can use the following code:

$objPHPExcel->getActiveSheet()->getRowDimension('5')->setOutlineLevel(1);

You can also collapse the row. Note that you should also set the row invisible, otherwise the collapse will not be visible in Excel 2007.

$objPHPExcel->getActiveSheet()->getRowDimension('5')->setCollapsed(true);

$objPHPExcel->getActiveSheet()->getRowDimension('5')->setVisible(false);

Here’s an example which collapses rows 50 to 80:

for ($i = 51; $i <= 80; $i++) {

$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, "FName $i");

$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, "LName $i");

$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, "PhoneNo $i");

$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, "FaxNo $i");

$objPHPExcel->getActiveSheet()->setCellValue('E' . $i, true);

$objPHPExcel->getActiveSheet()->getRowDimension($i)->setOutlineLevel(1);

$objPHPExcel->getActiveSheet()->getRowDimension($i)->setVisible(false);

}

$objPHPExcel->getActiveSheet()->getRowDimension(81)->setCollapsed(true);

You can instruct PHPExcel to add a summary below the collapsible rows (default), or above. The following code adds the summary above:

$objPHPExcel->getActiveSheet()->setShowSummaryBelow(false);

4.6.34. Merge/unmerge cells

If you have a big piece of data you want to display in a worksheet, you can merge two or more cells together, to become one cell. This can be done using the following code:

$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');

Removing a merge can be done using the unmergeCells method:

$objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');

4.6.35. Inserting rows/columns

You can insert/remove rows/columns at a specific position. The following code inserts 2 new rows, right before row 7:

$objPHPExcel->getActiveSheet()->insertNewRowBefore(7, 2);

Date: 2016-03-03; view: 1471