@mertdeveci55/univer-import-export

A robust Excel/CSV import and export library for Univer spreadsheets with full format preservation, including formulas, styling, charts, and conditional formatting.

Features

✅ Import Support

• Excel files (.xlsx, .xls)
• CSV files (.csv)
• Preserves ALL sheets (including empty ones)
• Handles special characters in sheet names (>>>, etc.)
• Maintains exact sheet order
• Full styling preservation (fonts, colors, borders, alignment)
• Formula and calculated value retention (including TRANSPOSE and array formulas)
• Merged cells support
• Images and charts
• Conditional formatting
• Data validation
• Hyperlinks and rich text

✅ Export Support

• Excel files (.xlsx)
• CSV files (.csv)
• Full formatting preservation
• Formula export
• Named ranges
• Multiple sheets

Installation

npm install @mertdeveci55/univer-import-export

or

yarn add @mertdeveci55/univer-import-export

Usage

Import Excel to Univer

import { LuckyExcel } from '@mertdeveci55/univer-import-export';

// Handle file input
const fileInput = document.getElementById('file-input');
fileInput.addEventListener('change', (e) => {
    const file = e.target.files[0];
    
    LuckyExcel.transformExcelToUniver(
        file,
        (univerData) => {
            // Use the Univer data
            console.log('Converted data:', univerData);
            
            // Create Univer instance with the data
            univer.createUnit(UniverInstanceType.UNIVER_SHEET, univerData);
        },
        (error) => {
            console.error('Import error:', error);
        }
    );
});

Import CSV to Univer

import { LuckyExcel } from '@mertdeveci55/univer-import-export';

LuckyExcel.transformCsvToUniver(
    csvFile,
    (univerData) => {
        // Use the converted CSV data
        univer.createUnit(UniverInstanceType.UNIVER_SHEET, univerData);
    },
    (error) => {
        console.error('CSV import error:', error);
    }
);

Export Univer to Excel

import { LuckyExcel } from '@mertdeveci55/univer-import-export';

// Get Univer snapshot
const snapshot = univer.getActiveWorkbook().save();

LuckyExcel.transformUniverToExcel({
    snapshot: snapshot,
    fileName: 'my-spreadsheet.xlsx',
    success: () => {
        console.log('Export successful');
    },
    error: (err) => {
        console.error('Export error:', err);
    }
});

Export Univer to CSV

import { LuckyExcel } from '@mertdeveci55/univer-import-export';

const snapshot = univer.getActiveWorkbook().save();

LuckyExcel.transformUniverToCsv({
    snapshot: snapshot,
    fileName: 'my-data.csv',
    sheetName: 'Sheet1', // Optional: specific sheet to export
    success: () => {
        console.log('CSV export successful');
    },
    error: (err) => {
        console.error('CSV export error:', err);
    }
});

API Reference

LuckyExcel.transformExcelToUniver(file, callback, errorHandler)

Converts Excel file to Univer format.

• file: File - The Excel file (.xlsx or .xls)
• callback: (data: IWorkbookData) => void - Success callback with converted data
• errorHandler: (error: Error) => void - Error callback

LuckyExcel.transformCsvToUniver(file, callback, errorHandler)

Converts CSV file to Univer format.

• file: File - The CSV file
• callback: (data: IWorkbookData) => void - Success callback
• errorHandler: (error: Error) => void - Error callback

LuckyExcel.transformUniverToExcel(params)

Exports Univer data to Excel file.

Parameters object:

• snapshot: any - Univer workbook snapshot
• fileName: string - Output filename (optional, default: excel_[timestamp].xlsx)
• getBuffer: boolean - Return buffer instead of downloading (optional, default: false)
• success: (buffer?: Buffer) => void - Success callback
• error: (err: Error) => void - Error callback

LuckyExcel.transformUniverToCsv(params)

Exports Univer data to CSV file.

Parameters object:

• snapshot: any - Univer workbook snapshot
• fileName: string - Output filename (optional, default: csv_[timestamp].csv)
• sheetName: string - Specific sheet to export (optional, exports all if not specified)
• getBuffer: boolean - Return content instead of downloading (optional, default: false)
• success: (content?: string) => void - Success callback
• error: (err: Error) => void - Error callback

Browser Support

The library works in all modern browsers that support:

• ES6+
• File API
• Blob API

Project Structure

src/
├── ToLuckySheet/         # Excel → LuckySheet conversion
│   ├── LuckyFile.ts      # Main file orchestrator - handles sheet discovery
│   ├── LuckySheet.ts     # Individual sheet processor
│   ├── ReadXml.ts        # XML parser with special character escaping
│   ├── LuckyCell.ts      # Cell data processor
│   └── ...
├── LuckyToUniver/        # LuckySheet → Univer conversion
│   ├── UniverWorkBook.ts # Workbook structure converter
│   ├── UniverSheet.ts    # Sheet data converter
│   └── ...
├── UniverToExcel/        # Univer → Excel export
│   ├── Workbook.ts       # Excel workbook builder using ExcelJS
│   └── ...
├── HandleZip.ts          # ZIP file operations using JSZip
└── main.ts               # Entry point with public API

dist/                     # Built output (ESM, CJS, UMD formats)
publish.sh                # Automated publishing script
gulpfile.js              # Build configuration
CLAUDE.md                # Detailed technical documentation

Key Implementation Details

Special Character Handling

Sheet names with special characters (like >>>) are handled through an escape/unescape mechanism in src/ToLuckySheet/ReadXml.ts:

// Escapes ">" to "__GT__" before XML parsing
escapeXmlAttributes(xmlString)
// Restores "__GT__" back to ">" after parsing
unescapeXmlAttributes(xmlString)

Empty Sheet Preservation

All sheets are preserved during import, even if completely empty. This maintains Excel file structure integrity.

Formula Support

Comprehensive formula support including:

• Standard formulas (SUM, AVERAGE, IF, VLOOKUP, etc.)
• Array formulas
• TRANSPOSE formulas with proper array handling
• Shared formulas
• Named range references

Development

Building

npm install
npm run build   # Uses gulp to compile TypeScript and bundle

Publishing

Always use the publish script for releases:

./publish.sh

This script:

1. Builds the project
2. Increments version
3. Commits changes
4. Pushes to GitHub
5. Publishes to npm

Testing

npm test

Key Improvements

v0.1.40 (Latest) - Streamlined Export System

1. Excel Compatibility: Eliminated Excel recovery warnings completely
2. Surgical Post-Processing: Conservative backend fixes that preserve all data integrity
3. Production Safety: Reverted experimental fixes that caused corruption
4. Documentation: Comprehensive documentation of remaining limitations and workarounds

v0.1.39 - Backend Integration

1. openpyxl Integration: Full backend post-processing system for ExcelJS limitations
2. Defined Names Fix: Complete solution for named ranges using Python/Django backend
3. Dual-Mode Export: Frontend + backend export with automatic fallback

v0.1.24 - Core Stability

1. Special Character Support: Handles sheet names with >>> and other special characters via escape/unescape mechanism
2. Empty Sheet Preservation: Empty sheets are never skipped during import
3. No Hardcoded Solutions: Removed all hardcoded sheet additions - all solutions are generic
4. Sheet Order: Maintains exact sheet order from original file
5. Style Preservation: Complete style mapping including bold, italic, colors, borders
6. Formula Handling: Preserves both formulas and calculated values, including TRANSPOSE
7. XLS Support: Automatic conversion of .xls files to .xlsx format
8. Better Error Handling: Comprehensive error messages and detailed logging

Dependencies

Core Dependencies

• @progress/jszip-esm - ZIP file handling for Excel files
• @zwight/exceljs - Excel file structure (export)
• @univerjs/core - Univer core types and interfaces
• dayjs - Date manipulation for Excel dates
• papaparse - CSV parsing
• xlsx - Additional Excel format support

Build Dependencies

• gulp - Build orchestration
• rollup - Module bundling
• typescript - Type safety
• terser - Minification (configured to preserve console.logs)

Related Projects & References

Core Dependencies

• Univer - The spreadsheet engine this library supports
• LuckySheet - Intermediate format inspiration
• LuckyExcel - Original codebase this fork is based on

Implementation Examples

• alphafrontend - Production implementation
  • See: src/utils/excel-import.ts for usage example
  • See: src/pages/SpreadsheetsPage.tsx for UI integration

Documentation

• CLAUDE.md - Detailed technical documentation for AI assistants
• publish.sh - Automated publishing script
• gulpfile.js - Build configuration

Known Issues & Solutions

✅ Resolved Issues

| Issue | Solution | Version Fixed | |-------|----------|---------------| | Sheets with special characters (>>>) not importing | Escape/unescape mechanism in ReadXml.ts | v0.1.23+ | | AttributeList undefined errors | Defensive initialization | v0.1.21+ | | Duplicate sheets appearing | Removed hardcoded sheet additions | v0.1.24 | | TRANSPOSE formulas not working | Array formula support | v0.1.18+ | | Border styles not importing | Added style collection in UniverWorkBook | v0.1.38 |

⚠️ Export Limitations & Solutions

Due to limitations in the underlying ExcelJS library, some advanced Excel features have issues during export. However, we provide comprehensive solutions:

🔧 Backend Post-Processing Solution

For production applications, we recommend using backend post-processing to achieve 100% Excel compatibility:

Architecture

Frontend Export → Backend API → openpyxl Post-Processing → Fixed Excel File

Django Integration Example

from spreadsheets.import_export import UniverToExcelConverter, ExcelPostProcessor

# Step 1: Export with existing functionality (preserves all working features)
converter = UniverToExcelConverter()
excel_buffer = converter.convert(univer_data)

# Step 2: Apply surgical fixes for ExcelJS limitations
post_processor = ExcelPostProcessor()
fixed_buffer = post_processor.process_excel_buffer(
    excel_buffer.getvalue(), 
    univer_data
)

What Gets Fixed

• ✅ Defined Names: All named ranges work perfectly in Excel
• ✅ Array Formula Attributes: Proper XML attributes for spill ranges
• ✅ Excel 365 Compatibility: Full support for modern Excel features
• ✅ Performance: ~7ms processing overhead
• ✅ Safety: Preserves ALL existing functionality

Frontend Integration

// Enhanced export with backend post-processing
const result = await exportToExcel({
  workbookData,
  fileName: 'spreadsheet.xlsx',
  useBackendExport: true,        // Enable backend processing
  enablePostProcessing: true,    // Fix ExcelJS limitations
  exportSpreadsheetToExcel: api.exportSpreadsheetToExcel
});

// Automatic fallback to frontend if backend unavailable
if (result.stats?.postProcessingApplied) {
  console.log('✅ Enhanced Excel compatibility applied!');
}

Setup Requirements

1. Python Backend with openpyxl installed
2. API Endpoint for post-processing
3. Optional: Feature flag for gradual rollout

📊 Current Export Status (v0.1.40)

✅ Fully Working Features

• ✅ Excel Compatibility: Files open without recovery warnings
• ✅ Data Integrity: All formulas, values, and structure preserved perfectly
• ✅ Named Ranges: All defined names work correctly in Excel (fixed in backend)
• ✅ Import Operations: 100% functionality preserved for all Excel features

⚠️ Known Visual/Display Issues

| Issue | Impact | Status | Workaround | |-------|--------|---------|------------| | TRANSPOSE @ Symbols | Formulas show =@TRANSPOSE(@$N$43:$N$45) | Cosmetic only - calculations work | Document as known issue | | Border Style Changes | Dashed borders may become solid | Visual styling only | Original design preserved in import | | ExcelJS Core Limitations | Some advanced Excel features not supported | Library limitation | Use backend post-processing |

🔧 Backend Post-Processing (Recommended)

Status: Production-ready surgical fixes for maximum Excel compatibility.

What it fixes:

• ✅ Removes Excel recovery warnings completely
• ✅ Ensures all named ranges work perfectly
• ✅ Maintains 100% data integrity and formula accuracy

What it doesn't fix (by design):

• ⚠️ TRANSPOSE @ symbols (requires ExcelJS core changes)
• ⚠️ Border style inconsistencies (requires ExcelJS improvements)

Performance: ~7ms overhead for complete Excel compatibility.

Import functionality works perfectly - these limitations only affect export operations.

Contributing

Contributions are welcome! Please ensure:

1. No hardcoded solutions - All fixes must be generic
2. Extensive logging - Add console.log for debugging
3. Use publish.sh - Never manually publish to npm
4. Test edge cases - Including special characters, empty sheets
5. Follow existing patterns - Check CLAUDE.md for architecture

License

MIT © mertdeveci

Credits

• Original LuckyExcel by DreamNum
• Univer spreadsheet engine
• All contributors and issue reporters

Support

For issues and feature requests:

• GitHub Issues
• Check CLAUDE.md for technical details
• Review closed issues for solutions