📁 Bulk Import & Export Excel Data for Joget Form Grids

ut type="file" id="excel-file-input" accept=".xlsx, .xls" style="display:none;" /> <button type="button" class="btn btn-primary" id="btn-import"> 📁 Import Excel </button> <button type="button" class="btn btn-secondary" id="btn-export"> 📥 Export Grid </button> <button type="button" class="btn btn-danger" id="btn-clear"> 🗑️ Clear Grid </button>

</div> <div id="import-status" class="status-msg" style="margin-top:8px; font-size:0.85em;"></div> </div> ```

JavaScript Logic: Add this to your Custom JavaScript or within a script tag.

(function() {
    'use strict';

    // Configuration
    const CONFIG = {
        gridId: 'procurement_grid',
        batchSize: 15,
        delayMs: 50,
        // Map Excel headers to Grid Field IDs
        // Keys are normalized Excel headers (lowercase, trimmed)
        // Values are the Joget Grid Field IDs
        headerMap: {
            'item name': 'item_name',
            'uom': 'item_uom',
            'quantity': 'quantity',
            'description': 'description',
            'tech req': 'technical_req'
        }
    };

    const GridBridge = {
        init: function() {
            $('#btn-import').on('click', () => $('#excel-file-input').click());
            $('#excel-file-input').on('change', this.handleFileSelect.bind(this));
            $('#btn-export').on('click', this.handleExport.bind(this));
            $('#btn-clear').on('click', this.handleClear.bind(this));
        },

        handleFileSelect: function(e) {
            const file = e.target.files[0];
            if (!file) return;

            this.updateStatus('Reading file...', 'info');
            const reader = new FileReader();

            reader.onload = (evt) => {
                try {
                    const data = evt.target.result;
                    const workbook = XLSX.read(data, { type: 'binary' });
                    const sheetName = workbook.SheetNames[0];
                    const sheet = workbook.Sheets[sheetName];
                    
                    // Parse to JSON with header:1 to get array of arrays
                    const rawData = XLSX.utils.sheet_to_json(sheet, { header: 1 });
                    
                    if (rawData.length < 2) {
                        this.updateStatus('File is empty or has no data rows.', 'error');
                        return;
                    }

                    this.processImport(rawData);
                } catch (err) {
                    console.error('Import Error:', err);
                    this.updateStatus('Failed to parse Excel file.', 'error');
                }
            };

            reader.readAsBinaryString(file);
            // Reset input to allow re-uploading same file
            $('#excel-file-input').val('');
        },

        processImport: function(rawData) {
            const headers = rawData[0].map(h => String(h).trim().toLowerCase());
            const columnIndices = {};

            // Build index map based on CONFIG.headerMap
            headers.forEach((h, idx) => {
                if (CONFIG.headerMap.hasOwnProperty(h)) {
                    columnIndices[CONFIG.headerMap[h]] = idx;
                }
            });

            const missingFields = Object.keys(CONFIG.headerMap).filter(f => !columnIndices.hasOwnProperty(f));
            if (missingFields.length > 0) {
                this.updateStatus(`Missing columns: ${missingFields.join(', ')}`, 'error');
                return;
            }

            const rowsToImport = rawData.slice(1).filter(r => r.length > 0);
            this.injectRows(rowsToImport, columnIndices);
        },

        injectRows: async function(rows, colMap) {
            const gridEl = FormUtil.getField(CONFIG.gridId);
            const addFnName = `${gridEl.attr('id')}_add`;
            const addFn = window[addFnName];

            if (typeof addFn !== 'function') {
                this.updateStatus('Grid add function not found. Check Grid ID.', 'error');
                return;
            }

            this.updateStatus(`Importing ${rows.length} rows...`, 'info');

            for (let i = 0; i < rows.length; i++) {
                const row = rows[i];
                const rowData = {};

                // Map data using column indices
                for (const [fieldId, colIdx] of Object.entries(colMap)) {
                    rowData[fieldId] = row[colIdx] !== undefined ? String(row[colIdx]) : '';
                }

                // Trigger Joget Grid Add
                addFn({
                    'result': JSON.stringify(rowData)
                });

                // Batch processing to yield UI thread
                if ((i + 1) % CONFIG.batchSize === 0) {
                    this.updateStatus(`Processed ${i + 1}/${rows.length} rows...`, 'info');
                    await new Promise(resolve => setTimeout(resolve, CONFIG.delayMs));
                }
            }

            this.updateStatus('Import complete!', 'success');
        },

        handleExport: function() {
            const tableEl = document.querySelector(`#${CONFIG.gridId} .tablesaw`);
            if (!tableEl) {
                this.updateStatus('No grid data to export.', 'error');
                return;
            }

            // Clone and clean
            const clone = tableEl.cloneNode(true);
            $(clone).find('.grid-action-cell, th:last-child, td:last-child').remove();
            $(clone).find('input, select, textarea').each(function() {
                this.removeAttribute('name');
                this.removeAttribute('id');
            });

            const wb = XLSX.utils.book_new();
            const ws = XLSX.utils.table_to_sheet(clone);
            XLSX.utils.book_append_sheet(wb, ws, 'Export');
            XLSX.writeFile(wb, `${CONFIG.gridId}_export.xlsx`);
            this.updateStatus('Export downloaded.', 'success');
        },

        handleClear: function() {
            if (confirm('Clear all rows from the grid?')) {
                const gridEl = FormUtil.getField(CONFIG.gridId);
                const clearFnName = `${gridEl.attr('id')}_clear`;
                const clearFn = window[clearFnName];
                
                if (typeof clearFn === 'function') {
                    clearFn();
                } else {
                    // Fallback DOM manipulation
                    $(`#${CONFIG.gridId} tbody tr:not(:first-child)`).remove();
                }
                this.updateStatus('Grid cleared.', 'info');
            }
        },

        updateStatus: function(msg, type) {
            const el = $('#import-status');
            el.text(msg)
              .removeClass('info error success')
              .addClass(type === 'error' ? 'text-danger' : type === 'success' ? 'text-success' : 'text-muted');
        }
    };

    $(document).ready(() => GridBridge.init());
})();

Pitfall Guide

Pitfall	Explanation	Fix
UI Thread Blocking	Injecting hundreds of rows synchronously freezes the browser, causing the user to think the app crashed.	Use async/await with setTimeout yields. Process rows in batches (e.g., 10–20 rows) with a 30–50ms delay to allow the browser to repaint.
Brittle Column Indexing	Relying on row[0] for "Item Name" breaks if the user adds a column or reorders the Excel file.	Implement dynamic header mapping. Parse the first row to find the index of each header name, then map data based on that index.
Export Action Columns	Exporting the grid directly includes "Edit" and "Delete" buttons, cluttering the Excel file.	Clone the DOM table before export and remove action cells (th:last-child, .grid-action-cell) and input attributes.
Grid ID Mismatch	Joget may generate dynamic IDs for grids, causing FormUtil.getField to fail or target the wrong element.	Always use the explicit ID set in the Form Builder. Verify the ID exists in the DOM before binding events.
Type Coercion Errors	Excel may return numbers as Number types, but Joget fields might expect strings, causing validation errors.	Explicitly cast values to strings (String(row[colIdx])) during the mapping phase before injection.
Missing Header Validation	If a required column is missing in the Excel file, the script may silently skip data or crash.	Validate that all mapped headers exist in the Excel file before processing. Return a clear error message listing missing columns.
Memory Leaks on Re-import	Uploading a file multiple times without clearing can duplicate data or accumulate event listeners.	Ensure the file input is reset after selection. Provide a "Clear Grid" function. Use IIFE or namespacing to avoid global scope pollution.

Production Bundle

Action Checklist

• Verify SheetJS Version: Ensure the SheetJS library is loaded before your script executes. Check the network tab for xlsx.full.min.js.
• Define Header Map: Update CONFIG.headerMap to match the exact headers your users will use in Excel. Normalize keys to lowercase.
• Test Async Limits: Adjust CONFIG.batchSize and CONFIG.delayMs based on your grid's complexity. Complex grids with many fields may require smaller batches.
• Validate Export Columns: Review the exported Excel file to ensure no action columns or internal IDs are leaking.
• Security Review: Confirm that no Excel data is logged to the console or sent to external analytics endpoints.
• User Feedback: Ensure the status messages provide clear feedback during long imports (e.g., "Processing 50/200 rows").

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
< 500 Rows, Sensitive Data	Client-Side SheetJS	Zero server load, instant feedback, data stays local.	Low (Client resources only).
> 5,000 Rows	Server-Side Import	Client-side parsing may OOM or timeout. Server handles streaming better.	Medium (Server compute).
Complex Validation Rules	Hybrid Approach	Client-side import for speed, but validate on submit via server-side plugin.	Medium (Dev effort).
Strict Schema Enforcement	Server-Side with Template	Server can reject malformed files and return detailed error reports.	Medium (Server logic).

Configuration Template

Copy this configuration object to customize the bridge for your specific grid.

const GRID_EXCEL_CONFIG = {
    // Joget Form Grid ID
    gridId: 'your_grid_id_here',
    
    // Performance tuning
    batchSize: 20,      // Rows per batch
    delayMs: 40,        // ms yield between batches
    
    // Header Mapping: Excel Header -> Joget Field ID
    // Ensure Excel headers match keys exactly (case-insensitive)
    headerMap: {
        'product code': 'prod_code',
        'quantity': 'qty',
        'unit price': 'price',
        'notes': 'notes'
    },
    
    // Export settings
    exportFilename: 'grid_data_export.xlsx'
};

Quick Start Guide

1. Add Library: Include SheetJS in your Joget Userview settings.
2. Insert HTML: Add the HTML container snippet to a Custom HTML element above your grid.
3. Configure Script: Paste the JavaScript logic, update CONFIG.gridId and CONFIG.headerMap to match your form.
4. Test Import: Create a sample Excel file with headers matching your map. Upload via the button and verify rows appear.
5. Test Export: Click Export and verify the downloaded file contains clean data without action columns.