Spreadsheets (xlsx)

const std = @import("std");

// Builds nothing to run: `zig build test` is all there is. See README.md.
pub fn build(b: *std.Build) void {
    const target = b.standardTargetOptions(.{});
    const optimize = b.standardOptimizeOption(.{});

    const spider_dep = b.dependency("spider", .{
        .target = target,
        .optimize = optimize,
        // The parts of Spider an app asks for by name.
        .xlsx = true,
        .qrcode = true,
        .r2 = true,
    });
    const spider_mod = spider_dep.module("spider");
    spider_mod.addImport("spider_config", b.createModule(.{
        .root_source_file = b.path("spider.config.zig"),
        .imports = &.{.{ .name = "spider", .module = spider_mod }},
    }));

    const tests = b.addTest(.{ .root_module = b.createModule(.{
        .root_source_file = b.path("src/tests.zig"),
        .target = target,
        .optimize = optimize,
        .imports = &.{.{ .name = "spider", .module = spider_mod }},
    }) });
    b.step("test", "Run the examples").dependOn(&b.addRunArtifact(tests).step);

    // Signing in at Keycloak needs a Keycloak: a step of its own, run by
    // login-test.sh, which starts one for the occasion.
    const login_tests = b.addTest(.{ .root_module = b.createModule(.{
        .root_source_file = b.path("login/tests.zig"),
        .target = target,
        .optimize = optimize,
        .imports = &.{.{ .name = "spider", .module = spider_mod }},
    }) });
    b.step("test-login", "Run the login examples (needs login/docker-compose.yml up)").dependOn(&b.addRunArtifact(login_tests).step);
}

const Order = struct { customer: []const u8, items: i64, total: f64 };

const orders = [_]Order{
    .{ .customer = "Ana Ribeiro", .items = 3, .total = 149.70 },
    .{ .customer = "Bo Larsen", .items = 1, .total = 49.90 },
    .{ .customer = "Cy Okafor", .items = 12, .total = 598.80 },
};

/// The orders as an Excel file the browser saves.
fn exportOrders(c: *spider.Ctx) !spider.Response {
    const book = try xlsx.Workbook.init(c.arena);
    defer book.deinit();

    const sheet = try book.addSheet("Orders");
    try sheet.setColumnWidth(0, 28);

    // Rows and columns count from zero: this is the first row.
    const heading: xlsx.Style = .{ .bold = true, .fill = 0xDDEEFF, .border = .thin };
    try sheet.setRow(0, 0, &.{ .{ .text = "Customer" }, .{ .text = "Items" }, .{ .text = "Total" } }, heading);

    for (orders, 1..) |order, row| {
        const r: u32 = @intCast(row);
        try sheet.set(r, 0, .{ .text = order.customer });
        try sheet.set(r, 1, .int(order.items));
        try sheet.setStyled(r, 2, .{ .number = order.total }, .{ .number_format = .thousands_decimal });
    }

    // The heading stays in view while the rows scroll, with filter buttons.
    try sheet.freeze(1, 0);
    try sheet.setAutoFilter(.{ .first_row = 0, .first_col = 0, .last_row = orders.len, .last_col = 2 });

    return c.download(try book.toOwnedSlice(c.arena), .{
        .filename = "orders.xlsx",
        .content_type = spider.content_types.xlsx,
    });
}

test "xlsx: the export is a file Excel opens, and reading it back gives the same rows" {
    const app = try spider.testing.start(run);
    var res = try app.get("/orders.xlsx");
    defer res.deinit();
    try res.expectStatus(200);
    try res.expectHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    try res.expectHeader("Content-Disposition", "attachment; filename=\"orders.xlsx\"");

    const book = try spider.xlsx.Reader.open(std.testing.allocator, res.body, .{});
    defer book.deinit();
    try std.testing.expectEqualStrings("Orders", book.sheets()[0].name);

    const rows = try book.rows(0, .{});
    defer rows.deinit();

    const heading = (try rows.next()).?;
    try std.testing.expectEqualStrings("Customer", heading.cells[0].value.text);

    const first = (try rows.next()).?;
    try std.testing.expectEqual(@as(u32, 1), first.number);
    try std.testing.expectEqualStrings("Ana Ribeiro", first.cells[0].value.text);
    try std.testing.expectEqual(@as(f64, 3), first.cells[1].value.number);
    try std.testing.expectEqual(@as(f64, 149.70), first.cells[2].value.number);
    // A number carries the format it is shown with.
    try std.testing.expectEqualStrings("#,##0.00", first.cells[2].format);
}

/// Reads a spreadsheet someone uploaded: a heading, then one order per row.
fn importOrders(c: *spider.Ctx) !spider.Response {
    const form = try c.parseMultipart();
    const files = form.getFile("sheet") orelse return c.text("Choose a file.", .{ .status = .unprocessable_entity });

    // The default limits are for files from strangers: size, rows, and
    // how far a small file may expand.
    const book = xlsx.Reader.open(c.arena, files[0].data, .{}) catch {
        return c.text("That is not a spreadsheet this app can read.", .{ .status = .unprocessable_entity });
    };
    defer book.deinit();

    const rows = try book.rows(0, .{});
    defer rows.deinit();

    var count: usize = 0;
    var items: f64 = 0;
    while (try rows.next()) |row| {
        if (row.number == 0) continue; // the heading
        var customer: []const u8 = "";
        var quantity: f64 = 0;
        // A row only has the cells that were filled: go by `cell.column`,
        // not by position.
        for (row.cells) |cell| switch (cell.column) {
            0 => switch (cell.value) {
                .text => |text| customer = text,
                else => {},
            },
            1 => switch (cell.value) {
                .number => |n| quantity = n,
                else => {},
            },
            else => {},
        };
        if (customer.len == 0) continue;
        count += 1;
        items += quantity;
    }
    return c.json(.{ .orders = count, .items = items }, .{});
}

/// A form with one file, as a browser sends it.
fn upload(arena: std.mem.Allocator, bytes: []const u8) ![]const u8 {
    return std.mem.concat(arena, u8, &.{
        "--edge\r\nContent-Disposition: form-data; name=\"sheet\"; filename=\"orders.xlsx\"\r\n\r\n",
        bytes,
        "\r\n--edge--\r\n",
    });
}

const multipart = &.{"Content-Type: multipart/form-data; boundary=edge"};

test "xlsx: an uploaded spreadsheet is read row by row" {
    const app = try spider.testing.start(run);
    var arena_state: std.heap.ArenaAllocator = .init(std.testing.allocator);
    defer arena_state.deinit();

    var exported = try app.get("/orders.xlsx");
    defer exported.deinit();

    var res = try app.request(.{ .method = "POST", .target = "/orders/import", .headers = multipart, .body = try upload(arena_state.allocator(), exported.body) });
    defer res.deinit();
    try res.expectStatus(200);
    try res.expectContains("\"orders\":3");
    try res.expectContains("\"items\":16");
}

test "xlsx: a file that is not a spreadsheet is refused, not read" {
    const app = try spider.testing.start(run);
    var arena_state: std.heap.ArenaAllocator = .init(std.testing.allocator);
    defer arena_state.deinit();

    var res = try app.request(.{ .method = "POST", .target = "/orders/import", .headers = multipart, .body = try upload(arena_state.allocator(), "name,items\nAna,3\n") });
    defer res.deinit();
    try res.expectStatus(422);
    try res.expectContains("That is not a spreadsheet this app can read.");
}