0.2.0
JS xlsx tools
Attributes
Includes Deno configuration
Repository
Current version released
6 months ago
Dependencies
deno.land/x
nextrj_xlsx
JS xlsx tools base on ExcelJS.
Examples
Click here to download excel sample file.
Generate a single sheet simple table
import { genSingleSheetWorkbook } from 'https://deno.land/x/nextrj_xlsx/mod.ts'
import type { HeadColumn } from 'https://deno.land/x/nextrj_xlsx/mod.ts'
// define head-column
const headColumns: HeadColumn[] = [
{ id: 'name', label: 'Name', width: 15, valueMapper: (row) => `${row.firstName} ${row.lastName}` },
{ id: 'date', width: 12, dataCellStyle: { numFmt: 'yyyy-MM-dd', alignment: { horizontal: 'center' } } },
{ id: 'int', label: 'Int', dataCellStyle: { numFmt: '#', alignment: { horizontal: 'right' } } },
{ id: 'decimal', label: 'Decimal', dataCellStyle: { numFmt: '#0.00', alignment: { horizontal: 'right' } } },
{
id: 'money',
label: 'Money',
width: 22,
dataCellStyle: { numFmt: '¥#,###,###,##0.00', alignment: { horizontal: 'right' } },
},
{ id: 'percent', label: 'Percent', dataCellStyle: { numFmt: '0.00%', alignment: { horizontal: 'right' } } },
]
// define data-row
const dataRows = [
{
firstName: 'John',
lastName: 'Smith',
date: '2024-01-01',
int: 1234,
decimal: 1234.567,
money: 1234567890.345,
percent: 0.12345,
},
{
firstName: 'Li',
lastName: 'Xiao',
},
{
firstName: 'Chen',
lastName: 'Hui',
date: '2024-01-03',
int: 222.333,
decimal: 888.666,
percent: 1,
},
]
// generate a workbook
const workbook = await genSingleSheetWorkbook({
headColumns,
dataRows,
sheetName: 'Sheet1',
// table title
caption: { value: 'Simple table example', style: { font: { bold: true } } },
// share style for all head-cell and data-cell
cellStyle: {
alignment: { vertical: 'middle' },
border: { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } },
},
// specific style for all head-cell
headCellStyle: {
alignment: { horizontal: 'center' },
font: { bold: true },
},
// specific style for all data-cell
dataCellStyle: { alignment: { horizontal: 'left' } },
// workbook properties
bookProperties: {
creator: 'NextRJ',
created: new Date(),
lastModifiedBy: 'NextRJ',
modified: new Date(),
},
// sheet properties
sheetProperties: {
defaultRowHeight: 20, // default 15
defaultColWidth: 10,
},
sheetView: {
showGridLines: false,
state: 'frozen',
xSplit: 1,
ySplit: 2,
activeCell: 'B3',
},
sheetPageSetup: {
paperSize: 9, // 8-A3、9-A4
orientation: 'landscape',
// units is inches, 0.2"*2.54=0.5cm
margins: { top: 0.2, left: 0.2, bottom: 0.2, right: 0.2, header: 0.2, footer: 0.2 },
fitToPage: true,
fitToWidth: 1,
fitToHeight: 0,
printArea: 'A:F',
printTitlesRow: '2:2',
},
})
// write to file
await workbook.xlsx.writeFile('temp/table1.xlsx')
// or send through standard Response
const response = new Response(await workbook.xlsx.writeBuffer(), {
status: 200,
headers: {
'content-type': 'application/vnd.ms-excel',
'Content-Disposition': 'attachment; filename="sample.xlsx"; filename*=UTF-8\'\'sample.xlsx',
'Access-Control-Expose-Headers': 'Content-Disposition',
'Access-Control-Allow-Origin': '*',
},
})
Generate a table with nested group
// define head-column
const headColumns: HeadColumn[] = [
{ id: 'teacher', label: 'Teacher', width: 15, valueMapper: (row) => `${row.firstName} ${row.lastName}` },
{
id: 'workdate',
label: 'Workdate',
width: 12,
dataCellStyle: { numFmt: 'yyyy-MM-dd', alignment: { horizontal: 'center' } },
},
{
label: 'Students',
children: [
{ pid: 'students', id: 'name', label: 'Name' },
{
pid: 'students',
id: 'birthdate',
label: 'Birthdate',
width: 12,
dataCellStyle: { numFmt: 'yyyy-MM-dd', alignment: { horizontal: 'center' } },
},
],
},
{
label: 'Favorites',
children: [
{ pid: 'favorites', id: 'name', label: 'Name' },
{
pid: 'favorites',
id: 'priority',
label: 'Priority',
width: 12,
dataCellStyle: { numFmt: '#', alignment: { horizontal: 'right' } },
},
],
},
]
// define data-row
// deno-lint-ignore no-explicit-any
const dataRows: Record<string, any>[] = [
{ firstName: 'John', lastName: 'Smith', workdate: '2000-01-01' },
{
firstName: 'Li',
lastName: 'Xiao',
students: [
{ name: 'Lili', birthdate: '2020-01-01' },
{ name: 'Suson', birthdate: '2023-12-01' },
],
favorites: [
{ priority: 1, name: 'Draw' },
{ priority: 2, name: 'Math' },
{ priority: 3, name: 'Sport' },
],
},
{
firstName: 'Chen',
lastName: 'Hui',
workdate: '2007-02-01',
students: [
{ name: 'Peter', birthdate: '2019-01-01' },
{ name: 'Alan', birthdate: '2024-03-13' },
{ name: 'Rocky', birthdate: '2022-11-23' },
],
favorites: [
{ priority: 1, name: 'Tech' },
{ priority: 2, name: 'Swim' },
],
},
{ firstName: 'Zhang', lastName: 'HuQi', favorites: [{ priority: 1, name: 'Sing' }] },
]
// generate a Workbook
const workbook = await genSingleSheetWorkbook({
headColumns,
dataRows,
sheetName: 'Sheet1',
caption: { value: 'ABC primary school', style: { font: { bold: true } } },
subCaption: 'Class One Grade Two',
cellStyle: {
alignment: { vertical: 'middle' },
border: { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } },
},
headCellStyle: { alignment: { horizontal: 'center' }, font: { bold: true } },
dataCellStyle: { alignment: { horizontal: 'left' } },
sheetProperties: {
defaultRowHeight: 20, // default 15
defaultColWidth: 10,
},
sheetView: {
showGridLines: false,
state: 'frozen',
xSplit: 1,
ySplit: 4,
activeCell: 'B5',
},
})
// write to file
await workbook.xlsx.writeFile('sample.xlsx')