@coatl/xlsx

A modern, high-performance TypeScript XLSX library with minimal dependencies and optimal bundle size.

🚀 Features

Core Features

• ✅ Lightweight: Only 2 dependencies (fast-xml-parser & fflate)
• ✅ High Performance: Optimized for speed and memory efficiency
• ✅ TypeScript Native: Full type safety and IntelliSense support
• ✅ Modern ESM: ES modules with Node.js 18+ support
• ✅ Excel Compatible: Reads and writes standard .xlsx files

Data Operations

• ✅ Rich Formatting: Support for fonts, colors, bold, backgrounds
• ✅ Formula Reading: Parse and read Excel formulas
• ✅ Bulk Operations: Efficient handling of large datasets
• ✅ Data Analysis: Built-in aggregation and statistical functions
• ✅ Multiple Export Formats: Export to CSV, JSON, or Excel

Advanced Features

• ✅ Array-like Operations: map, filter, reduce, find on sheet data
• ✅ Data Grouping & Sorting: Group by columns, sort datasets
• ✅ Row/Column Access: Easy access to rows and columns
• ✅ Range Detection: Automatic data range calculation
• ✅ Memory Optimization: Intelligent caching and pooling

📦 Installation

npm install @coatl/xlsx

🔥 Quick Start

Creating Excel Files

import { Excel } from '@coatl/xlsx'

// Create a new Excel workbook
const excel = new Excel()
const sheet = excel.sheet('Sales Report')

// Add headers with styling
sheet.cell('A1', 'Product', { bold: true, color: '#2563eb' })
sheet.cell('B1', 'Revenue', { bold: true, color: '#2563eb' })
sheet.cell('C1', 'Growth', { bold: true, color: '#2563eb' })

// Add data
sheet.cell('A2', 'Widget A')
sheet.cell('B2', 1500)
sheet.cell('C2', 0.15) // 15% growth

sheet.cell('A3', 'Widget B')
sheet.cell('B3', 2300)
sheet.cell('C3', -0.05) // -5% growth

// Save to buffer
const buffer = await excel.save()

// Save to file (Node.js)
import { writeFileSync } from 'fs'
writeFileSync('sales-report.xlsx', buffer)

Reading Excel Files

import { Excel } from '@coatl/xlsx'
import { readFileSync } from 'fs'

// Load from file
const buffer = readFileSync('sales-report.xlsx')
const excel = await Excel.load(buffer)

// Read data
const sheet = excel.sheet('Sales Report')
const product = sheet.cell('A2')?.value // 'Widget A'
const revenue = sheet.cell('B2')?.value // 1500
const growth = sheet.cell('C2')?.value  // 0.15

console.log(`${product}: $${revenue} (${growth * 100}% growth)`)

Bulk Operations

const excel = new Excel()
const sheet = excel.sheet('Large Dataset')

// Efficient bulk data insertion
const data = [
  ['Name', 'Age', 'City'],
  ['John Doe', 30, 'New York'],
  ['Jane Smith', 25, 'Los Angeles'],
  // ... thousands of rows
]

// Use bulkSet for optimal performance
sheet.bulkSet(data, 'A1', { bold: true }) // Headers with styling

// Alternative: row by row
data.forEach((row, rowIndex) => {
  row.forEach((value, colIndex) => {
    const cellRef = Excel.cellReference(rowIndex + 1, colIndex + 1)
    sheet.cell(cellRef, value)
  })
})

Data Analysis & Processing

const excel = await Excel.load(buffer)
const sheet = excel.sheet('Sales Data')

// Get structured data
const rows = sheet.rows(true) // Include headers as keys
console.log(rows[0]) // { Name: 'John Doe', Age: 30, City: 'New York' }

// Data analysis
const totalAge = sheet.sumBy('Age')
const ageGroups = sheet.groupBy('City')
const sortedByAge = sheet.sort('Age', 'desc')

// Array-like operations
const adults = sheet.filter(row => row.Age >= 18)
const names = sheet.map(row => row.Name)
const avgAge = sheet.reduce((acc, row) => acc + row.Age, 0) / sheet.count()

// Find specific records
const john = sheet.find(row => row.Name === 'John Doe')
const uniqueCities = sheet.unique('City')

Export to Different Formats

const excel = await Excel.load(buffer)
const sheet = excel.sheet('Data')

// Export as JSON
const jsonData = sheet.toJSON()
console.log(jsonData)

// Export as CSV
const csvData = sheet.toCSV()
console.log(csvData)

// Export specific columns only
const selected = sheet.select('Name', 'Age')

// Get raw data as 2D array
const rawData = sheet.data()
const headers = sheet.headers()

Working with Formulas

// Reading formulas (when loading existing files)
const excel = await Excel.load(buffer)
const sheet = excel.sheet('Calculations')

const cell = sheet.cell('C1')
console.log(cell?.formula) // "=A1+B1"
console.log(cell?.value)   // Calculated result

📖 API Reference

Excel Class

Constructor

new Excel()

Methods

• sheet(name?: string): SheetProxy - Get or create a worksheet
• getSheetNames(): string[] - Get all sheet names
• save(): Promise<ArrayBuffer> - Export as Excel buffer
• static load(buffer: ArrayBuffer): Promise<Excel> - Load from buffer
• parseAddress(addr: string): ParsedAddress - Parse cell address
• columnToNumber(col: string): number - Convert column letter to number
• numberToColumn(num: number): string - Convert number to column letter
• dateToSerial(date: Date): number - Convert date to Excel serial

SheetProxy Class

Core Cell Operations

• cell(ref: string): CellData | null - Get cell by reference
• cell(ref: string, value: CellValue, style?: StyleInfo): SheetProxy - Set cell value and style
• bulkSet(data: CellValue[][], startAddr?: string, style?: StyleInfo): SheetProxy - Set multiple cells efficiently

Data Access Methods

• getValue(address: string): CellValue - Get cell value
• getFormula(address: string): string | null - Get cell formula
• getAllCells(): Map<string, {value: CellValue, formula?: string}> - Get all cells
• getRange(): {minRow, maxRow, minCol, maxCol} - Get data range
• hasData(): boolean - Check if sheet has data

Data Structure Methods

• data(): CellValue[][] - Get data as 2D array
• headers(): CellValue[] - Get first row as headers
• rows(withHeaders?: boolean): Record<string, CellValue>[] - Get rows as objects
• row(index: number): Record<string, CellValue> | null - Get specific row
• column(keyOrIndex: string | number): CellValue[] - Get column values

Array-like Operations

• find(predicate: (row: Record<string, CellValue>) => boolean): Record<string, CellValue> | null
• filter(predicate: (row: Record<string, CellValue>) => boolean): Record<string, CellValue>[]
• map<T>(mapper: (row: Record<string, CellValue>) => T): T[]
• reduce<T>(reducer: (acc: T, row: Record<string, CellValue>, index: number) => T, initialValue: T): T

Data Analysis Methods

• count(): number - Count total rows
• countBy(key: string): Record<string, number> - Count occurrences by column
• sumBy(key: string): number - Sum values in a column
• groupBy(key: string): Record<string, Record<string, CellValue>[]> - Group rows by column value
• unique(key: string): CellValue[] - Get unique values from column

Sorting & Filtering

• sort(key: string, direction?: 'asc' | 'desc'): Record<string, CellValue>[] - Sort rows
• slice(start: number, end?: number): Record<string, CellValue>[] - Get subset of rows
• select(...keys: string[]): Record<string, CellValue>[] - Select specific columns
• where(conditions: Record<string, any>): Record<string, CellValue>[] - Filter by conditions

Export Methods

• toJSON(): Record<string, CellValue>[] - Export as JSON array
• toCSV(delimiter?: string): string - Export as CSV string

Type Definitions

type CellValue = string | number | boolean | Date | null | undefined

interface CellData {
  address: string
  value: CellValue
  type: 's' | 'n' | 'b'
  styleId: number
  formula?: string
}

interface StyleInfo {
  bg?: string        // Background color (hex)
  fg?: string        // Foreground/text color (hex) 
  bold?: boolean     // Bold text
}

🎯 Use Cases

Financial Reports with Analysis

const excel = new Excel()
const sheet = excel.sheet('Q4 Report')

// Headers
sheet.cell('A1', 'Quarter', { bold: true })
sheet.cell('B1', 'Revenue', { bold: true })
sheet.cell('C1', 'Profit', { bold: true })

// Data with formatting
sheet.cell('A2', 'Q4 2024')
sheet.cell('B2', 125000, { fg: '16a34a' }) // Green for revenue
sheet.cell('C2', 25000, { fg: '16a34a' })  // Green for profit

// After loading, analyze the data
const totalRevenue = sheet.sumBy('Revenue')
const avgProfit = sheet.reduce((acc, row) => acc + row.Profit, 0) / sheet.count()
const summary = sheet.groupBy('Quarter')

Data Export from Database

// Example with database results
const users = await db.query('SELECT * FROM users')
const excel = new Excel()
const sheet = excel.sheet('Users')

// Bulk insert all data at once
const data = [
  Object.keys(users[0]), // Headers
  ...users.map(user => Object.values(user)) // Data rows
]
sheet.bulkSet(data, 'A1', { bold: true }) // Headers bold

// Export to different formats
const csvData = sheet.toCSV()
const jsonData = sheet.toJSON()

Sales Analytics Dashboard Data

const excel = await Excel.load(salesBuffer)
const sheet = excel.sheet('Sales')

// Comprehensive data analysis
const salesByRegion = sheet.groupBy('Region')
const topPerformers = sheet.sort('Revenue', 'desc').slice(0, 10)
const averageOrderValue = sheet.sumBy('Revenue') / sheet.count()

// Filter and analyze
const highValueOrders = sheet.filter(row => row.Revenue > 1000)
const uniqueCustomers = sheet.unique('Customer')

// Complex aggregation
const regionSummary = sheet.reduce((acc, row) => {
  if (!acc[row.Region]) acc[row.Region] = { count: 0, total: 0 }
  acc[row.Region].count++
  acc[row.Region].total += row.Revenue
  return acc
}, {})

Survey Data Processing

const excel = await Excel.load(surveyBuffer)
const sheet = excel.sheet('Responses')

// Data cleaning and analysis
const validResponses = sheet.filter(row => row.Age >= 18 && row.Age <= 100)
const responsesByCategory = sheet.countBy('Category')
const averageRating = sheet.sumBy('Rating') / sheet.count()

// Demographic analysis
const ageGroups = validResponses.reduce((acc, row) => {
  const ageGroup = Math.floor(row.Age / 10) * 10
  acc[`${ageGroup}-${ageGroup + 9}`] = (acc[`${ageGroup}-${ageGroup + 9}`] || 0) + 1
  return acc
}, {})

// Export processed results
const processedData = sheet.select('ID', 'Age', 'Category', 'Rating')
const summarySheet = excel.sheet('Summary')
summarySheet.bulkSet([
  ['Metric', 'Value'],
  ['Total Responses', sheet.count()],
  ['Average Rating', averageRating.toFixed(2)],
  ['Unique Categories', Object.keys(responsesByCategory).length]
])

Inventory Management

const excel = new Excel()
const sheet = excel.sheet('Inventory')

// Product data with stock levels
const products = await getProductsFromDB()
sheet.bulkSet([
  ['SKU', 'Name', 'Stock', 'Reorder Level', 'Status'],
  ...products.map(p => [p.sku, p.name, p.stock, p.reorderLevel, 
    p.stock <= p.reorderLevel ? 'REORDER' : 'OK'])
])

// Analysis and alerts
const lowStockItems = sheet.filter(row => row.Status === 'REORDER')
const totalInventoryValue = sheet.sumBy('Stock') * sheet.sumBy('Price')
const stockByCategory = sheet.groupBy('Category')

// Generate report with alerts
lowStockItems.forEach((item, index) => {
  sheet.cell(`F${index + 2}`, item.Name, { bg: 'ffcccc' }) // Red background for alerts
})

🏗️ Architecture & Performance

This library is designed with performance and efficiency as core principles:

Memory Management

• Object Pooling: Reuses cell objects to minimize garbage collection
• Intelligent Caching: Caches parsed addresses, column mappings, and XML elements
• Batch Processing: Groups operations for optimal memory usage (batch sizes: 5000 cells, chunks: 2500)
• Lazy Evaluation: Builds row maps only when needed for data operations

Processing Optimizations

• Zero-copy Operations where possible to avoid unnecessary data duplication
• Streaming XML Parsing for memory-efficient processing of large files
• Bulk String Handling: Efficiently manages shared strings with batch insertion
• Optimized ZIP Operations using fflate for fast compression/decompression

Data Structure Design

• Map-based Storage: Uses Maps for O(1) cell access by address
• Flexible Row Representation: Supports both sparse (Map) and dense (Array) storage
• Style Deduplication: Reuses style definitions to minimize file size
• Range Tracking: Maintains min/max bounds for efficient iteration

Performance Characteristics

• Large Dataset Support: Handles 100K+ rows efficiently
• Fast Bulk Operations: bulkSet() optimized for inserting thousands of cells
• Minimal Bundle Impact: Only ~50KB gzipped with dependencies
• Memory Efficient: Constant memory usage regardless of dataset size

🔧 Browser & Node.js Support

Browser Support

Works in all modern browsers that support:

• ES2020+ features (async/await, optional chaining, nullish coalescing)
• Uint8Array and ArrayBuffer for binary data handling
• Native Promise support

Node.js Support

• Minimum Version: Node.js 18.0.0 or higher
• ES Modules: Full ESM support with proper import statements
• Performance: Optimized for both V8 and other JS engines
• Memory: Efficient memory usage even with large datasets

🚀 Performance Tips

For Large Datasets

// Use bulkSet instead of individual cell operations
sheet.bulkSet(data, 'A1') // ✅ Fast

// Avoid this for large datasets
data.forEach((row, r) => {  // ❌ Slow for >1000 rows
  row.forEach((cell, c) => {
    sheet.cell(`${String.fromCharCode(65+c)}${r+1}`, cell)
  })
})

Memory Optimization

// Process data in chunks for very large files
const chunkSize = 5000
for (let i = 0; i < data.length; i += chunkSize) {
  const chunk = data.slice(i, i + chunkSize)
  sheet.bulkSet(chunk, `A${i + 1}`)
}

Reading Performance

// Use structured data methods for better performance
const rows = sheet.rows(true) // ✅ Structured access
const specificData = sheet.select('Name', 'Age') // ✅ Column filtering

// Avoid cell-by-cell reading for large datasets  
for (let r = 1; r <= 1000; r++) { // ❌ Slow
  const value = sheet.getValue(`A${r}`)
}

🤝 Contributing

Issues and pull requests are welcome! Please ensure:

1. TypeScript compilation passes (npm run build)
2. Tests pass (npm test)
3. Follow existing code style

📝 License

MIT License - see LICENSE file for details.

🔗 Related

• fast-xml-parser - XML parsing
• fflate - ZIP compression

Made with ❤️ and TypeScript