@chronicstone/typed-xlsx v0.2.16
typed-xlsx
Export any data into xls/xlsx files effortlessly, while benefiting from great type-safety & developper experience.
Key Features :
🛠 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
INSTALLATION
pnpm add @chronicstone/typed-xlsx
USAGE EXAMPLE
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 }
}
}
2. Build a sheet schema :
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()
3. Safely compose excel file from schemas
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)
4. Have fun
Here's the generated file for the example from above
License
MIT License © 2023-PRESENT Cyprien THAO
19 days ago
22 days ago
22 days ago
22 days ago
23 days ago
26 days ago
27 days ago
28 days ago
29 days ago
29 days ago
29 days ago
29 days ago
29 days ago
6 months ago
6 months ago
6 months ago
6 months ago
6 months ago
6 months ago
6 months ago
6 months ago
6 months ago
6 months ago
6 months ago