React-spread-sheet-excel

A high-performance React component for building Excel-like spreadsheets with advanced features.

A simple very light weight component to Render large lists of input boxes in the table using React JS, Render a table with a large number of rows and columns. Able to render 1Lakh+ input boxes in react, A quick solution for web based spreadsheet or excel.

Live Demo

Demo

Features

• Blazing Fast Rendering: Handles large datasets efficiently (1Lakh+ input boxes).
• Comprehensive Calculation Engine: Supports complex formulas and calculations. (= 2 * A2 + (B2 * C4))
• Rich Formatting Options: Customize cell appearance with bold, italic, underline, and more.
• Undo, Redo actions.
• Calculations Support, value should starts with "="
• Select Multiple cells
• Intuitive Keyboard Navigation: Navigate and edit cells effortlessly.
• Delete values in selected cells.
• Customizable header values - (Do not pass numbers in header values, will affect calc)
• Resize columns
• Can Use as a Spreadsheet or Excel with react
• Flexible Data Management: Import, export, and manipulate data.
• Customizable Headers and Columns: Tailor the spreadsheet to your application.
• Read-Only Mode: Protect data from accidental modifications.
• CSV Export: Easily share data in a common format.
• Sticky Headers: Keep headers visible while scrolling.
• 100% Unit Test Coverage: Ensures reliability and stability.
• JSON based SpreadSheet

Getting Started

Input data format

[
  [{"value": 1},{"value": 1},{"value": "a"},{"value": "b"},{"value": "d"}]
]

npm install react-spread-sheet-excel

Example

import React, { useRef, useState } from "react";
import Sheet, { SheetRef } from "react-spread-sheet-excel";

//Create dummy data.
const createData = (count?: number) => {
  const val: any[][] = [];
  for (let i = 0; i < (count || 500) ; i++) {
    val.push(
      Array.from({ length: count || 30 }, () => ({
        value: "",
      }))
    );
  }
  return val;
};

function App() {
  const [state] = useState<any[][]>(createData());
  const childRef = useRef<SheetRef>(null);
  const fileInputRef = useRef<HTMLInputElement>(null);

  const onChange = (i: number, j: number, value: string) => {
    //Do not try to update state with this action, it will slow down your application
    console.log(`Value Updated at ${j}, ${j}`, value);
  };

  const getData = () => {
    console.log("Updated Data", childRef?.current?.getData()); 
  };

  //Generate CSV
  const exportCSV = () => {
    childRef?.current?.exportCsv("myCsvFile", false);
  };

  return (
    <div>
      <div>
        <button onClick={getData}>Get Updated data</button>
        <button data-testid="csv-export" onClick={exportCSV}>Export CSV data</button>
        <button onClick={() => exportToXlsx(childRef?.current?.getData() ?? [])}>Export XLSX</button>
        <label>
          <input
            ref={fileInputRef}
            type="file"
            accept=".xlsx"
            hidden
            onChange={handleFileChange}
          />
          <button onClick={handleImportClick}>Import XLSX</button>
        </label>
      </div>
      <div>
        <Sheet data={state} onChange={onChange} ref={childRef} />
      </div>
    </div>
  );
}

export default App;

XLSX Export/Import

npm i @e965/xlsx

import React, { useRef, useState } from "react";
import { getCalculatedVal, printToLetter } from "react-spread-sheet-excel";
import * as XLSX from "@e965/xlsx";


const importFromXlsx = (
  file: File,
  onSuccess: (data: any[][]) => void,
  onError?: (err: Error) => void,
) => {
  const reader = new FileReader();
  reader.onload = (e) => {
    try {
      const arrayBuffer = e.target?.result as ArrayBuffer;
      const workbook = XLSX.read(arrayBuffer, { type: "array" });
      const workSheet = workbook.Sheets[workbook.SheetNames[0]];
      const jsonData = XLSX.utils.sheet_to_json(workSheet, { header: 1 }) as any[][];

      const formatted = jsonData.map((row) => row.map((cell) => ({ value: cell })));
      onSuccess(formatted);
    } catch (err) {
      onError?.(err as Error);
    }
  };
  reader.readAsArrayBuffer(file);
};

const exportToXlsx = (
  results: any[][],
  fileName = "myXlsxFile.xlsx",
  headerValues?: string[],
  includeHeaders: boolean = false,
) => {
  const header = results[0].map((d, i) => printToLetter(i + 1, headerValues));
  const aoa: any[][] = [];

  if (includeHeaders) {
    aoa.push(["", ...header]); // Add column letters as header row
  }
  results.forEach((rowItem, rowIndex) => {
    const row: any[] = [];
    if (includeHeaders) row.push(rowIndex); // Add row number
    rowItem.forEach((colVal) => {
      let val = colVal.value;
      if (typeof val === "string" && val.trim().startsWith("=")) {
        row.push(getCalculatedVal(val, results, headerValues)); // using calculated val func
      } else {
        row.push(val);
      }
    });

    aoa.push(row);
  });

  const worksheet = XLSX.utils.aoa_to_sheet(aoa);
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
  XLSX.writeFile(workbook, fileName);
};

function App() {
  const [state] = useState<any[][]>([[]]);
  const childRef = useRef<SheetRef>(null);
  const fileInputRef = useRef<HTMLInputElement>(null);
  
  const handleImportClick = () => {
    fileInputRef.current?.click();
  };

  const handleFileChange = (e:any) =>{
    const file = e.target.files?.[0];
    if (file) {
      importFromXlsx(file, (data) => {
        childRef?.current?.setData(data);
      });
    }
  }

  return (
    <div>
      <div>
        <button onClick={() => exportToXlsx(childRef?.current?.getData() ?? [])}>Export XLSX</button>
        <label>
          <input
            ref={fileInputRef}
            type="file"
            accept=".xlsx"
            hidden
            onChange={handleFileChange}
          />
          <button onClick={handleImportClick}>Import XLSX</button>
        </label>
      </div>
      <div>
        <Sheet data={state} onChange={onChange} ref={childRef} />
      </div>
    </div>
  );
}

export default App;

props

Prop	Description	Default	Mandatory	type
data	Array of array with values (matrix)	[[]]	No	any[][]
onChange	Calls when a change is detected in input boxes, Do not set render component when value changes, the component should be uncontrolled		No
resize	show column resize option	false	No	boolean
hideXAxisHeader	Show serial numbers in X axis	false	No	boolean
hideYAxisHeader	Show serial numbers in Y axis	false	No	boolean
headerValues	array of header values, Number in header values could affect calculations	alphabets	No	string[]
hideTools	Hide tools	false	No	boolean

Ref (API's)

Ref	Description	Params
getData	Get updated data from sheet
setData	Set new data to sheet	[{ value: string; styles?: {[key: string]: string}}, ...]
exportCsv	Export to CSV	filename: (Mandatory), IncludeHeaders (default false)

Try here

Sandbox

Stackblitz

Performance

Benchmark: Rendered 1000 rows and 100 columns in mills. Optimization Techniques: Global state, Lazy loading.

Sojin Antony

Acknowledgments

• React-intersection-observer