如何实现Excel表格数据与Handsontable的双向数据互通？

Absolutely! You can absolutely build a two-way bridge between Excel and Handsontable—this is a super common use case, and it’s totally manageable with a couple of reliable tools. Let’s walk through exactly how to import Excel data into Handsontable and export Handsontable data back to Excel, step by step.

We’ll use SheetJS (xlsx) for parsing Excel files—it’s a lightweight, battle-tested library that handles both .xlsx and .xls formats perfectly. Handsontable doesn’t have built-in Excel parsing, so this library fills that gap.

Step 1: Set up your dependencies #

You have two options here—use CDNs for quick setup, or install via npm if you’re using a module bundler:

Option A: CDN links

Add these scripts/styles to your HTML:

<!-- Handsontable CSS -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/handsontable/dist/handsontable.full.min.css">
<!-- Handsontable JS -->
<script src="https://cdn.jsdelivr.net/npm/handsontable/dist/handsontable.full.min.js"></script>
<!-- SheetJS (xlsx) -->
<script src="https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js"></script>

Option B: npm installation

If you’re using Webpack, Vite, or similar:

npm install handsontable xlsx

Then import them in your JavaScript file:

import Handsontable from 'handsontable';
import 'handsontable/dist/handsontable.full.min.css';
import * as XLSX from 'xlsx';

Step 2: Create your HTML structure #

Add a file input for uploading Excel files, plus a container for Handsontable:

<input type="file" id="excelUpload" accept=".xlsx, .xls">
<div id="hotContainer"></div>

Step 3: Write the JavaScript logic #

Initialize Handsontable, then add an event listener to handle file uploads and data parsing:

// Initialize Handsontable with empty data
const hot = new Handsontable(document.getElementById('hotContainer'), {
  data: [],
  rowHeaders: true,
  colHeaders: true,
  contextMenu: true,
  height: 'auto',
  width: '100%'
});

// Handle Excel file upload
document.getElementById('excelUpload').addEventListener('change', (e) => {
  const file = e.target.files[0];
  if (!file) return;

  const reader = new FileReader();
  reader.onload = (event) => {
    // Parse the Excel file
    const data = new Uint8Array(event.target.result);
    const workbook = XLSX.read(data, { type: 'array' });

    // Get the first worksheet (adjust this if you need to support multiple sheets)
    const firstSheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[firstSheetName];

    // Convert worksheet data to an array of arrays (AoA)
    const excelData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });

    // Update Handsontable with the parsed data
    hot.loadData(excelData);

    // Optional: Set colHeaders to the first row of Excel data (and remove it from the main data)
    const headers = excelData[0];
    hot.updateSettings({
      colHeaders: headers,
      data: excelData.slice(1)
    });
  };
  reader.readAsArrayBuffer(file);
});

Quick import notes: #

• For multi-sheet Excel files, loop through workbook.SheetNames to let users select which sheet to import.
• Use sheet_to_json options like raw: false to keep formatted values (instead of raw cell data).

Again, we’ll use SheetJS to convert Handsontable’s data into a downloadable Excel file.

Step 1: Add an export button to your HTML #

<button id="exportToExcel">Export to Excel</button>

Step 2: Write the export logic #

Add a click event listener to the button that grabs Handsontable’s data, converts it to an Excel worksheet, and triggers a download:

document.getElementById('exportToExcel').addEventListener('click', () => {
  // Get all data from Handsontable
  const hotData = hot.getData();
  const colHeaders = hot.getColHeader();

  // Combine headers with data (remove this line if you don't want headers in the Excel file)
  const exportData = [colHeaders, ...hotData];

  // Create a worksheet from the data array
  const worksheet = XLSX.utils.aoa_to_sheet(exportData);

  // Create a workbook and add the worksheet
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Handsontable Data');

  // Export the workbook as an Excel file
  XLSX.writeFile(workbook, 'handsontable-data.xlsx');
});

Quick export notes: #

• Customize the worksheet name (replace 'Handsontable Data') and downloaded file name ('handsontable-data.xlsx') to fit your needs.
• For advanced formatting (like cell styles or number formats), you can use SheetJS’s cell styling APIs—just note that this adds a bit more complexity to the code.

Wrap-Up #

This setup gives you a smooth two-way integration between Excel and Handsontable. You can extend it further to handle edge cases like merged cells, date formatting, or custom validation rules, but the core logic here covers the basic import/export flow perfectly.

内容的提问来源于stack exchange，提问作者Priyal Pithadiya