db-csv-to-excel

A utility package to convert database CSV exports into formatted Excel (XLSX) files.

README generated by AI. Looks great generally and I manually corrected some apparent errors but hasn't checked it thoroughly. Feel free to report any issues.

Features

• Convert CSV data to Excel with custom column mapping
• Support for encrypted database columns (MySQL AES)
• Custom data processing per column
• Data sorting capabilities
• Custom styling for headers and content
• Configurable column widths

Installation

npm install db-csv-to-excel

Basic Usage

import { convertDBCsvToExcel } from 'db-csv-to-excel'

// Convert CSV to Excel
await convertDBCsvToExcel(
  'input.csv',
  'output.xlsx',
  {
    // Required if any columns are encrypted
    aesKey: 'your-encryption-key',
    // Define your database columns
    dbColumns: [
      { name: 'id' },
      { name: 'username' },
      { name: 'email', encrypted: true },
      { name: 'created_at', processor: value => new Date(value) },
    ],
    // Define your Excel columns
    excelColumns: [
      {
        title: 'ID',
        from: row => row.id,
        width: 10,
      },
      {
        title: 'User',
        from: row => row.username,
        width: 20,
      },
      {
        title: 'Email Address',
        from: row => row.email,
        width: 30,
      },
      {
        title: 'Registration Date',
        from: row => row.created_at.toLocaleDateString(),
        width: 20,
      },
    ],
    // Set to true if your CSV has a header row
    csvHasColumns: true,
    // Set the CSV delimiter if not a comma
    csvDelimiter: '\t',
  },
)

API Reference

convertDBCsvToExcel()

Main function to convert CSV to Excel.

declare function convertDBCsvToExcel<DBColumns>(
  inputFilePath: string | URL,
  outputFilePath: string | URL,
  options: ConvertParams<DBColumns>
): Promise<void>

Options

Parameter	Type	Description	Default
dbColumns	DBColumn[]	Database column definitions	Required
excelColumns	ExcelColumn[] | (firstRow?: DBRow) => ExcelColumn[]	Excel column definitions, either as array or function that gets first row	Required
aesKey	string	Encryption key for AES-encrypted columns	'' (Required if any column has encrypted: true)
sortFns	Array<(row) => number>	Functions to determine sort order	[]
sortMode	'asc' | 'desc'	Sort direction	'desc'
titleStyle	CellStyle	Default style for title cells	undefined
contentStyle	CellStyle	Default style for content cells	undefined
csvHasColumns	boolean	Whether CSV file includes header row	false
csvDelimiter	string	CSV delimiter character	','

Types

DBColumn<T>

interface DBColumn<T = unknown> {
  name: string
  encrypted?: boolean
  processor?: (value: string) => T
}

ExcelColumn<DBColumns>

interface ExcelColumn<DBColumns> {
  title: string
  from: (row: DBRow<DBColumns>) => string | null | undefined
  width?: number
  titleStyle?: CellStyle
  contentStyle?: CellStyle
}

Examples

Custom Styling

import { convertDBCsvToExcel } from 'db-csv-to-excel'

await convertDBCsvToExcel(
  'input.csv',
  'output.xlsx',
  {
    dbColumns: [/* ... */],
    excelColumns: [/* ... */],
    titleStyle: {
      font: { bold: true, color: { rgb: 'FFFFFF' } },
      fill: { fgColor: { rgb: '4472C4' } },
    },
    contentStyle: {
      font: { name: 'Arial' },
    },
  },
)

Sorting

await convertDBCsvToExcel(
  'input.csv',
  'output.xlsx',
  {
    dbColumns: [/* ... */],
    excelColumns: [/* ... */],
    sortFns: [
      // Sort by creation date
      row => row.created_at.getTime(),
      // Then by ID
      row => Number(row.id),
    ],
    // Ascending order
    sortMode: 'asc',
  },
)

Dynamic Excel Columns

You can generate Excel columns dynamically based on the first row of data:

await convertDBCsvToExcel(
  'input.csv',
  'output.xlsx',
  {
    dbColumns: [/* ... */],
    // Generate columns dynamically based on first row
    excelColumns: (firstRow) => {
      // Example: Create a column for each property in the first row
      if (!firstRow) return []

      return Object.keys(firstRow).map(key => ({
        title: key.toUpperCase(),
        from: row => row[key],
        width: 15,
      }))
    },
  },
)

License

ISC