Add support for data validation on ranges rather than single cells

[Daniel15]

I would like to add validation on whole columns. My current hacky workaround is to set the validation on single cells, output the XLSX file using PHPExcel, then do my own manipulation to sheet1.xml and change the sqref for every dataValidation node (eg. change "A2" to "A2:A1048576"). Support for ranges with data validation would be nice to have built-in to PHPExcel itself.

[Daniel15]

My hacky workaround:

// ... output using PHP normally, save to $filename
// ...
// Read the file output by PHPExcel
$zip = new ZipArchive();
$zip->open($filename);

// Retrieve sheets from file
$sheet_file = $zip->getFromName('xl/worksheets/sheet1.xml');
$sheet = simplexml_load_string($sheet_file);

// Extend validation ranges for the whole column. Unfortunately PHPExcel
// only supports per-cell validation. Just extend its validation ranges
// all the way down. Assumes cell is in format "A1" - First character is
// row. Converts the "A2" range to "A2:A1048576"
if ($sheet->dataValidations && $sheet->dataValidations->dataValidation) {
  foreach ($sheet->dataValidations->dataValidation as $validation) {
    // First character is column letter
    $row = substr($validation['sqref'], 0, 1);
    $validation['sqref'] .= ':' . $row . static::EXCEL_MAX_ROWS;
  }
}

// Replace files with modified versions
$zip->addFromString('xl/worksheets/sheet1.xml', $sheet->asXML());
$zip->close();

[wernerkrauss]

You can already add validation to an area of the sheet:

$sheet->setDataValidation($rangeString, $validationObj);

[PowerKiKi]

thanks for the contribution @wernerkrauss