Advanced Spreadsheet Implementation with RevoGrid in React

RevoGrid is a high-performance, virtualized spreadsheet component for React that can handle millions of rows and columns with smooth scrolling and editing capabilities. Built with Web Components and optimized for performance, it's ideal for enterprise applications requiring Excel-like functionality. This guide walks through implementing advanced spreadsheet features using RevoGrid with React, covering virtual scrolling, custom cell editors, and complex data manipulation. This is part 6 of a series on using RevoGrid with React.

Prerequisites

Before you begin, ensure you have:

• Node.js version 16.0 or higher
• npm, yarn, or pnpm package manager
• A React project (version 16.8 or higher) with hooks support
• Advanced understanding of React hooks, refs, and performance optimization
• Familiarity with Web Components and virtualization concepts
• Knowledge of TypeScript (highly recommended)

Installation

Install RevoGrid React wrapper:

npm install @revolist/revogrid-react

Or with yarn:

yarn add @revolist/revogrid-react

Or with pnpm:

pnpm add @revolist/revogrid-react

Your package.json should include:

{
  "dependencies": {
    "@revolist/revogrid-react": "^6.0.0",
    "react": "^18.0.0",
    "react-dom": "^18.0.0"
  }
}

Project Setup

RevoGrid requires minimal setup. Import the component and styles in your application:

// src/index.js
import React from 'react';
import ReactDOM from 'react-dom/client';
import '@revolist/revogrid/dist/revogrid.css';
import App from './App';

const root = ReactDOM.createRoot(document.getElementById('root'));
root.render(
  <React.StrictMode>
    <App />
  </React.StrictMode>
);

First Example / Basic Usage

Let's create a basic RevoGrid component. Create src/Spreadsheet.jsx:

// src/Spreadsheet.jsx
import React, { useRef, useEffect } from 'react';
import { RevoGrid } from '@revolist/revogrid-react';
import '@revolist/revogrid/dist/revogrid.css';

function Spreadsheet() {
  const gridRef = useRef(null);

  // Column definitions
  const columns = [
    { prop: 'id', name: 'ID', size: 100 },
    { prop: 'name', name: 'Name', size: 200 },
    { prop: 'email', name: 'Email', size: 250 },
    { prop: 'role', name: 'Role', size: 150 }
  ];

  // Row data
  const rows = [
    { id: 1, name: 'John Doe', email: '[e-mail]', role: 'Admin' },
    { id: 2, name: 'Jane Smith', email: '[e-mail]', role: 'User' },
    { id: 3, name: 'Bob Johnson', email: '[e-mail]', role: 'User' },
    { id: 4, name: 'Alice Williams', email: '[e-mail]', role: 'Admin' }
  ];

  useEffect(() => {
    if (gridRef.current) {
      const grid = gridRef.current;
      // Set data after component mounts
      grid.columns = columns;
      grid.source = rows;
    }
  }, []);

  return (
    <div style={{ height: '500px', width: '100%' }}>
      <RevoGrid ref={gridRef} />
    </div>
  );
}

export default Spreadsheet;

Understanding the Basics

RevoGrid uses a column-based configuration where:

• prop: Maps to a property in your row data
• name: Column header text
• size: Column width in pixels
• editor: Custom cell editor component
• cellTemplate: Custom cell renderer

Key concepts for advanced usage:

• Virtualization: Automatically handles large datasets through virtual scrolling
• Web Components: Built on Web Components standard for performance
• Refs: Use refs to access grid API and methods
• Event Handlers: Respond to cell edits, selections, and other interactions

Here's an example with editable cells:

// src/EditableSpreadsheet.jsx
import React, { useRef, useEffect, useState } from 'react';
import { RevoGrid } from '@revolist/revogrid-react';
import '@revolist/revogrid/dist/revogrid.css';

function EditableSpreadsheet() {
  const gridRef = useRef(null);
  const [data, setData] = useState([
    { id: 1, product: 'Laptop', price: 999.99, stock: 15 },
    { id: 2, product: 'Mouse', price: 29.99, stock: 8 },
    { id: 3, product: 'Keyboard', price: 79.99, stock: 12 }
  ]);

  const columns = [
    { prop: 'id', name: 'ID', size: 80, readonly: true },
    { prop: 'product', name: 'Product', size: 200, editor: 'string' },
    { 
      prop: 'price', 
      name: 'Price', 
      size: 120, 
      editor: 'number',
      cellTemplate: (h, { value }) => `$${value.toFixed(2)}`
    },
    { prop: 'stock', name: 'Stock', size: 100, editor: 'number' }
  ];

  useEffect(() => {
    if (gridRef.current) {
      const grid = gridRef.current;
      grid.columns = columns;
      grid.source = data;

      // Handle cell editing
      grid.addEventListener('beforecellfocus', (e) => {
        console.log('Cell focus:', e.detail);
      });

      grid.addEventListener('aftercellfocus', (e) => {
        console.log('Cell edited:', e.detail);
        // Update data state
        const { row, prop, val } = e.detail;
        setData(prev => prev.map((item, idx) => 
          idx === row ? { ...item, [prop]: val } : item
        ));
      });
    }
  }, [data]);

  return (
    <div style={{ height: '400px', width: '100%', border: '1px solid #ddd' }}>
      <RevoGrid ref={gridRef} />
    </div>
  );
}

export default EditableSpreadsheet;

Practical Example / Building Something Real

Let's build a comprehensive financial data spreadsheet with formulas, formatting, and advanced features:

// src/FinancialSpreadsheet.jsx
import React, { useRef, useEffect, useState, useCallback } from 'react';
import { RevoGrid } from '@revolist/revogrid-react';
import '@revolist/revogrid/dist/revogrid.css';

function FinancialSpreadsheet() {
  const gridRef = useRef(null);
  const [data, setData] = useState([]);

  // Initialize with sample financial data
  useEffect(() => {
    const initialData = [
      { month: 'January', revenue: 50000, expenses: 30000, profit: 20000 },
      { month: 'February', revenue: 55000, expenses: 32000, profit: 23000 },
      { month: 'March', revenue: 60000, expenses: 35000, profit: 25000 },
      { month: 'April', revenue: 58000, expenses: 33000, profit: 25000 },
      { month: 'May', revenue: 62000, expenses: 36000, profit: 26000 },
      { month: 'June', revenue: 65000, expenses: 38000, profit: 27000 }
    ];
    setData(initialData);
  }, []);

  const columns = [
    { 
      prop: 'month', 
      name: 'Month', 
      size: 150,
      pinned: 'left',
      readonly: true
    },
    { 
      prop: 'revenue', 
      name: 'Revenue', 
      size: 150,
      editor: 'number',
      cellTemplate: (h, { value }) => {
        return h('span', { style: { color: '#28a745', fontWeight: 'bold' } }, `$${value.toLocaleString()}`);
      }
    },
    { 
      prop: 'expenses', 
      name: 'Expenses', 
      size: 150,
      editor: 'number',
      cellTemplate: (h, { value }) => {
        return h('span', { style: { color: '#dc3545', fontWeight: 'bold' } }, `$${value.toLocaleString()}`);
      }
    },
    { 
      prop: 'profit', 
      name: 'Profit', 
      size: 150,
      editor: 'number',
      readonly: true,
      cellTemplate: (h, { value }) => {
        const color = value > 0 ? '#28a745' : '#dc3545';
        return h('span', { style: { color, fontWeight: 'bold' } }, `$${value.toLocaleString()}`);
      }
    },
    {
      prop: 'margin',
      name: 'Margin %',
      size: 120,
      readonly: true,
      cellTemplate: (h, { row }) => {
        const margin = ((row.profit / row.revenue) * 100).toFixed(2);
        const color = parseFloat(margin) > 30 ? '#28a745' : parseFloat(margin) > 20 ? '#ffc107' : '#dc3545';
        return h('span', { style: { color, fontWeight: 'bold' } }, `${margin}%`);
      }
    }
  ];

  useEffect(() => {
    if (gridRef.current && data.length > 0) {
      const grid = gridRef.current;

      // Calculate profit automatically when revenue or expenses change
      const updatedData = data.map(row => ({
        ...row,
        profit: row.revenue - row.expenses
      }));

      grid.columns = columns;
      grid.source = updatedData;

      // Handle cell editing
      const handleCellEdit = (e) => {
        const { row, prop, val } = e.detail;
        const newData = [...data];

        if (newData[row]) {
          newData[row] = { ...newData[row], [prop]: parseFloat(val) || 0 };
          // Recalculate profit
          newData[row].profit = newData[row].revenue - newData[row].expenses;
          setData(newData);
        }
      };

      grid.addEventListener('aftercellfocus', handleCellEdit);

      return () => {
        grid.removeEventListener('aftercellfocus', handleCellEdit);
      };
    }
  }, [data, columns]);

  const handleExport = useCallback(() => {
    if (gridRef.current) {
      const grid = gridRef.current;
      // Export functionality would be implemented here
      console.log('Exporting data:', data);
    }
  }, [data]);

  const handleAddRow = useCallback(() => {
    setData(prev => [...prev, {
      month: `Month ${prev.length + 1}`,
      revenue: 0,
      expenses: 0,
      profit: 0
    }]);
  }, []);

  return (
    <div style={{ padding: '20px' }}>
      <div style={{ marginBottom: '20px', display: 'flex', gap: '10px' }}>
        <h2>Financial Data Spreadsheet</h2>
        <button
          onClick={handleAddRow}
          style={{
            padding: '8px 16px',
            backgroundColor: '#007bff',
            color: 'white',
            border: 'none',
            borderRadius: '4px',
            cursor: 'pointer'
          }}
        >
          Add Row
        </button>
        <button
          onClick={handleExport}
          style={{
            padding: '8px 16px',
            backgroundColor: '#28a745',
            color: 'white',
            border: 'none',
            borderRadius: '4px',
            cursor: 'pointer'
          }}
        >
          Export
        </button>
      </div>
      <div style={{ height: '600px', width: '100%', border: '1px solid #ddd' }}>
        <RevoGrid 
          ref={gridRef}
          theme="material"
          range={true}
          resize={true}
          rowHeaders={true}
          columnHeaders={true}
        />
      </div>
    </div>
  );
}

export default FinancialSpreadsheet;

This advanced example demonstrates:

• Automatic formula calculation (profit = revenue - expenses)
• Custom cell templates with conditional styling
• Pinned columns (month column stays fixed)
• Real-time data updates
• Cell editing with validation
• Dynamic row addition
• Export functionality preparation
• Material theme styling

Common Issues / Troubleshooting

1. Grid not rendering: Ensure you've imported the CSS file (@revolist/revogrid/dist/revogrid.css). The grid requires explicit height on the container div.

2. Data not displaying: Make sure you're setting both columns and source properties on the grid ref after it mounts. Use useEffect to ensure the ref is available.

3. Cell editing not working: Verify that you've set editor property in column definitions and are handling the aftercellfocus event properly.

4. Performance issues: RevoGrid handles virtualization automatically, but for extremely large datasets (millions of rows), consider implementing data pagination or lazy loading.

5. TypeScript errors: Install type definitions if available, or create your own type declarations for the RevoGrid component and its API.

6. Event listeners not firing: Ensure you're adding event listeners in useEffect and cleaning them up properly to avoid memory leaks.

Next Steps

Now that you've mastered RevoGrid basics:

• Explore advanced features like row grouping and aggregation
• Implement custom cell editors and renderers
• Add formula support and calculation engine
• Learn about column resizing and reordering
• Implement server-side data loading
• Add export/import functionality (CSV, Excel)
• Explore theming and customization options
• Check the official repository: https://github.com/revolist/revogrid
• Look for part 7 of this series for more advanced topics

Summary

You've learned how to implement advanced spreadsheet functionality with RevoGrid, including virtual scrolling, custom cell rendering, formula calculations, and real-time data updates. RevoGrid provides excellent performance for large datasets and offers extensive customization options for building enterprise-grade spreadsheet applications.