This demo is inspired by a real-world IFRS reporting project delivered in partnership with one of the Big Four accounting firms. It illustrates a data validation and cleansing process commonly used before financial and regulatory reporting, including IFRS investment-contract reporting.
Users edit source data in the Input CSV tab and manage validation and transformation logic in the ETL Rule tab. The ETL rules are stored in Excel and serve as the live rule source for the cleansing process.
Users can run validation, review errors, jump directly to the affected cell, make corrections, and reprocess the data until it passes validation. The final output is transformed into a standardized format ready for downstream reporting systems.
Using Keikai enables business users to correct source data directly in the browser without exporting files or involving developers. Validation and transformation rules can also be maintained in Excel and reloaded into the system whenever reporting requirements change, allowing organizations to adapt quickly without modifying application code.
<?xml version="1.0" encoding="UTF-8"?>
<zk xmlns="http://www.zkoss.org/2005/zul"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.zkoss.org/2005/zul http://keikai.io/2019/zul/zul.xsd">
<style src="/demo/financial_data_cleansing/style.css"/>
<vlayout width="100%" vflex="1" apply="demo.financial_data_cleansing.FinancialDataCleansingComposer" spacing="0" sclass="ifrs-demo">
<hlayout width="100%" vflex="1" spacing="10px" sclass="ifrs-page">
<vlayout width="360px" vflex="1" spacing="10px">
<vlayout sclass="ifrs-panel" spacing="8px">
<label id="stage1ProcessIntro" value="Financial Data Cleansing" sclass="ifrs-title" />
<button id="startDataCleansingCoachmark" label="Start Guided Tour" iconSclass="z-icon-map-signs" sclass="ifrs-action" />
<button id="runStage1Etl" label="Run Data Validation and Cleansing" iconSclass="z-icon-play" sclass="ifrs-action" />
<vlayout sclass="ifrs-status" spacing="2px">
<hlayout spacing="6px" valign="middle">
<label value="Result:" sclass="ifrs-result-label" />
<label id="stage1NotStarted" value="Not started yet." sclass="ifrs-status-title" />
<label id="stage1StatusTitle" value="" sclass="ifrs-status-title" hflex="1" />
<button id="showValidationDetail" visible="false" iconSclass="z-icon-info-circle"
tooltiptext="Show validation details" sclass="ifrs-icon-action" />
<button id="showTransformationOutput" visible="false" iconSclass="z-icon-table"
tooltiptext="Show transformation output" sclass="ifrs-icon-action" />
</hlayout>
<label id="stage1StatusDetail" value="" sclass="ifrs-status-detail" />
</vlayout>
</vlayout>
<vlayout sclass="ifrs-panel" spacing="8px">
<label value="Color guide" sclass="ifrs-title" />
<vlayout sclass="ifrs-legend" spacing="5px">
<hlayout valign="middle" spacing="6px"><div sclass="ifrs-chip ifrs-chip-blue" /><label value="Identity fields: policy, month, product" sclass="ifrs-subtitle" /></hlayout>
<hlayout valign="middle" spacing="6px"><div sclass="ifrs-chip ifrs-chip-green" /><label value="Currency and FX fields" sclass="ifrs-subtitle" /></hlayout>
<hlayout valign="middle" spacing="6px"><div sclass="ifrs-chip ifrs-chip-amber" /><label value="Amount fields used in transformation" sclass="ifrs-subtitle" /></hlayout>
<hlayout valign="middle" spacing="6px"><div sclass="ifrs-chip ifrs-chip-pink" /><label value="Source control fields" sclass="ifrs-subtitle" /></hlayout>
<hlayout valign="middle" spacing="6px"><div sclass="ifrs-chip ifrs-chip-red" /><label value="Validation issue and required action columns" sclass="ifrs-subtitle" /></hlayout>
</vlayout>
</vlayout>
</vlayout>
<vlayout hflex="1" vflex="1" spacing="10px">
<vlayout sclass="ifrs-panel" vflex="1" spacing="6px">
<tabbox width="100%" vflex="1">
<tabs id="dataCleansingTabs">
<tab id="inputCsvTab" label="Input CSV" selected="true" />
<tab id="etlRuleTab" label="ETL Rule" />
</tabs>
<tabpanels>
<tabpanel vflex="1">
<spreadsheet id="etlSpreadsheet" width="100%" vflex="1"
src="/WEB-INF/books/ifrs_investment_contract_close.xlsx"
maxVisibleRows="20" maxVisibleColumns="10"
showSheetbar="false" showToolbar="false" showFormulabar="true"/>
</tabpanel>
<tabpanel vflex="1">
<spreadsheet id="etlRuleSpreadsheet" width="100%" vflex="1"
src="/WEB-INF/books/ifrs_investment_contract_close.xlsx"
maxVisibleRows="20" maxVisibleColumns="8"
showSheetbar="false" showToolbar="false" showFormulabar="true"/>
</tabpanel>
</tabpanels>
</tabbox>
<coachmark id="dataCleansingCoachmark1" target="inputCsvTab" position="end_before" sclass="ifrs-coachmark ifrs-coachmark-narrow" visible="false">
<vlayout spacing="8px">
<label value="Step 1: Load an IFRS source CSV. The content can be edited directly in Keikai as needed." />
<hbox width="100%" pack="end">
<button id="coachNextToEtlRule" label="Next" iconSclass="z-icon-arrow-right" />
</hbox>
</vlayout>
</coachmark>
<coachmark id="dataCleansingCoachmark2" target="etlRuleTab" position="end_before" sclass="ifrs-coachmark ifrs-coachmark-narrow" visible="false">
<vlayout spacing="8px">
<label value="Step 2: The ETL Rule tab is the live rule source. The validation process reads these rules and validates the CSV data accordingly." />
<hbox width="100%" pack="end">
<button id="coachNextToRunValidation" label="Next" iconSclass="z-icon-arrow-right" />
</hbox>
</vlayout>
</coachmark>
</vlayout>
</vlayout>
</hlayout>
<window id="validationDetailWindow" title="Validation Details" width="820px" height="430px"
mode="modal" border="normal" closable="true" visible="false">
<vlayout width="100%" height="100%" spacing="8px" sclass="ifrs-modal-body">
<label id="validationDetailSummary" value="Fix these fields in Keikai input, then run validation again." sclass="ifrs-subtitle" />
<hlayout spacing="6px" valign="middle">
<label id="selectedValidationCell" value="Select an issue to jump to the cell." sclass="ifrs-subtitle" />
<a id="gotoValidationCell" label="" visible="false" sclass="ifrs-goto-link" />
</hlayout>
<listbox id="validationDetailListbox" vflex="1" mold="paging" pageSize="8" sclass="ifrs-detail-listbox">
<listhead sizable="true">
<listheader label="Cell" width="70px" />
<listheader label="Policy" width="115px" />
<listheader label="Field" width="110px" />
<listheader label="Current value" width="120px" />
<listheader label="Issue" width="170px" />
<listheader label="Required action" />
</listhead>
</listbox>
</vlayout>
</window>
<window id="transformationOutputWindow" title="Transformation Output" width="900px" height="430px"
mode="modal" border="normal" closable="true" visible="false">
<vlayout width="100%" height="100%" spacing="8px" sclass="ifrs-modal-body">
<label id="transformationOutputSummary" value="System-required format after successful transformation." sclass="ifrs-subtitle" />
<grid vflex="1" mold="paging" pageSize="8">
<columns>
<column label="Policy ID" width="120px" />
<column label="Reporting DT" width="110px" />
<column label="Plan" width="120px" />
<column label="IFRS 9 Principal" width="130px" align="right" />
<column label="IFRS 15 Fee" width="110px" align="right" />
<column label="Accounting Treatment" />
</columns>
<rows id="etlOutputRows" />
</grid>
</vlayout>
</window>
<window id="dataCleansingTourIntroWindow" width="560px" mode="modal" border="normal" closable="false"
visible="false" sclass="ifrs-tour-intro">
<vlayout spacing="8px">
<label sclass="ifrs-tour-text"
value="This demo loads an invalid IFRS source CSV, validates it against ETL rules, and guides the user through correcting the source data until it passes validation. Once all issues are resolved, the data is transformed into the format required for financial reporting." />
<hbox width="100%" pack="end">
<button id="coachNextToInputCsv" label="Next" iconSclass="z-icon-arrow-right" />
</hbox>
</vlayout>
</window>
<coachmark id="dataCleansingCoachmark3" target="runStage1Etl" position="end_before" sclass="ifrs-coachmark" visible="false">
<vlayout spacing="8px">
<label value="Step 3: Click Run Data Validation and Cleansing to validate the current input and start cleansing." />
<hbox width="100%" pack="end">
<button id="coachRunValidation" label="Next" iconSclass="z-icon-arrow-right" />
</hbox>
</vlayout>
</coachmark>
<coachmark id="dataCleansingCoachmark4" target="showValidationDetail" position="end_before" sclass="ifrs-coachmark" visible="false">
<vlayout spacing="8px">
<label value="Step 4: Validation failed as intended. Click this icon to open the validation detail window." />
<hbox width="100%" pack="end">
<button id="coachOpenValidationDetail" label="Next" iconSclass="z-icon-arrow-right" />
</hbox>
</vlayout>
</coachmark>
<coachmark id="dataCleansingCoachmark5" target="validationDetailListbox" position="after_start" sclass="ifrs-coachmark" visible="false">
<vlayout spacing="8px">
<label value="Step 5: As hinted, cell D3 contains invalid currency 'NT'; it should be NTD or USD. Users can fix it directly in the Keikai spreadsheet." />
<hbox width="100%" pack="end">
<button id="coachFixD3" label="Fix D3" iconSclass="z-icon-wrench" />
</hbox>
</vlayout>
</coachmark>
<coachmark id="dataCleansingCoachmark6" target="runStage1Etl" position="end_before" sclass="ifrs-coachmark" visible="false">
<vlayout spacing="8px">
<label value="Step 6: Click Run Data Validation and Cleansing again. You should see a successful validation result and the transformed reporting data." />
<hbox width="100%" pack="end">
<button id="coachRunValidationAgain" label="Next" iconSclass="z-icon-arrow-right" />
</hbox>
</vlayout>
</coachmark>
<coachmark id="dataCleansingCoachmark7" target="showTransformationOutput" position="end_before" sclass="ifrs-coachmark" visible="false">
<vlayout spacing="8px">
<label value="Step 7: See data cleansing result. Click this icon to review the generated reporting data." />
<hbox width="100%" pack="end">
<button id="coachShowCleansingResult" label="See Result" iconSclass="z-icon-table" />
</hbox>
</vlayout>
</coachmark>
</vlayout>
</zk>
package demo.financial_data_cleansing;
import io.keikai.api.AreaRef;
import io.keikai.api.CellRef;
import io.keikai.api.Range;
import io.keikai.api.Ranges;
import io.keikai.api.model.CellStyle;
import io.keikai.api.model.Font;
import io.keikai.api.model.Sheet;
import io.keikai.ui.Spreadsheet;
import io.keikai.ui.event.StopEditingEvent;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.event.Event;
import org.zkoss.zk.ui.event.EventListener;
import org.zkoss.zk.ui.event.Events;
import org.zkoss.zk.ui.select.SelectorComposer;
import org.zkoss.zk.ui.select.annotation.Listen;
import org.zkoss.zk.ui.select.annotation.Wire;
import org.zkoss.zkmax.zul.Coachmark;
import org.zkoss.zul.A;
import org.zkoss.zul.Button;
import org.zkoss.zul.Label;
import org.zkoss.zul.Listbox;
import org.zkoss.zul.Listcell;
import org.zkoss.zul.Listitem;
import org.zkoss.zul.Row;
import org.zkoss.zul.Rows;
import org.zkoss.zul.Tab;
import org.zkoss.zul.Window;
import java.util.ArrayList;
import java.util.List;
public class FinancialDataCleansingComposer extends SelectorComposer<Component> {
private static final String INPUT_SHEET = "Input CSV";
private static final String RULE_SHEET = "ETL rule";
private static final String INPUT_SOURCE_SHEET = "Stage1_CSV_Input";
private static final String RULE_SOURCE_SHEET = "Cleansing_Rules";
@Wire
private Spreadsheet etlSpreadsheet;
@Wire
private Spreadsheet etlRuleSpreadsheet;
@Wire
private Tab inputCsvTab;
@Wire
private Tab etlRuleTab;
@Wire
private Label stage1StatusTitle;
@Wire
private Label stage1StatusDetail;
@Wire
private Label stage1NotStarted;
@Wire
private Listbox validationDetailListbox;
@Wire
private Label validationDetailSummary;
@Wire
private Label selectedValidationCell;
@Wire
private A gotoValidationCell;
@Wire
private Button showValidationDetail;
@Wire
private Button showTransformationOutput;
@Wire
private Window validationDetailWindow;
@Wire
private Window transformationOutputWindow;
@Wire
private Label transformationOutputSummary;
@Wire
private Rows etlOutputRows;
@Wire
private Window dataCleansingTourIntroWindow;
@Wire
private Coachmark dataCleansingCoachmark1;
@Wire
private Coachmark dataCleansingCoachmark2;
@Wire
private Coachmark dataCleansingCoachmark3;
@Wire
private Coachmark dataCleansingCoachmark4;
@Wire
private Coachmark dataCleansingCoachmark5;
@Wire
private Coachmark dataCleansingCoachmark6;
@Wire
private Coachmark dataCleansingCoachmark7;
private ValidationError selectedValidationError;
private boolean validationDetailEventsBound;
private boolean dataCleansingTourActive;
private int loadedInputRowCount;
private int pendingValidationFocusRow = -1;
private int pendingValidationFocusColumn = -1;
private String[][] inputRows;
private static final String[] INPUT_HEADERS = {
"policy_no", "reporting_month", "product_code", "currency", "premium_total",
"service_fee", "account_value", "fx_rate", "source_case"
};
private static final Object[][] ERROR_ROWS = {
{"POL-UL-001", "2026-02", "UL_NTD_001", "NT", 1000000, 12000, 988000, 1, "error_to_fix"},
{"POL-VA-002", "2026-02", "VA_USD_002", "USD", 30000, 1000, 29500, 33.10, "sample_valid"},
{"POL-FIA-003", "2026-02", "FIA_USD_003", "USD", 50000, 1600, 49200, 33.10, "sample_valid"}
};
private static final Object[][] DEFAULT_RULE_ROWS = {
{"Rule", "Field", "Operator", "Value", "Issue", "Required action"},
{"Allowed currency", "currency", "in", "NTD,USD", "Invalid currency", "Use one of: {value}"},
{"FX rate required", "fx_rate", "required_positive", "", "Missing FX rate", "Enter a positive FX rate"},
{"Service fee cap", "service_fee", "lte_field", "premium_total", "Service fee exceeds premium", "Reduce service_fee below premium_total"}
};
@Override
public void doAfterCompose(Component comp) throws Exception {
super.doAfterCompose(comp);
comp.addEventListener("onFocusValidationCell", new EventListener<Event>() {
public void onEvent(Event event) {
focusPendingValidationCell();
}
});
resolveValidationDetailComponents();
resolveTransformationOutputComponents();
configureInputWorkbook();
configureRuleWorkbook();
loadCsvRows(ERROR_ROWS);
}
@Listen(Events.ON_CLICK + " = #runStage1Etl")
public void runDataValidationAndCleansing() {
EtlResult result = validateRows();
resolveValidationDetailComponents();
resolveTransformationOutputComponents();
clearRows(etlOutputRows);
if (result.errors.isEmpty()) {
setVisible(stage1NotStarted, false);
stage1StatusTitle.setValue("Validation Passed");
stage1StatusDetail.setValue("Reporting Data Generated");
showValidationDetail.setVisible(false);
showTransformationOutput.setVisible(true);
clearValidationDetails();
clearRows(etlOutputRows);
setLabel(validationDetailSummary, "Validation passed.");
setLabel(transformationOutputSummary, result.validRows.size() + " rows transformed into the system-required format.");
for (InputRow input : result.validRows) {
addTransformationOutput(input);
}
} else {
setVisible(stage1NotStarted, false);
stage1StatusTitle.setValue("Validation Failed");
stage1StatusDetail.setValue(result.errors.get(0).summary() + ". Fix the listed fields in Keikai input, then run validation again.");
showValidationDetail.setVisible(true);
showTransformationOutput.setVisible(false);
clearValidationDetails();
setLabel(validationDetailSummary, result.errors.size() + " issue(s) found. Select an issue, then jump to the Keikai cell to fix it.");
for (ValidationError error : result.errors) {
addValidationDetail(error);
}
clearRows(etlOutputRows);
setLabel(transformationOutputSummary, "No transformation output because validation failed.");
}
}
@Listen(Events.ON_CLICK + " = #startDataCleansingCoachmark")
public void startDataCleansingCoachmark() {
dataCleansingTourActive = true;
loadCsvRows(ERROR_ROWS);
if (inputCsvTab != null) {
inputCsvTab.setSelected(true);
}
closeDataCleansingCoachmarks();
if (dataCleansingTourIntroWindow != null) {
dataCleansingTourIntroWindow.doModal();
}
}
private void nextDataCleansingCoachmarkFromIntro() {
if (dataCleansingTourIntroWindow != null) {
dataCleansingTourIntroWindow.setVisible(false);
}
if (inputCsvTab != null) {
inputCsvTab.setSelected(true);
}
openCoachmark(dataCleansingCoachmark1);
}
@Listen(Events.ON_CLICK + " = #dataCleansingTourIntroWindow #coachNextToInputCsv")
public void nextDataCleansingCoachmarkFromIntroButton() {
nextDataCleansingCoachmarkFromIntro();
}
@Listen("onTargetClick = #dataCleansingCoachmark1")
public void nextDataCleansingCoachmarkFromInputTab() {
if (etlRuleTab != null) {
etlRuleTab.setSelected(true);
}
closeCoachmark(dataCleansingCoachmark1);
openCoachmark(dataCleansingCoachmark2);
}
@Listen(Events.ON_CLICK + " = #coachNextToEtlRule")
public void nextDataCleansingCoachmarkFromInputTabButton() {
nextDataCleansingCoachmarkFromInputTab();
}
@Listen("onTargetClick = #dataCleansingCoachmark2")
public void nextDataCleansingCoachmarkFromRuleTab() {
if (inputCsvTab != null) {
inputCsvTab.setSelected(true);
}
closeCoachmark(dataCleansingCoachmark2);
openCoachmark(dataCleansingCoachmark3);
}
@Listen(Events.ON_CLICK + " = #coachNextToRunValidation")
public void nextDataCleansingCoachmarkFromRuleTabButton() {
nextDataCleansingCoachmarkFromRuleTab();
}
@Listen("onTargetClick = #dataCleansingCoachmark3")
public void runValidationFromCoachmark() {
closeCoachmark(dataCleansingCoachmark3);
runDataValidationAndCleansing();
if (showValidationDetail != null && showValidationDetail.isVisible()) {
openCoachmark(dataCleansingCoachmark4);
}
}
@Listen(Events.ON_CLICK + " = #coachRunValidation")
public void runValidationFromCoachmarkButton() {
runValidationFromCoachmark();
}
@Listen("onTargetClick = #dataCleansingCoachmark4")
public void openValidationDetailFromCoachmark() {
closeCoachmark(dataCleansingCoachmark4);
showValidationDetail();
}
@Listen(Events.ON_CLICK + " = #coachOpenValidationDetail")
public void openValidationDetailFromCoachmarkButton() {
openValidationDetailFromCoachmark();
}
@Listen("onTargetClick = #dataCleansingCoachmark5")
public void fixD3FromCoachmark() {
closeCoachmark(dataCleansingCoachmark5);
fixD3Currency();
openCoachmark(dataCleansingCoachmark6);
}
@Listen(Events.ON_CLICK + " = #coachFixD3")
public void fixD3FromCoachmarkButton() {
fixD3FromCoachmark();
}
@Listen("onTargetClick = #dataCleansingCoachmark6")
public void runValidationAgainFromCoachmark() {
closeCoachmark(dataCleansingCoachmark6);
runDataValidationAndCleansing();
if (showTransformationOutput != null && showTransformationOutput.isVisible()) {
openCoachmark(dataCleansingCoachmark7);
} else {
dataCleansingTourActive = false;
}
}
@Listen(Events.ON_CLICK + " = #coachRunValidationAgain")
public void runValidationAgainFromCoachmarkButton() {
runValidationAgainFromCoachmark();
}
@Listen(Events.ON_CLICK + " = #showValidationDetail")
public void showValidationDetail() {
resolveValidationDetailComponents();
if (validationDetailWindow != null) {
clearSelectedValidationError();
validationDetailWindow.doModal();
if (dataCleansingTourActive) {
targetD3ValidationCell();
openCoachmark(dataCleansingCoachmark5);
}
}
}
@Listen(Events.ON_CLOSE + " = #validationDetailWindow")
public void closeValidationDetail(Event event) {
event.stopPropagation();
if (validationDetailWindow != null) {
validationDetailWindow.setVisible(false);
}
closeCoachmark(dataCleansingCoachmark5);
closeCoachmark(dataCleansingCoachmark6);
closeCoachmark(dataCleansingCoachmark7);
}
@Listen(Events.ON_CLICK + " = #showTransformationOutput")
public void showTransformationOutput() {
closeCoachmark(dataCleansingCoachmark7);
dataCleansingTourActive = false;
resolveTransformationOutputComponents();
if (transformationOutputWindow != null) {
transformationOutputWindow.doModal();
}
}
@Listen("onTargetClick = #dataCleansingCoachmark7")
public void showCleansingResultFromCoachmark() {
showTransformationOutput();
}
@Listen(Events.ON_CLICK + " = #coachShowCleansingResult")
public void showCleansingResultFromCoachmarkButton() {
showCleansingResultFromCoachmark();
}
@Listen(Events.ON_CLOSE + " = #transformationOutputWindow")
public void closeTransformationOutput(Event event) {
event.stopPropagation();
if (transformationOutputWindow != null) {
transformationOutputWindow.setVisible(false);
}
}
@Listen("onStopEditing = #etlSpreadsheet")
public void updateInputRows(StopEditingEvent event) {
if (inputRows == null || event.getRow() < 2 || !INPUT_SHEET.equals(etlSpreadsheet.getSelectedSheetName())) {
return;
}
int inputRow = event.getRow() - 2;
int inputColumn = event.getColumn();
if (inputRow < 0 || inputRow >= inputRows.length || inputColumn < 0 || inputColumn >= INPUT_HEADERS.length) {
return;
}
Object value = event.getEditingValue();
inputRows[inputRow][inputColumn] = value == null ? "" : String.valueOf(value).trim();
}
@Listen(Events.ON_SELECT + " = #validationDetailListbox")
public void selectValidationDetail() {
resolveValidationDetailComponents();
if (validationDetailListbox == null || validationDetailListbox.getSelectedItem() == null) {
clearSelectedValidationError();
return;
}
Object value = validationDetailListbox.getSelectedItem().getValue();
if (!(value instanceof ValidationError)) {
clearSelectedValidationError();
return;
}
selectedValidationError = (ValidationError) value;
setLabel(selectedValidationCell, "Selected issue:");
if (gotoValidationCell != null) {
gotoValidationCell.setLabel("Goto " + selectedValidationError.cellAddress());
gotoValidationCell.setVisible(true);
}
}
@Listen(Events.ON_CLICK + " = #gotoValidationCell")
public void gotoValidationCell() {
if (selectedValidationError == null) {
return;
}
int row = selectedValidationError.row - 1;
int column = selectedValidationError.column;
if (row >= 0 && column >= 0) {
pendingValidationFocusRow = row;
pendingValidationFocusColumn = column;
if (inputCsvTab != null) {
inputCsvTab.setSelected(true);
}
etlSpreadsheet.setSelectedSheet(INPUT_SHEET);
}
if (validationDetailWindow != null) {
validationDetailWindow.setVisible(false);
}
Events.echoEvent("onFocusValidationCell", getSelf(), null);
}
private void focusPendingValidationCell() {
if (pendingValidationFocusRow < 0 || pendingValidationFocusColumn < 0) {
return;
}
if (inputCsvTab != null) {
inputCsvTab.setSelected(true);
}
etlSpreadsheet.setSelectedSheet(INPUT_SHEET);
focusSingleCell(etlSpreadsheet, pendingValidationFocusRow, pendingValidationFocusColumn);
pendingValidationFocusRow = -1;
pendingValidationFocusColumn = -1;
}
private void openCoachmark(Coachmark coachmark) {
if (coachmark != null) {
coachmark.setVisible(true);
coachmark.open();
}
}
private void closeCoachmark(Coachmark coachmark) {
if (coachmark != null) {
coachmark.close();
coachmark.setVisible(false);
}
}
private void closeDataCleansingCoachmarks() {
Coachmark[] coachmarks = {
dataCleansingCoachmark1, dataCleansingCoachmark2, dataCleansingCoachmark3,
dataCleansingCoachmark4, dataCleansingCoachmark5, dataCleansingCoachmark6,
dataCleansingCoachmark7
};
for (Coachmark coachmark : coachmarks) {
closeCoachmark(coachmark);
}
}
private void fixD3Currency() {
Sheet sheet = etlSpreadsheet.getBook().getSheet(INPUT_SHEET);
writeCellText(sheet, 2, 3, "NTD");
Ranges.range(sheet, 2, 3).notifyChange();
if (inputRows != null && inputRows.length > 0 && inputRows[0].length > 3) {
inputRows[0][3] = "NTD";
}
if (validationDetailWindow != null) {
validationDetailWindow.setVisible(false);
}
if (inputCsvTab != null) {
inputCsvTab.setSelected(true);
}
etlSpreadsheet.setSelectedSheet(INPUT_SHEET);
pendingValidationFocusRow = 2;
pendingValidationFocusColumn = 3;
Events.echoEvent("onFocusValidationCell", getSelf(), null);
}
private void focusSingleCell(Spreadsheet spreadsheet, int row, int col) {
if (spreadsheet == null) {
return;
}
spreadsheet.focus();
spreadsheet.focusTo(row, col);
spreadsheet.setCellFocus(new CellRef(row, col));
spreadsheet.setSelection(new AreaRef(row, col, row, col));
}
private void loadCsvRows(Object[][] rows) {
Sheet sheet = etlSpreadsheet.getBook().getSheet(INPUT_SHEET);
Ranges.range(sheet, 0, 0, 30, 10).clearContents();
writeCellText(sheet, 0, 0, "Editable CSV input");
for (int col = 0; col < INPUT_HEADERS.length; col++) {
writeCellText(sheet, 1, col, INPUT_HEADERS[col]);
}
for (int row = 0; row < rows.length; row++) {
for (int col = 0; col < rows[row].length; col++) {
writeCellText(sheet, row + 2, col, rows[row][col]);
}
}
Ranges.range(sheet, 0, 0, 30, 10).notifyChange();
applyInputStyles(sheet);
loadedInputRowCount = rows.length;
inputRows = copyInputRows(rows);
resolveValidationDetailComponents();
resolveTransformationOutputComponents();
clearValidationDetails();
clearRows(etlOutputRows);
showValidationDetail.setVisible(false);
showTransformationOutput.setVisible(false);
setLabel(validationDetailSummary, "Fix these fields in Keikai input, then run validation again.");
setLabel(transformationOutputSummary, "Run data validation to generate transformation output.");
clearSelectedValidationError();
setVisible(stage1NotStarted, true);
stage1StatusTitle.setValue("");
stage1StatusDetail.setValue("");
etlSpreadsheet.setSelectedSheet(INPUT_SHEET);
focusSingleCell(etlSpreadsheet, 2, 0);
etlSpreadsheet.invalidate();
}
private void configureInputWorkbook() {
if (etlSpreadsheet == null) {
return;
}
Sheet inputSheet = firstExistingSheet(etlSpreadsheet, INPUT_SHEET, INPUT_SOURCE_SHEET);
if (inputSheet != null && !INPUT_SHEET.equals(inputSheet.getSheetName())) {
Ranges.range(inputSheet).setSheetName(INPUT_SHEET);
inputSheet = etlSpreadsheet.getBook().getSheet(INPUT_SHEET);
}
for (int index = 0; index < etlSpreadsheet.getBook().getNumberOfSheets(); index++) {
Sheet sheet = etlSpreadsheet.getBook().getSheetAt(index);
Ranges.range(sheet).setSheetVisible(INPUT_SHEET.equals(sheet.getSheetName()) ? Range.SheetVisible.VISIBLE : Range.SheetVisible.HIDDEN);
}
if (inputSheet != null) {
Ranges.range(inputSheet).setSheetOrder(0);
applyInputStyles(inputSheet);
}
etlSpreadsheet.setSelectedSheet(INPUT_SHEET);
}
private void configureRuleWorkbook() {
if (etlRuleSpreadsheet == null) {
return;
}
Sheet ruleSheet = firstExistingSheet(etlRuleSpreadsheet, RULE_SHEET, RULE_SOURCE_SHEET);
if (ruleSheet != null && !RULE_SHEET.equals(ruleSheet.getSheetName())) {
Ranges.range(ruleSheet).setSheetName(RULE_SHEET);
ruleSheet = etlRuleSpreadsheet.getBook().getSheet(RULE_SHEET);
}
if (ruleSheet != null && !isExecutableRuleSheet()) {
initializeRules(ruleSheet);
}
for (int index = 0; index < etlRuleSpreadsheet.getBook().getNumberOfSheets(); index++) {
Sheet sheet = etlRuleSpreadsheet.getBook().getSheetAt(index);
Ranges.range(sheet).setSheetVisible(RULE_SHEET.equals(sheet.getSheetName()) ? Range.SheetVisible.VISIBLE : Range.SheetVisible.HIDDEN);
}
if (ruleSheet != null) {
Ranges.range(ruleSheet).setSheetOrder(0);
applyRuleStyles(ruleSheet);
}
etlRuleSpreadsheet.setSelectedSheet(RULE_SHEET);
}
private boolean isExecutableRuleSheet() {
return etlRuleSpreadsheet != null
&& "Rule".equals(readCell(etlRuleSpreadsheet, RULE_SHEET, 0, 0))
&& "Field".equals(readCell(etlRuleSpreadsheet, RULE_SHEET, 0, 1))
&& "Operator".equals(readCell(etlRuleSpreadsheet, RULE_SHEET, 0, 2))
&& "Value".equals(readCell(etlRuleSpreadsheet, RULE_SHEET, 0, 3));
}
private void initializeRules(Sheet ruleSheet) {
Ranges.range(ruleSheet, 0, 0, 30, 8).clearContents();
for (int row = 0; row < DEFAULT_RULE_ROWS.length; row++) {
for (int col = 0; col < DEFAULT_RULE_ROWS[row].length; col++) {
writeCellText(ruleSheet, row, col, DEFAULT_RULE_ROWS[row][col]);
}
}
Ranges.range(ruleSheet, 0, 0, 30, 8).notifyChange();
applyRuleStyles(ruleSheet);
}
private Sheet firstExistingSheet(Spreadsheet spreadsheet, String... sheetNames) {
for (String sheetName : sheetNames) {
Sheet sheet = spreadsheet.getBook().getSheet(sheetName);
if (sheet != null) {
return sheet;
}
}
return null;
}
private EtlResult validateRows() {
EtlResult result = new EtlResult();
if (loadedInputRowCount == 0) {
result.errors.add(new ValidationError(0, 0, "-", "input", "", "No CSV rows loaded", "Reload the demo to restore the sample input"));
return result;
}
List<ValidationRule> rules = readRules();
if (rules.isEmpty()) {
result.errors.add(new ValidationError(0, 0, "-", "ETL rule", "", "No validation rules", "Add rules in the ETL rule sheet"));
return result;
}
for (int row = 2; row < 2 + loadedInputRowCount; row++) {
if (isBlankInputRow(row)) {
continue;
}
List<ValidationError> errors = validateInputRow(row, rules);
if (errors.isEmpty()) {
result.validRows.add(readInputRow(row));
} else {
result.errors.addAll(errors);
}
}
if (result.validRows.isEmpty() && result.errors.isEmpty()) {
result.errors.add(new ValidationError(0, 0, "-", "input", "", "Loaded rows are blank", "Restore values in the Input CSV tab, then run validation again"));
}
return result;
}
private boolean isBlankInputRow(int row) {
return readInputCell(row, 0).isEmpty();
}
private InputRow readInputRow(int row) {
return new InputRow(
readInputCell(row, 0),
readInputCell(row, 1),
readInputCell(row, 2),
readInputCell(row, 3),
readInputAmount(row, 4),
readInputAmount(row, 5),
readInputAmount(row, 6),
readInputAmount(row, 7));
}
private void writeCellText(Sheet sheet, int row, int col, Object value) {
Ranges.range(sheet, row, col).setCellEditText(value == null ? "" : String.valueOf(value));
}
private List<ValidationRule> readRules() {
List<ValidationRule> rules = new ArrayList<ValidationRule>();
if (etlRuleSpreadsheet == null) {
return rules;
}
for (int row = 1; row < 30; row++) {
String field = readCell(etlRuleSpreadsheet, RULE_SHEET, row, 1);
String operator = readCell(etlRuleSpreadsheet, RULE_SHEET, row, 2);
if (field.isEmpty() && operator.isEmpty()) {
continue;
}
int inputColumn = fieldColumn(field);
if (inputColumn < 0 || operator.isEmpty()) {
continue;
}
rules.add(new ValidationRule(
readCell(etlRuleSpreadsheet, RULE_SHEET, row, 0),
field,
operator,
readCell(etlRuleSpreadsheet, RULE_SHEET, row, 3),
readCell(etlRuleSpreadsheet, RULE_SHEET, row, 4),
readCell(etlRuleSpreadsheet, RULE_SHEET, row, 5),
inputColumn));
}
return rules;
}
private List<ValidationError> validateInputRow(int row, List<ValidationRule> rules) {
List<ValidationError> errors = new ArrayList<ValidationError>();
String policyNo = readInputCell(row, 0);
for (ValidationRule rule : rules) {
if (!passesRule(row, rule)) {
String currentValue = readInputCell(row, rule.column);
errors.add(new ValidationError(row + 1, rule.column, policyNo, rule.field, currentValue, rule.issue(), rule.requiredFix()));
}
}
return errors;
}
private boolean passesRule(int row, ValidationRule rule) {
String currentValue = readInputCell(row, rule.column);
if ("in".equals(rule.operator)) {
for (String allowed : rule.value.split(",")) {
if (allowed.trim().equals(currentValue)) {
return true;
}
}
return false;
}
if ("required_positive".equals(rule.operator)) {
return !currentValue.isEmpty() && readInputAmount(row, rule.column) > 0;
}
if ("lte_field".equals(rule.operator)) {
int compareColumn = fieldColumn(rule.value);
return compareColumn >= 0 && readInputAmount(row, rule.column) <= readInputAmount(row, compareColumn);
}
return true;
}
private int fieldColumn(String field) {
for (int index = 0; index < INPUT_HEADERS.length; index++) {
if (INPUT_HEADERS[index].equals(field)) {
return index;
}
}
return -1;
}
private String readInputCell(int row, int col) {
int inputRow = row - 2;
if (inputRows != null && inputRow >= 0 && inputRow < inputRows.length && col >= 0 && col < inputRows[inputRow].length) {
String value = inputRows[inputRow][col];
return value == null ? "" : value.trim();
}
return readCell(etlSpreadsheet, INPUT_SHEET, row, col);
}
private String[][] copyInputRows(Object[][] rows) {
String[][] copy = new String[rows.length][INPUT_HEADERS.length];
for (int row = 0; row < rows.length; row++) {
for (int col = 0; col < INPUT_HEADERS.length; col++) {
Object value = col < rows[row].length ? rows[row][col] : "";
copy[row][col] = value == null ? "" : String.valueOf(value).trim();
}
}
return copy;
}
private double readInputAmount(int row, int col) {
return parseAmount(readInputCell(row, col));
}
private void addValidationDetail(ValidationError error) {
if (validationDetailListbox == null) {
return;
}
Listitem item = new Listitem();
item.setValue(error);
Listcell cellAddressCell = appendListcell(item, error.cellAddress());
if ("D3".equals(error.cellAddress())) {
cellAddressCell.setId("validationIssueD3Cell");
}
appendListcell(item, error.policyNo);
appendListcell(item, error.field);
appendListcell(item, error.currentValue);
appendListcell(item, error.issue);
appendListcell(item, error.requiredFix);
validationDetailListbox.appendChild(item);
}
private void addTransformationOutput(InputRow input) {
double principalBase = (input.premiumTotal - input.serviceFee) * input.fxRate;
double serviceFeeBase = input.serviceFee * input.fxRate;
String accountingTreatment = "IFRS 9 financial liability + IFRS 15 service fee";
addMessage(etlOutputRows, input.policyNo, input.reportingMonth + "-28", input.productCode,
formatAmount(principalBase), formatAmount(serviceFeeBase), accountingTreatment);
}
private void resolveValidationDetailComponents() {
if (validationDetailWindow == null) {
return;
}
Component listbox = validationDetailWindow.getFellowIfAny("validationDetailListbox");
if (validationDetailListbox == null && listbox instanceof Listbox) {
validationDetailListbox = (Listbox) listbox;
}
Component summary = validationDetailWindow.getFellowIfAny("validationDetailSummary");
if (validationDetailSummary == null && summary instanceof Label) {
validationDetailSummary = (Label) summary;
}
Component selected = validationDetailWindow.getFellowIfAny("selectedValidationCell");
if (selectedValidationCell == null && selected instanceof Label) {
selectedValidationCell = (Label) selected;
}
Component gotoLink = validationDetailWindow.getFellowIfAny("gotoValidationCell");
if (gotoValidationCell == null && gotoLink instanceof A) {
gotoValidationCell = (A) gotoLink;
}
bindValidationDetailEvents();
}
private void resolveTransformationOutputComponents() {
if (transformationOutputWindow == null) {
return;
}
Component rows = transformationOutputWindow.getFellowIfAny("etlOutputRows");
if (etlOutputRows == null && rows instanceof Rows) {
etlOutputRows = (Rows) rows;
}
Component summary = transformationOutputWindow.getFellowIfAny("transformationOutputSummary");
if (transformationOutputSummary == null && summary instanceof Label) {
transformationOutputSummary = (Label) summary;
}
}
private void bindValidationDetailEvents() {
if (validationDetailEventsBound || validationDetailListbox == null || gotoValidationCell == null) {
return;
}
validationDetailListbox.addEventListener(Events.ON_SELECT, new EventListener<Event>() {
public void onEvent(Event event) {
selectValidationDetail();
}
});
gotoValidationCell.addEventListener(Events.ON_CLICK, new EventListener<Event>() {
public void onEvent(Event event) {
gotoValidationCell();
}
});
validationDetailEventsBound = true;
}
private void clearValidationDetails() {
if (validationDetailListbox != null) {
validationDetailListbox.getItems().clear();
}
clearSelectedValidationError();
}
private void clearSelectedValidationError() {
selectedValidationError = null;
setLabel(selectedValidationCell, "Select an issue to jump to the cell.");
if (gotoValidationCell != null) {
gotoValidationCell.setLabel("");
gotoValidationCell.setVisible(false);
}
}
private Listcell appendListcell(Listitem item, String value) {
Listcell cell = new Listcell();
cell.appendChild(new Label(value == null ? "" : value));
item.appendChild(cell);
return cell;
}
private void targetD3ValidationCell() {
if (dataCleansingCoachmark5 == null || validationDetailWindow == null) {
return;
}
Component d3Cell = validationDetailWindow.getFellowIfAny("validationIssueD3Cell", true);
if (d3Cell != null) {
dataCleansingCoachmark5.setTarget(d3Cell);
}
}
private void clearRows(Rows rows) {
if (rows != null) {
rows.getChildren().clear();
}
}
private void setLabel(Label label, String value) {
if (label != null) {
label.setValue(value);
}
}
private void setVisible(Component component, boolean visible) {
if (component != null) {
component.setVisible(visible);
}
}
private void addMessage(Rows rows, String... values) {
if (rows == null) {
return;
}
Row row = new Row();
for (String value : values) {
row.appendChild(new Label(value == null ? "" : value));
}
rows.appendChild(row);
}
private void applyInputStyles(Sheet sheet) {
if (sheet == null) {
return;
}
applyInputColumnWidths(sheet);
styleCell(sheet, 0, 0, "#E0E7FF", "#172033", true);
for (int col = 0; col < INPUT_HEADERS.length; col++) {
String fill = "#E2E8F0";
if (col <= 2) {
fill = "#DBEAFE";
} else if (col == 3 || col == 7) {
fill = "#DCFCE7";
} else if (col >= 4 && col <= 6) {
fill = "#FEF3C7";
} else if (col >= 8) {
fill = "#FCE7F3";
}
styleCell(sheet, 1, col, fill, "#172033", true);
}
Ranges.range(sheet, 0, 0, 2 + Math.max(loadedInputRowCount, 3), INPUT_HEADERS.length).notifyChange();
}
private void applyInputColumnWidths(Sheet sheet) {
if (etlSpreadsheet != null) {
etlSpreadsheet.setAutofitColumnWidth(sheet.getInternalSheet(), 0, INPUT_HEADERS.length - 1);
}
setColumnWidth(sheet, 0, 115);
setColumnWidth(sheet, 1, 130);
setColumnWidth(sheet, 2, 145);
}
private void applyRuleStyles(Sheet sheet) {
if (sheet == null) {
return;
}
applyRuleColumnWidths(sheet);
for (int col = 0; col < 6; col++) {
styleCell(sheet, 0, col, "#E0E7FF", "#172033", true);
}
for (int row = 1; row < DEFAULT_RULE_ROWS.length; row++) {
styleCell(sheet, row, 0, "#F8FAFC", "#172033", true);
styleCell(sheet, row, 1, "#DBEAFE", "#172033", false);
styleCell(sheet, row, 2, "#DCFCE7", "#172033", false);
styleCell(sheet, row, 3, "#FEF3C7", "#172033", false);
styleCell(sheet, row, 4, "#FEE2E2", "#172033", false);
styleCell(sheet, row, 5, "#FEE2E2", "#172033", false);
}
Ranges.range(sheet, 0, 0, DEFAULT_RULE_ROWS.length + 1, 6).notifyChange();
}
private void applyRuleColumnWidths(Sheet sheet) {
if (etlRuleSpreadsheet != null) {
etlRuleSpreadsheet.setAutofitColumnWidth(sheet.getInternalSheet(), 0, 5);
}
}
private void setColumnWidth(Sheet sheet, int col, int width) {
Ranges.range(sheet, 0, col).toColumnRange().setColumnWidth(width);
}
private void styleCell(Sheet sheet, int row, int col, String fillColor, String fontColor, boolean bold) {
Range cell = Ranges.range(sheet, row, col);
Range.CellStyleHelper helper = cell.getCellStyleHelper();
CellStyle style = helper.builder(cell.getCellStyle())
.fillColor(helper.createColorFromHtmlColor(fillColor))
.backColor(helper.createColorFromHtmlColor(fillColor))
.fillPattern(CellStyle.FillPattern.SOLID)
.fontColor(helper.createColorFromHtmlColor(fontColor))
.fontBoldweight(bold ? Font.Boldweight.BOLD : Font.Boldweight.NORMAL)
.wrapText(true)
.build();
cell.setCellStyle(style);
}
private String readCell(Spreadsheet spreadsheet, String sheetName, int row, int col) {
Sheet sheet = spreadsheet.getBook().getSheet(sheetName);
if (sheet == null) {
return "";
}
Range cell = Ranges.range(sheet, row, col);
String value = cell.getCellEditText();
if (value != null && value.trim().startsWith("=")) {
Object cellValue = cell.getCellValue();
value = cellValue == null ? "" : String.valueOf(cellValue);
} else if (value == null || value.trim().isEmpty()) {
Object cellValue = cell.getCellValue();
value = cellValue == null ? "" : String.valueOf(cellValue);
}
return value == null ? "" : value.trim();
}
private double parseAmount(String text) {
String value = (text == null ? "" : text).replace(",", "");
if (value.isEmpty()) {
return 0;
}
try {
return Double.parseDouble(value);
} catch (NumberFormatException e) {
return 0;
}
}
private String formatAmount(double value) {
return String.format("%,.0f", value);
}
private static class EtlResult {
private final List<InputRow> validRows = new ArrayList<InputRow>();
private final List<ValidationError> errors = new ArrayList<ValidationError>();
}
private static class ValidationRule {
private final String name;
private final String field;
private final String operator;
private final String value;
private final String issue;
private final String requiredFix;
private final int column;
private ValidationRule(String name, String field, String operator, String value, String issue, String requiredFix, int column) {
this.name = name;
this.field = field;
this.operator = operator;
this.value = value;
this.issue = issue;
this.requiredFix = requiredFix;
this.column = column;
}
private String issue() {
return issue == null || issue.isEmpty() ? name : issue;
}
private String requiredFix() {
if (requiredFix == null || requiredFix.isEmpty()) {
return "Update " + field + " according to the ETL rule sheet";
}
return requiredFix.replace("{value}", value);
}
}
private static class InputRow {
private final String policyNo;
private final String reportingMonth;
private final String productCode;
private final double premiumTotal;
private final double serviceFee;
private final double accountValue;
private final double fxRate;
private InputRow(String policyNo, String reportingMonth, String productCode, String currency,
double premiumTotal, double serviceFee, double accountValue, double fxRate) {
this.policyNo = policyNo;
this.reportingMonth = reportingMonth;
this.productCode = productCode;
this.premiumTotal = premiumTotal;
this.serviceFee = serviceFee;
this.accountValue = accountValue;
this.fxRate = fxRate;
}
}
private static class ValidationError {
private final int row;
private final int column;
private final String policyNo;
private final String field;
private final String currentValue;
private final String issue;
private final String requiredFix;
private ValidationError(int row, int column, String policyNo, String field, String currentValue, String issue, String requiredFix) {
this.row = row;
this.column = column;
this.policyNo = policyNo;
this.field = field;
this.currentValue = currentValue;
this.issue = issue;
this.requiredFix = requiredFix;
}
private String cellAddress() {
if (row <= 0 || column < 0) {
return "-";
}
return columnName(column) + row;
}
private String summary() {
String value = currentValue == null || currentValue.isEmpty() ? "blank" : currentValue;
return cellAddress() + " " + issue + " (value: " + value + ")";
}
private static String columnName(int column) {
StringBuilder name = new StringBuilder();
int index = column;
do {
name.insert(0, (char) ('A' + (index % 26)));
index = index / 26 - 1;
} while (index >= 0);
return name.toString();
}
}
}