Excel: totals, groups, notes, pictures, protection

Excel export options: summary rows, a row per group with counts and aggregates, row colours, cell notes as comments, pictures from image columns, and read-only sheets with a password.

Paid feature · Excel export. Free for 30 days on a new domain; after that it needs a free personal key or a company key. See pricing

Live demoExcel options

Download with totals, groups, notes, pictures and protection.

Code for this demo

import { createGrid, type ColumnDef, type Row } from '@freegridjs/core';
import { makeRows } from '../data';
import { button, separator, text, tr, type DemoContext } from '../ui';

export default function mount(ctx: DemoContext) {
  const h = (en: string, ko: string) => tr(ctx.lang, { en, ko });
  const photo = (i: number) => `data:image/svg+xml;utf8,${encodeURIComponent(`<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><circle cx="12" cy="12" r="12" fill="hsl(${(i * 47) % 360} 70% 55%)"/></svg>`)}`;
  const grid = createGrid(ctx.host, {
    columns: [
      { field: 'country', header: h('Country', '국가'), width: 140 },
      { field: 'name', header: h('Name', '이름'), width: 160 },
      { field: 'q1', header: h('Q1', '1분기'), type: 'number', format: '#,##0', aggFunc: 'sum', width: 120 },
      { field: 'logo', header: '', width: 50, image: { width: 20, height: 20 } },
    ],
    data: makeRows(60).map((r, i) => ({ ...r, logo: photo(i) })),
    locale: ctx.lang, getRowId: r => String(r.id), cellNotes: true,
    notes: [{ rowId: '3', colId: 'q1', note: h('Includes a return', '반품 포함') }],
    groupBy: ['country'],
    summary: { rows: [{ label: h('Total', '합계'), columns: { q1: 'sum' } }] },
    rowStyle: ({ row }) => (row.q1 > 90000 ? { background: '#dcfce7' } : null),
  });
  grid.expandAll();
  button(ctx, h('Excel: totals and groups', '엑셀: 합계와 그룹'), () => grid.exportXlsx('groups.xlsx', { summary: true, groups: true }));
  button(ctx, h('Excel: protected (password "demo")', '엑셀: 보호(암호 "demo")'), () => grid.exportXlsx('protected.xlsx', { protect: { password: 'demo' } }));
  button(ctx, h('Excel: with pictures', '엑셀: 그림 포함'), () => grid.exportXlsx('pictures.xlsx', { images: true }));
  text(ctx, h('Notes become Excel comments; green rows keep their colour. (SVG pictures here are kept as addresses: PNG, JPEG and GIF are embedded.)',
    '메모는 엑셀 메모가 되고 초록 행은 색이 유지됩니다. (여기 SVG 그림은 주소로 남고, PNG·JPEG·GIF는 그림으로 들어갑니다.)'));
  return grid;
}

These options of exportXlsx(), getXlsx() and getXlsxSheet() add more of what is on screen to the file. The basics (styles, merged cells, frozen header, auto filter, several sheets) are on Excel and PDF export.

grid.exportXlsx('report.xlsx', {
  summary: true,                    // the summary rows at the end
  groups: true,                     // when grouped: a row per group (count, aggregates), then its rows
  notes: true,                      // cell notes as comments (default true)
  protect: { password: 'secret' },  // opens read-only; the password unprotects it
  images: true,                     // pictures of image columns in their cells (fetched first)
});

Options

Name	Type	Description
`summary`	`boolean`	Add the summary rows (bold, grey) after the data, with the row label and real values in the column format.
`groups`	`boolean`	When rows are grouped, add a row per group — "Country: Korea (12)" with the column aggregates — before its rows, nested by level. Merged cells are left out in this layout.
`notes`	`boolean`	Cell notes become Excel comments (default true).
`protect`	`boolean \| { password }`	Protect the sheet: it opens read-only. With a password, Excel asks for it to unprotect.
`images`	`boolean`	exportXlsx and getXlsxAsync: fetch the pictures of image columns and place them over their cells; rows grow to fit. A picture that cannot be loaded keeps its address as text.

Colours in the file

rowStyle colours (background, text colour, bold) are written to every cell of the row.
Conditional formats and cellClass colours come on top, cell by cell.
styles: false writes plain values.

Pictures

const grid = createGrid(el, {
  columns: [{ field: 'name' }, { field: 'photo', image: { width: 40, height: 40 } }],
  data,
});
grid.exportXlsx('people.xlsx', { images: true });            // downloads when the pictures are in
const bytes = await grid.getXlsxAsync({ images: true });     // or the bytes, to upload

Pictures from another site need CORS on that site; otherwise the cell keeps the address.
PNG, JPEG and GIF are embedded; each address is fetched once.
getXlsx() (synchronous) never fetches; use getXlsxAsync() for pictures.

Print and PDF

Print and PDF include summary rows and group rows unless summary: false / groups: false, and draw rowStyle colours too.