Create Interactive Spreadsheets in jQuery - Excel Grid

Excel Grid Library is a lightweight yet feature-rich jQuery plugin that allows you to create professional, interactive, Excel-style data grids (spreadsheets).

What we found particularly useful is its ability to handle real-time calculations. You can define cells with formulas that reference other cells, and the grid automatically tracks these dependencies. When a value changes, any dependent cell recalculates instantly. 

Features:

• Real-time formula calculations: Excel-style formulas with = prefix and automatic recalculation.
• Smart keyboard navigation: Arrow keys for cell-to-cell movement and text cursor positioning.
• Cell reference support: A1-style references up to ZZ columns with dependency tracking.
• Error handling system: Circular reference detection and formula error reporting.
• Inline editing capabilities: Click-to-edit functionality with proper cursor management.
• Range functions: Built-in SUM function with support for cell ranges.
• Mobile responsive design: Touch-friendly interface that works across devices.

Keyboard Interactions:

• Click: Starts editing with cursor positioned at click location
• Double-click: Starts editing with all text selected
• Enter: Completes editing and stays in current cell
• Tab/Shift+Tab: Moves to next/previous cell horizontally
• Arrow keys: Moves cursor within text or between cells when at boundaries
• Escape: Cancels editing without saving changes

Use Cases:

• Financial calculators and budgeting tools: Build interactive budget planners where users can input expenses and see automatic totals with percentage calculations.
• Project management dashboards: Create time tracking sheets where team members can input hours and see calculated totals, rates, and project costs in real-time.
• Inventory management systems: Develop stock tracking interfaces where quantity changes automatically update total values, reorder points, and cost calculations.
• Educational math applications: Design interactive worksheets for students to practice formulas and see immediate results as they input different values.

How to use it:

1. Load the Excel Grid plugin's files in your HTML document.

<!-- jQuery is required -->
<script src="/path/to/cdn/jquery.min.js"></script>

<!-- Excel Grid -->
<script src="excel-grid.js"></script>
<link rel="stylesheet" href="excel-grid.css">

2. Create a container element for your data grid.

<div id="example" class="excel-grid-container"></div>

3. Initialize the plugin to generate an empty date grid.

$('#example').excelGrid({
  // options here
});

4. You can populate the grid with headers, static values, and formulas using the initialData option:

const productData = [
  ['Product', 'Price', 'Quantity', 'Total'],
  ['Laptop', 999.99, 5, '=B2*C2'],
  ['Mouse', 29.99, 20, '=B3*C3'],
  ['Keyboard', 79.99, 10, '=B4*C4'],
  ['Grand Total', '', '', '=D2+D3+D4']
];

$('#example').excelGrid({
  initialData: productData
});

5. More configuration options to customize the data grid.

• rows (Number, default: 10): Number of rows in the grid
• cols (Number, default: 10): Number of columns in the grid
• readOnly (Boolean, default: false): Makes the entire grid non-editable
• onChange (Function, default: null): Callback function triggered when cell values change

$('#example').excelGrid({
  rows: 10,
  cols: 10,
  readOnly: false,
  onChange: function(cellCoord, rawValue, evaluatedValue) {
    console.log(`Cell ${cellCoord.ref} changed. New raw value: ${rawValue}`);
  }
});

6. API methods.

• getData(): Returns current grid data with both raw input and calculated values.
• setData(data): Replaces all grid content with new data.
• destroy(): Removes the grid and restores the original DOM element.

// getData()
const gridInstance = $('#example').data('excelGrid');
const data = gridInstance.getData();
console.log('Raw formulas:', data.raw);
console.log('Calculated values:', data.evaluated);

// setData(data)
const newData = [['Name', 'Score'], ['Alice', 95], ['Bob', 87]];
gridInstance.setData(newData);

// destroy()
gridInstance.destroy();

7. The formula engine supports standard mathematical operations with proper precedence:

// Basic arithmetic
=2+2*3        // Results in 8 (multiplication first)
=(2+2)*3      // Results in 12 (parentheses override precedence)
=15/3-1       // Results in 4

// Cell references
=A1+B1        // Sum of two cells
=A1*B1/C1     // Complex calculation across cells
=(A1+B1)/2    // Average of two cells

// Range functions
=SUM(A1:C1)   // Sum of horizontal range
=SUM(A1:A5)   // Sum of vertical range

FAQs:

Q: Can I add custom functions beyond the built-in SUM function?
A: Yes, you can extend the formula parser by modifying the FormulaParser.parseAndEvaluate method. The parser recognizes function calls with parentheses syntax, so adding functions like AVERAGE or COUNT follows the same pattern as the existing SUM implementation.

Q: How does the library handle circular references?
A: The dependency tracking system detects circular references during calculation and displays "#CIRC" in affected cells. The error prevents infinite loops while allowing users to correct the problematic formulas. The detection algorithm traces reference chains to identify cycles before they cause browser freezing.

Q: Is the library compatible with modern JavaScript frameworks like React or Vue?
A: The library works within framework components but requires careful lifecycle management. Initialize the grid in componentDidMount or mounted hooks, and call destroy() during cleanup to prevent memory leaks. The jQuery dependency may conflict with some modern build tools that prefer ES modules.

Q: Can users copy and paste data from Excel into the grid?
A: The library supports basic text clipboard operations, but complex Excel formatting and formulas don't transfer directly. Users can paste tab-separated values, which the grid interprets as cell data. For full Excel compatibility, consider preprocessing pasted content to convert Excel formulas into the library's syntax.

Related Resources:

• 10 Best Spreadsheet-like Data Grid Libraries In JavaScript

---

This awesome jQuery plugin is developed by saadjatt. For more Advanced Usages, please check the demo page or visit the official website.