@ifoche Thank you again I am using the app frequently. Today I got the below error. it seems it is related to option sets. can help what excatly is the issues and what should I do.
${programStage.executionDateLabel ?? “Date”} *`
);
if (programStage.programStageSections.length === 0) {
programStage.programStageSections.push({
dataElements: programStage.programStageDataElements.map(e => e.dataElement),
id: programStageT.id,
});
}
_.forEach(programStage.programStageSections, programStageSectionT => {
const programStageSection = programStageSectionT.dataElements
? programStageSectionT
: metadata.get(programStageSectionT.id);
const firstColumnId = columnId;
_.forEach(programStageSection.dataElements, dataElementT => {
const dataElement = metadata.get(dataElementT.id);
const { name, description } = this.translate(dataElement);
const validation = dataElement.optionSet
? dataElement.optionSet.id
: dataElement.valueType;
this.createColumn(
dataEntrySheet,
itemRow,
columnId,
`_${dataElement.id}`,
groupId,
this.validations.get(validation)
);
dataEntrySheet.column(columnId).setWidth(name.length / 2.5 + 10);
if (description !== undefined) {
dataEntrySheet.cell(itemRow, columnId).comment(description, {
height: "100pt",
width: "160pt",
});
}
columnId++;
});
if (firstColumnId < columnId)
dataEntrySheet
.cell(sectionRow, firstColumnId, sectionRow, columnId - 1, true)
.formula(`_${programStageSection.id}`)
.style(this.groupStyle(groupId));
groupId++;
});
});
}
};
SheetBuilder.prototype.toBlob = async function () {
try {
const data = await this.workbook.writeToBuffer();
return new Blob([data], {
type: “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”,
});
} catch (error) {
console.error(“Failed building/downloading template”);
throw error;
}
};
SheetBuilder.prototype.translate = function (item) {
const { elementMetadata, language } = this.builder;
const translations = item?.translations?.filter(({ locale }) => locale === language) ?? [];
const { value: formName } = translations.find(({ property }) => property === "FORM_NAME") ?? {};
const { value: regularName } = translations.find(({ property }) => property === "NAME") ?? {};
const { value: shortName } =
translations.find(({ property }) => property === "SHORT_NAME") ?? {};
const defaultName = item?.displayName ?? item?.formName ?? item?.name;
const name = formName ?? regularName ?? shortName ?? defaultName;
const { value: description = item?.description } =
translations.find(({ property }) => property === "DESCRIPTION") ?? {};
if (item?.type === "categoryOptionCombos" && name === defaultName) {
const options = item?.categoryOptions?.map(({ id }) => {
const element = elementMetadata.get(id);
const { name } = this.translate(element);
return name;
});
return { name: options.join(", "), description };
} else {
return { name, description };
}
};
SheetBuilder.prototype.createColumn = function (
sheet,
rowId,
columnId,
label,
groupId = null,
validation = null,
validationMessage = “Invalid choice was chosen”,
defaultLabel = false
) {
sheet.column(columnId).setWidth(20);
const cell = sheet.cell(rowId, columnId);
if (!defaultLabel) cell.style(groupId !== null ? this.groupStyle(groupId) : baseStyle);
else {
cell.style(groupId !== null ? this.groupStyle(groupId) : baseStyle).style(
this.transparentFontStyle(groupId)
);
}
if (label.startsWith("_")) cell.formula(label);
else cell.string(label);
if (validation !== null) {
const ref = `${Excel.getExcelAlpha(columnId)}${rowId + 1}:${Excel.getExcelAlpha(
columnId
)}1048576`;
sheet.addDataValidation({
type: "list",
allowBlank: true,
error: validationMessage,
errorStyle: "warning",
showDropDown: true,
sqref: ref,
formulas: [validation.toString()],
});
sheet.addConditionalFormattingRule(ref, {
type: "expression", // the conditional formatting type
priority: 1, // rule priority order (required)
formula: `ISERROR(MATCH(${Excel.getExcelAlpha(columnId)}${
rowId + 1
},${validation.toString().substr(1)},0))`, // formula that returns nonzero or 0
style: this.workbook.createStyle({
font: {
bold: true,
color: "FF0000",
},
}), // a style object containing styles to apply
});
}
};
SheetBuilder.prototype.transparentFontStyle = function (groupId) {
const { palette = defaultColorScale } = this.builder.theme ?? {};
return {
font: {
color: palette[groupId % palette.length],
},
};
};
SheetBuilder.prototype.groupStyle = function (groupId) {
const { palette = defaultColorScale } = this.builder.theme ?? {};
return {
…baseStyle,
fill: {
type: “pattern”,
patternType: “solid”,
fgColor: palette[groupId % palette.length],
},
};
};
/**
- Common cell style definition
-
@type {{alignment: {horizontal: string, vertical: string, wrapText: boolean, shrinkToFit: boolean}}}
*/
const baseStyle = {
alignment: {
horizontal: “center”,
vertical: “center”,
wrapText: true,
shrinkToFit: true,
},
fill: {
type: “pattern”,
patternType: “solid”,
fgColor: “ffffff”,
},
};
const protectedSheet = {
sheetProtection: {
sheet: true,
formatCells: false,
formatColumns: false,
formatRows: false,
password: “Wiscentd2019!”,
},
};
