Over the past year, I have been tasked with creating several Downloadable Excel Spreadsheets so that Clients can more easily import data into our system.
The data model we are importing into however, uses the unique validation rule on one of the columns. I've added code on the back-end to catch this error, however I thought it would be nice to try and prevent it in the first place.
To do so, I analyzed some of the recipes PhpSpreadSheet uses to create Dropdowns for a user, and noticed they are using the Data Validation feature of Excel.
From this, I was able to create a function which notifies the user that a duplicate has been entered.
I have the following in a trait called sheetHelper
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
public function preventDuplicates(
string $column,
Worksheet $sheet,
int $startRow,
string $errorMessage,
?int $endRow = null
): static {
$startCell = $column . $startRow;
$endRow = $endRow ?? $sheet->getHighestRow();
$endCell = $column . $endRow; //specified large number to include all cells up to
$validation = $sheet->getCell($startCell)->getDataValidation();
$validation->setType( DataValidation::TYPE_CUSTOM );
$validation->setErrorStyle( DataValidation::OPERATOR_EQUAL );
$validation->setAllowBlank(false);
$validation->setShowInputMessage(true);
$validation->setShowErrorMessage(true);
$validation->setShowDropDown(false);
$validation->setErrorTitle($errorMessage);
$validation->setFormula1('=COUNTIF($A$3:$A$10000,$A3)=1'); //$A$3 is absolute value, $A3 is absolute column, relative row 3
$validation->setSqref("{$startCell}:{$endCell}");
return $this;
}
This function can be used in your Exported Sheet. Just implement the WithStyles interface, and place it in the styles method:
public function styles(Worksheet $sheet) : void
{
parent::styles($sheet);
$this->preventDuplicates(
column: 'A',
sheet: $sheet,
startRow: 2,//we use row 1 for the header
errorMessage: 'Name already exists in this column',
endRow: 10000
);
}
Now, if a user enters a duplicated name in column 'A', a popup in Excel / Libre Office will appear notifying them that a duplicate name has been entered. It will then remove the text they entered.
The key values I used to get this to work was setting the SerrorStyle to DataValidation::OPERATOR_EQUAL, and setting the type to DataValidation::TYPE_CUSTOM
Happy Coding!
Top comments (0)