Sheets in Elysia

Elysia is a BunJS server-side framework.

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

This demo uses ElysiaJS 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 last tested on 2024 March 11 with ElysiaJS 0.8.17 and BunJS 1.0.30.

Integration Details

The SheetJS BunJS module can be imported from ElysiaJS server scripts.

Reading Data

The ElysiaJS body parser accepts a schema¹. The t.File method marks that a field is expected to be a file.

The following schema indicates the field upload should be a submitted file:

{
  body: t.Object({
    upload: t.File()
  })
}

ElysiaJS exposes the file as a Blob object. The Blob#arrayBuffer method returns a Promise that resolves to an ArrayBuffer. That ArrayBuffer 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:

import { Elysia, t } from "elysia";
import { read, utils } from "xlsx";

const app = new Elysia();
app.post("/", async({ body: { upload } }) => {
  const data = await upload.arrayBuffer();
  const wb = read(data);
  return utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]);
}, {
  body: t.Object({
    upload: t.File()
  })
});
app.listen(3000);

Writing Data

Given a SheetJS workbook object, the write method using type: "buffer"⁴ generates data objects which can be passed to the BunJS File constructor. The File constructor accepts a second parameter for the generated file name.

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 SheetJSElysia.xlsx:

import { Elysia } from "elysia";
import { utils, write } from "xlsx";

const app = new Elysia();
app.get("/", () => {
  var ws = utils.aoa_to_sheet(["SheetJS".split(""), [5,4,3,3,7,9,5]]);
  var wb = utils.book_new(); utils.book_append_sheet(wb, ws, "Data");
  /* generate buffer */
  var buf = write(wb, {type: "buffer", bookType: "xlsx"});
  return new File([buf], "SheetJSElysia.xlsx");
});
app.listen(3000);

Complete Example

1. Create a new ElysiaJS project:

bun create elysia sheetjs-elysia
cd sheetjs-elysia

2. Install the SheetJS BunJS module:

bun install https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

3. Save the following script to src/SheetJSElysia.ts:

src/SheetJSElysia.ts

import { Elysia, t } from "elysia";
import { read, utils, write } from "xlsx";

const app = new Elysia();
app.get("/", () => {
  var ws = utils.aoa_to_sheet(["SheetJS".split(""), [5,4,3,3,7,9,5]]);
  var wb = utils.book_new(); utils.book_append_sheet(wb, ws, "Data");
  /* generate buffer */
  var buf = write(wb, {type: "buffer", bookType: "xlsx"});
  return new File([buf], "SheetJSElysia.xlsx");
  /* set headers */
});
app.post("/", async({ body: { upload } }) => {
  const data = await upload.arrayBuffer();
  const wb = read(data);
  return utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]);
}, {
  body: t.Object({
    upload: t.File()
  })
});
app.listen(3000);

4. Run the server:

bun run src/SheetJSElysia.ts

5. Test POST requests using https://docs.sheetjs.com/pres.numbers . The commands should be run in a new terminal window:

curl -LO https://docs.sheetjs.com/pres.numbers
curl -X POST -F upload=@pres.numbers http://localhost:3000/

6. Test GET requests by opening http://localhost:3000/ in your browser.

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

Footnotes

1. See "Explicit Body" in the ElysiaJS documentation. ↩

2. See read in "Reading Files" ↩

3. See sheet_to_html in "Utilities" ↩

4. See write in "Writing Files" ↩

5. See aoa_to_sheet in "Utilities" ↩

6. See book_new in "Utilities" ↩

7. See book_append_sheet in "Utilities" ↩