# Directory Structure
```
├── .dockerignore
├── .github
│ └── workflows
│ └── publish.yml
├── .gitignore
├── .npmignore
├── dist
│ ├── handlers
│ │ ├── excelHandlers.js
│ │ └── logHandlers.js
│ ├── index.js
│ ├── tools
│ │ ├── cacheTools.js
│ │ ├── readTools.js
│ │ ├── structureTools.js
│ │ └── writeTools.js
│ ├── types
│ │ └── index.js
│ └── utils
│ ├── config.js
│ ├── excelUtils.js
│ ├── logCleaner.js
│ ├── utils.js
│ └── workbookCache.js
├── Dockerfile
├── LICENSE
├── package-lock.json
├── package.json
├── README_CN.md
├── README.md
├── smithery.yaml
├── src
│ ├── handlers
│ │ ├── excelHandlers.ts
│ │ └── logHandlers.ts
│ ├── index.ts
│ ├── tools
│ │ ├── cacheTools.ts
│ │ ├── readTools.ts
│ │ ├── structureTools.ts
│ │ └── writeTools.ts
│ ├── types
│ │ └── index.ts
│ └── utils
│ ├── config.ts
│ ├── excelUtils.ts
│ ├── logCleaner.ts
│ ├── utils.ts
│ └── workbookCache.ts
└── tsconfig.json
```
# Files
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # Excel MCP Server
2 | [](https://www.npmjs.com/package/@zhiweixu/excel-mcp-server)
3 | [](https://smithery.ai/server/@zhiwei5576/excel-mcp-server)
4 | [简体中文](./README_CN.md) | English
5 |
6 | Excel file processing server based on Model Context Protocol (MCP), providing functionalities for reading, writing, and analyzing Excel files.
7 |
8 | ## Features
9 |
10 | - 📖 Read Excel Files
11 |
12 | - Get worksheet list
13 | - Read specific worksheet data
14 | - Read all worksheets data
15 |
16 | - ✍️ Write Excel Files
17 |
18 | - Create new Excel files
19 | - Write to specific worksheet
20 | - Support multiple worksheets
21 |
22 | - 🔍 Analyze Excel Structure
23 |
24 | - Analyze worksheet structure
25 | - Export structure to new file
26 |
27 | - 💾 Cache Management
28 |
29 | - Automatic file content caching
30 | - Scheduled cache cleanup
31 | - Manual cache clearing
32 |
33 | - 📝 Log Management
34 | - Automatic operation logging
35 | - Periodic log cleanup
36 |
37 | ## Installation
38 |
39 | ### Installing via Smithery
40 |
41 | To install excel-mcp-server for Claude Desktop automatically via [Smithery](https://smithery.ai/server/@zhiwei5576/excel-mcp-server):
42 |
43 | ```bash
44 | npx -y @smithery/cli install @zhiwei5576/excel-mcp-server --client claude
45 | ```
46 |
47 | ### Installing Manually
48 | Installing via NPM
49 | excel-mcp-server can be automatically installed by adding the following configuration to the MCP servers configuration.
50 |
51 | Windows Platform:
52 |
53 | ```bash
54 | {
55 | "mcpServers": {
56 | "excel": {
57 | "command": "cmd",
58 | "args": ["/c", "npx", "--yes", "@zhiweixu/excel-mcp-server"],
59 | "env": {
60 | "LOG_PATH": "[set an accessible absolute path]",
61 | "CACHE_MAX_AGE": "1",
62 | "CACHE_CLEANUP_INTERVAL": "4",
63 | "LOG_RETENTION_DAYS": "7",
64 | "LOG_CLEANUP_INTERVAL": "24"
65 | }
66 | }
67 | }
68 | ```
69 |
70 | Other Platforms:
71 |
72 | ```bash
73 | {
74 | "mcpServers": {
75 | "excel": {
76 | "command": "npx",
77 | "args": ["--yes", "@zhiweixu/excel-mcp-server"],
78 | "env": {
79 | "LOG_PATH": "[set an accessible absolute path]",
80 | "CACHE_MAX_AGE": "1",
81 | "CACHE_CLEANUP_INTERVAL": "4",
82 | "LOG_RETENTION_DAYS": "7",
83 | "LOG_CLEANUP_INTERVAL": "24"
84 | }
85 | }
86 | }
87 | ```
88 | Note: LOG_PATH is optional. If not set, logs will be stored in the 'logs' folder under the application root directory.other arguments are optional.
89 |
90 | ## API Tools
91 |
92 | ### Structure Tools
93 |
94 | 1. analyzeExcelStructure
95 | - Function: Get Excel file structure including sheet list and column headers in JSON format
96 | - Parameters:
97 | - fileAbsolutePath: Absolute path of the Excel file
98 | - headerRows: Number of header rows (default: 1)
99 |
100 | 2. exportExcelStructure
101 | - Function: Export Excel file structure (sheets and headers) to a new Excel template file
102 | - Parameters:
103 | - sourceFilePath: Source Excel file path
104 | - targetFilePath: Target Excel file path
105 | - headerRows: Number of header rows (default: 1)
106 |
107 | ### Read Tools
108 |
109 | 1. readSheetNames
110 | - Function: Get all sheet names from the Excel file
111 | - Parameters:
112 | - fileAbsolutePath: Absolute path of the Excel file
113 |
114 | 2. readDataBySheetName
115 | - Function: Get data from a specific sheet in the Excel file
116 | - Parameters:
117 | - fileAbsolutePath: Absolute path of the Excel file
118 | - sheetName: Name of the sheet to read
119 | - headerRow: Header row number (default: 1)
120 | - dataStartRow: Data start row number (default: 2)
121 |
122 | 3. readSheetData
123 | - Function: Get data from all sheets in the Excel file
124 | - Parameters:
125 | - fileAbsolutePath: Absolute path of the Excel file
126 | - headerRow: Header row number (default: 1)
127 | - dataStartRow: Data start row number (default: 2)
128 |
129 | ### Write Tools
130 |
131 | 1. writeDataBySheetName
132 | - Function: Write data to a specific sheet in the Excel file (overwrites if sheet exists)
133 | - Parameters:
134 | - fileAbsolutePath: Absolute path of the Excel file
135 | - sheetName: Name of the sheet to write
136 | - data: Array of data to write
137 |
138 | 2. writeSheetData
139 | - Function: Create a new Excel file with provided data
140 | - Parameters:
141 | - fileAbsolutePath: Absolute path for the new Excel file
142 | - data: Object containing multiple sheet data
143 |
144 | ### Cache Tools
145 |
146 | 1. clearFileCache
147 | - Function: Clear cached data for the specified Excel file
148 | - Parameters:
149 | - fileAbsolutePath: Absolute path of the Excel file to clear from cache
150 |
151 | ## Configuration
152 |
153 | ### Environment Variables
154 |
155 | - `LOG_PATH`: Log files storage path
156 | - Optional
157 | - Default: 'logs' folder under application root directory
158 |
159 | - `CACHE_MAX_AGE`: Cache expiration time (hours)
160 | - Optional
161 | - Default: 1
162 |
163 | - `CACHE_CLEANUP_INTERVAL`: Cache cleanup interval (hours)
164 | - Optional
165 | - Default: 4
166 |
167 | - `LOG_RETENTION_DAYS`: Log retention days
168 | - Optional
169 | - Default: 7
170 |
171 | - `LOG_CLEANUP_INTERVAL`: Log cleanup interval (hours)
172 | - Optional
173 | - Default: 24
174 |
175 | ### Default Configuration
176 |
177 | - Cache Configuration
178 | - Cache expiration time: 1 hour
179 | - Cache cleanup interval: 4 hours
180 |
181 | - Log Configuration
182 | - Log retention days: 7 days
183 | - Cleanup interval: 24 hours
184 |
185 | ## Dependencies
186 |
187 | - @modelcontextprotocol/sdk: ^1.7.0
188 | - xlsx: ^0.18.5
189 | - typescript: ^5.8.2
190 |
191 | ## Development Dependencies
192 |
193 | - @types/node: ^22.13.10
194 | - nodemon: ^3.1.9
195 | - ts-node: ^10.9.2
196 |
197 | ## License
198 |
199 | This project is licensed under the MIT License. This means you are free to:
200 |
201 | - Use the software for commercial or non-commercial purposes
202 | - Modify the source code
203 | - Distribute original or modified code
204 | Requirements:
205 |
206 | - Retain the original copyright notice
207 | - No liability can be claimed against the authors for software use
208 | For detailed license information,please see the [LICENSE](./LICENSE) file.
209 |
```
--------------------------------------------------------------------------------
/src/handlers/logHandlers.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { LogCleaner } from "../utils/logCleaner.js";
2 |
3 |
4 | export function initializeLogger() {
5 | const logCleaner = new LogCleaner();
6 | logCleaner.start();
7 |
8 | return logCleaner;
9 | }
```
--------------------------------------------------------------------------------
/tsconfig.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "compilerOptions": {
3 | "target": "ES2020",
4 | "module": "NodeNext",
5 | "outDir": "./dist",
6 | "rootDir": "./src",
7 | "strict": true,
8 | "esModuleInterop": true,
9 | "resolveJsonModule": true,
10 | "skipLibCheck": true,
11 | "forceConsistentCasingInFileNames": true
12 | },
13 | "include": ["src/**/*"],
14 | "exclude": ["node_modules"]
15 | }
16 |
```
--------------------------------------------------------------------------------
/Dockerfile:
--------------------------------------------------------------------------------
```dockerfile
1 | # Generated by https://smithery.ai. See: https://smithery.ai/docs/config#dockerfile
2 | # 构建阶段
3 | FROM node:18 AS builder
4 |
5 | WORKDIR /app
6 |
7 | COPY package*.json ./
8 |
9 | RUN npm install
10 |
11 | COPY . .
12 |
13 | RUN npm run build
14 |
15 | # 生产阶段
16 | FROM node:18-slim
17 |
18 | WORKDIR /app
19 |
20 | COPY --from=builder /app/dist ./dist
21 | COPY package*.json ./
22 |
23 | RUN npm install --production
24 |
25 | EXPOSE 3000
26 |
27 | CMD ["node", "dist/index.js"]
28 |
```
--------------------------------------------------------------------------------
/smithery.yaml:
--------------------------------------------------------------------------------
```yaml
1 | # Smithery configuration file: https://smithery.ai/docs/config#smitheryyaml
2 |
3 | startCommand:
4 | type: stdio
5 | configSchema:
6 | # JSON Schema defining the configuration options for the MCP.
7 | type: object
8 | required: []
9 | properties:
10 | logPath:
11 | type: string
12 | description: Absolute path for log storage. Optional. If not provided, logs will
13 | be stored in the './logs' folder.
14 | commandFunction:
15 | # A JS function that produces the CLI command based on the given config to start the MCP on stdio.
16 | |-
17 | (config) => ({
18 | command: 'node',
19 | args: ['dist/index.js'],
20 | env: config.logPath ? { LOG_PATH: config.logPath } : {}
21 | })
22 | exampleConfig:
23 | logPath: /var/log/excel-mcp-server
24 |
```
--------------------------------------------------------------------------------
/.github/workflows/publish.yml:
--------------------------------------------------------------------------------
```yaml
1 | name: Publish Package
2 |
3 | on:
4 | push:
5 | tags:
6 | - 'v*'
7 |
8 | jobs:
9 | publish:
10 | runs-on: ubuntu-latest
11 | permissions:
12 | contents: write
13 | packages: write
14 | steps:
15 | - uses: actions/checkout@v3
16 |
17 | - name: Setup Node.js
18 | uses: actions/setup-node@v3
19 | with:
20 | node-version: '18'
21 | registry-url: 'https://registry.npmjs.org'
22 |
23 | - name: Install dependencies
24 | run: npm ci
25 |
26 | - name: Build
27 | run: npm run build
28 |
29 | - name: Publish to NPM
30 | run: npm publish
31 | env:
32 | NODE_AUTH_TOKEN: ${{ secrets.NPM_TOKEN }}
33 |
34 | - name: Create Release
35 | uses: softprops/action-gh-release@v1
36 | with:
37 | generate_release_notes: true
38 | draft: false
39 | prerelease: false
40 | env:
41 | GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
```
--------------------------------------------------------------------------------
/src/index.ts:
--------------------------------------------------------------------------------
```typescript
1 | #!/usr/bin/env node
2 |
3 | import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
4 | import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
5 | import packageJson from '../package.json' with { type: "json" };
6 | import { structureTools } from "./tools/structureTools.js";
7 | import { writeTools } from "./tools/writeTools.js";
8 | import { readTools } from "./tools/readTools.js";
9 | import { cacheTools } from "./tools/cacheTools.js";
10 | import { initializeLogger } from "./handlers/logHandlers.js";
11 |
12 | // Create an MCP server
13 | const server = new McpServer({
14 | name: "excel-mcp-server",
15 | version: packageJson.version
16 | });
17 |
18 | // 初始化日志清理器
19 | initializeLogger();
20 |
21 | // 注册工具
22 | structureTools(server);
23 | readTools(server);
24 | writeTools(server);
25 | cacheTools(server);
26 |
27 |
28 | // Start receiving messages on stdin and sending messages on stdout
29 | const transport = new StdioServerTransport();
30 | await server.connect(transport);
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "name": "@zhiweixu/excel-mcp-server",
3 | "version": "0.0.5",
4 | "type": "module",
5 | "main": "index.js",
6 | "bin": {
7 | "excel-mcp-server": "./dist/index.js"
8 | },
9 | "repository": {
10 | "type": "git",
11 | "url": "https://github.com/zhiwei5576/excel-mcp-server.git"
12 | },
13 | "files": [
14 | "dist",
15 | "README_CN.md"
16 | ],
17 | "scripts": {
18 | "start": "node dist/index.js",
19 | "dev": "nodemon src/index.ts",
20 | "build": "tsc",
21 | "watch": "tsc -w"
22 | },
23 | "keywords": [],
24 | "author": "zhiweixu",
25 | "license": "MIT",
26 | "description": "Excel file processing server based on Model Context Protocol (MCP)",
27 | "devDependencies": {
28 | "@types/node": "^22.13.10",
29 | "nodemon": "^3.1.9",
30 | "ts-node": "^10.9.2",
31 | "typescript": "^5.8.2"
32 | },
33 | "dependencies": {
34 | "@modelcontextprotocol/sdk": "^1.7.0",
35 | "xlsx": "^0.18.5"
36 | },
37 | "publishConfig": {
38 | "access": "public"
39 | },
40 | "volta": {
41 | "node": "18.20.6",
42 | "npm": "10.8.2"
43 | }
44 | }
45 |
```
--------------------------------------------------------------------------------
/src/utils/logCleaner.ts:
--------------------------------------------------------------------------------
```typescript
1 | import fs from 'fs';
2 | import path from 'path';
3 | import { getLogPath, getConfig } from './config.js';
4 |
5 | export class LogCleaner {
6 | private logDir: string;
7 | private retentionDays: number;
8 | private interval: NodeJS.Timeout | null = null;
9 |
10 | constructor() {
11 | const config = getConfig();
12 | this.logDir = getLogPath();
13 | this.retentionDays = config.log.retentionDays;
14 | }
15 |
16 | start() {
17 | const config = getConfig();
18 | // 立即执行一次清理
19 | this.cleanOldLogs();
20 | // 设置定时任务
21 | this.interval = setInterval(() => {
22 | this.cleanOldLogs();
23 | }, config.log.cleanupInterval * 60 * 60 * 1000);
24 | }
25 |
26 | stop() {
27 | if (this.interval) {
28 | clearInterval(this.interval);
29 | }
30 | }
31 |
32 | private cleanOldLogs() {
33 | try {
34 | const files = fs.readdirSync(this.logDir);
35 | const now = new Date();
36 |
37 | files.forEach(file => {
38 | if (file.endsWith('.log')) {
39 | const filePath = path.join(this.logDir, file);
40 | const stats = fs.statSync(filePath);
41 | const fileAge = (now.getTime() - stats.mtime.getTime()) / (1000 * 60 * 60 * 24);
42 |
43 | if (fileAge > this.retentionDays) {
44 | fs.unlinkSync(filePath);
45 | }
46 | }
47 | });
48 | } catch (error) {
49 | console.error('Error cleaning log files:', error);
50 | }
51 | }
52 | }
```
--------------------------------------------------------------------------------
/src/utils/config.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { fileURLToPath } from 'url';
2 | import path from 'path';
3 | import fs from 'fs';
4 | import type { Config } from '../types/index.js';
5 |
6 |
7 | const __filename = fileURLToPath(import.meta.url);
8 | const __dirname = path.dirname(__filename);
9 |
10 | export function getConfig(): Config {
11 | return {
12 | logPath: process.env.LOG_PATH || '',
13 | cache: {
14 | maxAge: Number(process.env.CACHE_MAX_AGE) || 1, // 默认1小时
15 | cleanupInterval: Number(process.env.CACHE_CLEANUP_INTERVAL) || 4, // 默认4小时
16 | },
17 | log: {
18 | retentionDays: Number(process.env.LOG_RETENTION_DAYS) || 7, // 默认7天
19 | cleanupInterval: Number(process.env.LOG_CLEANUP_INTERVAL) || 24 // 默认24小时
20 | }
21 | };
22 | }
23 |
24 | export function getLogPath(): string {
25 | const config = getConfig();
26 | let logPath: string;
27 |
28 | if (config.logPath) {
29 | try {
30 | //在指定目录下创建子目录excel-mcp-server-logs
31 | const subDir = 'excel-mcp-server-logs';
32 | const subDirPath = path.join(config.logPath, subDir);
33 | if (!fs.existsSync(subDirPath)) {
34 | fs.mkdirSync(subDirPath, { recursive: true });
35 | }
36 | fs.accessSync(subDirPath);
37 | logPath = subDirPath;
38 | } catch (error) {
39 | console.log(`LOG_PATH environment variable specifies an invalid or inaccessible path: ${config.logPath}, using default path instead.`);
40 | logPath = path.join(__dirname, '../logs');
41 | // 检查默认目录是否存在，不存在则创建
42 | if (!fs.existsSync(logPath)) {
43 | fs.mkdirSync(logPath, { recursive: true });
44 | }
45 | }
46 | } else {
47 | logPath = path.join(__dirname, '../logs');
48 | // 检查默认目录是否存在，不存在则创建
49 | if (!fs.existsSync(logPath)) {
50 | fs.mkdirSync(logPath, { recursive: true });
51 | }
52 | }
53 |
54 | return logPath;
55 | }
```
--------------------------------------------------------------------------------
/src/utils/workbookCache.ts:
--------------------------------------------------------------------------------
```typescript
1 | import type { WorkBook } from 'xlsx'
2 | import type { EnsureWorkbookResult } from '../types/index.js'
3 |
4 | interface CacheItem {
5 | workbook: WorkBook;
6 | timestamp: number;
7 | }
8 |
9 | import { getConfig } from './config.js';
10 |
11 | class WorkbookCache {
12 | private cache = new Map<string, CacheItem>();
13 | private readonly maxAge: number;
14 | private readonly cleanupInterval: number;
15 |
16 | constructor() {
17 | const config = getConfig();
18 | // 将小时转换为毫秒
19 | this.maxAge = config.cache.maxAge * 60 * 60 * 1000;
20 | this.cleanupInterval = config.cache.cleanupInterval * 60 * 60 * 1000;
21 |
22 | // 创建定时器，定期清理过期缓存
23 | setInterval(() => {
24 | const now = Date.now();
25 | for (const [key, item] of this.cache.entries()) {
26 | if (now - item.timestamp > this.maxAge) {
27 | this.delete(key);
28 | }
29 | }
30 | }, this.cleanupInterval);
31 | }
32 |
33 | set(filePathWithName: string, workbook: WorkBook): void {
34 | this.cache.set(filePathWithName, {
35 | workbook,
36 | timestamp: Date.now()
37 | });
38 | }
39 |
40 | get(filePathWithName: string): WorkBook | undefined {
41 | const item = this.cache.get(filePathWithName);
42 | if (!item) return undefined;
43 |
44 | // 检查是否过期
45 | if (Date.now() - item.timestamp > this.maxAge) {
46 | this.delete(filePathWithName);
47 | return undefined;
48 | }
49 |
50 | return item.workbook;
51 | }
52 |
53 | ensureWorkbook(filePathWithName: string): EnsureWorkbookResult {
54 | const workbook = this.get(filePathWithName);
55 | if (!workbook) {
56 | return {
57 | success: false
58 | };
59 | }
60 | return {
61 | success: true,
62 | data: workbook
63 | };
64 | }
65 | // 删除工作簿
66 | delete(filePathWithName: string): boolean {
67 | if (!this.cache.has(filePathWithName)) return false
68 | return this.cache.delete(filePathWithName)
69 | }
70 |
71 | // 清空缓存
72 | clear(): void {
73 | this.cache.clear()
74 | }
75 |
76 | // 检查是否存在
77 | has(filePathWithName: string): boolean {
78 | return this.cache.has(filePathWithName)
79 | }
80 | }
81 |
82 | export const workbookCache = new WorkbookCache();
```
--------------------------------------------------------------------------------
/src/tools/cacheTools.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { z } from "zod";
2 | import { fileExists, normalizePath } from "../utils/utils.js";
3 | import { workbookCache } from "../utils/workbookCache.js";
4 | export const cacheTools = (server: any) => {
5 | server.tool("clearFileCache", 'Clear cached data for the specified Excel file',
6 | {
7 | fileAbsolutePath: z.string().describe("The absolute path of the Excel file to clear from cache")
8 | },
9 | async (params: { fileAbsolutePath: string }) => {
10 | try {
11 | const normalizedPath = await normalizePath(params.fileAbsolutePath);
12 | if (normalizedPath === 'error') {
13 | return {
14 | content: [{
15 | type: "text",
16 | text: JSON.stringify({
17 | error: `Invalid file path: ${params.fileAbsolutePath}`,
18 | suggestion: "Please verify the file path and name"
19 | })
20 | }]
21 | };
22 | }
23 |
24 | const deleted = workbookCache.delete(normalizedPath);
25 |
26 | return {
27 | content: [{
28 | type: "text",
29 | text: JSON.stringify({
30 | success: true,
31 | message: deleted
32 | ? `Cache cleared successfully for file: ${normalizedPath}`
33 | : `No cache found for file: ${normalizedPath}`
34 | })
35 | }]
36 | };
37 |
38 | } catch (error) {
39 | return {
40 | content: [{
41 | type: "text",
42 | text: JSON.stringify({
43 | error: `Failed to clear cache: ${error}`,
44 | suggestion: "Please verify the file path"
45 | })
46 | }]
47 | };
48 | }
49 | }
50 | );
51 |
52 | }
53 |
54 |
```
--------------------------------------------------------------------------------
/src/utils/utils.ts:
--------------------------------------------------------------------------------
```typescript
1 | import * as fs from 'fs/promises';
2 | import * as path from 'path';
3 | import { fileURLToPath } from 'url';
4 | import { getLogPath } from './config.js';
5 |
6 | const __filename = fileURLToPath(import.meta.url);
7 | const __dirname = path.dirname(__filename);
8 |
9 | function formatDate(date: Date): string {
10 | return date.toLocaleString('zh-CN', {
11 | timeZone: 'Asia/Shanghai',
12 | year: 'numeric',
13 | month: '2-digit',
14 | day: '2-digit',
15 | hour: '2-digit',
16 | minute: '2-digit',
17 | second: '2-digit',
18 | hour12: false
19 | });
20 | }
21 |
22 | export async function logToFile(message: string) {
23 | try {
24 | const logDir = getLogPath();
25 | const date = new Date();
26 | const fileName = `${date.getFullYear()}-${(date.getMonth() + 1).toString().padStart(2, '0')}-${date.getDate().toString().padStart(2, '0')}.log`;
27 | const logPath = path.join(logDir, fileName);
28 |
29 | await fs.appendFile(logPath, `${formatDate(date)} - ${message}\n`);
30 | } catch (error) {
31 | console.error('log to file failure:', error);
32 | }
33 | }
34 | export async function normalizePath(filePathWithName: string): Promise<string> {
35 | try {
36 | // 对路径进行编码处理
37 | const encodedPath = encodeURIComponent(filePathWithName)
38 | .replace(/%2F/g, '/') // 保留路径分隔符
39 | .replace(/%20/g, ' '); // 保留空格
40 |
41 | // 解码路径，确保中文字符正确显示
42 | const decodedPath = decodeURIComponent(encodedPath);
43 |
44 | return decodedPath.trim();
45 | // return normalizedPath;
46 | } catch (error) {
47 | await logToFile(`normalize path failure: ${error}`);
48 | return 'error';
49 | }
50 | }
51 |
52 | //confirm file is exist
53 | export async function fileExists(filePathWithName: string): Promise<boolean> {
54 | try {
55 | const fs = await import('fs');
56 | if (!filePathWithName) {
57 | await logToFile("path is null");
58 | return false;
59 | }
60 | // 尝试规范化路径
61 | const fileExists = fs.existsSync(filePathWithName);
62 | if (!fileExists) {
63 | await logToFile(`file is not exist: ${fileExists}`);
64 | return false;
65 | }
66 | return true;
67 | } catch (error) {
68 | await logToFile(`file is not exist: ${error}`);
69 | return false;
70 | }
71 | }
```
--------------------------------------------------------------------------------
/src/utils/excelUtils.ts:
--------------------------------------------------------------------------------
```typescript
1 |
2 | import * as fs from 'fs'
3 | import * as XLSX from 'xlsx'
4 | import { workbookCache } from './workbookCache.js'
5 | import { logToFile } from './utils.js';
6 | import type { ReadSheetNamesResult } from '../types/index.js';
7 |
8 | const READ_TIMEOUT = 300000;
9 | export async function readAndCacheFile(filePathWithName: string): Promise<ReadSheetNamesResult> {
10 | try {
11 | const timeout = new Promise((_, reject) => {
12 | setTimeout(() => reject(new Error('File reading timeout')), READ_TIMEOUT);
13 | });
14 |
15 | const readOperation = new Promise<ReadSheetNamesResult>(async (resolve, reject) => {
16 | try {
17 | // Read file in chunks
18 | const fileStream = fs.createReadStream(filePathWithName, {
19 | highWaterMark: 1024 * 1024 // 1MB chunks
20 | });
21 |
22 | const chunks: Buffer[] = [];
23 |
24 | fileStream.on('data', (chunk: string | Buffer) => {
25 | if (Buffer.isBuffer(chunk)) {
26 | chunks.push(chunk);
27 | } else {
28 | chunks.push(Buffer.from(chunk));
29 | }
30 | });
31 |
32 | fileStream.on('end', () => {
33 | const buffer = Buffer.concat(chunks);
34 | const workbook = XLSX.read(buffer, {
35 | type: 'buffer',
36 | cellDates: true,
37 | cellNF: false,
38 | cellText: false,
39 | });
40 | workbookCache.set(filePathWithName, workbook);
41 | resolve({
42 | success: true,
43 | data: {
44 | SheetNames: workbook.SheetNames,
45 | errors: ''
46 | }
47 | });
48 | });
49 |
50 | fileStream.on('error', (error) => {
51 | reject(error);
52 | });
53 | } catch (error) {
54 | reject(error);
55 | }
56 | });
57 |
58 | const result = await Promise.race([readOperation, timeout]);
59 | return result as ReadSheetNamesResult;
60 |
61 | } catch (bufferError) {
62 | await logToFile(`[read-and-cache-file] Buffer read failure: ${bufferError}`);
63 | return {
64 | success: false,
65 | data: {
66 | SheetNames: [],
67 | errors: JSON.stringify(bufferError)
68 | }
69 | };
70 | }
71 | }
```
--------------------------------------------------------------------------------
/src/tools/structureTools.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { z } from "zod";
2 | import { fileExists, normalizePath } from "../utils/utils.js";
3 | import { analyzeExcelStructure, exportExcelStructure } from '../handlers/excelHandlers.js'
4 |
5 | export const structureTools = (server: any) => {
6 | server.tool("analyzeExcelStructure", 'Get Excel file structure including sheet list and column headers in JSON format',
7 | {
8 | fileAbsolutePath: z.string().describe("The absolute path of the Excel file"),
9 | headerRows: z.number().default(1).describe("Number of header rows to read (default: 1)")
10 | },
11 | async (params: {
12 | fileAbsolutePath: string;
13 | headerRows: number;
14 | }) => {
15 | try {
16 | const normalizedPath = await normalizePath(params.fileAbsolutePath);
17 | if (normalizedPath === 'error') {
18 | return {
19 | content: [{
20 | type: "text",
21 | text: JSON.stringify({
22 | error: `Invalid file path: ${params.fileAbsolutePath}`,
23 | suggestion: "Please verify the file path and name"
24 | })
25 | }]
26 | };
27 | }
28 |
29 | if (!(await fileExists(normalizedPath))) {
30 | return {
31 | content: [{
32 | type: "text",
33 | text: JSON.stringify({
34 | error: `File not found: ${params.fileAbsolutePath}`,
35 | suggestion: "Please verify the file path and name"
36 | })
37 | }]
38 | };
39 | }
40 |
41 | const result = await analyzeExcelStructure(normalizedPath, params.headerRows);
42 |
43 | return {
44 | content: [{
45 | type: "text",
46 | text: JSON.stringify(result)
47 | }]
48 | };
49 |
50 | } catch (error) {
51 | return {
52 | content: [{
53 | type: "text",
54 | text: JSON.stringify({
55 | error: `Failed to get Excel structure: ${error}`,
56 | suggestion: "Please verify all parameters"
57 | })
58 | }]
59 | };
60 | }
61 | }
62 | );
63 | server.tool("exportExcelStructure", 'Export Excel file structure (sheets and headers) to a new Excel template file',
64 | {
65 | sourceFilePath: z.string().describe("The source Excel file path to analyze"),
66 | targetFilePath: z.string().describe("The target Excel file path to save structure"),
67 | headerRows: z.number().default(1).describe("Number of header rows to analyze (default: 1)")
68 | },
69 | async (params: {
70 | sourceFilePath: string;
71 | targetFilePath: string;
72 | headerRows: number;
73 | }) => {
74 | try {
75 | // 验证源文件路径
76 | const normalizedSourcePath = await normalizePath(params.sourceFilePath);
77 | if (normalizedSourcePath === 'error') {
78 | return {
79 | content: [{
80 | type: "text",
81 | text: JSON.stringify({
82 | error: `Invalid source file path: ${params.sourceFilePath}`,
83 | suggestion: "Please verify the source file path"
84 | })
85 | }]
86 | };
87 | }
88 |
89 | // 验证源文件是否存在
90 | if (!(await fileExists(normalizedSourcePath))) {
91 | return {
92 | content: [{
93 | type: "text",
94 | text: JSON.stringify({
95 | error: `Source file not found: ${params.sourceFilePath}`,
96 | suggestion: "Please verify the source file exists"
97 | })
98 | }]
99 | };
100 | }
101 |
102 | // 验证目标文件路径
103 | const normalizedTargetPath = await normalizePath(params.targetFilePath);
104 | if (normalizedTargetPath === 'error') {
105 | return {
106 | content: [{
107 | type: "text",
108 | text: JSON.stringify({
109 | error: `Invalid target file path: ${params.targetFilePath}`,
110 | suggestion: "Please verify the target file path"
111 | })
112 | }]
113 | };
114 | }
115 |
116 | // 验证目标文件是否已存在
117 | if (await fileExists(normalizedTargetPath)) {
118 | return {
119 | content: [{
120 | type: "text",
121 | text: JSON.stringify({
122 | error: `Target file already exists: ${params.targetFilePath}`,
123 | suggestion: "Please specify a different target file path"
124 | })
125 | }]
126 | };
127 | }
128 |
129 | // 导出结构
130 | await exportExcelStructure(normalizedSourcePath, normalizedTargetPath, params.headerRows);
131 |
132 | return {
133 | content: [{
134 | type: "text",
135 | text: JSON.stringify({
136 | success: true,
137 | message: `Excel structure exported successfully to: ${normalizedTargetPath}`
138 | })
139 | }]
140 | };
141 |
142 | } catch (error) {
143 | return {
144 | content: [{
145 | type: "text",
146 | text: JSON.stringify({
147 | error: `Failed to export Excel structure: ${error}`,
148 | suggestion: "Please verify all parameters and try again"
149 | })
150 | }]
151 | };
152 | }
153 | }
154 | );
155 | }
156 |
157 |
```
--------------------------------------------------------------------------------
/src/tools/writeTools.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { z } from "zod";
2 | import { fileExists, normalizePath } from "../utils/utils.js";
3 | import { writeDataBySheetName, writeSheetData } from '../handlers/excelHandlers.js'
4 |
5 | export const writeTools = (server: any) => {
6 | server.tool("writeDataBySheetName", 'Write data to a specific sheet in the Excel file (overwrites if sheet exists)',
7 | {
8 | fileAbsolutePath: z.string().describe("The absolute path of the Excel file"),
9 | sheetName: z.string().describe("The name of the sheet to write"),
10 | data: z.array(
11 | z.record(
12 | z.string(),
13 | z.any()
14 | )
15 | ).describe("Array of objects to write to the sheet")
16 | },
17 | async (params: {
18 | fileAbsolutePath: string;
19 | sheetName: string;
20 | data: Record<string, any>[];
21 | }) => {
22 | try {
23 | const normalizedPath = await normalizePath(params.fileAbsolutePath);
24 | if (normalizedPath === 'error') {
25 | return {
26 | content: [{
27 | type: "text",
28 | text: JSON.stringify({
29 | error: `Invalid file path: ${params.fileAbsolutePath}`,
30 | suggestion: "Please verify the file path and name"
31 | })
32 | }]
33 | };
34 | }
35 |
36 | // 校验数据结构
37 | if (!Array.isArray(params.data) || params.data.length === 0) {
38 | return {
39 | content: [{
40 | type: "text",
41 | text: JSON.stringify({
42 | error: "Empty data array provided",
43 | suggestion: "Please provide non-empty array of data"
44 | })
45 | }]
46 | };
47 | }
48 |
49 | // 校验工作表名称
50 | if (!params.sheetName) {
51 | return {
52 | content: [{
53 | type: "text",
54 | text: JSON.stringify({
55 | error: "Invalid sheet name",
56 | suggestion: "Please provide a valid sheet name"
57 | })
58 | }]
59 | };
60 | }
61 |
62 | await writeDataBySheetName(normalizedPath, params.sheetName, params.data);
63 | return {
64 | content: [{
65 | type: "text",
66 | text: JSON.stringify({
67 | success: true,
68 | message: `Data written successfully to sheet '${params.sheetName}' in file: ${normalizedPath}`
69 | })
70 | }]
71 | };
72 |
73 | } catch (error) {
74 | return {
75 | content: [{
76 | type: "text",
77 | text: JSON.stringify({
78 | error: `Failed to write sheet data: ${error}`,
79 | suggestion: "Please verify all parameters and try again"
80 | })
81 | }]
82 | };
83 | }
84 | }
85 | );
86 | server.tool("writeSheetData", 'Create a new Excel file with provided data',
87 | {
88 | fileAbsolutePath: z.string().describe("The absolute path for the new Excel file"),
89 | data: z.record(
90 | z.string(), // 表名（动态）
91 | z.array( // 表数据数组
92 | z.record( // 每行数据对象
93 | z.string(), // 字段名（动态）
94 | z.any() // 字段值（任意类型）
95 | )
96 | )
97 | ).describe("Data object with dynamic sheet names and column names")
98 | },
99 | async (params: {
100 | fileAbsolutePath: string;
101 | data: Record<string, Record<string, any>[]>;
102 | }) => {
103 | try {
104 | const normalizedPath = await normalizePath(params.fileAbsolutePath);
105 | if (normalizedPath === 'error') {
106 | return {
107 | content: [{
108 | type: "text",
109 | text: JSON.stringify({
110 | error: `Invalid file path: ${params.fileAbsolutePath}`,
111 | suggestion: "Please verify the file path and name"
112 | })
113 | }]
114 | };
115 | }
116 |
117 | // 校验数据结构
118 | if (Object.keys(params.data).length === 0) {
119 | return {
120 | content: [{
121 | type: "text",
122 | text: JSON.stringify({
123 | error: "Empty data object provided",
124 | suggestion: "Please provide at least one sheet with data"
125 | })
126 | }]
127 | };
128 | }
129 |
130 | // 校验每个表的数据
131 | for (const [sheetName, sheetData] of Object.entries(params.data)) {
132 | if (!Array.isArray(sheetData) || sheetData.length === 0) {
133 | return {
134 | content: [{
135 | type: "text",
136 | text: JSON.stringify({
137 | error: `Invalid data format in sheet "${sheetName}": Data must be a non-empty array`,
138 | suggestion: "Please check the data format of each sheet"
139 | })
140 | }]
141 | };
142 | }
143 | }
144 |
145 | if (await fileExists(normalizedPath)) {
146 | return {
147 | content: [{
148 | type: "text",
149 | text: JSON.stringify({
150 | error: `File already exists: ${params.fileAbsolutePath}`,
151 | suggestion: "Please specify a different file path"
152 | })
153 | }]
154 | };
155 | }
156 |
157 | await writeSheetData(normalizedPath, params.data);
158 | return {
159 | content: [{
160 | type: "text",
161 | text: JSON.stringify({
162 | success: true,
163 | message: `Excel file created successfully: ${normalizedPath}`
164 | })
165 | }]
166 | };
167 |
168 | } catch (error) {
169 | return {
170 | content: [{
171 | type: "text",
172 | text: JSON.stringify({
173 | error: `Failed to write Excel data: ${error}`,
174 | suggestion: "Please verify the data format and file path"
175 | })
176 | }]
177 | };
178 | }
179 | }
180 | );
181 | }
182 |
183 |
```
--------------------------------------------------------------------------------
/src/tools/readTools.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { z } from "zod";
2 | import { fileExists, normalizePath } from "../utils/utils.js";
3 | import { readAllSheetData, readDataBySheetName, readSheetNames } from '../handlers/excelHandlers.js'
4 |
5 | export const readTools = (server: any) => {
6 | server.tool("readSheetNames", 'Get all sheet names from the Excel file',
7 | {
8 | fileAbsolutePath: z.string().describe("The absolute path of the Excel file")
9 | },
10 | async (params: { fileAbsolutePath: string }) => {
11 | try {
12 | const normalizedPath = await normalizePath(params.fileAbsolutePath);
13 | if (normalizedPath === 'error') {
14 | return {
15 | content: [{
16 | type: "text",
17 | text: JSON.stringify({
18 | error: `path is not valid: ${params.fileAbsolutePath}`,
19 | suggestion: "please check the path and filename"
20 | })
21 | }]
22 | };
23 | }
24 | if (!(await fileExists(normalizedPath))) {
25 | return {
26 | content: [{
27 | type: "text",
28 | text: JSON.stringify({
29 | error: `file is not exist: ${params.fileAbsolutePath}`,
30 | suggestion: "please check the path and filename"
31 | })
32 | }]
33 | };
34 |
35 | }
36 | const result = await readSheetNames(normalizedPath);
37 | return {
38 | content: [{
39 | type: "text",
40 | text: JSON.stringify(result)
41 | }]
42 | };
43 | } catch (error) {
44 | return {
45 | content: [{
46 | type: "text",
47 | text: JSON.stringify({
48 | error: `read sheet names failure: ${error}`,
49 | suggestion: "please check the path and filename"
50 | })
51 | }]
52 | };
53 | }
54 |
55 | }
56 | );
57 |
58 | server.tool("readDataBySheetName", 'Get data from a specific sheet in the Excel file',
59 | {
60 | fileAbsolutePath: z.string().describe("The absolute path of the Excel file"),
61 | sheetName: z.string().describe("tThe name of the sheet to read"),
62 | headerRow: z.number().default(1).describe("tThe row number to use as field names (default: 1)"),
63 | dataStartRow: z.number().default(2).describe("The row number to start reading data from (default: 2)")
64 | },
65 | async (params: {
66 | fileAbsolutePath: string,
67 | sheetName: string,
68 | headerRow: number,
69 | dataStartRow: number
70 | }) => {
71 | try {
72 | const normalizedPath = await normalizePath(params.fileAbsolutePath);
73 | if (normalizedPath === 'error') {
74 | return {
75 | content: [{
76 | type: "text",
77 | text: JSON.stringify({
78 | error: `Invalid file path: ${params.fileAbsolutePath}`,
79 | suggestion: "Please verify the file path and name"
80 | })
81 | }]
82 | };
83 | }
84 | if (!(await fileExists(normalizedPath))) {
85 | return {
86 | content: [{
87 | type: "text",
88 | text: JSON.stringify({
89 | error: `File not exist: ${params.fileAbsolutePath}`,
90 | suggestion: "Please verify the file path and name"
91 | })
92 | }]
93 | };
94 |
95 | }
96 | if (!params.sheetName) {
97 | return {
98 | content: [{
99 | type: "text",
100 | text: JSON.stringify({
101 | error: `Invalid sheet name: ${params.sheetName}`,
102 | suggestion: "Please verify the sheet name"
103 | })
104 | }]
105 | };
106 |
107 | }
108 | const result = await readDataBySheetName(normalizedPath, params.sheetName, params.headerRow, params.dataStartRow);
109 | return {
110 | content: [{
111 | type: "text",
112 | text: JSON.stringify(result)
113 | }]
114 | };
115 | } catch (error) {
116 | return {
117 | content: [{
118 | type: "text",
119 | text: JSON.stringify({
120 | error: `Failed to read data from sheet: ${params.sheetName} failure: ${error}`,
121 | suggestion: "Please verify all parameters"
122 | })
123 | }]
124 | };
125 | }
126 |
127 | }
128 | );
129 | server.tool("readSheetData", 'Get data from all sheets in the Excel file',
130 | {
131 | fileAbsolutePath: z.string().describe("The absolute path of the Excel file"),
132 | headerRow: z.number().default(1).describe("The row number to use as field names (default: 1)"),
133 | dataStartRow: z.number().default(2).describe("The row number to start reading data from (default: 2)")
134 | },
135 | async (params: { fileAbsolutePath: string, headerRow: number, dataStartRow: number }) => {
136 | try {
137 | const normalizedPath = await normalizePath(params.fileAbsolutePath);
138 | if (normalizedPath === 'error') {
139 | return {
140 | content: [{
141 | type: "text",
142 | text: JSON.stringify({
143 | error: `Invalid file path: ${params.fileAbsolutePath}`,
144 | suggestion: "Please verify the file path and name"
145 | })
146 | }]
147 | };
148 | }
149 |
150 | if (!(await fileExists(normalizedPath))) {
151 | return {
152 | content: [{
153 | type: "text",
154 | text: JSON.stringify({
155 | error: `File not found: ${params.fileAbsolutePath}`,
156 | suggestion: "Please verify the file path and name"
157 | })
158 | }]
159 | };
160 | }
161 |
162 | const result = await readAllSheetData(
163 | normalizedPath,
164 | params.headerRow,
165 | params.dataStartRow
166 | );
167 |
168 | return {
169 | content: [{
170 | type: "text",
171 | text: JSON.stringify(result)
172 | }]
173 | };
174 |
175 | } catch (error) {
176 | return {
177 | content: [{
178 | type: "text",
179 | text: JSON.stringify({
180 | error: `Failed to read Excel data: ${error}`,
181 | suggestion: "Please verify all parameters"
182 | })
183 | }]
184 | };
185 | }
186 | }
187 | );
188 | }
189 |
190 |
```
--------------------------------------------------------------------------------
/src/handlers/excelHandlers.ts:
--------------------------------------------------------------------------------
```typescript
1 | import { workbookCache } from "../utils/workbookCache.js";
2 | import * as XLSX from 'xlsx'
3 | import { logToFile,fileExists } from '../utils/utils.js';
4 | import { readAndCacheFile } from "../utils/excelUtils.js";
5 | import type { EnsureWorkbookResult, ReadSheetNamesResult, SheetData ,ExcelStructure} from '../types/index.js';
6 | export async function readSheetNames(filePathWithName: string): Promise<string[]> {
7 |
8 |
9 | try {
10 | //从缓存中获取workbook
11 | const workbookResult: EnsureWorkbookResult = workbookCache.ensureWorkbook(filePathWithName);
12 |
13 | if (!workbookResult.success) {
14 | // 缓存中没有workbook，尝试读取并缓存文件
15 | const readResult: ReadSheetNamesResult = await readAndCacheFile(filePathWithName);
16 | if (!readResult.success) {
17 | // 读取文件失败，返回错误信息
18 | throw new Error(`read file failure: ${readResult.data.errors}`);
19 | } else {
20 | return readResult.data.SheetNames;
21 | }
22 |
23 | } else {
24 | const workbook = workbookResult.data as XLSX.WorkBook;
25 | return workbook.SheetNames
26 | }
27 |
28 | } catch (error) {
29 | const errorMessage = error instanceof Error ? error.message : String(error);
30 | throw new Error(`read file failure: ${errorMessage}`);
31 | }
32 | }
33 |
34 | export async function readDataBySheetName(
35 | filePathWithName: string,
36 | sheetName: string,
37 | headerRow: number = 1, // 默认第一行为表头
38 | dataStartRow: number = 2 // 默认第二行开始为数据
39 | ): Promise<any> {
40 | try {
41 | const workbookResult: EnsureWorkbookResult = workbookCache.ensureWorkbook(filePathWithName);
42 | let workbook: XLSX.WorkBook;
43 |
44 | if (!workbookResult.success) {
45 | const readResult: ReadSheetNamesResult = await readAndCacheFile(filePathWithName);
46 | if (!readResult.success) {
47 | throw new Error(`read file failure: ${readResult.data.errors}`);
48 | }
49 | workbook = workbookCache.get(filePathWithName)!;
50 | } else {
51 | workbook = workbookResult.data as XLSX.WorkBook;
52 | }
53 |
54 | const worksheet = workbook.Sheets[sheetName];
55 | if (!worksheet) {
56 | throw new Error(`sheet ${sheetName} not found`);
57 | }
58 |
59 | // 将 Excel 行号转换为数组索引（减1）
60 | const headerIndex = headerRow - 1;
61 | const dataStartIndex = dataStartRow - 1;
62 |
63 | // 获取原始数据
64 | const rawData = XLSX.utils.sheet_to_json(worksheet, {
65 | raw: true,
66 | defval: '',
67 | header: 1
68 | });
69 |
70 | if (rawData.length <= headerIndex) {
71 | throw new Error(`sheet is empty or header row (${headerRow}) exceeds sheet length`);
72 | }
73 |
74 | // 获取表头
75 | const headers = rawData[headerIndex] as string[];
76 |
77 | // 验证数据起始行
78 | if (rawData.length <= dataStartIndex) {
79 | throw new Error(`data start row (${dataStartRow}) exceeds sheet length`);
80 | }
81 |
82 | // 处理数据行
83 | const dataRows = rawData.slice(dataStartIndex);
84 | const result = dataRows.map((row: any) => {
85 | const item: { [key: string]: any } = {};
86 | headers.forEach((header, index) => {
87 | if (header) {
88 | item[header] = row[index] || '';
89 | }
90 | });
91 | return item;
92 | });
93 |
94 | return result;
95 |
96 | } catch (error) {
97 | const errorMessage = error instanceof Error ? error.message : String(error);
98 | throw new Error(`read sheet data failure: ${errorMessage}`);
99 | }
100 | }
101 |
102 | export async function readAllSheetData(
103 | filePathWithName: string,
104 | headerRow: number = 1,
105 | dataStartRow: number = 2
106 | ): Promise<{ [sheetName: string]: any[] }> {
107 | try {
108 | const workbookResult: EnsureWorkbookResult = workbookCache.ensureWorkbook(filePathWithName);
109 | let workbook: XLSX.WorkBook;
110 |
111 | if (!workbookResult.success) {
112 | const readResult: ReadSheetNamesResult = await readAndCacheFile(filePathWithName);
113 | if (!readResult.success) {
114 | throw new Error(`Failed to read file: ${readResult.data.errors}`);
115 | }
116 | workbook = workbookCache.get(filePathWithName)!;
117 | } else {
118 | workbook = workbookResult.data as XLSX.WorkBook;
119 | }
120 |
121 | const result: { [sheetName: string]: any[] } = {};
122 | const headerIndex = headerRow - 1;
123 | const dataStartIndex = dataStartRow - 1;
124 |
125 | // 遍历所有工作表
126 | for (const sheetName of workbook.SheetNames) {
127 | const worksheet = workbook.Sheets[sheetName];
128 |
129 | // 获取原始数据
130 | const rawData = XLSX.utils.sheet_to_json(worksheet, {
131 | raw: true,
132 | defval: '',
133 | header: 1
134 | });
135 |
136 | if (rawData.length <= headerIndex) {
137 | await logToFile(`Sheet ${sheetName} is empty or header row (${headerRow}) exceeds sheet length`);
138 | continue;
139 | }
140 |
141 | // 获取表头
142 | const headers = rawData[headerIndex] as string[];
143 |
144 | // 验证数据起始行
145 | if (rawData.length <= dataStartIndex) {
146 | await logToFile(`Sheet ${sheetName} data start row (${dataStartRow}) exceeds sheet length`);
147 | continue;
148 | }
149 |
150 | // 处理数据行
151 | const dataRows = rawData.slice(dataStartIndex);
152 | result[sheetName] = dataRows.map((row: any) => {
153 | const item: { [key: string]: any } = {};
154 | headers.forEach((header, index) => {
155 | if (header) {
156 | item[header] = row[index] || '';
157 | }
158 | });
159 | return item;
160 | });
161 | }
162 |
163 | return result;
164 |
165 | } catch (error) {
166 | const errorMessage = error instanceof Error ? error.message : String(error);
167 | throw new Error(`Failed to read Excel data: ${errorMessage}`);
168 | }
169 | }
170 |
171 |
172 | export async function writeSheetData(
173 | filePathWithName: string,
174 | data: SheetData
175 | ): Promise<boolean> {
176 | try {
177 | // 创建新的工作簿
178 | const workbook = XLSX.utils.book_new();
179 |
180 | // 遍历数据对象的每个工作表
181 | for (const [sheetName, sheetData] of Object.entries(data)) {
182 | // 将数据转换为工作表
183 | const worksheet = XLSX.utils.json_to_sheet(sheetData);
184 |
185 | // 将工作表添加到工作簿
186 | XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);
187 | }
188 |
189 | // 写入文件
190 | XLSX.writeFile(workbook, filePathWithName);
191 |
192 | return true;
193 | } catch (error) {
194 | const errorMessage = error instanceof Error ? error.message : String(error);
195 | throw new Error(`Failed to write Excel data: ${errorMessage}`);
196 | }
197 | }
198 |
199 | export async function writeDataBySheetName(
200 | filePathWithName: string,
201 | sheetName: string,
202 | data: any[]
203 | ): Promise<boolean> {
204 | try {
205 | let workbook: XLSX.WorkBook;
206 |
207 |
208 | // 检查文件是否存在，注：filePathWithName ,已经经过了normalizePath
209 | if (await fileExists(filePathWithName)) {
210 | // 如果文件存在，读取现有工作簿
211 | const workbookResult = workbookCache.ensureWorkbook(filePathWithName);
212 | if (!workbookResult.success) {
213 | const readResult = await readAndCacheFile(filePathWithName);
214 | if (!readResult.success) {
215 | throw new Error(`Failed to read existing file: ${readResult.data.errors}`);
216 | }
217 | workbook = workbookCache.get(filePathWithName)!;
218 | } else {
219 | workbook = workbookResult.data as XLSX.WorkBook;
220 | }
221 | } else {
222 | // 如果文件不存在，创建新的工作簿
223 | workbook = XLSX.utils.book_new();
224 | }
225 |
226 | // 将数据转换为工作表
227 | const worksheet = XLSX.utils.json_to_sheet(data);
228 |
229 | // 检查工作表是否已存在
230 | if (workbook.SheetNames.includes(sheetName)) {
231 | // 如果存在，删除旧的工作表
232 | const index = workbook.SheetNames.indexOf(sheetName);
233 | workbook.SheetNames.splice(index, 1);
234 | delete workbook.Sheets[sheetName];
235 | }
236 |
237 | // 添加新的工作表
238 | XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);
239 |
240 | // 写入文件
241 | XLSX.writeFile(workbook, filePathWithName);
242 |
243 | // 更新缓存
244 | workbookCache.set(filePathWithName, workbook);
245 |
246 | return true;
247 | } catch (error) {
248 | const errorMessage = error instanceof Error ? error.message : String(error);
249 | throw new Error(`Failed to write sheet data: ${errorMessage}`);
250 | }
251 | }
252 |
253 | export async function analyzeExcelStructure(
254 | filePathWithName: string,
255 | headerRows: number = 1
256 | ): Promise<ExcelStructure> {
257 | try {
258 | const workbookResult: EnsureWorkbookResult = workbookCache.ensureWorkbook(filePathWithName);
259 | let workbook: XLSX.WorkBook;
260 |
261 | if (!workbookResult.success) {
262 | const readResult = await readAndCacheFile(filePathWithName);
263 | if (!readResult.success) {
264 | throw new Error(`Failed to read file: ${readResult.data.errors}`);
265 | }
266 | workbook = workbookCache.get(filePathWithName)!;
267 | } else {
268 | workbook = workbookResult.data as XLSX.WorkBook;
269 | }
270 |
271 | const result: ExcelStructure = {
272 | sheetList: [],
273 | sheetField: []
274 | };
275 |
276 | result.sheetList = workbook.SheetNames.map((sheetName, index) => ({
277 | SheetNo: index + 1, // 添加从1开始的序号
278 | SheetName: sheetName
279 | }));
280 | // 遍历所有工作表
281 | for (const sheetName of workbook.SheetNames) {
282 | const worksheet = workbook.Sheets[sheetName];
283 |
284 | // 获取原始数据
285 | const rawData = XLSX.utils.sheet_to_json(worksheet, {
286 | raw: true,
287 | defval: '',
288 | header: 1
289 | });
290 |
291 | if (rawData.length === 0) {
292 | continue;
293 | }
294 | // 获取每列的数据
295 | const columnCount = (rawData[0] as any[]).length;
296 | for (let colIndex = 0; colIndex < columnCount; colIndex++) {
297 | const fieldInfo: any = {
298 | SheetName: sheetName
299 | };
300 |
301 | // 根据 headerRows 获取指定数量的表头行
302 | for (let i = 1; i <= headerRows; i++) {
303 | const headerIndex = i - 1;
304 | if (rawData.length > headerIndex) {
305 | const rowData = rawData[headerIndex] as any[];
306 | fieldInfo[`Field${i}`] = rowData[colIndex] || '';
307 | } else {
308 | fieldInfo[`Field${i}`] = '';
309 | }
310 | }
311 |
312 | result.sheetField = result.sheetField || [];
313 | result.sheetField.push(fieldInfo);
314 | }
315 | }
316 |
317 | return result
318 | // {
319 | // // 修改 sheetList 的映射，添加 SheetNo
320 | // sheetList: workbook.SheetNames.map((sheetName, index) => ({
321 | // SheetNo: index + 1, // 添加从1开始的序号
322 | // SheetName: sheetName
323 | // })),
324 | // sheetField: result.sheetField || []
325 | // };
326 |
327 | } catch (error) {
328 | const errorMessage = error instanceof Error ? error.message : String(error);
329 | throw new Error(`Failed to get Excel structure: ${errorMessage}`);
330 | }
331 | }
332 |
333 | export async function exportExcelStructure(
334 | sourceFilePath: string,
335 | targetFilePath: string,
336 | headerRows: number = 1
337 | ): Promise<boolean> {
338 | try {
339 | // 1. 获取源文件的结构
340 | const structure = await analyzeExcelStructure(sourceFilePath, headerRows);
341 |
342 | // 校验结构数据
343 | if (!structure.sheetList || !Array.isArray(structure.sheetList) || structure.sheetList.length === 0) {
344 | throw new Error('Invalid Excel structure: sheetList is empty or invalid');
345 | }
346 |
347 | if (!structure.sheetField || !Array.isArray(structure.sheetField) || structure.sheetField.length === 0) {
348 | throw new Error('Invalid Excel structure: sheetField is empty or invalid');
349 | }
350 |
351 | // 2. 转换为 SheetData 格式
352 | const data: SheetData = {
353 | 'sheetList': structure.sheetList,
354 | 'sheetField': structure.sheetField
355 | };
356 |
357 | // 3. 使用 writeSheetData 写入文件
358 | return await writeSheetData(targetFilePath, data);
359 |
360 | } catch (error) {
361 | const errorMessage = error instanceof Error ? error.message : String(error);
362 | throw new Error(`Failed to export Excel structure: ${errorMessage}`);
363 | }
364 | }
365 |
366 |
```