Sheets in Drash

Drash is a Deno server-side framework. The body parser works in memory and can handle file uploads on hosted services.

SheetJS is a JavaScript library for reading and writing data from spreadsheets.

This demo uses Drash and SheetJS to read and write data. We'll explore how to parse uploaded files in a POST request handler and respond to GET requests with downloadable spreadsheets.

The "Complete Example" section includes a complete server.

Tested Deployments

This demo was tested in the following deployments:

Drash	Deno	Date
2.8.1	1.44.1	2024-12-19
2.8.1	2.1.4	2024-12-19

Integration Details

The SheetJS Deno module can be imported from Drash server scripts.

Reading Data

Request#bodyParam reads body parameters. For uploaded files, the content property is a Uint8Array which can be parsed with the SheetJS read method¹.

This example server responds to POST requests. The server will look for a file in the request body under the "upload" key. If a file is present, the server will parse the file, generate an HTML table using the sheet_to_html method² and respond with the HTML code:

// @deno-types="https://cdn.sheetjs.com/xlsx-0.20.3/package/types/index.d.ts"
import { read, utils } from 'https://cdn.sheetjs.com/xlsx-0.20.3/package/xlsx.mjs';

import * as Drash from "https://cdn.jsdelivr.net/gh/drashland/[email protected]/mod.ts";

class ParseResource extends Drash.Resource {
  public paths = ["/"];

  public POST(request: Drash.Request, response: Drash.Response) {
    // assume a form upload like <input type="file" id="upload" name="upload">
    const file = request.bodyParam<Drash.Types.BodyFile>("upload");
    if (!file) throw new Error("File is required!");

    // read file
    var wb = read(file.content);

    // respond with HTML from first worksheet
    var ws = wb.Sheets[wb.SheetNames[0]];
    var html = utils.sheet_to_html(html)
    return response.html(html);
  }
}

Writing Data

Headers are manually set with Response#headers.set while the raw body is set with Response#send. The raw body can be Uint8Array or ArrayBuffer objects.

Given a SheetJS workbook object, the write method using type: "buffer"³ generates data objects compatible with Drash.

This example server responds to GET requests. The server will generate a SheetJS worksheet object from an array of arrays⁴, build up a new workbook using the book_new⁵ and book_append_sheet⁶ utility methods, generate a XLSX file using write, and send the file with appropriate headers to initiate a download with file name "SheetJSDrash.xlsx":

// @deno-types="https://cdn.sheetjs.com/xlsx-0.20.3/package/types/index.d.ts"
import { utils, write } from 'https://cdn.sheetjs.com/xlsx-0.20.3/package/xlsx.mjs';

import * as Drash from "https://cdn.jsdelivr.net/gh/drashland/[email protected]/mod.ts";

class WriteResource extends Drash.Resource {
  public paths = ["/export"];

  public GET(request: Drash.Request, response: Drash.Response): void {
    // create some fixed workbook
    const data = ["SheetJS".split(""), [5,4,3,3,7,9,5]];
    const ws = utils.aoa_to_sheet(data);
    const wb = utils.book_new(); utils.book_append_sheet(wb, ws, "data");
  
    // write the workbook to XLSX as a Uint8Array
    const file = write(wb, { bookType: "xlsx", type: "buffer"});
    // set headers
    response.headers.set("Content-Disposition", 'attachment; filename="SheetJSDrash.xlsx"');
    // send data
    return response.send("application/vnd.ms-excel", file);
  }
}

Complete Example

1. Download SheetJSDrash.ts:

curl -LO https://docs.sheetjs.com/drash/SheetJSDrash.ts

2. Run the server:

deno run --allow-net SheetJSDrash.ts

Deno 2 requires the --allow-import entitlement:

deno run --allow-net --allow-write --allow-import SheetJSDenoDOM.ts

3. Download the test file https://docs.sheetjs.com/pres.numbers

4. Open http://localhost:7262/ in your browser.

Click "Choose File" and select pres.numbers. Then click "Submit"

The page should show the contents of the file as an HTML table.

5. Open http://localhost:7262/export in your browser.

The page should attempt to download SheetJSDrash.xlsx . Open the new file.

Footnotes

1. See read in "Reading Files" ↩

2. See sheet_to_html in "Utilities" ↩

3. See write in "Writing Files" ↩

4. See aoa_to_sheet in "Utilities" ↩

5. See book_new in "Utilities" ↩

6. See book_append_sheet in "Utilities" ↩