A 13-part series on the UmimaClean stack. 1 — AdonisJS · 2 — VineJS · 3 — Lucid · 4 — Inertia · 5 — Transformers · 6 — Transmit · 7 — Leaflet · 8 — Midtrans · 9 — Fonnte · 10 — ExcelJS · 11 — Frontend · 12 — Security · 13 — Testing

Every admin screen in this app exports to .xlsx. Six exports, one of them six sheets.

Export features have a reputation as filler — the thing you add because enterprise software has it. That reputation comes from exports nobody uses, and exports go unused for two specific, fixable reasons. This part is about fixing both.

Why Not CSV

CSV is one line of code and the honest default for machine consumption.

The consumer here is a shop owner opening the file in Excel. What they want:

• Currency that looks like currency and sums when selected
• Dates Excel understands, so they sort chronologically rather than alphabetically
• Multiple sheets in one file, because a dashboard export is six related tables
• Column widths that do not require dragging before anything is readable
• Autofilters, because filtering is what they came to do

CSV gives none of it. It cannot even express two of them.

The generated file is what the owner works in. It should arrive usable.

Numbers Must Be Numbers

The single rule that decides whether an export gets used.

/**
 * Number format for money. The cell stays a number so an admin can sum a
 * column in Excel — a pre-formatted `"Rp 30.000"` string cannot be added up.
 */
export const RUPIAH_FORMAT = '"Rp"#,##0'

export const DATE_FORMAT = 'dd/mm/yyyy'
export const DATETIME_FORMAT = 'dd/mm/yyyy hh:mm'

The cell holds 85000. The format makes it display as Rp 85.000.

Write "Rp 85.000" as a string and the file looks identical on screen — and every column is text. It will not sum, will not sort by value, will not chart, and sorts Rp 9.000 above Rp 85.000 because 9 comes after 8 alphabetically.

Nothing announces this. The file opens, the numbers are right, and the first time anyone selects a column Excel reports no total. That is the moment an export stops being trusted.

The type that enforces it:

/**
 * What may land in a cell. `Date` and `number` are passed through as real
 * Excel values rather than text so that sorting and arithmetic work in the
 * spreadsheet; `null` leaves the cell empty rather than printing "null".
 */
export type CellValue = string | number | Date | null

Four types, and null is in there deliberately. String(null) produces "null" in a cell, which looks like data.

The numeric Coercion, Again

The bug this rule actually shipped came from Part 3: Postgres returns numeric columns as JavaScript strings.

/**
 * Postgres hands `numeric` columns back as strings — `order.totalPrice` reads
 * `'85000.00'` at runtime however it is typed — so a price written straight
 * from a model lands in the file as text: no currency format, no sorting by
 * value, and a column that cannot be summed. The screens never notice because
 * their transformers run every amount through `formatRupiah`, which coerces on
 * the way past; the export has no such step, so it coerces here.
 */
export function excelNumber(value: number | string | null | undefined): number | null {
	if (value === null || value === undefined || value === '') {
		return null
	}

	const parsed = Number(value)

	return Number.isFinite(parsed) ? parsed : null
}

The screens were fine because the transformers coerce on the way past. The export had no such step. Same data, different path, different bug — which is the general shape of this class of problem: a coercion that happens to live on one path is not a coercion, it is a coincidence.

Number.isFinite rather than !isNaN catches Infinity as well, and empty string is excluded explicitly because Number('') is 0 — which would write a real zero into a cell that should be blank.

The Timezone Bug

The one that took longest to see, and the one I would warn anyone about before they write their first export.

/**
 * ExcelJS writes a `Date` from its UTC components, so handing it the real
 * instant makes a Jakarta midnight land in the file as 17:00 the day before —
 * every date in the export off by one for anyone east of Greenwich. Rebuilding
 * the value from the local wall clock as if it were UTC is what makes the cell
 * read back the date the admin saw on screen. A spreadsheet cell carries no
 * timezone of its own, so nothing is lost by dropping it here.
 */
export function excelDate(value: DateTime | null | undefined): Date | null {
	if (!value) {
		return null
	}

	return new Date(
		Date.UTC(value.year, value.month - 1, value.day, value.hour, value.minute, value.second)
	)
}

Walk through it.

Jakarta is UTC+7. An order created at midnight local time is 2026-07-28T00:00:00+07:00 — which is 2026-07-27T17:00:00Z.

ExcelJS serialises a Date using its UTC components. So it writes 27 July, 17:00.

Every date in the file is one day early for anything between midnight and 07:00 local. Which is a lot of rows, and — this is the dangerous part — it is consistently wrong. It does not look like corruption. It looks like the data. An owner reconciling a week of orders would find the totals right and the days shifted, and would probably conclude they had misremembered.

The fix reads like a hack and is correct: take the wall clock the admin saw — year, month, day, hour, minute — and build a Date treating those numbers as UTC. ExcelJS then writes exactly those components back out.

A spreadsheet cell has no timezone. It holds a serial number representing a date. There is no offset to preserve, so converting at the boundary loses nothing — and the alternative is a file that disagrees with the screen it came from.

The general principle: when a format has no timezone, convert at the boundary and pick the wall clock the user saw. Same reasoning as toISODate() in the transformers, in the opposite direction.

A Generic Builder That Erases Itself

The workbook builder knows nothing about orders, users, or services. Callers describe columns against their own record type and get back a plain Sheet:

export type Column<T> = {
	header: string
	/** Width in characters. Left off for a sensible default. */
	width?: number
	/** An Excel number format, e.g. `RUPIAH_FORMAT`. Omit for plain text. */
	format?: string
	value: (row: T) => CellValue
}

/**
 * This is where the generic disappears: every caller declares its columns
 * against its own record type and gets back a plain `Sheet`, which is what
 * lets one workbook hold sheets built from six unrelated shapes.
 */
export function sheet<T>(definition: { name: string; columns: Column<T>[]; rows: T[] }): Sheet {
	return {
		name: sheetName(definition.name),
		headers: definition.columns.map((column) => column.header),
		widths: definition.columns.map((column) => column.width ?? DEFAULT_WIDTH),
		formats: definition.columns.map((column) => column.format),
		rows: definition.rows.map((row) => definition.columns.map((column) => column.value(row)))
	}
}

The type parameter exists inside the function and is gone from its return type. Each caller gets full type checking — column.value receives a typed row, and a typo in a field name is a compile error — and the workbook builder receives six Sheet values it can treat identically.

That is the shape I keep coming back to for this kind of problem: generic at the edge, concrete in the middle. The alternative is either a builder that knows about every domain type, or callers casting to any.

The value callback is what makes formatting composable:

value: (order) => excelNumber(order.totalPrice) // real number, RUPIAH_FORMAT
value: (order) => excelDate(order.createdAt) // real date, DATETIME_FORMAT
value: (order) => OrderStatusLabel[order.status] // Indonesian label, plain text

Status is a display string here, unlike on the wire. A spreadsheet is a final rendering, not a payload something else will interpret — nobody writes a formula matching on awaiting_payment.

The Sheet Name Trap

/**
 * Excel rejects sheet names over 31 characters or containing `[]:*?/\`, and
 * fails the whole download rather than the one sheet, so names are cleaned
 * here instead of trusted from the caller.
 */
function sheetName(name: string): string {
	return name.replace(/[[\]:*?/\\]/g, ' ').slice(0, 31)
}

Excel’s limits, not ExcelJS’s. Exceed either and the file is corrupt — the whole download fails, not the one sheet.

Sanitising centrally rather than trusting callers is the right instinct for any constraint where violating it breaks something global. A caller that gets it wrong should produce a slightly odd sheet name, not a broken file.

Sheet Formatting

private addSheet(workbook: ExcelJS.Workbook, definition: Sheet): void {
  const worksheet = workbook.addWorksheet(definition.name)

  worksheet.columns = definition.headers.map((header, index) => ({
    header,
    width: definition.widths[index],
    style: definition.formats[index] ? { numFmt: definition.formats[index] } : undefined,
  }))

  for (const row of definition.rows) {
    worksheet.addRow(row)
  }

  const header = worksheet.getRow(1)
  header.font = { bold: true, color: { argb: 'FFFFFFFF' } }
  header.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF111827' } }
  header.alignment = { vertical: 'middle' }
  header.height = 22

  /**
   * The header stays put while scrolling and carries the dropdowns, so a
   * file of a few hundred orders is something an admin can actually work
   * through rather than just look at.
   */
  worksheet.views = [{ state: 'frozen', ySplit: 1 }]

  if (definition.rows.length > 0) {
    worksheet.autoFilter = {
      from: { row: 1, column: 1 },
      to: { row: definition.rows.length + 1, column: definition.headers.length },
    }
  }
}

Number formats live on the column, not per cell. One assignment instead of one per row, and every future row in that column inherits it.

Frozen header plus autofilter is the pair that makes a file workable. Scrolling past row 40 without a frozen header means guessing what column D was; without autofilters, “show me only the unpaid ones” means writing a formula.

FF111827 is ARGB — alpha first. '111827' alone silently does nothing, which is a fun twenty minutes.

The autofilter is guarded on row count. An empty range on an empty sheet produces a file Excel complains about opening.

Delivery

This service touches the HTTP response, which is the one deliberate exception to the layering rule from Part 1:

/**
 * This is the one service that touches the HTTP response, because producing a
 * file download is the whole job — splitting the buffer out from the headers
 * that make a browser save it would only spread three lines across six
 * controllers.
 */
async download(response: HttpContext['response'], name: string, sheets: Sheet[]): Promise<void> {
  const buffer = await this.build(sheets)

  response.header('Content-Type', XLSX_MIME)
  response.header('Content-Disposition', `attachment; filename="${this.filename(name)}"`)
  /**
   * The file is generated fresh on every request and is often the same URL
   * with the same filters, so a cached copy would quietly hand an admin
   * yesterday's numbers.
   */
  response.header('Cache-Control', 'no-store')

  response.send(buffer)
}

The no-store header is the non-obvious one. The export URL for a given screen and filter set is stable, so a browser or proxy is entitled to cache it — and a cached export is yesterday’s numbers presented as today’s, with nothing on the file to say so.

const XLSX_MIME = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

That string is the MIME type Excel, LibreOffice, and Google Sheets all recognise. Get it wrong and the browser saves something the OS opens with the wrong application.

/**
 * The name the browser saves the file under, stamped with the moment it was
 * taken so that two exports of the same screen never overwrite each other in
 * the downloads folder.
 */
filename(name: string): string {
  const stamp = DateTime.now().toFormat('yyyyLLdd-HHmm')

  return `umimaclean-${name}-${stamp}.xlsx`
}

umimaclean-orders-20260728-1430.xlsx. Sortable, unambiguous, and it does not become orders (3).xlsx.

The Export Must Not Stop at the Page

The second reason exports go unused, and it is a rule about the query rather than the file.

An export carries the current filters — status, type, search term, date range — because the user has just spent time narrowing to what they want.

It never stops at the current page. They are looking at page 1 of 8; the file must contain all 8.

async getStuckOrders(filters: Filters): Promise<ModelPaginatorContract<Order>> {
  return this.stuckOrdersQuery(filters).paginate(filters.page, 10)
}

/**
 * The same backlog, unpaginated, for the spreadsheet export — the list an
 * admin takes to the bank statement to work out which payments really did
 * arrive.
 */
async getStuckOrdersForExport(filters: Filters): Promise<Order[]> {
  return this.stuckOrdersQuery(filters).limit(EXPORT_ROW_LIMIT)
}

One shared query builder, two consumers. The screen paginates; the export takes the lot. Sharing the builder is what stops the two disagreeing about what “the current filters” mean — which is the bug where the file quietly contains different rows than the screen.

The cap is a safety net, not a policy:

/**
 * An admin asking for "all orders" wants a file they can open, not the whole
 * table streamed into memory and then into a browser download. The cap is far
 * above what this shop produces in a year, so in practice it only ever bites
 * on a runaway query.
 */
export const EXPORT_ROW_LIMIT = 5000

The workbook is built entirely in memory and sent as one buffer. That is fine at 5,000 rows and would not be at 500,000 — ExcelJS has a streaming writer for that case, and using it here would be complexity in exchange for nothing.

The Six Exports

The multi-sheet ones are why .xlsx rather than CSV. A dashboard export as CSV is six files, and the relationship between them is in the filenames.

Each has a real workflow behind it. The reconciliation export exists because the actual task is sitting with a bank statement and this list side by side. The revenue report exists because the owner wants to sort services by revenue in a way the screen does not offer.

An export is a screen the user gets to keep and manipulate. Once that is the frame, “numbers must be numbers” stops being a detail and becomes the entire requirement.

What I Would Change

No streaming. Correct at this scale, and the ceiling is real.

Column definitions live in each controller. Six sets of column arrays, and the order columns and the reconciliation columns overlap substantially. A shared orderColumns() would remove the drift risk.

Export generation is synchronous in the request. A large export holds a connection while building. At this size it is milliseconds; at a hundred times the data it wants a job and a download link — which would mean adding the queue Part 1 deliberately does not have.

No tests on the file contents. The functional tests assert an export returns the right content type and a non-empty body. Nothing opens the workbook and checks that a currency cell is numeric — which is precisely the bug that shipped. Parsing the buffer back with ExcelJS and asserting on cell types would be a genuinely valuable ten-line test.

Next: Part 11 — Tailwind 4, Base UI, and Recharts, where the config file disappears and the components are copied rather than installed.