/**
* Stage one: the inputs, gathered into one book. Axyra opens the two XLSX
* files and the CSV file itself, a query result stands in for the
* database, and the rules workbook contributes its Rules and Mapping sheets.
* The Sources sheet says which of them feed the run.
*/
private Workbook loadSources() {
Workbook wb = Workbook.create();
Sheet control = wb.createSheet(SOURCES);
title(control, 0, "Sources · set Include to FALSE to leave one out of the run");
header(control, 1, "Source", "Type", "Origin", "Include");
for (int i = 0; i < SOURCE_FILES.length; i++) {
String[] source = SOURCE_FILES[i];
Sheet sheet = wb.createSheet(source[0]);
if (source[2] == null) {
row(sheet, 0, (Object[]) HEADERS);
for (int r = 0; r < GL_ROWS.length; r++) {
row(sheet, r + 1, GL_ROWS[r]);
}
} else {
try (Workbook file = Workbook.openBytes(readBook(source[2]),
source[1].toLowerCase(Locale.ROOT))) {
copyValues(file.sheet(0), sheet);
}
}
String origin = source[2] == null ? QUERY : source[2].replace("financialDataFlow-", "");
row(control, 2 + i, source[0], source[1], origin, Boolean.TRUE);
}
try (Workbook rules = Workbook.openBytes(readBook(RULES_FILE), "xlsx")) {
copyValues(rules.sheet(RULES), wb.createSheet(RULES));
copyValues(rules.sheet(MAPPING), wb.createSheet(MAPPING));
}
describeRules(wb.sheet(RULES));
restyle(wb);
wb.setActiveSheet(control.index());
return wb;
}This demo turns several financial sources into mapped accounting data, balanced journal entries and three reports.
Two XLSX files and a CSV file are opened by Axyra, and the rows of a simulated database query are added beside them. The Sources sheet decides which of them feed the run. The Rules sheet holds five rules — required fields and formats, a numeric range, the account mapping, the merge key and the split — and the Mapping sheet classifies each account code and names its debit and credit accounts. All of them are editable cells.
Each stage runs only when the previous one passed. A failed check marks its cell in red with a comment, and the Validation sheet links every issue back to its cell, with a correction where one can be derived from what was typed: Break two inputs introduces two such slips and Apply suggested fixes repairs them.
Mapping copies the rule's lookup formula down the Mapped sheet, so the classification is calculated by the engine. Report 1 groups by the merge key, Report 2 by the split column first and Report 3 by mapped category. Hovering a report amount shows the source cells behind it, the Lineage sheet lists them all, and each input row becomes one balanced debit/credit pair in the Journal Entries sheet; a negative amount reverses the two sides. The result can be downloaded as a single workbook.
The debit and credit accounts in the Mapping sheet are demonstration assumptions, not an approved chart of accounts.