📁 Bulk Import & Export Excel Data for Joget Form Grids

('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.

```javascript
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.