1. Spreadsheet docs
  2. Developers

Workbooks in Node

Create, fill, calculate and save xlsx workbooks on a server with createWorkbook, with no page and no browser.

createWorkbook() makes a workbook with no page. It has the same model, formulas, styles, tables, names and undo as a mounted one, and runs in Node with no browser and no native modules. Use it to build reports and exports on a server, to fill a template's cells and calculate them, or to test the code that drives your embedded workbooks.

The React, Vue and Angular components don't apply here; this page uses @8sheets/core directly.

Build an xlsx file

Script

// report.mjs: node report.mjs
import { createWriteStream } from 'node:fs';
import { once } from 'node:events';
import { createWorkbook } from '@8sheets/core';

const wb = createWorkbook({ theme: 'Lagoon' });
const sheet = wb.activeSheet;
sheet.name = 'Sales';
sheet.range('A1').values = [
  ['Region', 'Units', 'Price', 'Revenue'],
  ['North', 120, 2.5, null],
  ['South', 80, 3.25, null],
  ['West', 45, 4, null],
];
const table = sheet.addTable('A1:D4', { name: 'Sales', style: 'TableStyleMedium2' });
sheet.range('D2:D4').formulas = '=[@Units]*[@Price]';
table.setOption('totalsRow', true);
sheet.range('C2:D5').style = { numFmt: '#,##0.00' };
sheet.setColWidth('A', 'D', 90);
await wb.whenCalculated();

const file = createWriteStream('sales.xlsx');
await wb.saveXlsx({
  async write(bytes) {
    if (!file.write(Buffer.from(bytes))) await once(file, 'drain');   // respect backpressure
  },
});
file.end();
await once(file, 'finish');

HTTP server

// server.mjs: GET /report.xlsx returns a fresh workbook
import { createServer } from 'node:http';
import { once } from 'node:events';
import { createWorkbook } from '@8sheets/core';

async function buildReport(rows) {
  const wb = createWorkbook();
  const sheet = wb.activeSheet;
  sheet.range('A1').values = [['Customer', 'Amount'], ...rows.map((r) => [r.customer, r.amount])];
  sheet.range(`B${rows.length + 2}`).formulas = `=SUM(B2:B${rows.length + 1})`;
  sheet.range('A1:B1').style = { bold: true };
  return wb;
}

createServer(async (req, res) => {
  if (req.url !== '/report.xlsx') return res.writeHead(404).end();
  const wb = await buildReport([{ customer: 'ACME', amount: 1200 }, { customer: 'Globex', amount: 830 }]);
  res.writeHead(200, {
    'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    'Content-Disposition': 'attachment; filename="report.xlsx"',
  });
  await wb.saveXlsx({
    async write(bytes) {
      if (!res.write(Buffer.from(bytes))) await once(res, 'drain');
    },
  });
  res.end();
}).listen(3000);

saveXlsx waits for calculation, so the saved file carries current values and opens with results already shown, in 8sheets and in Excel. The file is written in chunks through the sink, so a large workbook streams out rather than being built in memory first.

Options

createWorkbook();                       // one empty sheet
createWorkbook({ sheets: 3 });          // three sheets
createWorkbook({ theme: 'Fjord' });     // a gallery theme, or one from wb.theme.build
createWorkbook({ data: savedJson });    // from wb.toJSON() output, made in a browser or in Node

Everything on Working with data works the same: sheets, ranges, values, input, formulas, styles, number formats, names, tables, sorting, filtering, validation, conditional formatting, notes, protection, charts, undo and the bulk paths. Model events such as cells-changed and calc-end fire too, so listener code can be tested in Node.

Other outputs

const json = wb.toJSON();                               // plain data for your database
const doc = await wb.saveDocument();                    // a .8sh document as a Blob
const html = await wb.rangeToHtml('A1:D5');             // an HTML table for an email
const pdf = [];
await wb.activeSheet.print.toPdf({ write: async (b) => { pdf.push(Buffer.from(b)); } });

Limits

  • Reading xlsx and CSV needs a mounted workbook. openFile runs in the page's Worker, so in Node it throws "Opening a file needs a mounted workbook". A workbook can come into Node as JSON (createWorkbook({ data })), or as a .8sh document opened with openDocument, unless that document was saved from a workbook opened from a file or holding PivotTables or data sheets: such a document carries an xlsx package inside, which again needs a page.
  • PDF in Node lays out the cells, with their styles and page setup. Charts, shapes and pictures are drawn into PDFs only by a mounted workbook.
  • Options for the screen (parts, scroll bars, tabs and so on) don't apply. setOptions on a headless workbook takes chrome (stored) and theme, and throws a TypeError for the rest.
  • Types. The declarations name DOM types, because the same API mounts in a page; a project that checks library types (skipLibCheck: false) needs "DOM" in lib.