Optimization scheme for Excel import and export

Features

---

Technical Architecture

┌─────────────────────────────────────────────────┐
│           Business Layer (Vue/React/...)        │
├─────────────────────────────────────────────────┤
│  ExcelImporter    │  ExcelExporter    │ Validator │
│  (SheetJS Read)    │  (ExcelJS Write)   │ (Rules)  │
├───────────────────┼──────────────────┼──────────┤
│  TypeConverter    │  excelWorker      │          │
│  (Type Convert)   │  (Worker Build)   │          │
├───────────────────┴──────────────────┴──────────┤
│              SheetJS (xlsx)  │  ExcelJS          │
│              (Fast Read/Export) │ (Style/Complex) │
└─────────────────────────────────────────────────┘

Responsibilities:

• SheetJS (xlsx): Responsible for fast Excel file reading and plain data export (no styling), extremely fast
• ExcelJS: Responsible for advanced features like styled export, complex headers, image embedding, data validation, etc.
• Web Worker: Moves ExcelJS CPU-intensive operations (workbook building + writeBuffer) to background thread

---

Quick Start

# Clone the project
git clone https://github.com/Adou377/excel-demo.git
cd excel-demo

# Install dependencies
npm install

# Start development server
npm run dev

# Build production version
npm run build

After starting, visit http://localhost:3000 to experience all features.

---

Integration in Other Projects

Method 1: Directly Copy Utility Files (Recommended)

There are only 5 core utility files, zero framework dependencies, can be directly copied into any frontend project.

Step 1: Install Dependencies

npm install exceljs xlsx

Step 2: Copy Utility Files

Copy the following files into your project (recommended in src/utils/excel/ directory):

src/utils/excel/
├── ExcelImporter.js    # Importer
├── ExcelExporter.js    # Exporter
├── TypeConverter.js    # Type Converter
├── Validator.js        # Data Validator
└── excelWorker.js      # Web Worker Export

Step 3: Adjust Import Paths

The import paths inside utility files need to be adjusted according to your directory structure:

// In ExcelImporter.js
import { Validator } from './Validator.js'
import { TypeConverter } from './TypeConverter.js'

// Ensure relative paths match your directory structure

Step 4: Use in Business Code

import { ExcelImporter } from '@/utils/excel/ExcelImporter'
import { ExcelExporter } from '@/utils/excel/ExcelExporter'
import { Validator } from '@/utils/excel/Validator'
import { TypeConverter } from '@/utils/excel/TypeConverter'

Method 2: Usage in Non-Vite Projects

If you use Webpack or other build tools, Web Worker import needs adjustment:

// Vite approach (this project)
const worker = new Worker(new URL('./utils/excelWorker.js', import.meta.url), { type: 'module' })

// Webpack approach
const worker = new Worker(new URL('../../utils/excel/excelWorker.js', import.meta.url))

// Or use worker-loader
import ExcelWorker from './excelWorker.js'
const worker = new ExcelWorker()

Method 3: Usage in React / Vanilla JS Projects

Utility classes are framework-agnostic, can be directly used in React, Angular, vanilla JS:

// React example
import { ExcelExporter } from './utils/excel/ExcelExporter'

function ExportButton({ data }) {
  const handleExport = async () => {
    const exporter = new ExcelExporter()
    await exporter.export(data, [
      { title: 'Name', key: 'name', width: 12 },
      { title: 'Age', key: 'age', width: 8 }
    ], 'exported_data.xlsx')
  }

  return <button onClick={handleExport}>Export Excel</button>
}

---

Core Module API Documentation

ExcelImporter - Importer

Excel file import based on SheetJS, supporting field mapping, type conversion, data validation.

Constructor

const importer = new ExcelImporter(config)

config options:

Instance Methods

import(file, onProgress?)

Complete import flow: read → parse → convert → validate.

const importer = new ExcelImporter({
  fieldTypes: {
    name: 'string',
    age: 'integer',
    email: 'string',
    birthday: 'date'
  },
  fieldMapping: {
    'Name': 'name',
    'Age': 'age',
    'Email': 'email',
    'Birthday': 'birthday'
  },
  enumOptions: {
    gender: ['Male', 'Female']
  },
  validationRules: {
    name: [{ type: 'required', message: 'Name is required' }],
    age: [
      { type: 'required', message: 'Age is required' },
      { type: 'range', min: 0, max: 150, message: 'Age must be between 0-150' }
    ],
    email: [{ type: 'email', message: 'Invalid email format' }]
  }
})

const result = await importer.import(file, (percent, status) => {
  console.log(`${percent}% - ${status}`)
})

// result structure:
// {
//   success: boolean,       // Whether all validations passed
//   data: object[],         // Valid data array
//   errors: Error[],        // Error list
//   total: number,          // Total rows
//   validCount: number,     // Valid rows count
//   errorCount: number      // Error rows count
// }

quickImport(file) (Static Method)

Quick import, only reads data, no type conversion or validation. Suitable for simple data preview scenarios.

const result = await ExcelImporter.quickImport(file)
// result: { headers: string[], data: object[] }

---

ExcelExporter - Exporter

Excel file export based on ExcelJS, supporting styles, complex headers, templates, error reports.

Constructor

const exporter = new ExcelExporter(config)

config options:

Default headerStyle:

{
  font: { bold: true, color: { argb: 'FFFFFFFF' } },
  fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF4472C4' } },
  alignment: { horizontal: 'center', vertical: 'middle' },
  border: { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } }
}

Instance Methods

export(data, columns, filename?, onProgress?)

Standard export with styles and progress callback.

const exporter = new ExcelExporter()

await exporter.export(
  [
    { name: 'Zhang San', age: 28, department: 'Engineering' },
    { name: 'Li Si', age: 32, department: 'Sales' }
  ],
  [
    { title: 'Name', key: 'name', width: 12 },
    { title: 'Age', key: 'age', width: 8 },
    {
      title: 'Department', key: 'department', width: 12,
      render: (value, row) => `${value} Dept`,  // Custom render
      style: { font: { bold: true } }            // Custom cell style
    }
  ],
  'employee_data.xlsx',
  (percent) => console.log(`Export progress: ${percent}%`)
)

columns options:

exportWithComplexHeader(data, config, filename?)

Complex header export, supports multi-level headers and merged cells.

await exporter.exportWithComplexHeader(data, {
  headers: [
    ['Name', 'Scores', 'Scores', 'Remarks'],   // First header row
    ['', 'Chinese', 'Math', '']                // Second header row
  ],
  merges: [
    { s: { r: 0, c: 0 }, e: { r: 1, c: 0 } },  // "Name" spans 2 rows
    { s: { r: 0, c: 1 }, e: { r: 0, c: 2 } },  // "Scores" spans 2 columns
    { s: { r: 0, c: 3 }, e: { r: 1, c: 3 } }    // "Remarks" spans 2 rows
  ],
  columns: [
    { key: 'name', width: 12 },
    { key: 'chinese', width: 10 },
    { key: 'math', width: 10 },
    { key: 'remark', width: 20 }
  ],
  dataStartRow: 3   // Data starts at row 3
}, 'score_sheet.xlsx')

merges format: { s: { r, c }, e: { r, c } }, r is row index (0-based), c is column index (0-based).

exportTemplate(config, filename?)

Generate import templates with dropdown validation, field descriptions, sample data.

await exporter.exportTemplate({
  fields: [
    {
      key: 'name',
      label: 'Name',
      description: 'Required, max 20 characters',
      width: 15,
      required: true,
      validation: { maxLength: 20 }
    },
    {
      key: 'gender',
      label: 'Gender',
      description: 'Required, Male/Female',
      width: 10,
      required: true,
      options: ['Male', 'Female']   // Generate dropdown
    },
    {
      key: 'department',
      label: 'Department',
      description: 'Required, select from list',
      width: 15,
      required: true,
      options: ['Engineering', 'Sales', 'HR', 'Finance']
    }
  ],
  sampleData: [
    { name: 'Zhang San', gender: 'Male', department: 'Engineering' },
    { name: 'Li Si', gender: 'Female', department: 'Sales' }
  ]
}, 'import_template.xlsx')

Generated template includes:

• Data Sheet: Row 1 is field name (blue header), row 2 is gray description row, sample data starts from row 3
• Dropdown Validation: Fields with options automatically generate dropdown lists (supports 1000 rows)
• Instruction Sheet: Lists each field's name, description, validation rules

exportErrorReport(data, errors, columns, filename?)

Generate error report, error cells are marked red with comments.

await exporter.exportErrorReport(
  rawData,     // Raw data array
  [            // Error list
    { row: 2, field: 'email', value: 'abc', message: 'Invalid email format' },
    { row: 3, field: 'age', value: -1, message: 'Age must be between 0-150' }
  ],
  [            // Column definitions
    { title: 'Name', key: 'name', width: 12 },
    { title: 'Email', key: 'email', width: 25 },
    { title: 'Age', key: 'age', width: 8 }
  ],
  'error_report.xlsx'
)

Generated error report includes:

• Data Sheet: Error cells with red background + comments (hover to see error message)
• Error Summary Sheet: Row number, field, error value, error message

---

TypeConverter - Type Converter

Handles data type differences between Excel and JavaScript.

Constructor

const converter = new TypeConverter()

Instance Methods

setEnumOptions(field, options)

Set enum field options.

converter.setEnumOptions('department', ['Engineering', 'Sales', 'HR', 'Finance'])

convert(value, type, field?)

Convert value to specified type.

converter.convert('1,234.56', 'number')   // 1234.56
converter.convert('3.14', 'integer')       // 3
converter.convert('3.14159', 'float')      // 3.14 (2 decimal places)
converter.convert(0.85, 'percentage')      // 85
converter.convert('yes', 'boolean')        // true
converter.convert(44927, 'date')           // Date object
converter.convert('Engineering', 'enum', 'department')  // 'Engineering' (validate within enum)

Supported types:

Boolean recognition rules:

• true: 'yes', 'true', '1', 'Y', 'y'
• false: 'no', 'false', '0', 'N', 'n'

Excel date serial number explanation:

Excel internally stores dates as numbers (days since January 1, 1900), TypeConverter automatically converts them to JavaScript Date objects.

---

Validator - Data Validator

Constructor

const validator = new Validator(rules)

Validation Rules

const rules = {
  name: [
    { type: 'required', message: 'Name is required' },
    { type: 'maxLength', value: 20, message: 'Name cannot exceed 20 characters' }
  ],
  age: [
    { type: 'required', message: 'Age is required' },
    { type: 'number', message: 'Age must be a number' },
    { type: 'range', min: 0, max: 150, message: 'Age must be between 0-150' }
  ],
  email: [
    { type: 'email', message: 'Invalid email format' }
  ],
  phone: [
    { type: 'phone', message: 'Invalid phone number format' }
  ],
  department: [
    { type: 'enum', value: ['Engineering', 'Sales'], message: 'Department not in allowed list' }
  ],
  website: [
    { type: 'url', message: 'Invalid URL format' }
  ],
  code: [
    { type: 'pattern', value: /^[A-Z]\d{6}$/, message: 'Invalid code format' }
  ],
  customField: [
    { type: 'custom', validator: (value) => value !== 'admin', message: 'Cannot use admin' }
  ]
}

Supported validation types:

Instance Methods

// Validate single field
const result = validator.validateField('name', '')
// result: { valid: false, field: 'name', message: 'Name is required' }

// Validate entire row
const errors = validator.validateRow(row, rowIndex)
// errors: [{ row, field, value, message }, ...]

// Validate all data
const allErrors = validator.validateAll(dataArray)

// Uniqueness validation
const uniqueErrors = validator.validateUniqueness(dataArray, 'email')

---

excelWorker - Web Worker Export

Executes ExcelJS building and writing operations in Web Worker to avoid blocking main thread.

Usage

function workerExport(data, columns, filename, options = {}) {
  return new Promise((resolve, reject) => {
    const worker = new Worker(
      new URL('./utils/excelWorker.js', import.meta.url),
      { type: 'module' }
    )

    worker.onmessage = (e) => {
      const msg = e.data
      if (msg.type === 'progress') {
        console.log(`Progress: ${msg.percent}%`)
      } else if (msg.type === 'complete') {
        worker.terminate()
        resolve(msg)
      } else if (msg.type === 'error') {
        worker.terminate()
        reject(new Error(msg.error))
      }
    }

    worker.onerror = (e) => {
      worker.terminate()
      reject(new Error(e.message))
    }

    const payload = { data, columns, filename, ...options }
    if (options.imageBuffer) {
      worker.postMessage(payload, [options.imageBuffer])
    } else {
      worker.postMessage(payload)
    }
  })
}

Parameters

Return Result

{
  type: 'complete',
  buildTime: number,     // Build time (ms)
  writeTime: number,     // Write time (ms)
  bufferSize: number,    // File size (bytes)
  buffer: ArrayBuffer,   // File data (Transferable)
  filename: string
}

Download File

const result = await workerExport(data, columns, 'export.xlsx')
const blob = new Blob([result.buffer], {
  type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
})
const url = URL.createObjectURL(blob)
const a = document.createElement('a')
a.href = url
a.download = result.filename
document.body.appendChild(a)
a.click()
document.body.removeChild(a)
URL.revokeObjectURL(url)

---

Performance Optimization Details

Problem: UI Freeze with Large Data Export

Traditional approach completes all operations in main thread at once, with large data (50k+ rows) this causes:

• Page freezes, no interaction possible
• Progress bar doesn't update
• Browser may show "Page Unresponsive" prompt

Optimization Strategies

1. Batch Data Generation + yieldToMain

async function yieldToMain() {
  return new Promise(resolve => setTimeout(resolve, 0))
}

const BATCH_SIZE = 5000
for (let i = 0; i < totalCount; i += BATCH_SIZE) {
  const batch = generateData(Math.min(BATCH_SIZE, totalCount - i))
  allData.push(...batch)
  await yieldToMain()  // Yield main thread, UI can respond
}

Principle: setTimeout(resolve, 0) puts subsequent code into macrotask queue, giving browser chance to handle rendering and user interaction events.

2. Web Worker Background Building

// Main thread
const worker = new Worker(new URL('./excelWorker.js', import.meta.url), { type: 'module' })
worker.postMessage({ data, columns })

// Worker thread (excelWorker.js)
self.onmessage = async function (e) {
  const workbook = new ExcelJS.Workbook()
  // ... build workbook (doesn't block main thread)
  const buffer = await workbook.xlsx.writeBuffer()
  self.postMessage({ type: 'complete', buffer }, [buffer])  // Transferable transmission
}

Principle: Web Worker executes in independent thread, won't block main thread UI rendering and event handling.

3. Transferable Zero-Copy Transmission

// In Worker
const transferable = rawBuffer instanceof ArrayBuffer
  ? rawBuffer
  : rawBuffer.buffer.slice(rawBuffer.byteOffset, rawBuffer.byteOffset + rawBuffer.byteLength)

self.postMessage({ buffer: transferable }, [transferable])

Principle: Uses Transferable Objects to transmit ArrayBuffer, avoiding memory copy overhead of structured cloning.

4. Batch Writing Inside Worker

// Inside excelWorker.js
const BATCH_SIZE = 5000
for (let i = 0; i < totalRows; i += BATCH_SIZE) {
  const batch = data.slice(i, Math.min(i + BATCH_SIZE, totalRows))
  for (const item of batch) {
    worksheet.addRow(item)
  }
  self.postMessage({ type: 'progress', percent: ... })
  await new Promise(resolve => setTimeout(resolve, 0))
}

Principle: Even in Worker, batch processing avoids long CPU occupation, allows Worker progress messages to be sent timely.

Performance Comparison

Key difference isn't total time, but that UI remains responsive during optimized approach, users can normally operate page.

---

Usage Scenarios and Examples

Scenario 1: Basic Data Import

import { ExcelImporter } from './utils/excel/ExcelImporter'

const importer = new ExcelImporter({
  fieldMapping: { 'Name': 'name', 'Age': 'age' },
  fieldTypes: { age: 'integer' }
})

const result = await importer.import(file)
if (result.success) {
  console.log('Import successful', result.data)
} else {
  console.log('Errors present', result.errors)
}

Scenario 2: Import with Validation

const importer = new ExcelImporter({
  fieldTypes: {
    name: 'string',
    age: 'integer',
    email: 'string',
    phone: 'string'
  },
  enumOptions: {
    gender: ['Male', 'Female']
  },
  validationRules: {
    name: [{ type: 'required', message: 'Name is required' }],
    age: [
      { type: 'required', message: 'Age is required' },
      { type: 'range', min: 18, max: 65, message: 'Age must be 18-65' }
    ],
    email: [{ type: 'email', message: 'Invalid email format' }],
    phone: [{ type: 'phone', message: 'Invalid phone format' }]
  }
})

const result = await importer.import(file)

// Export error report
if (!result.success) {
  const exporter = new ExcelExporter()
  await exporter.exportErrorReport(result.data, result.errors, columns, 'error_report.xlsx')
}

Scenario 3: Large Data Export (Worker)

async function exportLargeData(data, columns, filename) {
  const worker = new Worker(
    new URL('./utils/excel/excelWorker.js', import.meta.url),
    { type: 'module' }
  )

  return new Promise((resolve, reject) => {
    worker.onmessage = (e) => {
      if (e.data.type === 'complete') {
        worker.terminate()
        const blob = new Blob([e.data.buffer], {
          type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        })
        const url = URL.createObjectURL(blob)
        const a = document.createElement('a')
        a.href = url
        a.download = filename
        a.click()
        URL.revokeObjectURL(url)
        resolve()
      } else if (e.data.type === 'error') {
        worker.terminate()
        reject(new Error(e.data.error))
      }
    }

    worker.postMessage({ data, columns, filename })
  })
}

Scenario 4: Export with Image

async function exportWithImage(data, columns, filename) {
  const imageResp = await fetch('/avatar.png')
  const imageBuffer = await imageResp.arrayBuffer()

  const worker = new Worker(
    new URL('./utils/excel/excelWorker.js', import.meta.url),
    { type: 'module' }
  )

  return new Promise((resolve, reject) => {
    worker.onmessage = (e) => {
      if (e.data.type === 'complete') {
        worker.terminate()
        const blob = new Blob([e.data.buffer], {
          type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        })
        const url = URL.createObjectURL(blob)
        const a = document.createElement('a')
        a.href = url
        a.download = filename
        a.click()
        URL.revokeObjectURL(url)
        resolve()
      } else if (e.data.type === 'error') {
        worker.terminate()
        reject(new Error(e.data.error))
      }
    }

    worker.postMessage(
      { data, columns, withImage: true, imageBuffer, filename },
      [imageBuffer]  // Transferable
    )
  })
}

Scenario 5: Dynamic Header Export (e.g. Attendance Sheet)

const dates = ['2024-01-15', '2024-01-16', '2024-01-17']

const header1 = ['Name']
const header2 = ['']
dates.forEach(date => {
  header1.push(date, date)
  header2.push('Check In', 'Check Out')
})

const merges = [{ s: { r: 0, c: 0 }, e: { r: 1, c: 0 } }]
dates.forEach((_, i) => {
  merges.push({ s: { r: 0, c: i * 2 + 1 }, e: { r: 0, c: i * 2 + 2 } })
})

const columns = [{ key: 'name', width: 12 }]
dates.forEach((_, i) => {
  columns.push({ key: `checkIn_${i}`, width: 10 })
  columns.push({ key: `checkOut_${i}`, width: 10 })
})

const exporter = new ExcelExporter()
await exporter.exportWithComplexHeader(attendanceData, {
  headers: [header1, header2],
  merges,
  columns,
  dataStartRow: 3
}, 'attendance.xlsx')

Scenario 6: Generate Import Template

const exporter = new ExcelExporter()
await exporter.exportTemplate({
  fields: [
    { key: 'name', label: 'Name', description: 'Required', width: 15, required: true },
    { key: 'gender', label: 'Gender', description: 'Male/Female', width: 10, options: ['Male', 'Female'] },
    { key: 'dept', label: 'Department', description: 'Select from list', width: 15, options: ['Engineering', 'Sales'] }
  ],
  sampleData: [
    { name: 'Zhang San', gender: 'Male', dept: 'Engineering' }
  ]
}, 'import_template.xlsx')

---

Dependencies

Core utility classes (ExcelImporter / ExcelExporter / TypeConverter / Validator / excelWorker) don't depend on any frontend framework, can be used in any environment: Vue, React, Angular, vanilla JS, etc.

---

FAQ

Q: Why use both SheetJS and ExcelJS?

A: Each has its strengths:

• SheetJS reads fast, plain data export is extremely fast, but cannot set styles
• ExcelJS supports styles, merged cells, images, data validation and other advanced features, but is slower

This solution chooses appropriate library based on scenario: import uses SheetJS (fast), styled export uses ExcelJS (full-featured), plain data export uses SheetJS (fast).

Q: Can ExcelJS be used in Web Worker?

A: Yes. ExcelJS is pure JavaScript implementation, doesn't depend on DOM, can run normally in Web Worker. But note:

• Cannot access browser APIs like document, window in Worker
• File download needs to be done in main thread (Worker passes buffer back via postMessage)

Q: How to use excelWorker in Webpack projects?

A: Webpack needs to adjust Worker import approach:

// Method 1: Use worker-loader
import ExcelWorker from 'worker-loader!./utils/excel/excelWorker.js'
const worker = new ExcelWorker()

// Method 2: Use URL syntax (Webpack 5+)
const worker = new Worker(new URL('./utils/excel/excelWorker.js', import.meta.url))

Q: What to do with insufficient memory when exporting large data?

A: Following strategies can alleviate memory issues:

1. Use Web Worker, move memory overhead to Worker thread
2. Batch generate data, await yieldToMain() after each batch gives GC chance to collect
3. Release original data reference promptly after data generation (data.length = 0)
4. For extremely large data (500k+ rows), recommend generating on backend and providing download link

Q: How to customize export styles?

A: Through config parameter in constructor or style property in column definition:

const exporter = new ExcelExporter({
  headerStyle: {
    font: { bold: true, color: { argb: 'FF000000' } },
    fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFF0000' } }
  }
})

// Or customize a column's style in column definition
const columns = [
  {
    title: 'Status', key: 'status', width: 10,
    render: (value) => value === 1 ? 'Enabled' : 'Disabled',
    style: {
      font: { bold: true },
      alignment: { horizontal: 'center' }
    }
  }
]

Style format follows ExcelJS Style documentation.

Q: Date parsing incorrect during import?

A: Excel internally stores dates as numeric serial numbers. Ensure:

1. Declare date field as 'date' type in fieldTypes
2. TypeConverter automatically handles Excel serial number → JS Date conversion
3. If date column is text format in Excel, TypeConverter also attempts to parse with new Date()

Q: How to handle Chinese header mapping?

A: Use fieldMapping configuration:

const importer = new ExcelImporter({
  fieldMapping: {
    'Name': 'name',
    'Age': 'age',
    'Department': 'department',
    'Hire Date': 'hireDate'
  }
})

Headers not configured in fieldMapping will be used directly as field names.