in Code

Formatting currency columns in tables with underscore.zip and accounting.js

This article runs through and demonstrates using accounting.js and underscore.js to format columns of numbers inside tables. It’s not complicated, and you might find it interesting, but it’s really a specific solution aimed at a common problem.

So. Probably the most common usage example for formatting accounting columns is inside tables, for example:

EXPENSES
Hair Net                $   (1.50) « symbols and units lined up
Treatment Creme         $  (12.99)
Cut and Finish          $ (125.00)

This table is easily templated from an array of arrays (a ‘matrix’), each array representing a row (<tr>) in the table, each value representing a cell (<td>) – like so:

// Each nested array represents a <tr>, each value a <td>
var expenseReport = [
	["Hair Net", -1.5],
	["Treatment Creme", -12.99],
	["Cut and Finish", -125]
];
// Example templated markup:
<table><tbody><tr><td>Hair Net</td><td>-1.5</td>

The problem here is that each of these arrays represents a row, each containing a single one of the values you need – but if you want to column-format those numbers with accounting.js’s formatColumn() method, you’ll need to have those values in an array by themselves, so that you can do this:

var values = [-1.5, -12.99, -125];
accounting.formatColumn(values);
// ["$   (1.50)", "$  (12.99)", "$ (125.00)"]

Hence you need a matrix where each nested array represents a vertical column in the table, like this:

// Each nested array is now a hypothetical 'column' in the table:
var expenseReport = [
	["Hair Net", "Treatment Creme", "Cut and Finish"],
	[1.5, 12.99, 125] // we gots the values!
];

So what you effectively need to do is flip around that matrix of values, so that you have access to all those numbers without having to pluck out the second value from each row manually.

This is a process known as transposing a matrix, and it is exactly this that underscore.js performs with _.zip(). From the underscore.js docs (emphasis added):

zip   _.zip(*arrays)

Merges together the values of each of the arrays with the values at the corresponding position. Useful when you have separate data sources that are coordinated through matching array indexes. If you’re working with a matrix of nested arrays, zip.apply can transpose the matrix in a similar fashion.

_.zip(['moe', 'larry', 'curly'], [30, 40, 50], [true, false, false]);
=> [["moe", 30, true], ["larry", 40, false], ["curly", 50, false]]

Obviously it’s unfeasible to pass in each row of the table manually (e.g. _.zip(row1, row2, row3, ...) so you need to use _.zip.apply([], matrix), which will call _.zip with the matrix’s nested arrays (table rows) as arguments, returning a transposed matrix where all the values are swapped into equivalent positions.

Example of matrix transposition

I threw together a little jsFiddle demo to show what this does. Click the button a few times.

Not loading? View it on jsFiddle here.

The process, step-by-step:

If you have a table represented by a matrix (array of arrays) in a javascript variable, where each array represents a table row, you could use this example code to apply accounting.formatColumn() to a column of values at the 2nd index (row[1]):

// The original matrix:
var expenseReport = [
	["Hair Net", -1.5],
	["Treatment Creme", -12.99],
	["Cut and Finish", -125]
];

// Options for accounting.js (see docs)
var options = {
	symbol: "HK$",
	precision: 0,  
	format: {
		pos : "%s %v",   // for positive values, eg. "$ 1.00"
		neg : "%s (%v)", // for negative values, eg. "$ (1.00)"
		zero : "%s -- "  // for zero values, eg. "$ --"
	}
};

// Transpose the matrix to access the columns as arrays:
expenseReport = _.zip.apply([], expenseReport);

// Format the column of values (at position [1]):
expenseReport[1] = accounting.formatColumn(expenseReport[1], options);

// Transpose the matrix back to original positions:
expenseReport = _.zip.apply([], expenseReport);

Done! The expenseReport matrix now looks like this – an array of rows, ready to be templated into an HTML table:

[
  [
    "Hair Net",
    "HK$   (2)"
  ],
  [
    "Treatment Creme",
    "HK$  (13)"
  ],
  [
    "Cut and Finish",
    "HK$ (125)"
  ]
]

This same logic applies to big tables of data, too – once you’ve used _.zip to access the table’s columns as arrays, you can use formatColumn on as many of them as you need to, with as many different configurations as you like.

The next step

I’m also hoping to implement this into accounting.js somehow, if there’s demand for it. Something like formatTable, although it could get complicated, so it’d probably be implemented as an additional plugin, maybe for jayQwery or the awesome DataTables plugin. Heck, look at me, I’m ramblin’ again.

Comment up if you have questions, comments or personal problems you’d like to discuss.

Write a Comment

Comment