In this document, we will be walking through a simple spreadsheet-building exercise based on a real use case. We will discuss the general high-level strategy in a typical application of fxl.js, and provide the relevant code snippets. See the complete source code and the example dataset used in this document.
- The Typical fxl.js Flow
- An Inventory-Planning Spreadsheet
- Component Building
- Component 1: Form Headers
- Component 2: Form Footers
- Component 3: Inventory Table
- Sub-Component 1: Header
- Sub-Component 2: Raw-Material Column
- Sub-Component 3: Single-Month Inventory
- Sub-Integration 1: Single-Quarter Inventory
- Sub-Integration 2: Full-Year Inventory
- Integration: Full Report
- Writing to File
Building spreadsheets with fxl.js should feel like building structures using lego blocks. Typically, we would start by creating individual components that make up the final structure separately before finally putting the structure together at the end. A component that makes up a structure may itself be composed of sub-components that go through a similar recursive process of isolated construction and integration.
Much like a lego structure, a fxl.js spreadsheet is composed of a coherent group of cells that make a spreadsheet component. A component is coherent because it can typically be operated on as a unit - we may move it around or style them differently, and the group still serves the original purpose.
We make a higher-level component through integrating lower-level components. For instance, a header of column names and a body of record values can be viewed as two separate components that make up a table. This table may, in turn, be used as a component that makes up an even higher-level component, such as a report page.
Once we build up the entire spreadsheet through this recursive process of component building, the only thing left to do is to complete the IO operation by writing to a file.
Here's a simplistic, un-styled example of the three steps:
// lowest level component building
const tableHeader = fxl.rowToCells(['Name', 'Surname', 'Age']);
const tableBody = fxl.tableToCells([
['Alice', 'Smith', 17],
['Bob', 'Thomas', 23],
['Cleo', 'Cameron', 37],
]);
// low-level component integration
const table = fxl.concatBelow(tableHeader, tableBody);
// medium-level component integration
const page = fxl.concatBelow(
pageHeader,
fxl.concatRight(table, anotherTable),
pageFooter
);
// high-level component integration
const report = fxl.concateBelow(fxl.padBelow(2, page), anotherPage);
// IO operation
await fxl.writeXlsx(report, 'report.xlsx')Before we start the spreadsheet building process, we first need to prepare the dataset. Here's a cleaner, hard-coded version of the data preparation:
import * as fs from 'fs';
import * as fxl from '@01group/fxl';
const JSON_PATH = 'packages/example/src/assets/inventory.json';
const RAW_DATA = fs.readFileSync(JSON_PATH).toString();
const INVENTORY = JSON.parse(RAW_DATA);
const RAW_MATERIALS = [
'Arrowroot', 'Caffeine',
'Calciferol', 'Calcium Bromate',
'Casein', 'Chlorine',
'Chlorine Dioxide', 'Corn Syrup',
'Dipotassium Phosphate', 'Disodium Phosphate',
'Edible Bone phosphate', 'Extenders',
'Fructose', 'Gelatine',
'H. Veg. protein', 'Invert Sugar',
'Iodine', 'Lactose',
'Niacin/Nicotinic Acid', 'Polysorbate 60',
'Potassium Bromate', 'Sodium Chloride/Salt',
'Sucrose', 'Thiamine',
'Vanillin', 'Yellow 2G'
]
const FORM_DATA = {
docId: 'F-ABC-123',
revisionNumber: 2,
site: 'Jakarta',
timestamp: 'Fri Nov 05 2021'
}
The component has a tabular format, so we lay out the cell values as a nested array and invoke fxl.tableToCells to take care of the relative coordinates.
As for the style, we treat the first label column differently to the second value column. We bake this condition into a holistic styling function, before applying it to every cell in the component:
const plainFormHeader = fxl.tableToCells([
['Document ID:', FORM_DATA.docId],
['Revision Number:', FORM_DATA.revisionNumber],
['Site:', FORM_DATA.site],
['Timestamp', FORM_DATA.timestamp],
]);
function setFormStyle(cell: fxl.Cell): fxl.Cell {
const setBorders = fxl.setAllBorders(fxl.toBorder('medium', 'black'));
if (cell.coord.col == 0) {
return fxl.pipe(
cell,
setBorders,
fxl.setBold(true),
fxl.setHorizontalAlignement('right'),
fxl.setSolidFg('light_gray')
);
} else {
return fxl.pipe(cell, setBorders, fxl.setHorizontalAlignement('center'));
}
}
const formHeader = plainFormHeader.map(setFormStyle);If you are unfamiliar with fxl.pipe and the curried shortcut functions, see fxl.js Shortcut Functions.
Now that we're done with this component, we put it to one side, and forget about them until we need to put together the report later.
|
|
The form footers are similar to the report header. This time we reuse the styling function setFormStyle from before:
const plainCreateFooter = fxl.tableToCells([
['Created By:', undefined],
['Date:', undefined],
]);
const plainCheckFooter = fxl.tableToCells([
['Checked By:', undefined],
['Date:', undefined],
]);
const createFooter = plainCreateFooter.map(setFormStyle);
const checkFooter = plainCheckFooter.map(setFormStyle);The entire inventory table is complex, because it has to present the quantities for each month grouped by quarter, and, in turn, the quantities by quarter must be stacked on top of one another. Even though the entire structure may be complex, we may still break it down into simpler sub-components.
The header may look complex at first glance, but it turns out that we can hard-code most of the table with the exception of the quarter and the month names. Here, we define a function that produces the table header cells with parameterised quarter and month names:
function setHeaderStyle(cell: fxl.Cell): fxl.Cell {
return fxl.pipe(cell, fxl.setBold(true), fxl.setSolidFg('light_gray'));
}
function inventoryHeader(
quarter: string,
months: [string, string, string]
): fxl.Cell[] {
const firstRow = fxl.rowToCells([
quarter,
undefined,
months[0],
undefined,
undefined,
months[1],
undefined,
undefined,
months[2],
undefined,
]);
const secondRow = fxl.rowToCells([
'Raw Material',
'Opening',
'Inflows',
'Outflows',
'Opening',
'Inflows',
'Outflows',
'Opening',
'Inflows',
'Outflows',
]);
return fxl.concatBelow(firstRow, secondRow).map(setHeaderStyle);
}The raw-material column is a one-liner with the same style as the table header:
const rawMaterialColumn = fxl.colToCells(RAW_MATERIALS).map(setHeaderStyle);The inventory for a single month is, at its core, just a table. However, we would like to flag certain quantities that are running low or are projected to run out with different highlighting. Here, we use a styling function that is conditional upon the value of the cell:
function highlightShortage(cell: fxl.Cell): fxl.Cell {
if (typeof cell.value == 'number') {
if (cell.value > 100) {
return cell;
} else if (cell.value > 0) {
return fxl.setSolidFg('yellow')(cell);
} else {
return fxl.pipe(cell, fxl.setSolidFg('red'), fxl.setFontColor('white'));
}
} else {
return cell;
}
}
function setInventoryBodyStyle(cell: fxl.Cell): fxl.Cell {
return fxl.pipe(cell, highlightShortage, fxl.setNumFmt('#,##0'));
}
function singleMonthInventory(month: string): fxl.Cell[] {
const monthsInventory = INVENTORY[month];
const inventoryTable = RAW_MATERIALS.map((name) => [
monthsInventory.opening[name],
monthsInventory.inflows[name],
monthsInventory.outflows[name],
]);
return fxl.tableToCells(inventoryTable).map(setInventoryBodyStyle);
}| Raw-Material Column | Single-Month Inventory |
|---|---|
|
|
Integrating our sub-components into a single-quarter inventory involves concatenating the raw-material column with the inventory table body before putting header on top of the combination:
function setInventoryTableStyle(cell: fxl.Cell): fxl.Cell {
return fxl.pipe(
cell,
fxl.setAllBorders(fxl.toBorder('thin', 'black')),
fxl.applyIfElse(
(x) => x.coord.col == 0 && x.coord.row != 0,
fxl.setHorizontalAlignement('right'),
fxl.setHorizontalAlignement('center')
)
);
}
function singleQuarterInventory(
quarter: string,
months: [string, string, string]
): fxl.Cell[] {
const singleMonthBodies = months.map(singleMonthInventory);
const plain = fxl.concatBelow(
inventoryHeader(quarter, months),
fxl.concatRight(rawMaterialColumn, ...singleMonthBodies)
);
return plain.map(setInventoryTableStyle);
}
The integration to a component of a full-year inventory is straightforward - we stack each quarter's inventory vertically. We also add two-cell paddings below each quarter's inventory for cosmetics reasons:
const inventoryTables = fxl.concatBelow(
fxl.padBelow(2, singleQuarterInventory('Q1', ['Jan', 'Feb', 'Mar'])),
fxl.padBelow(2, singleQuarterInventory('Q2', ['Apr', 'May', 'Jun'])),
fxl.padBelow(2, singleQuarterInventory('Q3', ['Jul', 'Aug', 'Sep'])),
singleQuarterInventory('Q4', ['Oct', 'Nov', 'Dec'])
);We put all of our components together whilst considering the horizontal and vertical padding for cosmetic reasons. We finally automatically set the column width depending on the value of the cell - the maximum width of each column will be used in the final spreadsheet:
const unstyledReport = fxl.concatBelow(
fxl.padBelow(2, formHeader),
fxl.padBelow(2, inventoryTables),
fxl.concatRight(fxl.padRight(2, createFooter), checkFooter)
);
function setAutoColWidth(cell: fxl.Cell): fxl.Cell {
if (cell.value) {
const colWidth = Math.max(cell.value.toString().length, 10);
return fxl.setColWidth(colWidth)(cell);
} else {
return cell;
}
}
const report = unstyledReport.map(setAutoColWidth);| Spreadsheet Components | Page 1 | Page 2 |
|---|---|---|
|
|
|
If we got all the cell objects correct, fxl.writeXlsx should return None, which is a ts-results Option object:
const result = await fxl.writeXlsx(report, 'inventory-report.xlsx');
if (result.some) {
console.log(`Error messages: ${ result.val }`);
}