Excel 表格和 Node.js 实现数据转换工具

这个工具的核心思路是：

前端：使用 SheetJS (xlsx) 解析和导出 Excel 数据，并提供 UI 进行增删改查。
后端 (Node.js + Express)：处理 Excel 数据的转换、存储，并提供 API 接口。

1. 安装依赖

在前端和后端项目分别安装 xlsx 相关依赖。

javascript">npm install xlsx

后端 (Node.js + Express)

javascript">npm install express multer xlsx fs

2. 前端实现 (Vue 3 + SheetJS)

前端主要实现：

解析 Excel 文件，并展示为可编辑的表格
提供增删改查操作
处理 Excel 文件的导出

前端代码

javascript"><template><div><input type="file" @change="handleFileUpload" /><button @click="exportExcel">导出 Excel</button><table border="1"><thead><tr><th v-for="(col, index) in columns" :key="index">{{ col }}</th><th>操作</th></tr></thead><tbody><tr v-for="(row, rowIndex) in tableData" :key="rowIndex"><td v-for="(value, colIndex) in row" :key="colIndex"><input v-model="tableData[rowIndex][columns[colIndex]]" /></td><td><button @click="deleteRow(rowIndex)">删除</button></td></tr></tbody></table><button @click="addRow">添加行</button></div>
</template><script setup>
import { ref } from "vue";
import * as XLSX from "xlsx";const tableData = ref([]);
const columns = ref([]);// 解析上传的 Excel 文件
const handleFileUpload = (event) => {const file = event.target.files[0];const reader = new FileReader();reader.onload = (e) => {const data = new Uint8Array(e.target.result);const workbook = XLSX.read(data, { type: "array" });const sheetName = workbook.SheetNames[0];const sheet = workbook.Sheets[sheetName];const parsedData = XLSX.utils.sheet_to_json(sheet);if (parsedData.length) {columns.value = Object.keys(parsedData[0]);tableData.value = parsedData;}};reader.readAsArrayBuffer(file);
};// 添加新行
const addRow = () => {const newRow = {};columns.value.forEach((col) => {newRow[col] = "";});tableData.value.push(newRow);
};// 删除行
const deleteRow = (index) => {tableData.value.splice(index, 1);
};// 导出 Excel 文件
const exportExcel = () => {const worksheet = XLSX.utils.json_to_sheet(tableData.value);const workbook = XLSX.utils.book_new();XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");XLSX.writeFile(workbook, "table_data.xlsx");
};
</script>

3. 后端实现 (Node.js + Express + Multer)

后端主要实现：

解析 Excel 并转换为 JSON
处理增删改查 API
支持上传和下载 Excel

后端代码

javascript">const express = require("express");
const multer = require("multer");
const fs = require("fs");
const XLSX = require("xlsx");const app = express();
const upload = multer({ dest: "uploads/" });app.use(express.json());// 解析 Excel 并转换为 JSON
app.post("/upload", upload.single("file"), (req, res) => {const filePath = req.file.path;const workbook = XLSX.readFile(filePath);const sheetName = workbook.SheetNames[0];const sheet = workbook.Sheets[sheetName];const jsonData = XLSX.utils.sheet_to_json(sheet);fs.unlinkSync(filePath); // 删除临时文件res.json({ data: jsonData });
});// 导出 Excel
app.get("/export", (req, res) => {const sampleData = [{ Name: "Alice", Age: 25, City: "New York" },{ Name: "Bob", Age: 30, City: "Los Angeles" },];const worksheet = XLSX.utils.json_to_sheet(sampleData);const workbook = XLSX.utils.book_new();XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");const filePath = "export.xlsx";XLSX.writeFile(workbook, filePath);res.download(filePath, () => fs.unlinkSync(filePath)); // 发送后删除
});app.listen(3000, () => console.log("Server running on port 3000"));