Export & Clipboard

Export Excel

Export grid data to Excel using third-party libraries like ExcelJS.

Several open-source libraries can generate Excel files in JavaScript. This guide uses ExcelJS, but alternatives like SheetJs work with the same approach.

Excel Download

The demo below lets you download an Excel file containing all grid data. It uses ExcelJS to create a Blob and trigger the download.


30 collapsed lines
1
import "@1771technologies/lytenyte-pro/components.css";
2
import "@1771technologies/lytenyte-pro/light-dark.css";
3
import { Grid, useClientDataSource } from "@1771technologies/lytenyte-pro";
4
import { bankDataSmall } from "@1771technologies/grid-sample-data/bank-data-smaller";
5
import { BalanceCell, DurationCell, NumberCell } from "./components.jsx";
6
import { useRef } from "react";
7
import type { Worksheet } from "exceljs";
8

9
type BankData = (typeof bankDataSmall)[number];
10
export interface GridSpec {
11
  readonly data: BankData;
12
}
13

14
const columns: Grid.Column<GridSpec>[] = [
15
  { name: "Job", id: "job", width: 120 },
16
  { name: "Age", id: "age", type: "number", width: 80, cellRenderer: NumberCell },
17
  { name: "Balance", id: "balance", type: "number", cellRenderer: BalanceCell },
18
  { name: "Education", id: "education" },
19
  { name: "Marital", id: "marital" },
20
  { name: "Default", id: "default" },
21
  { name: "Housing", id: "housing" },
22
  { name: "Loan", id: "loan" },
23
  { name: "Contact", id: "contact" },
24
  { name: "Day", id: "day", type: "number", cellRenderer: NumberCell },
25
  { name: "Month", id: "month" },
26
  { name: "Duration", id: "duration", type: "number", cellRenderer: DurationCell },
27
];
28

29
const base = { width: 100 };
30

31
export default function ExportDemo() {
32
  const ds = useClientDataSource({ data: bankDataSmall });
33
  const apiRef = useRef<Grid.API<GridSpec> | null>(null);
34

35
  return (
36
    <>
37
      <div className="border-ln-border border-b p-2">
38
        <button
39
          data-ln-button="website"
40
          data-ln-size="md"
41
          onClick={async () => {
42
            const api = apiRef.current;
43
            if (!api) return;
44

45
            const rect = await api.exportData();
46

47
            downloadBlob(await getExcelFile(rect), "data.xlsx");
48
          }}
49
        >
50
          Download Excel File
51
        </button>
52
      </div>
53
      <div className="ln-grid" style={{ display: "flex", flexDirection: "column" }}>
54
        <div style={{ height: 500 }}>
55
          <Grid columns={columns} columnBase={base} rowSource={ds} ref={apiRef} />
56
        </div>
57
      </div>
58
    </>
59
  );
60
}
61

62
function downloadBlob(blob: Blob, name: string) {
63
  // Convert your blob into a Blob URL (a special url that points to an object in the browser's memory)
64
  const blobUrl = URL.createObjectURL(blob);
65

66
  // Create a link element
67
  const link = document.createElement("a");
68

69
  // Set link's href to point to the Blob URL
70
  link.href = blobUrl;
71
  link.download = name;
72

73
  // Append link to the body
74
  document.body.appendChild(link);
75

76
  // Dispatch click event on the link
77
  // This is necessary as link.click() does not work on the latest firefox
78
  link.dispatchEvent(
79
    new MouseEvent("click", {
80
      bubbles: true,
81
      cancelable: true,
82
      view: window,
83
    }),
84
  );
85

86
  // Remove link from body
87
  document.body.removeChild(link);
88
}
89

90
async function getExcelFile(d: Grid.T.ExportDataRectResult<GridSpec>) {
91
  const excelJs = await import("exceljs");
92
  const workbook = new excelJs.Workbook();
93

94
  workbook.creator = "1771 Technologies";
95
  workbook.lastModifiedBy = "1771 Technologies";
96

97
  // Set default options
98
  const {
99
    sheetName = "Data Export",
100
    headerBgColor = "4472C4", // Medium blue without # symbol
101
    headerFgColor = "FFFFFF", // White without # symbol
102
    alternateRowColors = true,
103
    evenRowBgColor = "F2F2F2", // Light gray without # symbol
104
    oddRowBgColor = "FFFFFF", // White without # symbol
105
  } = {};
106

107
  // Create a new workbook and worksheet
108
  workbook.creator = "ExcelJS Export";
109
  workbook.lastModifiedBy = "ExcelJS Export";
110
  workbook.created = new Date();
111
  workbook.modified = new Date();
112

113
  const worksheet = workbook.addWorksheet(sheetName);
114

115
  // Define columns
116
  worksheet.columns = d.columns.map((column, index) => ({
117
    header: d.headers[index] || column.name,
118
    key: index.toString(),
119
    width: Math.max(column.id!.length + 2, 12), // Dynamic width based on header name length
120
    style: {
121
      numFmt: column.type === "number" ? "#,##0.00" : "@", // Format numbers with 2 decimal places
122
    },
123
  }));
124

125
  // Add the data
126
  d.data.forEach((row) => {
127
    const rowData: Record<string, unknown> = {};
128
    row.forEach((cell, cellIndex) => {
129
      rowData[cellIndex.toString()] = cell;
130
    });
131
    worksheet.addRow(rowData);
132
  });
133

134
  // Style the header row
135
  const headerRow = worksheet.getRow(1);
136
  headerRow.height = 22;
137
  headerRow.font = {
138
    name: "Calibri",
139
    size: 11,
140
    bold: true,
141
    color: { argb: headerFgColor },
142
  };
143

144
  headerRow.eachCell((cell) => {
145
    cell.fill = {
146
      type: "pattern",
147
      pattern: "solid",
148
      fgColor: { argb: headerBgColor },
149
    };
150
    cell.border = {
151
      top: { style: "thin" },
152
      left: { style: "thin" },
153
      bottom: { style: "thin" },
154
      right: { style: "thin" },
155
    };
156
    cell.alignment = {
157
      vertical: "middle",
158
      horizontal: "center",
159
    };
160
  });
161

162
  // Apply alternate row colors for data rows
163
  if (alternateRowColors) {
164
    for (let i = 2; i <= worksheet.rowCount; i++) {
165
      const rowColor = i % 2 === 0 ? evenRowBgColor : oddRowBgColor;
166
      const row = worksheet.getRow(i);
167

168
      row.eachCell({ includeEmpty: true }, (cell) => {
169
        cell.fill = {
170
          type: "pattern",
171
          pattern: "solid",
172
          fgColor: { argb: rowColor },
173
        };
174

175
        // Style borders
176
        cell.border = {
177
          top: { style: "thin", color: { argb: "D3D3D3" } },
178
          left: { style: "thin", color: { argb: "D3D3D3" } },
179
          bottom: { style: "thin", color: { argb: "D3D3D3" } },
180
          right: { style: "thin", color: { argb: "D3D3D3" } },
181
        };
182

183
        // Align cell content
184
        cell.alignment = {
185
          vertical: "middle",
186
          horizontal: cell.value !== null && typeof cell.value === "number" ? "right" : "left",
187
        };
188
      });
189
    }
190
  }
191

192
  // Apply auto-filters to the header row
193
  worksheet.autoFilter = {
194
    from: { row: 1, column: 1 },
195
    to: { row: 1, column: d.headers.length },
196
  };
197

198
  // Freeze the top row
199
  worksheet.views = [{ state: "frozen", xSplit: 0, ySplit: 1, activeCell: "A2" }];
200

201
  d.columns.forEach((_, index) => {
202
    const columnWidth = estimateColumnWidth(worksheet, index);
203
    worksheet.getColumn(index + 1).width = columnWidth;
204
  });
205

206
  // Write to a buffer
207
  const buffer = await workbook.xlsx.writeBuffer();
208

209
  // Create a blob from the buffer
210
  const blob = new Blob([buffer], {
211
    type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
212
  });
213

214
  return blob;
215
}
216
function estimateColumnWidth(worksheet: Worksheet, columnIndex: number) {
217
  let maxWidth = 10; // Default minimum width
218

219
  // Check header width (adding extra space for padding)
220
  const headerCell = worksheet.getCell(1, columnIndex + 1);
221
  if (headerCell && headerCell.value) {
222
    const headerLength = String(headerCell.value).length;
223
    maxWidth = Math.max(maxWidth, headerLength + 4); // Add padding
224
  }
225

226
  // Check data cell widths
227
  worksheet.eachRow({ includeEmpty: false }, function (row, rowNumber) {
228
    if (rowNumber > 1) {
229
      // Skip header
230
      const cell = row.getCell(columnIndex + 1);
231
      if (cell && cell.value !== null && cell.value !== undefined) {
232
        let cellLength = String(cell.value).length;
233
        // Add extra width for numbers with formatting
234
        if (typeof cell.value === "number") {
235
          cellLength += 3; // Add extra space for formatting
236
        }
237
        maxWidth = Math.max(maxWidth, cellLength + 2); // Add padding
238
      }
239
    }
240
  });
241

242
  // Cap maximum width to prevent extremely wide columns
243
  return Math.min(maxWidth, 50);
244
}

1
import type { Grid } from "@1771technologies/lytenyte-pro";
2
import type { GridSpec } from "./demo";
3

4
const formatter = new Intl.NumberFormat("en-US", {
5
  maximumFractionDigits: 2,
6
  minimumFractionDigits: 0,
7
});
8
export function BalanceCell({ api, row, column }: Grid.T.CellRendererParams<GridSpec>) {
9
  const field = api.columnField(column, row);
10

11
  if (typeof field === "number") {
12
    if (field < 0) return `-$${formatter.format(Math.abs(field))}`;
13

14
    return "$" + formatter.format(field);
15
  }
16

17
  return `${field ?? "-"}`;
18
}
19
export function DurationCell({ api, row, column }: Grid.T.CellRendererParams<GridSpec>) {
20
  const field = api.columnField(column, row);
21

22
  return typeof field === "number" ? `${formatter.format(field)} days` : `${field ?? "-"}`;
23
}
24

25
export function NumberCell({ api, row, column }: Grid.T.CellRendererParams<GridSpec>) {
26
  const field = api.columnField(column, row);
27

28
  return typeof field === "number" ? formatter.format(field) : `${field ?? "-"}`;
29
}

The implementation uses exportData to retrieve the current view of cells.

1
<button
2
  onClick={async () => {
3
    const api = apiRef.current;
4
    if (!api) return;
5

6
    const rect = await api.exportData();
7

8
    downloadBlob(await getExcelFile(rect), "data.xlsx");
9
  }}
10
>
11
  Download Excel File
12
</button>

Next Steps

CSV Data Export: Export grid data as a CSV file.
Export Arrow: Export grid data as an Arrow file.
Export Parquet: Export grid data as a Parquet file.

Export Excel

Excel Download

Excel Export & Download

Next Steps

On this page