xlsx-parser.js

/**
 * XLSX Parser using JSZip - Converts Excel files to JSON
 * Handles .xlsx files reliably without external dependencies beyond JSZip
 */

class XLSXParser {
    static async parse(arrayBuffer) {
        try {
            // Wait for JSZip to be available
            if (typeof JSZip === 'undefined') {
                throw new Error('JSZip library not available');
            }

            const zip = new JSZip();
            await zip.loadAsync(arrayBuffer);

            // Get workbook.xml
            const workbookData = await zip.file('xl/workbook.xml')?.async('string');
            if (!workbookData) {
                throw new Error('workbook.xml not found - this may not be a valid Excel file');
            }

            // Parse sheet names
            const sheetNames = this.parseSheetNames(workbookData);
            if (sheetNames.length === 0) {
                throw new Error('No sheets found in workbook');
            }

            // Try to load shared strings (for formula references)
            let sharedStrings = [];
            const sharedStringsData = await zip.file('xl/sharedStrings.xml')?.async('string');
            if (sharedStringsData) {
                sharedStrings = this.parseSharedStrings(sharedStringsData);
            }

            // Parse each sheet
            const sheets = {};
            for (let i = 0; i < sheetNames.length; i++) {
                const sheetFile = `xl/worksheets/sheet${i + 1}.xml`;
                const sheetData = await zip.file(sheetFile)?.async('string');
                
                if (sheetData) {
                    sheets[sheetNames[i]] = this.parseSheetData(sheetData, sharedStrings);
                } else {
                    sheets[sheetNames[i]] = [];
                }
            }

            return {
                SheetNames: sheetNames,
                Sheets: sheets
            };
        } catch (error) {
            console.error('XLSX parsing error:', error);
            throw error;
        }
    }

    static parseSheetNames(workbookXml) {
        const names = [];
        const regex = /<sheet\s+[^>]*name="([^"]+)"/g;
        let match;
        
        while ((match = regex.exec(workbookXml)) !== null) {
            names.push(match[1]);
        }
        
        return names;
    }

    static parseSharedStrings(sharedStringsXml) {
        const strings = [];
        const stringRegex = /<si>[\s\S]*?<t>([^<]*)<\/t>[\s\S]*?<\/si>/g;
        let match;

        while ((match = stringRegex.exec(sharedStringsXml)) !== null) {
            strings.push(match[1]);
        }

        return strings;
    }

    static parseSheetData(sheetXml, sharedStrings = []) {
        try {
            const data = [];
            const rowRegex = /<row[^>]*>([\s\S]*?)<\/row>/g;
            let rowMatch;
            let headers = [];
            let isFirstRow = true;

            while ((rowMatch = rowRegex.exec(sheetXml)) !== null) {
                const rowContent = rowMatch[1];
                const cells = this.parseCells(rowContent, sharedStrings);

                if (cells.length > 0 && cells.some(c => c && c.trim().length > 0)) {
                    if (isFirstRow) {
                        headers = cells;
                        isFirstRow = false;
                    } else {
                        const rowObj = {};
                        headers.forEach((header, index) => {
                            rowObj[header] = cells[index] || '';
                        });
                        data.push(rowObj);
                    }
                }
            }

            return data;
        } catch (error) {
            console.error('Error parsing sheet data:', error);
            return [];
        }
    }

    static parseCells(rowContent, sharedStrings = []) {
        const cells = [];
        const cellRegex = /<c[^>]*t="([^"]*)"[^>]*>([\s\S]*?)<\/c>|<c[^>]*>([\s\S]*?)<\/c>/g;
        let cellMatch;

        while ((cellMatch = cellRegex.exec(rowContent)) !== null) {
            const cellType = cellMatch[1] || ''; // 's' for shared string, 'n' for number, etc.
            const cellContent = cellMatch[2] || cellMatch[3] || '';
            
            let value = '';
            
            // Try to find value in <v> tag
            const valueMatch = /<v>([^<]*)<\/v>/.exec(cellContent);
            
            if (valueMatch) {
                value = valueMatch[1];
                
                // If it's a shared string reference, look it up
                if (cellType === 's') {
                    const stringIndex = parseInt(value, 10);
                    value = sharedStrings[stringIndex] || value;
                }
            } else {
                // Try to find in <t> tag (sometimes used for text)
                const textMatch = /<t>([^<]*)<\/t>/.exec(cellContent);
                value = textMatch ? textMatch[1] : '';
            }
            
            cells.push(value);
        }

        return cells;
    }
}

// Export for use
if (typeof module !== 'undefined' && module.exports) {
    module.exports = XLSXParser;
}