{"action":"closed","issue":{"url":"https://api.github.com/repos/PHPOffice/PhpSpreadsheet/issues/131","repository_url":"https://api.github.com/repos/PHPOffice/PhpSpreadsheet","labels_url":"https://api.github.com/repos/PHPOffice/PhpSpreadsheet/issues/131/labels{/name}","comments_url":"https://api.github.com/repos/PHPOffice/PhpSpreadsheet/issues/131/comments","events_url":"https://api.github.com/repos/PHPOffice/PhpSpreadsheet/issues/131/events","html_url":"https://github.com/PHPOffice/PhpSpreadsheet/issues/131","id":219134590,"number":131,"title":"Data Validations are generated one-per-cell instead of per-range","user":{"login":"billblume","id":2068276,"avatar_url":"https://avatars0.githubusercontent.com/u/2068276?v=4","gravatar_id":"","url":"https://api.github.com/users/billblume","html_url":"https://github.com/billblume","followers_url":"https://api.github.com/users/billblume/followers","following_url":"https://api.github.com/users/billblume/following{/other_user}","gists_url":"https://api.github.com/users/billblume/gists{/gist_id}","starred_url":"https://api.github.com/users/billblume/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/billblume/subscriptions","organizations_url":"https://api.github.com/users/billblume/orgs","repos_url":"https://api.github.com/users/billblume/repos","events_url":"https://api.github.com/users/billblume/events{/privacy}","received_events_url":"https://api.github.com/users/billblume/received_events","type":"User","site_admin":false},"labels":[{"id":395149151,"url":"https://api.github.com/repos/PHPOffice/PhpSpreadsheet/labels/help%20wanted","name":"help wanted","color":"128A0C","default":true},{"id":424227494,"url":"https://api.github.com/repos/PHPOffice/PhpSpreadsheet/labels/writer/xlsx","name":"writer/xlsx","color":"e99695","default":false}],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2017-04-04T04:03:25Z","updated_at":"2017-09-07T17:30:57Z","closed_at":"2017-09-07T17:30:57Z","author_association":"CONTRIBUTOR","body":"Data Validation elements written by the Xlsx writer are generated one-per-cell instead of by column ranges.  This causes two issues:\r\n1. The generated Xlsx files may be much larger than necessary\r\n2. Excel has a limit on the number of data validations per worksheet.  I don't know what this limit is exactly, but I suspect that it is 64k.  If you exceed this limit, excel will drop all data validations for the worksheet.\r\n\r\nFor us, we had a worksheet with 15,000 rows and 8 data validations spanning 8 different columns.  This resulted in 120,000 data validation elements in the Xslx file, causing both issues described above.\r\n\r\nData validations should be generated per range of cells, not per cell.\r\n\r\nHere is the fix we implemented for PHPExcel 1.8.1.  Given that the data validation generation code is unchanged for PhpSpreadsheet, you should be able to use this fix with few or no changes,\r\n\r\n```php\r\n        /* This function is unchanged except for the addition of the call to the new function _mergeDataValidationRanges() */\r\n\tprivate function _writeDataValidations(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)\r\n\t{\r\n\t\t// Datavalidation collection\r\n\t\t$dataValidationCollection = $pSheet->getDataValidationCollection();\r\n\r\n\t\t// Write data validations?\r\n\t\tif (!empty($dataValidationCollection)) {\r\n\t\t\t$dataValidationCollection = $this->_mergeDataValidationRanges($dataValidationCollection);\r\n\r\n\t\t\t$objWriter->startElement('dataValidations');\r\n\t\t\t$objWriter->writeAttribute('count', count($dataValidationCollection));\r\n\r\n\t\t\tforeach ($dataValidationCollection as $coordinate => $dv) {\r\n\t\t\t\t$objWriter->startElement('dataValidation');\r\n                                ...\r\n\t\t\t\t$objWriter->writeAttribute('sqref', $coordinate);\r\n                                ...\r\n\t\t\t\t$objWriter->endElement();\r\n\t\t\t}\r\n\r\n\t\t\t$objWriter->endElement();\r\n\t\t}\r\n\t}\r\n\r\n        /* New function that converts a map of cell-coordinates to data-validations to a map cell-ranges to data-validations. */\r\n\tprivate function _mergeDataValidationRanges(array $dataValidationCollection)\r\n\t{\r\n\t\t$hashedDvs = array();\r\n\r\n\t\tforeach ($dataValidationCollection as $coord => $dv) {\r\n\t\t\tlist($column, $row) = PHPExcel_Cell::coordinateFromString($coord);\r\n\t\t\t$row = intval(ltrim($row,'$'));\r\n\t\t\t$hashCode = $column . '-' . $dv->getHashCode();\r\n\r\n\t\t\tif (! isset($hashedDvs[$hashCode])) {\r\n\t\t\t\t$hashedDvs[$hashCode] = (object) array(\r\n\t\t\t\t\t'dv' => $dv,\r\n\t\t\t\t\t'col' => $column,\r\n\t\t\t\t\t'rows' => array($row)\r\n\t\t\t\t);\r\n\t\t\t} else {\r\n\t\t\t\t$hashedDvs[$hashCode]->rows[] = $row;\r\n\t\t\t}\r\n\t\t}\r\n\r\n\t\t$mergedDataValidationCollection = array();\r\n\t\tksort($hashedDvs);\r\n\r\n\t\tforeach ($hashedDvs as $hashedDv) {\r\n\t\t\tsort($hashedDv->rows);\r\n\t\t\t$rowStart = null;\r\n\t\t\t$rowEnd = null;\r\n\t\t\t$ranges = array();\r\n\r\n\t\t\tforeach ($hashedDv->rows as $row) {\r\n\t\t\t\tif ($rowStart === null) {\r\n\t\t\t\t\t$rowStart = $row;\r\n\t\t\t\t\t$rowEnd = $row;\r\n\t\t\t\t} else if ($rowEnd === $row - 1) {\r\n\t\t\t\t\t$rowEnd = $row;\r\n\t\t\t\t} else {\r\n\t\t\t\t\tif ($rowStart == $rowEnd) {\r\n\t\t\t\t\t\t$ranges[] = $hashedDv->col . $rowStart;\r\n\t\t\t\t\t} else {\r\n\t\t\t\t\t\t$ranges[] = $hashedDv->col . $rowStart . ':' . $hashedDv->col . $rowEnd;\r\n\t\t\t\t\t}\r\n\r\n\t\t\t\t\t$rowStart = $row;\r\n\t\t\t\t\t$rowEnd = $row;\r\n\t\t\t\t}\r\n\t\t\t}\r\n\r\n\t\t\tif ($rowStart !== null) {\r\n\t\t\t\tif ($rowStart == $rowEnd) {\r\n\t\t\t\t\t$ranges[] = $hashedDv->col . $rowStart;\r\n\t\t\t\t} else {\r\n\t\t\t\t\t$ranges[] = $hashedDv->col . $rowStart . ':' . $hashedDv->col . $rowEnd;\r\n\t\t\t\t}\r\n\t\t\t}\r\n\r\n\t\t\tforeach ($ranges as $range) {\r\n\t\t\t\t$mergedDataValidationCollection[$range] = $hashedDv->dv;\r\n\t\t\t}\r\n\t\t}\r\n\r\n\t\treturn $mergedDataValidationCollection;\r\n\t}\r\n```"}}
{"actor":{"display_login":"PowerKiKi"}}