# 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
# Excel MCP Server
[](https://www.npmjs.com/package/@zhiweixu/excel-mcp-server)
[](https://smithery.ai/server/@zhiwei5576/excel-mcp-server)
[简体中文](./README_CN.md) | English
Excel file processing server based on Model Context Protocol (MCP), providing functionalities for reading, writing, and analyzing Excel files.
## Features
- 📖 Read Excel Files
- Get worksheet list
- Read specific worksheet data
- Read all worksheets data
- ✍️ Write Excel Files
- Create new Excel files
- Write to specific worksheet
- Support multiple worksheets
- 🔍 Analyze Excel Structure
- Analyze worksheet structure
- Export structure to new file
- 💾 Cache Management
- Automatic file content caching
- Scheduled cache cleanup
- Manual cache clearing
- 📝 Log Management
- Automatic operation logging
- Periodic log cleanup
## Installation
### Installing via Smithery
To install excel-mcp-server for Claude Desktop automatically via [Smithery](https://smithery.ai/server/@zhiwei5576/excel-mcp-server):
```bash
npx -y @smithery/cli install @zhiwei5576/excel-mcp-server --client claude
```
### Installing Manually
Installing via NPM
excel-mcp-server can be automatically installed by adding the following configuration to the MCP servers configuration.
Windows Platform:
```bash
{
"mcpServers": {
"excel": {
"command": "cmd",
"args": ["/c", "npx", "--yes", "@zhiweixu/excel-mcp-server"],
"env": {
"LOG_PATH": "[set an accessible absolute path]",
"CACHE_MAX_AGE": "1",
"CACHE_CLEANUP_INTERVAL": "4",
"LOG_RETENTION_DAYS": "7",
"LOG_CLEANUP_INTERVAL": "24"
}
}
}
```
Other Platforms:
```bash
{
"mcpServers": {
"excel": {
"command": "npx",
"args": ["--yes", "@zhiweixu/excel-mcp-server"],
"env": {
"LOG_PATH": "[set an accessible absolute path]",
"CACHE_MAX_AGE": "1",
"CACHE_CLEANUP_INTERVAL": "4",
"LOG_RETENTION_DAYS": "7",
"LOG_CLEANUP_INTERVAL": "24"
}
}
}
```
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.
## API Tools
### Structure Tools
1. analyzeExcelStructure
- Function: Get Excel file structure including sheet list and column headers in JSON format
- Parameters:
- fileAbsolutePath: Absolute path of the Excel file
- headerRows: Number of header rows (default: 1)
2. exportExcelStructure
- Function: Export Excel file structure (sheets and headers) to a new Excel template file
- Parameters:
- sourceFilePath: Source Excel file path
- targetFilePath: Target Excel file path
- headerRows: Number of header rows (default: 1)
### Read Tools
1. readSheetNames
- Function: Get all sheet names from the Excel file
- Parameters:
- fileAbsolutePath: Absolute path of the Excel file
2. readDataBySheetName
- Function: Get data from a specific sheet in the Excel file
- Parameters:
- fileAbsolutePath: Absolute path of the Excel file
- sheetName: Name of the sheet to read
- headerRow: Header row number (default: 1)
- dataStartRow: Data start row number (default: 2)
3. readSheetData
- Function: Get data from all sheets in the Excel file
- Parameters:
- fileAbsolutePath: Absolute path of the Excel file
- headerRow: Header row number (default: 1)
- dataStartRow: Data start row number (default: 2)
### Write Tools
1. writeDataBySheetName
- Function: Write data to a specific sheet in the Excel file (overwrites if sheet exists)
- Parameters:
- fileAbsolutePath: Absolute path of the Excel file
- sheetName: Name of the sheet to write
- data: Array of data to write
2. writeSheetData
- Function: Create a new Excel file with provided data
- Parameters:
- fileAbsolutePath: Absolute path for the new Excel file
- data: Object containing multiple sheet data
### Cache Tools
1. clearFileCache
- Function: Clear cached data for the specified Excel file
- Parameters:
- fileAbsolutePath: Absolute path of the Excel file to clear from cache
## Configuration
### Environment Variables
- `LOG_PATH`: Log files storage path
- Optional
- Default: 'logs' folder under application root directory
- `CACHE_MAX_AGE`: Cache expiration time (hours)
- Optional
- Default: 1
- `CACHE_CLEANUP_INTERVAL`: Cache cleanup interval (hours)
- Optional
- Default: 4
- `LOG_RETENTION_DAYS`: Log retention days
- Optional
- Default: 7
- `LOG_CLEANUP_INTERVAL`: Log cleanup interval (hours)
- Optional
- Default: 24
### Default Configuration
- Cache Configuration
- Cache expiration time: 1 hour
- Cache cleanup interval: 4 hours
- Log Configuration
- Log retention days: 7 days
- Cleanup interval: 24 hours
## Dependencies
- @modelcontextprotocol/sdk: ^1.7.0
- xlsx: ^0.18.5
- typescript: ^5.8.2
## Development Dependencies
- @types/node: ^22.13.10
- nodemon: ^3.1.9
- ts-node: ^10.9.2
## License
This project is licensed under the MIT License. This means you are free to:
- Use the software for commercial or non-commercial purposes
- Modify the source code
- Distribute original or modified code
Requirements:
- Retain the original copyright notice
- No liability can be claimed against the authors for software use
For detailed license information,please see the [LICENSE](./LICENSE) file.
```
--------------------------------------------------------------------------------
/src/handlers/logHandlers.ts:
--------------------------------------------------------------------------------
```typescript
import { LogCleaner } from "../utils/logCleaner.js";
export function initializeLogger() {
const logCleaner = new LogCleaner();
logCleaner.start();
return logCleaner;
}
```
--------------------------------------------------------------------------------
/tsconfig.json:
--------------------------------------------------------------------------------
```json
{
"compilerOptions": {
"target": "ES2020",
"module": "NodeNext",
"outDir": "./dist",
"rootDir": "./src",
"strict": true,
"esModuleInterop": true,
"resolveJsonModule": true,
"skipLibCheck": true,
"forceConsistentCasingInFileNames": true
},
"include": ["src/**/*"],
"exclude": ["node_modules"]
}
```
--------------------------------------------------------------------------------
/Dockerfile:
--------------------------------------------------------------------------------
```dockerfile
# Generated by https://smithery.ai. See: https://smithery.ai/docs/config#dockerfile
# 构建阶段
FROM node:18 AS builder
WORKDIR /app
COPY package*.json ./
RUN npm install
COPY . .
RUN npm run build
# 生产阶段
FROM node:18-slim
WORKDIR /app
COPY --from=builder /app/dist ./dist
COPY package*.json ./
RUN npm install --production
EXPOSE 3000
CMD ["node", "dist/index.js"]
```
--------------------------------------------------------------------------------
/smithery.yaml:
--------------------------------------------------------------------------------
```yaml
# Smithery configuration file: https://smithery.ai/docs/config#smitheryyaml
startCommand:
type: stdio
configSchema:
# JSON Schema defining the configuration options for the MCP.
type: object
required: []
properties:
logPath:
type: string
description: Absolute path for log storage. Optional. If not provided, logs will
be stored in the './logs' folder.
commandFunction:
# A JS function that produces the CLI command based on the given config to start the MCP on stdio.
|-
(config) => ({
command: 'node',
args: ['dist/index.js'],
env: config.logPath ? { LOG_PATH: config.logPath } : {}
})
exampleConfig:
logPath: /var/log/excel-mcp-server
```
--------------------------------------------------------------------------------
/.github/workflows/publish.yml:
--------------------------------------------------------------------------------
```yaml
name: Publish Package
on:
push:
tags:
- 'v*'
jobs:
publish:
runs-on: ubuntu-latest
permissions:
contents: write
packages: write
steps:
- uses: actions/checkout@v3
- name: Setup Node.js
uses: actions/setup-node@v3
with:
node-version: '18'
registry-url: 'https://registry.npmjs.org'
- name: Install dependencies
run: npm ci
- name: Build
run: npm run build
- name: Publish to NPM
run: npm publish
env:
NODE_AUTH_TOKEN: ${{ secrets.NPM_TOKEN }}
- name: Create Release
uses: softprops/action-gh-release@v1
with:
generate_release_notes: true
draft: false
prerelease: false
env:
GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
```
--------------------------------------------------------------------------------
/src/index.ts:
--------------------------------------------------------------------------------
```typescript
#!/usr/bin/env node
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import packageJson from '../package.json' with { type: "json" };
import { structureTools } from "./tools/structureTools.js";
import { writeTools } from "./tools/writeTools.js";
import { readTools } from "./tools/readTools.js";
import { cacheTools } from "./tools/cacheTools.js";
import { initializeLogger } from "./handlers/logHandlers.js";
// Create an MCP server
const server = new McpServer({
name: "excel-mcp-server",
version: packageJson.version
});
// 初始化日志清理器
initializeLogger();
// 注册工具
structureTools(server);
readTools(server);
writeTools(server);
cacheTools(server);
// Start receiving messages on stdin and sending messages on stdout
const transport = new StdioServerTransport();
await server.connect(transport);
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
{
"name": "@zhiweixu/excel-mcp-server",
"version": "0.0.5",
"type": "module",
"main": "index.js",
"bin": {
"excel-mcp-server": "./dist/index.js"
},
"repository": {
"type": "git",
"url": "https://github.com/zhiwei5576/excel-mcp-server.git"
},
"files": [
"dist",
"README_CN.md"
],
"scripts": {
"start": "node dist/index.js",
"dev": "nodemon src/index.ts",
"build": "tsc",
"watch": "tsc -w"
},
"keywords": [],
"author": "zhiweixu",
"license": "MIT",
"description": "Excel file processing server based on Model Context Protocol (MCP)",
"devDependencies": {
"@types/node": "^22.13.10",
"nodemon": "^3.1.9",
"ts-node": "^10.9.2",
"typescript": "^5.8.2"
},
"dependencies": {
"@modelcontextprotocol/sdk": "^1.7.0",
"xlsx": "^0.18.5"
},
"publishConfig": {
"access": "public"
},
"volta": {
"node": "18.20.6",
"npm": "10.8.2"
}
}
```
--------------------------------------------------------------------------------
/src/utils/logCleaner.ts:
--------------------------------------------------------------------------------
```typescript
import fs from 'fs';
import path from 'path';
import { getLogPath, getConfig } from './config.js';
export class LogCleaner {
private logDir: string;
private retentionDays: number;
private interval: NodeJS.Timeout | null = null;
constructor() {
const config = getConfig();
this.logDir = getLogPath();
this.retentionDays = config.log.retentionDays;
}
start() {
const config = getConfig();
// 立即执行一次清理
this.cleanOldLogs();
// 设置定时任务
this.interval = setInterval(() => {
this.cleanOldLogs();
}, config.log.cleanupInterval * 60 * 60 * 1000);
}
stop() {
if (this.interval) {
clearInterval(this.interval);
}
}
private cleanOldLogs() {
try {
const files = fs.readdirSync(this.logDir);
const now = new Date();
files.forEach(file => {
if (file.endsWith('.log')) {
const filePath = path.join(this.logDir, file);
const stats = fs.statSync(filePath);
const fileAge = (now.getTime() - stats.mtime.getTime()) / (1000 * 60 * 60 * 24);
if (fileAge > this.retentionDays) {
fs.unlinkSync(filePath);
}
}
});
} catch (error) {
console.error('Error cleaning log files:', error);
}
}
}
```
--------------------------------------------------------------------------------
/src/utils/config.ts:
--------------------------------------------------------------------------------
```typescript
import { fileURLToPath } from 'url';
import path from 'path';
import fs from 'fs';
import type { Config } from '../types/index.js';
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
export function getConfig(): Config {
return {
logPath: process.env.LOG_PATH || '',
cache: {
maxAge: Number(process.env.CACHE_MAX_AGE) || 1, // 默认1小时
cleanupInterval: Number(process.env.CACHE_CLEANUP_INTERVAL) || 4, // 默认4小时
},
log: {
retentionDays: Number(process.env.LOG_RETENTION_DAYS) || 7, // 默认7天
cleanupInterval: Number(process.env.LOG_CLEANUP_INTERVAL) || 24 // 默认24小时
}
};
}
export function getLogPath(): string {
const config = getConfig();
let logPath: string;
if (config.logPath) {
try {
//在指定目录下创建子目录excel-mcp-server-logs
const subDir = 'excel-mcp-server-logs';
const subDirPath = path.join(config.logPath, subDir);
if (!fs.existsSync(subDirPath)) {
fs.mkdirSync(subDirPath, { recursive: true });
}
fs.accessSync(subDirPath);
logPath = subDirPath;
} catch (error) {
console.log(`LOG_PATH environment variable specifies an invalid or inaccessible path: ${config.logPath}, using default path instead.`);
logPath = path.join(__dirname, '../logs');
// 检查默认目录是否存在，不存在则创建
if (!fs.existsSync(logPath)) {
fs.mkdirSync(logPath, { recursive: true });
}
}
} else {
logPath = path.join(__dirname, '../logs');
// 检查默认目录是否存在，不存在则创建
if (!fs.existsSync(logPath)) {
fs.mkdirSync(logPath, { recursive: true });
}
}
return logPath;
}
```
--------------------------------------------------------------------------------
/src/utils/workbookCache.ts:
--------------------------------------------------------------------------------
```typescript
import type { WorkBook } from 'xlsx'
import type { EnsureWorkbookResult } from '../types/index.js'
interface CacheItem {
workbook: WorkBook;
timestamp: number;
}
import { getConfig } from './config.js';
class WorkbookCache {
private cache = new Map<string, CacheItem>();
private readonly maxAge: number;
private readonly cleanupInterval: number;
constructor() {
const config = getConfig();
// 将小时转换为毫秒
this.maxAge = config.cache.maxAge * 60 * 60 * 1000;
this.cleanupInterval = config.cache.cleanupInterval * 60 * 60 * 1000;
// 创建定时器，定期清理过期缓存
setInterval(() => {
const now = Date.now();
for (const [key, item] of this.cache.entries()) {
if (now - item.timestamp > this.maxAge) {
this.delete(key);
}
}
}, this.cleanupInterval);
}
set(filePathWithName: string, workbook: WorkBook): void {
this.cache.set(filePathWithName, {
workbook,
timestamp: Date.now()
});
}
get(filePathWithName: string): WorkBook | undefined {
const item = this.cache.get(filePathWithName);
if (!item) return undefined;
// 检查是否过期
if (Date.now() - item.timestamp > this.maxAge) {
this.delete(filePathWithName);
return undefined;
}
return item.workbook;
}
ensureWorkbook(filePathWithName: string): EnsureWorkbookResult {
const workbook = this.get(filePathWithName);
if (!workbook) {
return {
success: false
};
}
return {
success: true,
data: workbook
};
}
// 删除工作簿
delete(filePathWithName: string): boolean {
if (!this.cache.has(filePathWithName)) return false
return this.cache.delete(filePathWithName)
}
// 清空缓存
clear(): void {
this.cache.clear()
}
// 检查是否存在
has(filePathWithName: string): boolean {
return this.cache.has(filePathWithName)
}
}
export const workbookCache = new WorkbookCache();
```
--------------------------------------------------------------------------------
/src/tools/cacheTools.ts:
--------------------------------------------------------------------------------
```typescript
import { z } from "zod";
import { fileExists, normalizePath } from "../utils/utils.js";
import { workbookCache } from "../utils/workbookCache.js";
export const cacheTools = (server: any) => {
server.tool("clearFileCache", 'Clear cached data for the specified Excel file',
{
fileAbsolutePath: z.string().describe("The absolute path of the Excel file to clear from cache")
},
async (params: { fileAbsolutePath: string }) => {
try {
const normalizedPath = await normalizePath(params.fileAbsolutePath);
if (normalizedPath === 'error') {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Invalid file path: ${params.fileAbsolutePath}`,
suggestion: "Please verify the file path and name"
})
}]
};
}
const deleted = workbookCache.delete(normalizedPath);
return {
content: [{
type: "text",
text: JSON.stringify({
success: true,
message: deleted
? `Cache cleared successfully for file: ${normalizedPath}`
: `No cache found for file: ${normalizedPath}`
})
}]
};
} catch (error) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Failed to clear cache: ${error}`,
suggestion: "Please verify the file path"
})
}]
};
}
}
);
}
```
--------------------------------------------------------------------------------
/src/utils/utils.ts:
--------------------------------------------------------------------------------
```typescript
import * as fs from 'fs/promises';
import * as path from 'path';
import { fileURLToPath } from 'url';
import { getLogPath } from './config.js';
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
function formatDate(date: Date): string {
return date.toLocaleString('zh-CN', {
timeZone: 'Asia/Shanghai',
year: 'numeric',
month: '2-digit',
day: '2-digit',
hour: '2-digit',
minute: '2-digit',
second: '2-digit',
hour12: false
});
}
export async function logToFile(message: string) {
try {
const logDir = getLogPath();
const date = new Date();
const fileName = `${date.getFullYear()}-${(date.getMonth() + 1).toString().padStart(2, '0')}-${date.getDate().toString().padStart(2, '0')}.log`;
const logPath = path.join(logDir, fileName);
await fs.appendFile(logPath, `${formatDate(date)} - ${message}\n`);
} catch (error) {
console.error('log to file failure:', error);
}
}
export async function normalizePath(filePathWithName: string): Promise<string> {
try {
// 对路径进行编码处理
const encodedPath = encodeURIComponent(filePathWithName)
.replace(/%2F/g, '/') // 保留路径分隔符
.replace(/%20/g, ' '); // 保留空格
// 解码路径，确保中文字符正确显示
const decodedPath = decodeURIComponent(encodedPath);
return decodedPath.trim();
// return normalizedPath;
} catch (error) {
await logToFile(`normalize path failure: ${error}`);
return 'error';
}
}
//confirm file is exist
export async function fileExists(filePathWithName: string): Promise<boolean> {
try {
const fs = await import('fs');
if (!filePathWithName) {
await logToFile("path is null");
return false;
}
// 尝试规范化路径
const fileExists = fs.existsSync(filePathWithName);
if (!fileExists) {
await logToFile(`file is not exist: ${fileExists}`);
return false;
}
return true;
} catch (error) {
await logToFile(`file is not exist: ${error}`);
return false;
}
}
```
--------------------------------------------------------------------------------
/src/utils/excelUtils.ts:
--------------------------------------------------------------------------------
```typescript
import * as fs from 'fs'
import * as XLSX from 'xlsx'
import { workbookCache } from './workbookCache.js'
import { logToFile } from './utils.js';
import type { ReadSheetNamesResult } from '../types/index.js';
const READ_TIMEOUT = 300000;
export async function readAndCacheFile(filePathWithName: string): Promise<ReadSheetNamesResult> {
try {
const timeout = new Promise((_, reject) => {
setTimeout(() => reject(new Error('File reading timeout')), READ_TIMEOUT);
});
const readOperation = new Promise<ReadSheetNamesResult>(async (resolve, reject) => {
try {
// Read file in chunks
const fileStream = fs.createReadStream(filePathWithName, {
highWaterMark: 1024 * 1024 // 1MB chunks
});
const chunks: Buffer[] = [];
fileStream.on('data', (chunk: string | Buffer) => {
if (Buffer.isBuffer(chunk)) {
chunks.push(chunk);
} else {
chunks.push(Buffer.from(chunk));
}
});
fileStream.on('end', () => {
const buffer = Buffer.concat(chunks);
const workbook = XLSX.read(buffer, {
type: 'buffer',
cellDates: true,
cellNF: false,
cellText: false,
});
workbookCache.set(filePathWithName, workbook);
resolve({
success: true,
data: {
SheetNames: workbook.SheetNames,
errors: ''
}
});
});
fileStream.on('error', (error) => {
reject(error);
});
} catch (error) {
reject(error);
}
});
const result = await Promise.race([readOperation, timeout]);
return result as ReadSheetNamesResult;
} catch (bufferError) {
await logToFile(`[read-and-cache-file] Buffer read failure: ${bufferError}`);
return {
success: false,
data: {
SheetNames: [],
errors: JSON.stringify(bufferError)
}
};
}
}
```
--------------------------------------------------------------------------------
/src/tools/structureTools.ts:
--------------------------------------------------------------------------------
```typescript
import { z } from "zod";
import { fileExists, normalizePath } from "../utils/utils.js";
import { analyzeExcelStructure, exportExcelStructure } from '../handlers/excelHandlers.js'
export const structureTools = (server: any) => {
server.tool("analyzeExcelStructure", 'Get Excel file structure including sheet list and column headers in JSON format',
{
fileAbsolutePath: z.string().describe("The absolute path of the Excel file"),
headerRows: z.number().default(1).describe("Number of header rows to read (default: 1)")
},
async (params: {
fileAbsolutePath: string;
headerRows: number;
}) => {
try {
const normalizedPath = await normalizePath(params.fileAbsolutePath);
if (normalizedPath === 'error') {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Invalid file path: ${params.fileAbsolutePath}`,
suggestion: "Please verify the file path and name"
})
}]
};
}
if (!(await fileExists(normalizedPath))) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `File not found: ${params.fileAbsolutePath}`,
suggestion: "Please verify the file path and name"
})
}]
};
}
const result = await analyzeExcelStructure(normalizedPath, params.headerRows);
return {
content: [{
type: "text",
text: JSON.stringify(result)
}]
};
} catch (error) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Failed to get Excel structure: ${error}`,
suggestion: "Please verify all parameters"
})
}]
};
}
}
);
server.tool("exportExcelStructure", 'Export Excel file structure (sheets and headers) to a new Excel template file',
{
sourceFilePath: z.string().describe("The source Excel file path to analyze"),
targetFilePath: z.string().describe("The target Excel file path to save structure"),
headerRows: z.number().default(1).describe("Number of header rows to analyze (default: 1)")
},
async (params: {
sourceFilePath: string;
targetFilePath: string;
headerRows: number;
}) => {
try {
// 验证源文件路径
const normalizedSourcePath = await normalizePath(params.sourceFilePath);
if (normalizedSourcePath === 'error') {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Invalid source file path: ${params.sourceFilePath}`,
suggestion: "Please verify the source file path"
})
}]
};
}
// 验证源文件是否存在
if (!(await fileExists(normalizedSourcePath))) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Source file not found: ${params.sourceFilePath}`,
suggestion: "Please verify the source file exists"
})
}]
};
}
// 验证目标文件路径
const normalizedTargetPath = await normalizePath(params.targetFilePath);
if (normalizedTargetPath === 'error') {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Invalid target file path: ${params.targetFilePath}`,
suggestion: "Please verify the target file path"
})
}]
};
}
// 验证目标文件是否已存在
if (await fileExists(normalizedTargetPath)) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Target file already exists: ${params.targetFilePath}`,
suggestion: "Please specify a different target file path"
})
}]
};
}
// 导出结构
await exportExcelStructure(normalizedSourcePath, normalizedTargetPath, params.headerRows);
return {
content: [{
type: "text",
text: JSON.stringify({
success: true,
message: `Excel structure exported successfully to: ${normalizedTargetPath}`
})
}]
};
} catch (error) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Failed to export Excel structure: ${error}`,
suggestion: "Please verify all parameters and try again"
})
}]
};
}
}
);
}
```
--------------------------------------------------------------------------------
/src/tools/writeTools.ts:
--------------------------------------------------------------------------------
```typescript
import { z } from "zod";
import { fileExists, normalizePath } from "../utils/utils.js";
import { writeDataBySheetName, writeSheetData } from '../handlers/excelHandlers.js'
export const writeTools = (server: any) => {
server.tool("writeDataBySheetName", 'Write data to a specific sheet in the Excel file (overwrites if sheet exists)',
{
fileAbsolutePath: z.string().describe("The absolute path of the Excel file"),
sheetName: z.string().describe("The name of the sheet to write"),
data: z.array(
z.record(
z.string(),
z.any()
)
).describe("Array of objects to write to the sheet")
},
async (params: {
fileAbsolutePath: string;
sheetName: string;
data: Record<string, any>[];
}) => {
try {
const normalizedPath = await normalizePath(params.fileAbsolutePath);
if (normalizedPath === 'error') {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Invalid file path: ${params.fileAbsolutePath}`,
suggestion: "Please verify the file path and name"
})
}]
};
}
// 校验数据结构
if (!Array.isArray(params.data) || params.data.length === 0) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: "Empty data array provided",
suggestion: "Please provide non-empty array of data"
})
}]
};
}
// 校验工作表名称
if (!params.sheetName) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: "Invalid sheet name",
suggestion: "Please provide a valid sheet name"
})
}]
};
}
await writeDataBySheetName(normalizedPath, params.sheetName, params.data);
return {
content: [{
type: "text",
text: JSON.stringify({
success: true,
message: `Data written successfully to sheet '${params.sheetName}' in file: ${normalizedPath}`
})
}]
};
} catch (error) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Failed to write sheet data: ${error}`,
suggestion: "Please verify all parameters and try again"
})
}]
};
}
}
);
server.tool("writeSheetData", 'Create a new Excel file with provided data',
{
fileAbsolutePath: z.string().describe("The absolute path for the new Excel file"),
data: z.record(
z.string(), // 表名（动态）
z.array( // 表数据数组
z.record( // 每行数据对象
z.string(), // 字段名（动态）
z.any() // 字段值（任意类型）
)
)
).describe("Data object with dynamic sheet names and column names")
},
async (params: {
fileAbsolutePath: string;
data: Record<string, Record<string, any>[]>;
}) => {
try {
const normalizedPath = await normalizePath(params.fileAbsolutePath);
if (normalizedPath === 'error') {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Invalid file path: ${params.fileAbsolutePath}`,
suggestion: "Please verify the file path and name"
})
}]
};
}
// 校验数据结构
if (Object.keys(params.data).length === 0) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: "Empty data object provided",
suggestion: "Please provide at least one sheet with data"
})
}]
};
}
// 校验每个表的数据
for (const [sheetName, sheetData] of Object.entries(params.data)) {
if (!Array.isArray(sheetData) || sheetData.length === 0) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Invalid data format in sheet "${sheetName}": Data must be a non-empty array`,
suggestion: "Please check the data format of each sheet"
})
}]
};
}
}
if (await fileExists(normalizedPath)) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `File already exists: ${params.fileAbsolutePath}`,
suggestion: "Please specify a different file path"
})
}]
};
}
await writeSheetData(normalizedPath, params.data);
return {
content: [{
type: "text",
text: JSON.stringify({
success: true,
message: `Excel file created successfully: ${normalizedPath}`
})
}]
};
} catch (error) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Failed to write Excel data: ${error}`,
suggestion: "Please verify the data format and file path"
})
}]
};
}
}
);
}
```
--------------------------------------------------------------------------------
/src/tools/readTools.ts:
--------------------------------------------------------------------------------
```typescript
import { z } from "zod";
import { fileExists, normalizePath } from "../utils/utils.js";
import { readAllSheetData, readDataBySheetName, readSheetNames } from '../handlers/excelHandlers.js'
export const readTools = (server: any) => {
server.tool("readSheetNames", 'Get all sheet names from the Excel file',
{
fileAbsolutePath: z.string().describe("The absolute path of the Excel file")
},
async (params: { fileAbsolutePath: string }) => {
try {
const normalizedPath = await normalizePath(params.fileAbsolutePath);
if (normalizedPath === 'error') {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `path is not valid: ${params.fileAbsolutePath}`,
suggestion: "please check the path and filename"
})
}]
};
}
if (!(await fileExists(normalizedPath))) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `file is not exist: ${params.fileAbsolutePath}`,
suggestion: "please check the path and filename"
})
}]
};
}
const result = await readSheetNames(normalizedPath);
return {
content: [{
type: "text",
text: JSON.stringify(result)
}]
};
} catch (error) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `read sheet names failure: ${error}`,
suggestion: "please check the path and filename"
})
}]
};
}
}
);
server.tool("readDataBySheetName", 'Get data from a specific sheet in the Excel file',
{
fileAbsolutePath: z.string().describe("The absolute path of the Excel file"),
sheetName: z.string().describe("tThe name of the sheet to read"),
headerRow: z.number().default(1).describe("tThe row number to use as field names (default: 1)"),
dataStartRow: z.number().default(2).describe("The row number to start reading data from (default: 2)")
},
async (params: {
fileAbsolutePath: string,
sheetName: string,
headerRow: number,
dataStartRow: number
}) => {
try {
const normalizedPath = await normalizePath(params.fileAbsolutePath);
if (normalizedPath === 'error') {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Invalid file path: ${params.fileAbsolutePath}`,
suggestion: "Please verify the file path and name"
})
}]
};
}
if (!(await fileExists(normalizedPath))) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `File not exist: ${params.fileAbsolutePath}`,
suggestion: "Please verify the file path and name"
})
}]
};
}
if (!params.sheetName) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Invalid sheet name: ${params.sheetName}`,
suggestion: "Please verify the sheet name"
})
}]
};
}
const result = await readDataBySheetName(normalizedPath, params.sheetName, params.headerRow, params.dataStartRow);
return {
content: [{
type: "text",
text: JSON.stringify(result)
}]
};
} catch (error) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Failed to read data from sheet: ${params.sheetName} failure: ${error}`,
suggestion: "Please verify all parameters"
})
}]
};
}
}
);
server.tool("readSheetData", 'Get data from all sheets in the Excel file',
{
fileAbsolutePath: z.string().describe("The absolute path of the Excel file"),
headerRow: z.number().default(1).describe("The row number to use as field names (default: 1)"),
dataStartRow: z.number().default(2).describe("The row number to start reading data from (default: 2)")
},
async (params: { fileAbsolutePath: string, headerRow: number, dataStartRow: number }) => {
try {
const normalizedPath = await normalizePath(params.fileAbsolutePath);
if (normalizedPath === 'error') {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Invalid file path: ${params.fileAbsolutePath}`,
suggestion: "Please verify the file path and name"
})
}]
};
}
if (!(await fileExists(normalizedPath))) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `File not found: ${params.fileAbsolutePath}`,
suggestion: "Please verify the file path and name"
})
}]
};
}
const result = await readAllSheetData(
normalizedPath,
params.headerRow,
params.dataStartRow
);
return {
content: [{
type: "text",
text: JSON.stringify(result)
}]
};
} catch (error) {
return {
content: [{
type: "text",
text: JSON.stringify({
error: `Failed to read Excel data: ${error}`,
suggestion: "Please verify all parameters"
})
}]
};
}
}
);
}
```
--------------------------------------------------------------------------------
/src/handlers/excelHandlers.ts:
--------------------------------------------------------------------------------
```typescript
import { workbookCache } from "../utils/workbookCache.js";
import * as XLSX from 'xlsx'
import { logToFile,fileExists } from '../utils/utils.js';
import { readAndCacheFile } from "../utils/excelUtils.js";
import type { EnsureWorkbookResult, ReadSheetNamesResult, SheetData ,ExcelStructure} from '../types/index.js';
export async function readSheetNames(filePathWithName: string): Promise<string[]> {
try {
//从缓存中获取workbook
const workbookResult: EnsureWorkbookResult = workbookCache.ensureWorkbook(filePathWithName);
if (!workbookResult.success) {
// 缓存中没有workbook，尝试读取并缓存文件
const readResult: ReadSheetNamesResult = await readAndCacheFile(filePathWithName);
if (!readResult.success) {
// 读取文件失败，返回错误信息
throw new Error(`read file failure: ${readResult.data.errors}`);
} else {
return readResult.data.SheetNames;
}
} else {
const workbook = workbookResult.data as XLSX.WorkBook;
return workbook.SheetNames
}
} catch (error) {
const errorMessage = error instanceof Error ? error.message : String(error);
throw new Error(`read file failure: ${errorMessage}`);
}
}
export async function readDataBySheetName(
filePathWithName: string,
sheetName: string,
headerRow: number = 1, // 默认第一行为表头
dataStartRow: number = 2 // 默认第二行开始为数据
): Promise<any> {
try {
const workbookResult: EnsureWorkbookResult = workbookCache.ensureWorkbook(filePathWithName);
let workbook: XLSX.WorkBook;
if (!workbookResult.success) {
const readResult: ReadSheetNamesResult = await readAndCacheFile(filePathWithName);
if (!readResult.success) {
throw new Error(`read file failure: ${readResult.data.errors}`);
}
workbook = workbookCache.get(filePathWithName)!;
} else {
workbook = workbookResult.data as XLSX.WorkBook;
}
const worksheet = workbook.Sheets[sheetName];
if (!worksheet) {
throw new Error(`sheet ${sheetName} not found`);
}
// 将 Excel 行号转换为数组索引（减1）
const headerIndex = headerRow - 1;
const dataStartIndex = dataStartRow - 1;
// 获取原始数据
const rawData = XLSX.utils.sheet_to_json(worksheet, {
raw: true,
defval: '',
header: 1
});
if (rawData.length <= headerIndex) {
throw new Error(`sheet is empty or header row (${headerRow}) exceeds sheet length`);
}
// 获取表头
const headers = rawData[headerIndex] as string[];
// 验证数据起始行
if (rawData.length <= dataStartIndex) {
throw new Error(`data start row (${dataStartRow}) exceeds sheet length`);
}
// 处理数据行
const dataRows = rawData.slice(dataStartIndex);
const result = dataRows.map((row: any) => {
const item: { [key: string]: any } = {};
headers.forEach((header, index) => {
if (header) {
item[header] = row[index] || '';
}
});
return item;
});
return result;
} catch (error) {
const errorMessage = error instanceof Error ? error.message : String(error);
throw new Error(`read sheet data failure: ${errorMessage}`);
}
}
export async function readAllSheetData(
filePathWithName: string,
headerRow: number = 1,
dataStartRow: number = 2
): Promise<{ [sheetName: string]: any[] }> {
try {
const workbookResult: EnsureWorkbookResult = workbookCache.ensureWorkbook(filePathWithName);
let workbook: XLSX.WorkBook;
if (!workbookResult.success) {
const readResult: ReadSheetNamesResult = await readAndCacheFile(filePathWithName);
if (!readResult.success) {
throw new Error(`Failed to read file: ${readResult.data.errors}`);
}
workbook = workbookCache.get(filePathWithName)!;
} else {
workbook = workbookResult.data as XLSX.WorkBook;
}
const result: { [sheetName: string]: any[] } = {};
const headerIndex = headerRow - 1;
const dataStartIndex = dataStartRow - 1;
// 遍历所有工作表
for (const sheetName of workbook.SheetNames) {
const worksheet = workbook.Sheets[sheetName];
// 获取原始数据
const rawData = XLSX.utils.sheet_to_json(worksheet, {
raw: true,
defval: '',
header: 1
});
if (rawData.length <= headerIndex) {
await logToFile(`Sheet ${sheetName} is empty or header row (${headerRow}) exceeds sheet length`);
continue;
}
// 获取表头
const headers = rawData[headerIndex] as string[];
// 验证数据起始行
if (rawData.length <= dataStartIndex) {
await logToFile(`Sheet ${sheetName} data start row (${dataStartRow}) exceeds sheet length`);
continue;
}
// 处理数据行
const dataRows = rawData.slice(dataStartIndex);
result[sheetName] = dataRows.map((row: any) => {
const item: { [key: string]: any } = {};
headers.forEach((header, index) => {
if (header) {
item[header] = row[index] || '';
}
});
return item;
});
}
return result;
} catch (error) {
const errorMessage = error instanceof Error ? error.message : String(error);
throw new Error(`Failed to read Excel data: ${errorMessage}`);
}
}
export async function writeSheetData(
filePathWithName: string,
data: SheetData
): Promise<boolean> {
try {
// 创建新的工作簿
const workbook = XLSX.utils.book_new();
// 遍历数据对象的每个工作表
for (const [sheetName, sheetData] of Object.entries(data)) {
// 将数据转换为工作表
const worksheet = XLSX.utils.json_to_sheet(sheetData);
// 将工作表添加到工作簿
XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);
}
// 写入文件
XLSX.writeFile(workbook, filePathWithName);
return true;
} catch (error) {
const errorMessage = error instanceof Error ? error.message : String(error);
throw new Error(`Failed to write Excel data: ${errorMessage}`);
}
}
export async function writeDataBySheetName(
filePathWithName: string,
sheetName: string,
data: any[]
): Promise<boolean> {
try {
let workbook: XLSX.WorkBook;
// 检查文件是否存在，注：filePathWithName ,已经经过了normalizePath
if (await fileExists(filePathWithName)) {
// 如果文件存在，读取现有工作簿
const workbookResult = workbookCache.ensureWorkbook(filePathWithName);
if (!workbookResult.success) {
const readResult = await readAndCacheFile(filePathWithName);
if (!readResult.success) {
throw new Error(`Failed to read existing file: ${readResult.data.errors}`);
}
workbook = workbookCache.get(filePathWithName)!;
} else {
workbook = workbookResult.data as XLSX.WorkBook;
}
} else {
// 如果文件不存在，创建新的工作簿
workbook = XLSX.utils.book_new();
}
// 将数据转换为工作表
const worksheet = XLSX.utils.json_to_sheet(data);
// 检查工作表是否已存在
if (workbook.SheetNames.includes(sheetName)) {
// 如果存在，删除旧的工作表
const index = workbook.SheetNames.indexOf(sheetName);
workbook.SheetNames.splice(index, 1);
delete workbook.Sheets[sheetName];
}
// 添加新的工作表
XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);
// 写入文件
XLSX.writeFile(workbook, filePathWithName);
// 更新缓存
workbookCache.set(filePathWithName, workbook);
return true;
} catch (error) {
const errorMessage = error instanceof Error ? error.message : String(error);
throw new Error(`Failed to write sheet data: ${errorMessage}`);
}
}
export async function analyzeExcelStructure(
filePathWithName: string,
headerRows: number = 1
): Promise<ExcelStructure> {
try {
const workbookResult: EnsureWorkbookResult = workbookCache.ensureWorkbook(filePathWithName);
let workbook: XLSX.WorkBook;
if (!workbookResult.success) {
const readResult = await readAndCacheFile(filePathWithName);
if (!readResult.success) {
throw new Error(`Failed to read file: ${readResult.data.errors}`);
}
workbook = workbookCache.get(filePathWithName)!;
} else {
workbook = workbookResult.data as XLSX.WorkBook;
}
const result: ExcelStructure = {
sheetList: [],
sheetField: []
};
result.sheetList = workbook.SheetNames.map((sheetName, index) => ({
SheetNo: index + 1, // 添加从1开始的序号
SheetName: sheetName
}));
// 遍历所有工作表
for (const sheetName of workbook.SheetNames) {
const worksheet = workbook.Sheets[sheetName];
// 获取原始数据
const rawData = XLSX.utils.sheet_to_json(worksheet, {
raw: true,
defval: '',
header: 1
});
if (rawData.length === 0) {
continue;
}
// 获取每列的数据
const columnCount = (rawData[0] as any[]).length;
for (let colIndex = 0; colIndex < columnCount; colIndex++) {
const fieldInfo: any = {
SheetName: sheetName
};
// 根据 headerRows 获取指定数量的表头行
for (let i = 1; i <= headerRows; i++) {
const headerIndex = i - 1;
if (rawData.length > headerIndex) {
const rowData = rawData[headerIndex] as any[];
fieldInfo[`Field${i}`] = rowData[colIndex] || '';
} else {
fieldInfo[`Field${i}`] = '';
}
}
result.sheetField = result.sheetField || [];
result.sheetField.push(fieldInfo);
}
}
return result
// {
// // 修改 sheetList 的映射，添加 SheetNo
// sheetList: workbook.SheetNames.map((sheetName, index) => ({
// SheetNo: index + 1, // 添加从1开始的序号
// SheetName: sheetName
// })),
// sheetField: result.sheetField || []
// };
} catch (error) {
const errorMessage = error instanceof Error ? error.message : String(error);
throw new Error(`Failed to get Excel structure: ${errorMessage}`);
}
}
export async function exportExcelStructure(
sourceFilePath: string,
targetFilePath: string,
headerRows: number = 1
): Promise<boolean> {
try {
// 1. 获取源文件的结构
const structure = await analyzeExcelStructure(sourceFilePath, headerRows);
// 校验结构数据
if (!structure.sheetList || !Array.isArray(structure.sheetList) || structure.sheetList.length === 0) {
throw new Error('Invalid Excel structure: sheetList is empty or invalid');
}
if (!structure.sheetField || !Array.isArray(structure.sheetField) || structure.sheetField.length === 0) {
throw new Error('Invalid Excel structure: sheetField is empty or invalid');
}
// 2. 转换为 SheetData 格式
const data: SheetData = {
'sheetList': structure.sheetList,
'sheetField': structure.sheetField
};
// 3. 使用 writeSheetData 写入文件
return await writeSheetData(targetFilePath, data);
} catch (error) {
const errorMessage = error instanceof Error ? error.message : String(error);
throw new Error(`Failed to export Excel structure: ${errorMessage}`);
}
}
```