write custom table

[expdts]

How to make a table with styles from the sample data using writeXlsx and if possible writeOds?

[
	{
	"name": "List A",
	"list": [
		{
			"name": "A",
			"color": ["red", "yellow", "black"],
			"sum": 10
		},
		{
			"name": "A2",
			"color": ["green", "grey"],
			"sum": 5
		}
	]
	},
	{
	"name": "List B",
	"list": [
		{
			"name": "B",
			"color": ["orange", "black"],
			"sum": 5
		},
		{
			"name": "B2",
			"color": ["yellow", "white"],
			"sum": 15
		}
	]
	}
]

[productdevbook]

Here is a complete version. I ran this against the current build and checked both files read back, so it is not pseudocode.

The shape of the problem is that your data is grouped — two levels — and a sheet is flat. So build the rows first, remembering which row indexes are group titles and which are totals, then style those by index.

import { writeXlsx, writeOds } from "hucre"

const data = [
  { name: "List A", list: [
      { name: "A",  color: ["red", "yellow", "black"], sum: 10 },
      { name: "A2", color: ["green", "grey"],          sum: 5 },
  ]},
  { name: "List B", list: [
      { name: "B",  color: ["orange", "black"],  sum: 5 },
      { name: "B2", color: ["yellow", "white"],  sum: 15 },
  ]},
]

// 1. Flatten, keeping track of which rows are special.
const rows: (string | number | null)[][] = [["Name", "Color", "Sum"]]
const groupRows: number[] = []
const totalRows: number[] = []

for (const group of data) {
  groupRows.push(rows.length)
  rows.push([group.name, null, null])

  for (const item of group.list) {
    rows.push([item.name, item.color.join(", "), item.sum])
  }

  totalRows.push(rows.length)
  rows.push(["Total", null, group.list.reduce((n, i) => n + i.sum, 0)])
}

// 2. Styles, applied per cell by row index.
const header = {
  font: { bold: true, color: { rgb: "FFFFFF" } },
  fill: { type: "pattern", pattern: "solid", fgColor: { rgb: "4472C4" } },
  alignment: { horizontal: "center" },
} as const
const groupTitle = {
  font: { bold: true },
  fill: { type: "pattern", pattern: "solid", fgColor: { rgb: "D9E1F2" } },
} as const
const total = { font: { bold: true }, numFmt: "#,##0" } as const

const cells = new Map()
for (let c = 0; c < 3; c++) cells.set(`0,${c}`, { value: rows[0][c], style: header })
for (const r of groupRows)
  for (let c = 0; c < 3; c++) cells.set(`${r},${c}`, { value: rows[r][c], style: groupTitle })
for (const r of totalRows)
  for (let c = 0; c < 3; c++) cells.set(`${r},${c}`, { value: rows[r][c], style: total })

// 3. One sheet definition, both formats.
const sheet = {
  name: "Lists",
  rows,
  cells,
  columns: [{ width: 14 }, { width: 28 }, { width: 10 }],
  freezePane: { rows: 1 },
  merges: groupRows.map((r) => `A${r + 1}:C${r + 1}`), // group title spans the width
  autoFilter: { range: "A1:C1" },
}

const xlsx = await writeXlsx({ sheets: [sheet] })
const ods = await writeOds({ sheets: [sheet] })

Two things worth knowing rather than finding out:

cells and rows are both needed, and they overlap. rows is the grid; cells carries the per-cell detail. Where both describe a position, cells wins. Keeping the value in both — as above — is the usual way, and means the sheet still reads correctly for anything that only looks at rows.

Ranges take either spelling. merges: ["A2:C2"] and merges: [{ startRow: 1, startCol: 0, endRow: 1, endCol: 2 }] are the same thing since v1.0.1.

And now the part you asked about that has a real answer

and if possible writeOds?

The same sheet object goes to both writers, but ODS does not carry all of it. Measured on the output above:

	.xlsx	.ods
values, merges	✔	✔
bold, italic, font size, font colour, background colour, number format	✔	✔
freeze pane	✔	—
auto-filter	✔	—
column widths	✔	—
alignment, borders, font name, underline	✔	—

The dropped ones are silent — writeOds takes the fields and does not emit them. That is docs/PARITY.md's "not modelled in either direction" list, and it is a real gap rather than a bug in your usage.

So: if the styled table is the deliverable, .xlsx is the format that carries it. If you need .ods as well, expect the group shading, the bold and the number format to survive, and the freeze/filter/widths not to.

If it would help, paste back what the screenshot shows for the color column — I have assumed a comma-joined string, but if you want one colour per row with the name merged down the group, that is a different flatten and I can write it.

[expdts]

Thanks for the detailed example, it closes some of the questions that have arisen. Unfortunately, in your solution, not all styles have been applied to all individual cells, so this is still a question. For example, wrapping text in a specific cell or another style to a specific cell rather than a row. I have very complex tables with an unknown number of columns, rows, and arrays of rows contained in them. In my opinion, this approach of creating tables in this way is too complicated. Why not do as in exceljs, assemble an array of rows with styles applied to each cell and combine the necessary cells in the row, get the column address (this allows you to build with an unknown columns ), and then transfer only the rows without complications? Yes, you have a different approach, and hucre works fast. I hope you will still have a simpler approach to creating tables.

[productdevbook]

You were right, and it was worse than an ergonomics complaint. #562 has the fix.

What you asked for is what the streaming writer already took

Why not do as in exceljs, assemble an array of rows with styles applied to each cell

XlsxStreamWriter.addRow has accepted exactly that since it existed:

writer.addRow([{ value: "Region", style: { font: { bold: true } } }, "Revenue"])

writeOdsStream too. Only writeXlsx and writeOds — the buffered ones I showed you — did not. So the answer to "why not" was: no reason, half the library already does.

And the half that did not, lost the cell

I went to check what writeXlsx does with the shape before answering. It does not throw and it does not ignore the style:

rows: [["plain", { value: "wrapped", style: { alignment: { wrapText: true } } }]]

<row r="1"><c r="A1" t="s"><v>0</v></c></row>

B1 is gone. Not unstyled — absent. The value, the style and a formula if there was one, all dropped, with no error. resolveRows read the object as a value, null-ish, and emitted nothing. The ODS writer did the same.

So the natural spelling — the one you reached for, the one the streaming API teaches — silently produced files with holes. That is a bug, not a missing convenience, and it is why my earlier example had to route everything through the cells map.

What lands in #562

await writeXlsx({
  sheets: [
    {
      name: "Report",
      rows: [
        [{ value: "Region", style: { font: { bold: true } } }, "Revenue"],
        ["EU", { value: 12500, style: { numFmt: "$#,##0.00" } }],
        ["Total", { formula: "SUM(B2:B2)" }],
      ],
    },
  ],
})

Anything a cell carries goes there — style, formula, richText, hyperlink, checkbox — so wrapText on one cell rather than a row is now one object in the row. Both writers, and saveXlsx. cells still works and still wins where both name the same position, so nothing you may have written already changes.

For the unknown-column-count case: rows needs no column addresses at all, and where you do need one, colToLetter(i) and cellRef(row, col) are exported.

On merging within a row — merges is still a sheet-level list rather than something a cell says about itself:

merges: ["A2:C2", { startRow: 5, startCol: 0, endRow: 5, endCol: 2 }]

If you want { value, colSpan } on the cell instead, that is a separate and reasonable ask — open it and I will look, since it is the one part of your description this does not cover.

Two things still to know before you build on it: the ODS gaps in my table above are unchanged (freeze pane, auto-filter, column widths, alignment, borders, font name, underline are not written to .ods), and per-cell styles are not available on the streaming ODS writer, only the buffered one.

Thank you for pushing back on the first answer. The complicated approach was a workaround for a defect I had not found yet.