TExcelFile.CheckDataValidationsInWorkbook Method
Checks if all the cells in the file inside data validations have values that are valid according to the data validation specifications.
Syntax
Unit: FlexCel.Core
function TExcelFile.CheckDataValidationsInWorkbook(const maxErrors: Integer): TArray<TCellAddress>; virtual; abstract;Parameters
| <-> | Parameter | Type | Description |
|---|---|---|---|
| const | maxErrors | Integer | The maximum number of errors reported by this method. To avoid getting a too big list, set it to a number like 100. If set to 0 or a negative number, the full list of errors will be returned, which can be very big. |
Returns
An array with the first maxErrors cells that do not conform to the data validation. And empty array if all the cells conform.
Examples
The following code will mark in red all the cells in a file which don't comply with the data validations and save the file with a different name:
procedure CheckDataValidationsInWorkbook(const sourceFileName: String; const targetFileName: String);
var
xls: TXlsFile;
cellsWithErrors: TCellAddressArray;
fmt: TFlxFormat;
xf: Int32;
i: Int32;
begin
xls := TXlsFile.Create(sourceFileName, true);
try
cellsWithErrors := xls.CheckDataValidationsInWorkbook(0);
fmt := xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Bad, 0), true);
xf := xls.AddFormat(fmt);
for i := 0 to Length(cellsWithErrors) - 1 do
begin
xls.ActiveSheetByName := cellsWithErrors[i].Sheet;
xls.SetCellFormat(cellsWithErrors[i].Row, cellsWithErrors[i].Col, xf);
end;
xls.Save(targetFileName);
finally
xls.Free;
end;
end;