I started coding a validation of my Excel Sheet. I implemented quite a bit so I try to keep the code short.
My source code:
function main(workbook: ExcelScript.Workbook) {
console.log("starting...");
let cs = createVariantCategorySheet(workbook.getWorksheet("variant categories"));
cs.validateSheet();
for (let m of cs.logMessages) {
console.log(m);
}
console.log("finished...");
}
function createVariantCategorySheet(worksheet: ExcelScript.Worksheet): ImportSheet {
let sheet = new ImportSheetBuilder()
.name("variant categories")
.index(1)
.worksheet(worksheet)
.addColumnInfo(
new ColumnInfoBuilder()
.index(4)
.name("Sort Order")
// .addChecker(new NotEmptyChecker())
.addChecker(new UniqueSortOrderChecker(worksheet, worksheet.getRange("B:B")))
.build()
)
.build();
return sheet;
}
class ImportSheet {
public name: string;
public index: number;
public worksheet?: ExcelScript.Worksheet
columns: ColumnInfo[];
public logMessages: string[];
constructor() {
this.index = -1;
this.name = "";
this.columns = [];
this.logMessages = [];
}
// ... Some usefull private methods ...
validateSheet() {
let rowCount = this.getRowCount();
for (let rowIndex = 1; rowIndex < rowCount; rowIndex++) {
if (this.isRowEmpty(rowIndex)) {
this.logMessages.push("no more lines... ")
return;
}
this.validatingRow(rowIndex);
}
}
private validatingRow(rowIndex: number) {
this.logMessages.push("iterating over line [" + rowIndex + "]")
for (let columnItem of this.columns) {
this.validatingColumn(columnItem, this.rangeToValidate(rowIndex, columnItem.index));
}
}
private validatingColumn(columnItem: ColumnInfo, rangeToValidate: ExcelScript.Range){
for (let validator of columnItem.validator) {
let stringToValidate = rangeToValidate.getValue().toString();
this.logMessages.push("###### " + validator.isValid)
if (!validator.isValid(stringToValidate, rangeToValidate.getRowIndex())) {
this.logMessages.push("## ## ERROR: Failed Validation " + validator.getName() +
" in '" + rangeToValidate.getAddress() + "' for column '" +
columnItem.name + "'.");
} else {
this.logMessages.push("## ## INFO: Success Validation " + validator.getName() +
" in '" + rangeToValidate.getAddress() + "' for column '" +
columnItem.name + "'.");
}
}
}
}
class ColumnInfo {
name: string;
index: number;
validator: Checker[];
}
interface Checker {
isValid(value: string, rowIndex: number): boolean
getName(): string
}
class NotEmptyChecker implements Checker {
isValid(value: string): boolean {
if (value)
return true;
else
return false;
}
getName(): string {
return NotEmptyChecker.name;
}
}
class UniqueSortOrderChecker implements Checker {
sortOrderMap: Map<string, Set<string>>;
range: ExcelScript.Range
worksheet: ExcelScript.Worksheet
constructor(worksheet: ExcelScript.Worksheet, range: ExcelScript.Range) {
this.range = range;
this.worksheet = worksheet;
this.sortOrderMap = new Map<string, Set<string>>();
}
isValid(value: string, rowIndex: number): boolean {
return true;
}
getName(): string {
return UniqueSortOrderChecker.name;
}
}
I left out the Builders as they just create the objects.
The above code works and validates the file like I wish.
The generated code for the Method UniqueSortOrderChecker.isValid looks like:
function (value, rowIndex) {
ExcelScript.engine.traceLine(undefined);
return true;
}
So the interesting part is, once I change the UniqueSortOrderChecker into:
isValid(value: string, rowIndex: number): boolean {
console.log("test");
return true;
}
The generated code looks now totally different and returns a promise:
function (value, rowIndex) {
return __awaiter(this, void 0, void 0, function () {
return __generator(this, function (_a) {
switch (_a.label) {
case 0:
ExcelScript.engine.traceLine(264);
return [4 /*yield*/, console.log("test")];
case 1:
(_a.sent());
ExcelScript.engine.traceLine(undefined);
return [2 /*return*/, true];
}
});
});
}
Same happens when I try to read cell value like:
isValid(value: string, rowIndex: number): boolean {
let cellValue = this.range.getRow(rowIndex).getValue().toString();
return true;
}
turns it into a promise return type like:
function (value, rowIndex) {
return __awaiter(this, void 0, void 0, function () {
var cellValue;
return __generator(this, function (_a) {
switch (_a.label) {
case 0:
ExcelScript.engine.traceLine(264);
return [4 /*yield*/, this.range.getRow(rowIndex).getValue()];
case 1:
cellValue = (_a.sent()).toString();
ExcelScript.engine.traceLine(undefined);
return [2 /*return*/, true];
}
});
});
}
Can anyone help me out here? As once it is turned into a promise the return value is always true and my validation does not work.
Hope the question is not to long, but I wanted to give context.