-
🛠 Type-safe Schema Builder: Design your spreadsheet schema with strong typing for enhanced reliability and developer experience.
-
🔄 Type-safe Data Serialization & Transformation: Ensure data integrity through type-safe serialization and transformation functionalities.
-
🔧 Shared Type-safe Custom Value Pre-processors: Utilize shared pre-processors for consistent value transformaiton
-
🧮 Column Summary : Auto-insert computed column summaries for efficient data analysis and overview.
-
🧩 Complex Row Structures with Auto-Merging: Implement advanced row layouts with sub-rows for automatic row merging / styling for seamless data organization and display.
-
🎯 Easy Default Values Management: Manage default values effortlessly, ensuring your data is presented exactly as you intend.
-
📊 Dynamic Column Selection: Selectively choose which columns of the schema to use when building a table
-
🗺️ Dynamic Column Mapping with Type-safe Context: Map columns dynamically with a type-safe context, injected when building sheet
-
🎨 Dynamic Cell Styling/Formatting: Customize cell styling and formatting dynamically per-row with ease
-
📑 Multi-sheet Support: Create spreadsheets with multiple sheets
-
🏗️ Multiple Tables Per Sheet Support: Include as many tables you need inside a same sheet
-
🌐 Linear or Grid-like Layout for Sheets with Multiple Tables: Choose between linear or grid layouts for sheets
pnpm add @chronicstone/typed-xlsx
1. Define the type of exported data (Or infer it from a function / a db query, or wherever you want) :
interface Organization {
id: number
name: string
}
interface User {
id: number
firstName: string
lastName: string
email: string
roles: string[]
organizations: Organization[]
results: {
general: { overall: number }
technical: { overall: number }
interview?: { overall: number }
}
}
import { ExcelSchemaBuilder } from '@chronicstone/typed-xlsx'
// OPTIONAL : DEFINE SHARED TRANSFORMERS THAT CAN BE USE TO TRANSFORM VALUE INSERTED INTO A CELL
const transformers = {
boolean: (value: boolean) => value ? 'Yes' : 'No',
list: (value: (string)[]) => value.join(', '),
arrayLength: (value: any[]) => value.length,
} satisfies TransformersMap
// Use the schema builder to define your sheet schema
const userExportSchema = ExcelSchemaBuilder
.create<User>()
.withTransformers(transformers)
.column('id', {
key: 'id',
summary: [{ value: () => 'TOTAL BEFORE VAT' }, { value: () => 'TOTAL' }],
})
.column('firstName', { key: 'firstName' })
.column('lastName', { key: 'lastName' })
.column('email', { key: 'email' })
.column('roles', {
key: 'roles',
transform: 'list',
cellStyle: data => ({ font: { color: { rgb: data.roles.includes('admin') ? 'd10808' : undefined } } }),
})
.column('balance', {
key: 'balance',
format: '"$"#,##0.00_);\\("$"#,##0.00\\)',
summary: [
{
value: data => data.reduce((acc, user) => acc + user.balance, 0),
format: '"$"#,##0.00_);\\("$"#,##0.00\\)',
},
{
value: data => data.reduce((acc, user) => acc + user.balance, 0) * 1.2,
format: '"$"#,##0.00_);\\("$"#,##0.00\\)',
},
],
})
.column('nbOrgs', { key: 'organizations', transform: 'arrayLength' })
.column('orgs', { key: 'organizations', transform: org => org.map(org => org.name).join(', ') })
.column('generalScore', {
key: 'results.general.overall',
format: '# / 10',
summary: [{
value: data => data.reduce((acc, user) => acc + user.results.general.overall, 0) / data.length,
format: '# / 10',
}],
})
.column('technicalScore', {
key: 'results.technical.overall',
summary: [{
value: data => data.reduce((acc, user) => acc + user.results.technical.overall, 0) / data.length,
}],
})
.column('interviewScore', { key: 'results.interview.overall', default: 'N/A' })
.column('createdAt', { key: 'createdAt', format: 'd mmm yyyy' })
.group('group:org', (builder, context: Organization[]) => {
for (const org of context) {
builder
.column(`orga-${org.id}`, {
label: `User in ${org.name}`,
key: 'organizations',
transform: orgs => orgs.some(o => o.id === org.id) ? 'YES' : 'NO',
cellStyle: data => ({
font: {
color: { rgb: data.organizations.some(o => o.id === org.id) ? '61eb34' : 'd10808' },
},
}),
})
}
})
.build()
import { ExcelBuilder } from '@chronicstone/typed-xlsx'
const buffer = ExcelBuilder
.create()
.sheet('Users - full')
.addTable({
data: users,
schema: assessmentExport,
context: {
'group:org': organizations,
},
})
.sheet('Users - partial')
.addTable({
data: users,
schema: assessmentExport,
select: {
firstName: true,
lastName: true,
email: true,
},
})
.sheet('User - neg partial')
.addTable({
data: users,
schema: assessmentExport,
select: {
firstName: false,
lastName: false,
email: false,
},
context: {
'group:org': organizations,
},
})
.sheet('User - Multiple tables')
.sheet('Multi-tables-grid', { tablesPerRow: 2 })
.addTable({
title: 'Table 1',
data: users.filter((_, i) => i < 5),
schema: assessmentExport,
select: { firstName: true, lastName: true, email: true, createdAt: true },
})
.addTable({
title: 'Table 2',
data: users.filter((_, i) => i < 5),
schema: assessmentExport,
select: { firstName: true, lastName: true, email: true, balance: true },
})
.addTable({
title: 'Table 3',
data: users.filter((_, i) => i < 5),
schema: assessmentExport,
select: { firstName: true, lastName: true, email: true, balance: true },
})
.addTable({
title: 'Table 4',
data: users.filter((_, i) => i < 5),
schema: assessmentExport,
select: { firstName: true, lastName: true, email: true, createdAt: true },
})
.build({ output: 'buffer' })
fs.writeFileSync('test.xlsx', arrayBuffer)
Here's the generated file for the example from above
MIT License © 2023-PRESENT Cyprien THAO