DOM table ingress

utils.table_to_sheet(element, opts), utils.table_to_book(element, opts) and utils.sheet_add_dom(ws, element, opts) read an HTML <table> element and its CSS. They need a DOM. Use a browser, or jsdom in Node.

In a browser:

<table id="report">
  <tr><th>Item</th><th>Qty</th></tr>
  <tr><td>Bolt</td><td>10</td></tr>
</table>
<button id="download">Download</button>
<script src="xlsx.full.min.js"></script>
<script>
  document.getElementById("download").addEventListener("click", function () {
    var wb = XLSX.utils.table_to_book(document.getElementById("report"), { sheet: "Report" });
    XLSX.writeFile(wb, "report.xlsx", { cellStyles: true });
  });
</script>

Copy dist/xlsx.full.min.js from the package to your web server next to this page.

Use jsdom in Node

Install jsdom in your project. Give the library getComputedStyle as a global so that CSS is seen.

const { JSDOM } = require("jsdom");
const XLSX = require("@agent-sheet/wasm");
const dom = new JSDOM(`<table id="t"><tr>
  <td style="font-weight:700;color:#ff0000;background-color:#00ff00;text-align:right">bold red</td>
  <td>normal, <b>this part is bold</b></td></tr></table>`);
global.getComputedStyle = dom.window.getComputedStyle;
const ws = XLSX.utils.table_to_sheet(dom.window.document.getElementById("t"));
console.log(ws.A1.s.bold, ws.A1.s.alignment.horizontal, ws.B1.R.length);

Output

true right 2

jsdom computes nested inheritance imperfectly. Prefer explicit classes or inline styles over deeply nested tags. Add TD, TH { vertical-align: middle; } so that alignment is read.

Page CSS for a faithful import

TABLE { border-collapse: collapse; }   /* Excel draws shared borders */
* { box-sizing: border-box; }          /* width and height attributes are read as such */
TH, TD { vertical-align: middle; }     /* set vertical alignment explicitly */

Options

Option	Meaning
origin	start cell: an A1 string, a cell object, a row number (zero-based, first column) or -1 to append after the last row. The default is A1
borders	read cell borders (ignored by default)
rawDates	do not try to parse text as dates. JavaScript’s Date.parse accepts surprising text
raw, display, sheetRows, UTC, dateNF	keep raw text, use displayed text, limit rows, date handling
sheet	sheet name (table_to_book only)

Which CSS is read

CSS on the <td> is read. If the cell contains one <span> with text, the text styles of the span are used for the cell.

Excel feature	CSS read
bold, italic	font-weight: bold or 700; font-style: italic
underline, strike	text-decoration: underline or line-through
text color	color (name or RGB/RGBA)
font name, size	the first font-family; font-size in px or pt
subscript, superscript	vertical-align: sub or super (put these on a <span> and use font-size: .83em)
horizontal, vertical alignment	text-align; vertical-align (top, middle, bottom)
fill	background-color
column width, row height	width on cells that are not merged; height on the <tr>

Rich text. A cell with several child nodes becomes a list of rich-text runs in R. The cell-level styles still come from the <td>.

Other rules. Text with a newline (a <br>, or white-space: pre) turns on wrapping. Elements with aria-hidden="true" are dropped, so mark icon fonts with it. Numbers and dates are detected in the text.

Append several tables to one sheet

const { JSDOM } = require("jsdom");
const XLSX = require("@agent-sheet/wasm");
const dom = new JSDOM(`<table id="a"><tr><td>1</td></tr></table><table id="b"><tr><td>2</td></tr></table>`);
global.getComputedStyle = dom.window.getComputedStyle;
const doc = dom.window.document;
function gap(ws, n) {                       // leave n empty rows
  const ref = XLSX.utils.decode_range(ws["!ref"]);
  ref.e.r += n;
  ws["!ref"] = XLSX.utils.encode_range(ref);
}
const ws = XLSX.utils.table_to_sheet(doc.getElementById("a"));
gap(ws, 1);
XLSX.utils.sheet_add_dom(ws, doc.getElementById("b"), { origin: -1 });
console.log(ws["!ref"], ws.A1.v, ws.A3.v);

Output

A1:A3 1 2

Colors are numbers

The reader produces colors as numbers, for example { rgb: 16711680 }.

The writer accepts numeric RGB values. You can optionally convert them to six-digit hex strings for a consistent color representation.

const XLSX = require("@agent-sheet/wasm");
function hexColors(ws) {
  const fix = (o) => {
    for (const k in o) {
      if (k === "rgb" && typeof o[k] === "number") o[k] = o[k].toString(16).padStart(6, "0").toUpperCase();
      else if (o[k] && typeof o[k] === "object") fix(o[k]);
    }
  };
  for (const a in ws) if (a[0] !== "!") {
    if (ws[a].s) fix(ws[a].s);
    if (ws[a].R) fix(ws[a].R);
  }
}
const ws = XLSX.utils.aoa_to_sheet([["red"]]);
ws.A1.s = { color: { rgb: 16711680 } };
hexColors(ws);
console.log(ws.A1.s.color.rgb);

Output

FF0000

Without a DOM

utils.html_to_rs parses <b> and <i> snippets without a DOM. An HTML string can also be read with XLSX.read(html, { type: "string" }). That gives a sheet from its <table>.

const XLSX = require("@agent-sheet/wasm");
const html = "<table><tr><th>Item</th><th>Qty</th></tr><tr><td>bolt</td><td>10</td></tr></table>";
const wb = XLSX.read(html, { type: "string" });
console.log(XLSX.utils.sheet_to_json(wb.Sheets.Sheet1));

Output

[ { Item: 'bolt', Qty: 10 } ]