# Directory Structure
```
├── .devcontainer
│ ├── devcontainer.json
│ ├── Dockerfile
│ ├── init-firewall.sh
│ └── postCreateCommand.sh
├── .dockerignore
├── .editorconfig
├── .github
│ └── workflows
│ └── publish.yml
├── .gitignore
├── .goreleaser.yaml
├── .npmignore
├── CLAUDE.md
├── cmd
│ └── excel-mcp-server
│ └── main.go
├── Dockerfile
├── docs
│ ├── design
│ │ └── excel-style-schema.md
│ └── img
│ ├── icon-400.png
│ └── icon-800.png
├── go.mod
├── go.sum
├── internal
│ ├── excel
│ │ ├── excel_excelize.go
│ │ ├── excel_ole.go
│ │ ├── excel.go
│ │ ├── pagination.go
│ │ └── util.go
│ ├── mcp
│ │ └── error.go
│ ├── server
│ │ └── server.go
│ └── tools
│ ├── common.go
│ ├── config.go
│ ├── excel_copy_sheet.go
│ ├── excel_create_table.go
│ ├── excel_describe_sheets.go
│ ├── excel_format_range.go
│ ├── excel_read_sheet.go
│ ├── excel_screen_capture.go
│ └── excel_write_to_sheet.go
├── launcher
│ └── launcher.ts
├── LICENSE
├── package-lock.json
├── package.json
├── projectBrief.md
├── README.md
├── smithery.yaml
└── tsconfig.json
```
# Files
--------------------------------------------------------------------------------
/.dockerignore:
--------------------------------------------------------------------------------
```
/dist
/node_modules
```
--------------------------------------------------------------------------------
/.npmignore:
--------------------------------------------------------------------------------
```
/.github
/cmd
/internal
/go.mod
/go.sum
/.goreleaser.yaml
```
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
/node_modules
/dist
/memory-bank
# Added by goreleaser init:
dist/
# RooFlow
.roo
.roomodes
.clinerules-default
# repomix
repomix-output.xml
# Claude Code
CLAUDE.local.md
.claude/settings.local.json
```
--------------------------------------------------------------------------------
/.editorconfig:
--------------------------------------------------------------------------------
```
# EditorConfig is awesome: https://EditorConfig.org
# top-most EditorConfig file
root = true
[*]
charset = utf-8
indent_style = space
indent_size = 2
end_of_line = lf
trim_trailing_whitespace = true
insert_final_newline = true
[*.md]
trim_trailing_whitespace = false
indent_size = 4
```
--------------------------------------------------------------------------------
/.goreleaser.yaml:
--------------------------------------------------------------------------------
```yaml
version: 2
before:
hooks:
# You may remove this if you don't use go modules.
- go mod tidy
# you may remove this if you don't need go generate
- go generate ./...
builds:
- env:
- CGO_ENABLED=0
main: ./cmd/excel-mcp-server/main.go
goos:
- linux
- windows
- darwin
archives:
- formats: ['tar.gz']
# this name template makes the OS and Arch compatible with the results of `uname`.
name_template: >-
{{ .ProjectName }}_
{{- title .Os }}_
{{- if eq .Arch "amd64" }}x86_64
{{- else if eq .Arch "386" }}i386
{{- else }}{{ .Arch }}{{ end }}
{{- if .Arm }}v{{ .Arm }}{{ end }}
# use zip for windows archives
format_overrides:
- goos: windows
formats: ['zip']
builds_info:
mode: 0755
changelog:
sort: asc
filters:
exclude:
- "^docs:"
- "^test:"
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
# Excel MCP Server
<img src="https://github.com/negokaz/excel-mcp-server/blob/main/docs/img/icon-800.png?raw=true" width="128">
<a href="https://glama.ai/mcp/servers/@negokaz/excel-mcp-server">
<img width="380" height="200" src="https://glama.ai/mcp/servers/@negokaz/excel-mcp-server/badge" alt="Excel Server MCP server" />
</a>
[](https://www.npmjs.com/package/@negokaz/excel-mcp-server)
[](https://smithery.ai/server/@negokaz/excel-mcp-server)
A Model Context Protocol (MCP) server that reads and writes MS Excel data.
## Features
- Read/Write text values
- Read/Write formulas
- Create new sheets
**🪟Windows only:**
- Live editing
- Capture screen image from a sheet
For more details, see the [tools](#tools) section.
## Requirements
- Node.js 20.x or later
## Supported file formats
- xlsx (Excel book)
- xlsm (Excel macro-enabled book)
- xltx (Excel template)
- xltm (Excel macro-enabled template)
## Installation
### Installing via NPM
excel-mcp-server is automatically installed by adding the following configuration to the MCP servers configuration.
For Windows:
```json
{
"mcpServers": {
"excel": {
"command": "cmd",
"args": ["/c", "npx", "--yes", "@negokaz/excel-mcp-server"],
"env": {
"EXCEL_MCP_PAGING_CELLS_LIMIT": "4000"
}
}
}
}
```
For other platforms:
```json
{
"mcpServers": {
"excel": {
"command": "npx",
"args": ["--yes", "@negokaz/excel-mcp-server"],
"env": {
"EXCEL_MCP_PAGING_CELLS_LIMIT": "4000"
}
}
}
}
```
### Installing via Smithery
To install Excel MCP Server for Claude Desktop automatically via [Smithery](https://smithery.ai/server/@negokaz/excel-mcp-server):
```bash
npx -y @smithery/cli install @negokaz/excel-mcp-server --client claude
```
<h2 id="tools">Tools</h2>
### `excel_describe_sheets`
List all sheet information of specified Excel file.
**Arguments:**
- `fileAbsolutePath`
- Absolute path to the Excel file
### `excel_read_sheet`
Read values from Excel sheet with pagination.
**Arguments:**
- `fileAbsolutePath`
- Absolute path to the Excel file
- `sheetName`
- Sheet name in the Excel file
- `range`
- Range of cells to read in the Excel sheet (e.g., "A1:C10"). [default: first paging range]
- `showFormula`
- Show formula instead of value [default: false]
- `showStyle`
- Show style information for cells [default: false]
### `excel_screen_capture`
**[Windows only]** Take a screenshot of the Excel sheet with pagination.
**Arguments:**
- `fileAbsolutePath`
- Absolute path to the Excel file
- `sheetName`
- Sheet name in the Excel file
- `range`
- Range of cells to read in the Excel sheet (e.g., "A1:C10"). [default: first paging range]
### `excel_write_to_sheet`
Write values to the Excel sheet.
**Arguments:**
- `fileAbsolutePath`
- Absolute path to the Excel file
- `sheetName`
- Sheet name in the Excel file
- `newSheet`
- Create a new sheet if true, otherwise write to the existing sheet
- `range`
- Range of cells to read in the Excel sheet (e.g., "A1:C10").
- `values`
- Values to write to the Excel sheet. If the value is a formula, it should start with "="
### `excel_create_table`
Create a table in the Excel sheet
**Arguments:**
- `fileAbsolutePath`
- Absolute path to the Excel file
- `sheetName`
- Sheet name where the table is created
- `range`
- Range to be a table (e.g., "A1:C10")
- `tableName`
- Table name to be created
### `excel_copy_sheet`
Copy existing sheet to a new sheet
**Arguments:**
- `fileAbsolutePath`
- Absolute path to the Excel file
- `srcSheetName`
- Source sheet name in the Excel file
- `dstSheetName`
- Sheet name to be copied
### `excel_format_range`
Format cells in the Excel sheet with style information
**Arguments:**
- `fileAbsolutePath`
- Absolute path to the Excel file
- `sheetName`
- Sheet name in the Excel file
- `range`
- Range of cells in the Excel sheet (e.g., "A1:C3")
- `styles`
- 2D array of style objects for each cell. If a cell does not change style, use null. The number of items of the array must match the range size.
- Style object properties:
- `border`: Array of border styles (type, color, style)
- `font`: Font styling (bold, italic, underline, size, strike, color, vertAlign)
- `fill`: Fill/background styling (type, pattern, color, shading)
- `numFmt`: Custom number format string
- `decimalPlaces`: Number of decimal places (0-30)
<h2 id="configuration">Configuration</h2>
You can change the MCP Server behaviors by the following environment variables:
### `EXCEL_MCP_PAGING_CELLS_LIMIT`
The maximum number of cells to read in a single paging operation.
[default: 4000]
## License
Copyright (c) 2025 Kazuki Negoro
excel-mcp-server is released under the [MIT License](LICENSE)
```
--------------------------------------------------------------------------------
/CLAUDE.md:
--------------------------------------------------------------------------------
```markdown
# CLAUDE.md
This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.
## Project Overview
This is an Excel MCP (Model Context Protocol) Server that enables AI assistants to interact with Microsoft Excel files. It provides a bridge between AI models and Excel spreadsheets for programmatic data manipulation.
## Development Commands
### Build and Development
```bash
npm run build # Build Go binaries + compile TypeScript
npm run watch # Watch TypeScript files for changes
npm run debug # Debug with MCP inspector
```
### Testing
```bash
go test ./... # Run all Go tests
go test ./internal/tools -v # Run specific package tests
go test -run TestReadSheetData ./internal/tools # Run specific test
```
### Linting and Formatting
```bash
go fmt ./... # Format Go code
go vet ./... # Vet Go code for issues
```
## Architecture
### Core Components
**Dual Backend Architecture**: The server supports two Excel backends:
- **Windows**: OLE automation for live Excel interaction (`excel_ole.go`)
- **Cross-platform**: Excelize library for file operations (`excel_excelize.go`)
**Key Interfaces**:
- `ExcelInterface` in `internal/excel/excel.go` - Unified Excel operations API
- `Tool` interface in `internal/tools/` - MCP tool implementations
**Entry Points**:
- `cmd/excel-mcp-server/main.go` - Go binary entry point
- `launcher/launcher.ts` - Cross-platform launcher that selects appropriate binary
### Tool System
MCP tools are implemented in `internal/tools/`:
- `excel_describe_sheets` - List worksheets and metadata
- `excel_read_sheet` - Read sheet data with pagination
- `excel_write_to_sheet` - Write data to sheets
- `excel_create_table` - Create Excel tables
- `excel_copy_sheet` - Copy sheets between workbooks
- `excel_screen_capture` - Windows-only screenshot functionality
### Pagination System
Large datasets are handled through configurable pagination:
- Default limit: 4000 cells
- Configurable via `EXCEL_MCP_PAGING_CELLS_LIMIT` environment variable
- Implemented in `internal/excel/pagination.go`
## File Structure
```
cmd/excel-mcp-server/ # Main application entry point
internal/
excel/ # Excel abstraction layer
server/ # MCP server implementation
tools/ # MCP tool implementations
launcher/ # TypeScript launcher
memory-bank/ # Development context and progress
```
## Build System
Uses GoReleaser (`.goreleaser.yaml`) to create cross-platform binaries:
- Windows: amd64, 386, arm64
- macOS: amd64, arm64
- Linux: amd64, 386, arm64
TypeScript launcher is compiled to `dist/launcher.js` and published to NPM.
## Platform Differences
**Windows-specific features**:
- Live Excel interaction via OLE automation
- Screen capture capabilities
- Requires Excel to be installed
**Cross-platform features**:
- File-based Excel operations only
- No live editing capabilities
- Works with xlsx, xlsm, xltx, xltm formats
## Configuration
Environment variables:
- `EXCEL_MCP_PAGING_CELLS_LIMIT` - Maximum cells per page (default: 4000)
## Dependencies
**Go**: Requires Go 1.23.0+ with Go 1.24.0 toolchain
**Node.js**: Requires Node.js 20.x+ for TypeScript compilation
**Key packages**:
- `github.com/mark3labs/mcp-go` - MCP framework
- `github.com/xuri/excelize/v2` - Excel file operations
- `github.com/go-ole/go-ole` - Windows OLE automation
```
--------------------------------------------------------------------------------
/.devcontainer/postCreateCommand.sh:
--------------------------------------------------------------------------------
```bash
#!/bin/bash
set -ex
npm install -g @anthropic-ai/claude-code
npm install -g @goreleaser/goreleaser
```
--------------------------------------------------------------------------------
/Dockerfile:
--------------------------------------------------------------------------------
```dockerfile
FROM node:20-slim AS release
# Set the working directory
WORKDIR /app
RUN npm install -g @negokaz/[email protected]
# Command to run the application
ENTRYPOINT ["excel-mcp-server"]
```
--------------------------------------------------------------------------------
/cmd/excel-mcp-server/main.go:
--------------------------------------------------------------------------------
```go
package main
import (
"fmt"
"os"
"github.com/negokaz/excel-mcp-server/internal/server"
)
var (
version = "dev"
)
func main() {
s := server.New(version)
err := s.Start()
if err != nil {
fmt.Fprintf(os.Stderr, "Failed to start the server: %v\n", err)
os.Exit(1)
}
}
```
--------------------------------------------------------------------------------
/.devcontainer/devcontainer.json:
--------------------------------------------------------------------------------
```json
{
"name": "Claude Code Dev Container",
"build": {
"dockerfile": "Dockerfile"
},
"features": {
"ghcr.io/devcontainers/features/go:1":{}
},
"mounts": [
"source=${localEnv:HOME}/.claude,target=/home/node/.claude,type=bind,consistency=cached"
],
"postCreateCommand": ".devcontainer/postCreateCommand.sh",
"remoteUser": "node"
}
```
--------------------------------------------------------------------------------
/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.
{}
commandFunction:
# A function that produces the CLI command to start the MCP on stdio.
|-
(config) => ({ command: 'node', args: ['dist/launcher.js'], env: {} })
```
--------------------------------------------------------------------------------
/tsconfig.json:
--------------------------------------------------------------------------------
```json
{
"compilerOptions": {
"target": "ES2022",
"module": "Node16",
"moduleResolution": "Node16",
"strict": true,
"esModuleInterop": true,
"skipLibCheck": true,
"forceConsistentCasingInFileNames": true,
"resolveJsonModule": true,
"outDir": "./dist",
"rootDir": "./launcher",
},
"include": ["launcher/**/*"],
"exclude": ["node_modules"]
}
```
--------------------------------------------------------------------------------
/internal/tools/config.go:
--------------------------------------------------------------------------------
```go
package tools
import (
z "github.com/Oudwins/zog"
"github.com/Oudwins/zog/zenv"
)
type EnvConfig struct {
EXCEL_MCP_PAGING_CELLS_LIMIT int
}
var configSchema = z.Struct(z.Shape{
"EXCEL_MCP_PAGING_CELLS_LIMIT": z.Int().GT(0).Default(4000),
})
func LoadConfig() (EnvConfig, z.ZogIssueMap) {
config := EnvConfig{}
issues := configSchema.Parse(zenv.NewDataProvider(), &config)
return config, issues
}
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
{
"name": "@negokaz/excel-mcp-server",
"version": "0.12.0",
"description": "An MCP server that reads and writes spreadsheet data to MS Excel file",
"author": "negokaz",
"license": "MIT",
"bin": {
"excel-mcp-server": "dist/launcher.js"
},
"scripts": {
"build": "goreleaser build --snapshot --clean && tsc",
"watch": "tsc --watch",
"debug": "npx @modelcontextprotocol/inspector dist/launcher.js"
},
"devDependencies": {
"@types/node": "^22.13.4",
"typescript": "^5.7.3"
},
"publishConfig": {
"access": "public"
}
}
```
--------------------------------------------------------------------------------
/internal/mcp/error.go:
--------------------------------------------------------------------------------
```go
package mcp
import (
"fmt"
z "github.com/Oudwins/zog"
"github.com/mark3labs/mcp-go/mcp"
)
func NewToolResultInvalidArgumentError(message string) *mcp.CallToolResult {
return mcp.NewToolResultError(fmt.Sprintf("Invalid argument: %s", message))
}
func NewToolResultZogIssueMap(errs z.ZogIssueMap) *mcp.CallToolResult {
issues := z.Issues.SanitizeMap(errs)
var issueResults []mcp.Content
for k, messages := range issues {
for _, message := range messages {
issueResults = append(issueResults, mcp.NewTextContent(fmt.Sprintf("Invalid argument: %s: %s", k, message)))
}
}
return &mcp.CallToolResult{
Content: issueResults,
IsError: true,
}
}
```
--------------------------------------------------------------------------------
/internal/server/server.go:
--------------------------------------------------------------------------------
```go
package server
import (
"runtime"
"github.com/mark3labs/mcp-go/server"
"github.com/negokaz/excel-mcp-server/internal/tools"
)
type ExcelServer struct {
server *server.MCPServer
}
func New(version string) *ExcelServer {
s := &ExcelServer{}
s.server = server.NewMCPServer(
"excel-mcp-server",
version,
)
tools.AddExcelDescribeSheetsTool(s.server)
tools.AddExcelReadSheetTool(s.server)
if runtime.GOOS == "windows" {
tools.AddExcelScreenCaptureTool(s.server)
}
tools.AddExcelWriteToSheetTool(s.server)
tools.AddExcelCreateTableTool(s.server)
tools.AddExcelCopySheetTool(s.server)
tools.AddExcelFormatRangeTool(s.server)
return s
}
func (s *ExcelServer) Start() error {
return server.ServeStdio(s.server)
}
```
--------------------------------------------------------------------------------
/launcher/launcher.ts:
--------------------------------------------------------------------------------
```typescript
#!/usr/bin/env node
import * as path from 'path'
import * as childProcess from 'child_process'
const BINARY_DISTRIBUTION_PACKAGES: any = {
win32_ia32: "excel-mcp-server_windows_386_sse2",
win32_x64: "excel-mcp-server_windows_amd64_v1",
win32_arm64: "excel-mcp-server_windows_arm64_v8.0",
darwin_x64: "excel-mcp-server_darwin_amd64_v1",
darwin_arm64: "excel-mcp-server_darwin_arm64_v8.0",
linux_ia32: "excel-mcp-server_linux_386_sse2",
linux_x64: "excel-mcp-server_linux_amd64_v1",
linux_arm64: "excel-mcp-server_linux_arm64_v8.0",
}
function getBinaryPath(): string {
const suffix = process.platform === 'win32' ? '.exe' : '';
const pkg = BINARY_DISTRIBUTION_PACKAGES[`${process.platform}_${process.arch}`];
if (pkg) {
return path.resolve(__dirname, pkg, `excel-mcp-server${suffix}`);
} else {
throw new Error(`Unsupported platform: ${process.platform}_${process.arch}`);
}
}
childProcess.execFileSync(getBinaryPath(), process.argv, {
stdio: 'inherit',
});
```
--------------------------------------------------------------------------------
/.github/workflows/publish.yml:
--------------------------------------------------------------------------------
```yaml
name: Publish Package to npmjs
on:
push:
tags:
- 'v*'
jobs:
build:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
with:
fetch-depth: 0
- name: Set up Go
uses: actions/setup-go@v5
with:
go-version: stable
- name: Install GoReleaser
uses: goreleaser/goreleaser-action@v6
with:
install-only: true
distribution: goreleaser
# 'latest', 'nightly', or a semver
version: "~> v2"
- name: Setup Node.js
uses: actions/setup-node@v4
with:
node-version: '20.x'
registry-url: 'https://registry.npmjs.org'
- name: Install dependencies
run: npm ci
- name: Build
run: npm run build
- name: Publish to npmjs
run: npm publish --no-git-checks
env:
NODE_AUTH_TOKEN: ${{ secrets.NPM_TOKEN }}
- name: Create GitHub Releases
run: goreleaser release --clean
env:
GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
```
--------------------------------------------------------------------------------
/internal/excel/util.go:
--------------------------------------------------------------------------------
```go
package excel
import (
"fmt"
"os"
"path"
"regexp"
"github.com/xuri/excelize/v2"
)
// parseRange parses Excel's range string (e.g. A1:C10 or A1)
func ParseRange(rangeStr string) (int, int, int, int, error) {
re := regexp.MustCompile(`^(\$?[A-Z]+\$?\d+)(?::(\$?[A-Z]+\$?\d+))?$`)
matches := re.FindStringSubmatch(rangeStr)
if matches == nil {
return 0, 0, 0, 0, fmt.Errorf("invalid range format: %s", rangeStr)
}
startCol, startRow, err := excelize.CellNameToCoordinates(matches[1])
if err != nil {
return 0, 0, 0, 0, err
}
if matches[2] == "" {
// Single cell case
return startCol, startRow, startCol, startRow, nil
}
endCol, endRow, err := excelize.CellNameToCoordinates(matches[2])
if err != nil {
return 0, 0, 0, 0, err
}
return startCol, startRow, endCol, endRow, nil
}
func NormalizeRange(rangeStr string) string {
startCol, startRow, endCol, endRow, _ := ParseRange(rangeStr)
startCell, _ := excelize.CoordinatesToCellName(startCol, startRow)
endCell, _ := excelize.CoordinatesToCellName(endCol, endRow)
return fmt.Sprintf("%s:%s", startCell, endCell)
}
// FileIsNotReadable checks if a file is not writable
func FileIsNotWritable(absolutePath string) bool {
f, err := os.OpenFile(path.Clean(absolutePath), os.O_WRONLY, os.ModePerm)
if err != nil {
return true
}
defer f.Close()
return false
}
```
--------------------------------------------------------------------------------
/.devcontainer/Dockerfile:
--------------------------------------------------------------------------------
```dockerfile
FROM node:20
ARG TZ
ENV TZ="$TZ"
# Install basic development tools and iptables/ipset
RUN apt update && apt install -y less \
git \
procps \
sudo \
fzf \
zsh \
man-db \
unzip \
gnupg2 \
gh \
iptables \
ipset \
iproute2 \
dnsutils \
aggregate \
jq
# Ensure default node user has access to /usr/local/share
RUN mkdir -p /usr/local/share/npm-global && \
chown -R node:node /usr/local/share
ARG USERNAME=node
# Persist bash history.
RUN SNIPPET="export PROMPT_COMMAND='history -a' && export HISTFILE=/commandhistory/.bash_history" \
&& mkdir /commandhistory \
&& touch /commandhistory/.bash_history \
&& chown -R $USERNAME /commandhistory
# Set `DEVCONTAINER` environment variable to help with orientation
ENV DEVCONTAINER=true
# Create workspace and config directories and set permissions
RUN mkdir -p /workspace /home/node/.claude && \
chown -R node:node /workspace /home/node/.claude
WORKDIR /workspace
RUN ARCH=$(dpkg --print-architecture) && \
wget "https://github.com/dandavison/delta/releases/download/0.18.2/git-delta_0.18.2_${ARCH}.deb" && \
sudo dpkg -i "git-delta_0.18.2_${ARCH}.deb" && \
rm "git-delta_0.18.2_${ARCH}.deb"
# Set up non-root user
USER node
# Install global packages
ENV NPM_CONFIG_PREFIX=/usr/local/share/npm-global
ENV PATH=$PATH:/usr/local/share/npm-global/bin
# Set the default shell to zsh rather than sh
ENV SHELL=/bin/zsh
# Default powerline10k theme
RUN sh -c "$(wget -O- https://github.com/deluan/zsh-in-docker/releases/download/v1.2.0/zsh-in-docker.sh)" -- \
-p git \
-p fzf \
-a "source /usr/share/doc/fzf/examples/key-bindings.zsh" \
-a "source /usr/share/doc/fzf/examples/completion.zsh" \
-a "export PROMPT_COMMAND='history -a' && export HISTFILE=/commandhistory/.bash_history" \
-x
# Install Claude
RUN npm install -g @anthropic-ai/claude-code
# Copy and set up firewall script
COPY init-firewall.sh /usr/local/bin/
USER root
RUN chmod +x /usr/local/bin/init-firewall.sh && \
echo "node ALL=(root) NOPASSWD: /usr/local/bin/init-firewall.sh" > /etc/sudoers.d/node-firewall && \
chmod 0440 /etc/sudoers.d/node-firewall
USER node
```
--------------------------------------------------------------------------------
/internal/tools/excel_copy_sheet.go:
--------------------------------------------------------------------------------
```go
package tools
import (
"context"
"fmt"
"html"
z "github.com/Oudwins/zog"
"github.com/mark3labs/mcp-go/mcp"
"github.com/mark3labs/mcp-go/server"
excel "github.com/negokaz/excel-mcp-server/internal/excel"
imcp "github.com/negokaz/excel-mcp-server/internal/mcp"
)
type ExcelCopySheetArguments struct {
FileAbsolutePath string `zog:"fileAbsolutePath"`
SrcSheetName string `zog:"srcSheetName"`
DstSheetName string `zog:"dstSheetName"`
}
var excelCopySheetArgumentsSchema = z.Struct(z.Shape{
"fileAbsolutePath": z.String().Required(),
"srcSheetName": z.String().Required(),
"dstSheetName": z.String().Required(),
})
func AddExcelCopySheetTool(server *server.MCPServer) {
server.AddTool(mcp.NewTool("excel_copy_sheet",
mcp.WithDescription("Copy existing sheet to a new sheet"),
mcp.WithString("fileAbsolutePath",
mcp.Required(),
mcp.Description("Absolute path to the Excel file"),
),
mcp.WithString("srcSheetName",
mcp.Required(),
mcp.Description("Source sheet name in the Excel file"),
),
mcp.WithString("dstSheetName",
mcp.Required(),
mcp.Description("Sheet name to be copied"),
),
), handleCopySheet)
}
func handleCopySheet(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
args := ExcelCopySheetArguments{}
if issues := excelCopySheetArgumentsSchema.Parse(request.Params.Arguments, &args); len(issues) != 0 {
return imcp.NewToolResultZogIssueMap(issues), nil
}
return copySheet(args.FileAbsolutePath, args.SrcSheetName, args.DstSheetName)
}
func copySheet(fileAbsolutePath string, srcSheetName string, dstSheetName string) (*mcp.CallToolResult, error) {
workbook, release, err := excel.OpenFile(fileAbsolutePath)
if err != nil {
return nil, err
}
defer release()
srcSheet, err := workbook.FindSheet(srcSheetName)
if err != nil {
return imcp.NewToolResultInvalidArgumentError(err.Error()), nil
}
defer srcSheet.Release()
srcSheetName, err = srcSheet.Name()
if err != nil {
return nil, err
}
if err := workbook.CopySheet(srcSheetName, dstSheetName); err != nil {
return nil, err
}
if err := workbook.Save(); err != nil {
return nil, err
}
result := "# Notice\n"
result += fmt.Sprintf("backend: %s\n", workbook.GetBackendName())
result += fmt.Sprintf("Sheet [%s] copied to [%s].\n", html.EscapeString(srcSheetName), html.EscapeString(dstSheetName))
return mcp.NewToolResultText(result), nil
}
```
--------------------------------------------------------------------------------
/internal/tools/excel_create_table.go:
--------------------------------------------------------------------------------
```go
package tools
import (
"context"
"fmt"
"html"
z "github.com/Oudwins/zog"
"github.com/mark3labs/mcp-go/mcp"
"github.com/mark3labs/mcp-go/server"
excel "github.com/negokaz/excel-mcp-server/internal/excel"
imcp "github.com/negokaz/excel-mcp-server/internal/mcp"
)
type ExcelCreateTableArguments struct {
FileAbsolutePath string `zog:"fileAbsolutePath"`
SheetName string `zog:"sheetName"`
Range string `zog:"range"`
TableName string `zog:"tableName"`
}
var excelCreateTableArgumentsSchema = z.Struct(z.Shape{
"fileAbsolutePath": z.String().Test(AbsolutePathTest()).Required(),
"sheetName": z.String().Required(),
"range": z.String(),
"tableName": z.String().Required(),
})
func AddExcelCreateTableTool(server *server.MCPServer) {
server.AddTool(mcp.NewTool("excel_create_table",
mcp.WithDescription("Create a table in the Excel sheet"),
mcp.WithString("fileAbsolutePath",
mcp.Required(),
mcp.Description("Absolute path to the Excel file"),
),
mcp.WithString("sheetName",
mcp.Required(),
mcp.Description("Sheet name where the table is created"),
),
mcp.WithString("range",
mcp.Description("Range to be a table (e.g., \"A1:C10\")"),
),
mcp.WithString("tableName",
mcp.Required(),
mcp.Description("Table name to be created"),
),
), handleCreateTable)
}
func handleCreateTable(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
args := ExcelCreateTableArguments{}
if issues := excelCreateTableArgumentsSchema.Parse(request.Params.Arguments, &args); len(issues) != 0 {
return imcp.NewToolResultZogIssueMap(issues), nil
}
return createTable(args.FileAbsolutePath, args.SheetName, args.Range, args.TableName)
}
func createTable(fileAbsolutePath string, sheetName string, tableRange string, tableName string) (*mcp.CallToolResult, error) {
workbook, release, err := excel.OpenFile(fileAbsolutePath)
if err != nil {
return nil, err
}
defer release()
worksheet, err := workbook.FindSheet(sheetName)
if err != nil {
return imcp.NewToolResultInvalidArgumentError(err.Error()), nil
}
defer worksheet.Release()
if err := worksheet.AddTable(tableRange, tableName); err != nil {
return nil, err
}
if err := workbook.Save(); err != nil {
return nil, err
}
result := "# Notice\n"
result += fmt.Sprintf("backend: %s\n", workbook.GetBackendName())
result += fmt.Sprintf("Table [%s] created.\n", html.EscapeString(tableName))
return mcp.NewToolResultText(result), nil
}
```
--------------------------------------------------------------------------------
/internal/tools/excel_describe_sheets.go:
--------------------------------------------------------------------------------
```go
package tools
import (
"context"
"encoding/json"
z "github.com/Oudwins/zog"
"github.com/mark3labs/mcp-go/mcp"
"github.com/mark3labs/mcp-go/server"
"github.com/negokaz/excel-mcp-server/internal/excel"
imcp "github.com/negokaz/excel-mcp-server/internal/mcp"
)
type ExcelDescribeSheetsArguments struct {
FileAbsolutePath string `zog:"fileAbsolutePath"`
}
var excelDescribeSheetsArgumentsSchema = z.Struct(z.Shape{
"fileAbsolutePath": z.String().Test(AbsolutePathTest()).Required(),
})
func AddExcelDescribeSheetsTool(server *server.MCPServer) {
server.AddTool(mcp.NewTool("excel_describe_sheets",
mcp.WithDescription("List all sheet information of specified Excel file"),
mcp.WithString("fileAbsolutePath",
mcp.Required(),
mcp.Description("Absolute path to the Excel file"),
),
), handleDescribeSheets)
}
func handleDescribeSheets(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
args := ExcelDescribeSheetsArguments{}
issues := excelDescribeSheetsArgumentsSchema.Parse(request.Params.Arguments, &args)
if len(issues) != 0 {
return imcp.NewToolResultZogIssueMap(issues), nil
}
return describeSheets(args.FileAbsolutePath)
}
type Response struct {
Backend string `json:"backend"`
Sheets []Worksheet `json:"sheets"`
}
type Worksheet struct {
Name string `json:"name"`
UsedRange string `json:"usedRange"`
Tables []Table `json:"tables"`
PivotTables []PivotTable `json:"pivotTables"`
PagingRanges []string `json:"pagingRanges"`
}
type Table struct {
Name string `json:"name"`
Range string `json:"range"`
}
type PivotTable struct {
Name string `json:"name"`
Range string `json:"range"`
}
func describeSheets(fileAbsolutePath string) (*mcp.CallToolResult, error) {
config, issues := LoadConfig()
if issues != nil {
return imcp.NewToolResultZogIssueMap(issues), nil
}
workbook, release, err := excel.OpenFile(fileAbsolutePath)
defer release()
if err != nil {
return nil, err
}
sheetList, err := workbook.GetSheets()
if err != nil {
return nil, err
}
worksheets := make([]Worksheet, len(sheetList))
for i, sheet := range sheetList {
defer sheet.Release()
name, err := sheet.Name()
if err != nil {
return nil, err
}
usedRange, err := sheet.GetDimention()
if err != nil {
return nil, err
}
tables, err := sheet.GetTables()
if err != nil {
return nil, err
}
tableList := make([]Table, len(tables))
for i, table := range tables {
tableList[i] = Table{
Name: table.Name,
Range: table.Range,
}
}
pivotTables, err := sheet.GetPivotTables()
if err != nil {
return nil, err
}
pivotTableList := make([]PivotTable, len(pivotTables))
for i, pivotTable := range pivotTables {
pivotTableList[i] = PivotTable{
Name: pivotTable.Name,
Range: pivotTable.Range,
}
}
var pagingRanges []string
strategy, err := sheet.GetPagingStrategy(config.EXCEL_MCP_PAGING_CELLS_LIMIT)
if err == nil {
pagingService := excel.NewPagingRangeService(strategy)
pagingRanges = pagingService.GetPagingRanges()
}
worksheets[i] = Worksheet{
Name: name,
UsedRange: usedRange,
Tables: tableList,
PivotTables: pivotTableList,
PagingRanges: pagingRanges,
}
}
response := Response{
Backend: workbook.GetBackendName(),
Sheets: worksheets,
}
jsonBytes, err := json.MarshalIndent(response, "", " ")
if err != nil {
return nil, err
}
return mcp.NewToolResultText(string(jsonBytes)), nil
}
```
--------------------------------------------------------------------------------
/internal/tools/excel_screen_capture.go:
--------------------------------------------------------------------------------
```go
package tools
import (
"context"
"fmt"
z "github.com/Oudwins/zog"
"github.com/mark3labs/mcp-go/mcp"
"github.com/mark3labs/mcp-go/server"
"github.com/negokaz/excel-mcp-server/internal/excel"
imcp "github.com/negokaz/excel-mcp-server/internal/mcp"
)
type ExcelScreenCaptureArguments struct {
FileAbsolutePath string `zog:"fileAbsolutePath"`
SheetName string `zog:"sheetName"`
Range string `zog:"range"`
}
var ExcelScreenCaptureArgumentsSchema = z.Struct(z.Shape{
"fileAbsolutePath": z.String().Test(AbsolutePathTest()).Required(),
"sheetName": z.String().Required(),
"range": z.String(),
})
func AddExcelScreenCaptureTool(server *server.MCPServer) {
server.AddTool(mcp.NewTool("excel_screen_capture",
mcp.WithDescription("[Windows only] Take a screenshot of the Excel sheet with pagination."),
mcp.WithString("fileAbsolutePath",
mcp.Required(),
mcp.Description("Absolute path to the Excel file"),
),
mcp.WithString("sheetName",
mcp.Required(),
mcp.Description("Sheet name in the Excel file"),
),
mcp.WithString("range",
mcp.Description("Range of cells to read in the Excel sheet (e.g., \"A1:C10\"). [default: first paging range]"),
),
), handleScreenCapture)
}
func handleScreenCapture(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
args := ExcelScreenCaptureArguments{}
issues := ExcelScreenCaptureArgumentsSchema.Parse(request.Params.Arguments, &args)
if len(issues) != 0 {
return imcp.NewToolResultZogIssueMap(issues), nil
}
return readSheetImage(args.FileAbsolutePath, args.SheetName, args.Range)
}
func readSheetImage(fileAbsolutePath string, sheetName string, rangeStr string) (*mcp.CallToolResult, error) {
workbook, releaseWorkbook, err := excel.NewExcelOle(fileAbsolutePath)
defer releaseWorkbook()
if err != nil {
workbook, releaseWorkbook, err = excel.NewExcelOleWithNewObject(fileAbsolutePath)
defer releaseWorkbook()
if err != nil {
return imcp.NewToolResultInvalidArgumentError(fmt.Errorf("failed to open workbook: %w", err).Error()), nil
}
}
worksheet, err := workbook.FindSheet(sheetName)
if err != nil {
return imcp.NewToolResultInvalidArgumentError(err.Error()), nil
}
defer worksheet.Release()
pagingStrategy, err := worksheet.GetPagingStrategy(5000)
if err != nil {
return nil, err
}
pagingService := excel.NewPagingRangeService(pagingStrategy)
allRanges := pagingService.GetPagingRanges()
if len(allRanges) == 0 {
return imcp.NewToolResultInvalidArgumentError("no range available to read"), nil
}
var currentRange string
if rangeStr == "" && len(allRanges) > 0 {
// range が指定されていない場合は最初の Range を使用
currentRange = allRanges[0]
} else {
// range が指定されている場合は指定された範囲を使用
currentRange = rangeStr
}
// Find next paging range if current range matches a paging range
nextRange := pagingService.FindNextRange(allRanges, currentRange)
base64image, err := worksheet.CapturePicture(currentRange)
if err != nil {
return nil, fmt.Errorf("failed to copy range to image: %w", err)
}
text := "# Metadata\n"
text += fmt.Sprintf("- backend: %s\n", workbook.GetBackendName())
text += fmt.Sprintf("- sheet name: %s\n", sheetName)
text += fmt.Sprintf("- read range: %s\n", currentRange)
text += "# Notice\n"
if nextRange != "" {
text += "This sheet has more ranges.\n"
text += "To read the next range, you should specify 'range' argument as follows.\n"
text += fmt.Sprintf("`{ \"range\": \"%s\" }`", nextRange)
} else {
text += "This is the last range or no more ranges available.\n"
}
// 結果を返却
return mcp.NewToolResultImage(
text,
base64image,
"image/png",
), nil
}
```
--------------------------------------------------------------------------------
/.devcontainer/init-firewall.sh:
--------------------------------------------------------------------------------
```bash
#!/bin/bash
set -euo pipefail # Exit on error, undefined vars, and pipeline failures
IFS=$'\n\t' # Stricter word splitting
# Flush existing rules and delete existing ipsets
iptables -F
iptables -X
iptables -t nat -F
iptables -t nat -X
iptables -t mangle -F
iptables -t mangle -X
ipset destroy allowed-domains 2>/dev/null || true
# First allow DNS and localhost before any restrictions
# Allow outbound DNS
iptables -A OUTPUT -p udp --dport 53 -j ACCEPT
# Allow inbound DNS responses
iptables -A INPUT -p udp --sport 53 -j ACCEPT
# Allow outbound SSH
iptables -A OUTPUT -p tcp --dport 22 -j ACCEPT
# Allow inbound SSH responses
iptables -A INPUT -p tcp --sport 22 -m state --state ESTABLISHED -j ACCEPT
# Allow localhost
iptables -A INPUT -i lo -j ACCEPT
iptables -A OUTPUT -o lo -j ACCEPT
# Create ipset with CIDR support
ipset create allowed-domains hash:net
# Fetch GitHub meta information and aggregate + add their IP ranges
echo "Fetching GitHub IP ranges..."
gh_ranges=$(curl -s https://api.github.com/meta)
if [ -z "$gh_ranges" ]; then
echo "ERROR: Failed to fetch GitHub IP ranges"
exit 1
fi
if ! echo "$gh_ranges" | jq -e '.web and .api and .git' >/dev/null; then
echo "ERROR: GitHub API response missing required fields"
exit 1
fi
echo "Processing GitHub IPs..."
while read -r cidr; do
if [[ ! "$cidr" =~ ^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}/[0-9]{1,2}$ ]]; then
echo "ERROR: Invalid CIDR range from GitHub meta: $cidr"
exit 1
fi
echo "Adding GitHub range $cidr"
ipset add allowed-domains "$cidr"
done < <(echo "$gh_ranges" | jq -r '(.web + .api + .git)[]' | aggregate -q)
# Resolve and add other allowed domains
for domain in \
"registry.npmjs.org" \
"api.anthropic.com" \
"sentry.io" \
"statsig.anthropic.com" \
"statsig.com"; do
echo "Resolving $domain..."
ips=$(dig +short A "$domain")
if [ -z "$ips" ]; then
echo "ERROR: Failed to resolve $domain"
exit 1
fi
while read -r ip; do
if [[ ! "$ip" =~ ^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$ ]]; then
echo "ERROR: Invalid IP from DNS for $domain: $ip"
exit 1
fi
echo "Adding $ip for $domain"
ipset add allowed-domains "$ip"
done < <(echo "$ips")
done
# Get host IP from default route
HOST_IP=$(ip route | grep default | cut -d" " -f3)
if [ -z "$HOST_IP" ]; then
echo "ERROR: Failed to detect host IP"
exit 1
fi
HOST_NETWORK=$(echo "$HOST_IP" | sed "s/\.[0-9]*$/.0\/24/")
echo "Host network detected as: $HOST_NETWORK"
# Set up remaining iptables rules
iptables -A INPUT -s "$HOST_NETWORK" -j ACCEPT
iptables -A OUTPUT -d "$HOST_NETWORK" -j ACCEPT
# Set default policies to DROP first
iptables -P INPUT DROP
iptables -P FORWARD DROP
iptables -P OUTPUT DROP
# First allow established connections for already approved traffic
iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
iptables -A OUTPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
# Then allow only specific outbound traffic to allowed domains
iptables -A OUTPUT -m set --match-set allowed-domains dst -j ACCEPT
echo "Firewall configuration complete"
echo "Verifying firewall rules..."
if curl --connect-timeout 5 https://example.com >/dev/null 2>&1; then
echo "ERROR: Firewall verification failed - was able to reach https://example.com"
exit 1
else
echo "Firewall verification passed - unable to reach https://example.com as expected"
fi
# Verify GitHub API access
if ! curl --connect-timeout 5 https://api.github.com/zen >/dev/null 2>&1; then
echo "ERROR: Firewall verification failed - unable to reach https://api.github.com"
exit 1
else
echo "Firewall verification passed - able to reach https://api.github.com as expected"
fi
```
--------------------------------------------------------------------------------
/internal/tools/excel_write_to_sheet.go:
--------------------------------------------------------------------------------
```go
package tools
import (
"context"
"fmt"
z "github.com/Oudwins/zog"
"github.com/mark3labs/mcp-go/mcp"
"github.com/mark3labs/mcp-go/server"
"github.com/negokaz/excel-mcp-server/internal/excel"
imcp "github.com/negokaz/excel-mcp-server/internal/mcp"
"github.com/xuri/excelize/v2"
)
type ExcelWriteToSheetArguments struct {
FileAbsolutePath string `zog:"fileAbsolutePath"`
SheetName string `zog:"sheetName"`
NewSheet bool `zog:"newSheet"`
Range string `zog:"range"`
Values [][]string `zog:"values"`
}
var excelWriteToSheetArgumentsSchema = z.Struct(z.Shape{
"fileAbsolutePath": z.String().Test(AbsolutePathTest()).Required(),
"sheetName": z.String().Required(),
"newSheet": z.Bool().Required().Default(false),
"range": z.String().Required(),
"values": z.Slice(z.Slice(z.String())).Required(),
})
func AddExcelWriteToSheetTool(server *server.MCPServer) {
server.AddTool(mcp.NewTool("excel_write_to_sheet",
mcp.WithDescription("Write values to the Excel sheet"),
mcp.WithString("fileAbsolutePath",
mcp.Required(),
mcp.Description("Absolute path to the Excel file"),
),
mcp.WithString("sheetName",
mcp.Required(),
mcp.Description("Sheet name in the Excel file"),
),
mcp.WithBoolean("newSheet",
mcp.Required(),
mcp.Description("Create a new sheet if true, otherwise write to the existing sheet"),
),
mcp.WithString("range",
mcp.Required(),
mcp.Description("Range of cells in the Excel sheet (e.g., \"A1:C10\")"),
),
mcp.WithArray("values",
mcp.Required(),
mcp.Description("Values to write to the Excel sheet. If the value is a formula, it should start with \"=\""),
mcp.Items(map[string]any{
"type": "array",
"items": map[string]any{
"anyOf": []any{
map[string]any{
"type": "string",
},
map[string]any{
"type": "number",
},
map[string]any{
"type": "boolean",
},
map[string]any{
"type": "null",
},
},
},
}),
),
), handleWriteToSheet)
}
func handleWriteToSheet(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
args := ExcelWriteToSheetArguments{}
issues := excelWriteToSheetArgumentsSchema.Parse(request.Params.Arguments, &args)
if len(issues) != 0 {
return imcp.NewToolResultZogIssueMap(issues), nil
}
// zog が any type のスキーマをサポートしていないため、自力で実装
valuesArg, ok := request.GetArguments()["values"].([]any)
if !ok {
return imcp.NewToolResultInvalidArgumentError("values must be a 2D array"), nil
}
values := make([][]any, len(valuesArg))
for i, v := range valuesArg {
value, ok := v.([]any)
if !ok {
return imcp.NewToolResultInvalidArgumentError("values must be a 2D array"), nil
}
values[i] = value
}
return writeSheet(args.FileAbsolutePath, args.SheetName, args.NewSheet, args.Range, values)
}
func writeSheet(fileAbsolutePath string, sheetName string, newSheet bool, rangeStr string, values [][]any) (*mcp.CallToolResult, error) {
workbook, closeFn, err := excel.OpenFile(fileAbsolutePath)
if err != nil {
return nil, err
}
defer closeFn()
startCol, startRow, endCol, endRow, err := excel.ParseRange(rangeStr)
if err != nil {
return imcp.NewToolResultInvalidArgumentError(err.Error()), nil
}
// データの整合性チェック
rangeRowSize := endRow - startRow + 1
if len(values) != rangeRowSize {
return imcp.NewToolResultInvalidArgumentError(fmt.Sprintf("number of rows in data (%d) does not match range size (%d)", len(values), rangeRowSize)), nil
}
if newSheet {
if err := workbook.CreateNewSheet(sheetName); err != nil {
return nil, err
}
}
// シートの取得
worksheet, err := workbook.FindSheet(sheetName)
if err != nil {
return imcp.NewToolResultInvalidArgumentError(err.Error()), nil
}
defer worksheet.Release()
// データの書き込み
wroteFormula := false
for i, row := range values {
rangeColumnSize := endCol - startCol + 1
if len(row) != rangeColumnSize {
return imcp.NewToolResultInvalidArgumentError(fmt.Sprintf("number of columns in row %d (%d) does not match range size (%d)", i, len(row), rangeColumnSize)), nil
}
for j, cellValue := range row {
cell, err := excelize.CoordinatesToCellName(startCol+j, startRow+i)
if err != nil {
return nil, err
}
if cellStr, ok := cellValue.(string); ok && isFormula(cellStr) {
// if cellValue is formula, set it as formula
err = worksheet.SetFormula(cell, cellStr)
wroteFormula = true
} else {
// if cellValue is not formula, set it as value
err = worksheet.SetValue(cell, cellValue)
}
if err != nil {
return nil, err
}
}
}
if err := workbook.Save(); err != nil {
return nil, err
}
// HTMLテーブルの生成
var table *string
if wroteFormula {
table, err = CreateHTMLTableOfFormula(worksheet, startCol, startRow, endCol, endRow)
} else {
table, err = CreateHTMLTableOfValues(worksheet, startCol, startRow, endCol, endRow)
}
if err != nil {
return nil, err
}
html := "<h2>Written Sheet</h2>\n"
html += *table + "\n"
html += "<h2>Metadata</h2>\n"
html += "<ul>\n"
html += fmt.Sprintf("<li>backend: %s</li>\n", workbook.GetBackendName())
html += fmt.Sprintf("<li>sheet name: %s</li>\n", sheetName)
html += fmt.Sprintf("<li>read range: %s</li>\n", rangeStr)
html += "</ul>\n"
html += "<h2>Notice</h2>\n"
html += "<p>Values wrote successfully.</p>\n"
return mcp.NewToolResultText(html), nil
}
func isFormula(value string) bool {
return len(value) > 0 && value[0] == '='
}
```
--------------------------------------------------------------------------------
/internal/tools/excel_read_sheet.go:
--------------------------------------------------------------------------------
```go
package tools
import (
"context"
"fmt"
"html"
z "github.com/Oudwins/zog"
"github.com/mark3labs/mcp-go/mcp"
"github.com/mark3labs/mcp-go/server"
excel "github.com/negokaz/excel-mcp-server/internal/excel"
imcp "github.com/negokaz/excel-mcp-server/internal/mcp"
)
type ExcelReadSheetArguments struct {
FileAbsolutePath string `zog:"fileAbsolutePath"`
SheetName string `zog:"sheetName"`
Range string `zog:"range"`
ShowFormula bool `zog:"showFormula"`
ShowStyle bool `zog:"showStyle"`
}
var excelReadSheetArgumentsSchema = z.Struct(z.Shape{
"fileAbsolutePath": z.String().Test(AbsolutePathTest()).Required(),
"sheetName": z.String().Required(),
"range": z.String(),
"showFormula": z.Bool().Default(false),
"showStyle": z.Bool().Default(false),
})
func AddExcelReadSheetTool(server *server.MCPServer) {
server.AddTool(mcp.NewTool("excel_read_sheet",
mcp.WithDescription("Read values from Excel sheet with pagination."),
mcp.WithString("fileAbsolutePath",
mcp.Required(),
mcp.Description("Absolute path to the Excel file"),
),
mcp.WithString("sheetName",
mcp.Required(),
mcp.Description("Sheet name in the Excel file"),
),
mcp.WithString("range",
mcp.Description("Range of cells to read in the Excel sheet (e.g., \"A1:C10\"). [default: first paging range]"),
),
mcp.WithBoolean("showFormula",
mcp.Description("Show formula instead of value"),
),
mcp.WithBoolean("showStyle",
mcp.Description("Show style information for cells"),
),
), handleReadSheet)
}
func handleReadSheet(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
args := ExcelReadSheetArguments{}
if issues := excelReadSheetArgumentsSchema.Parse(request.Params.Arguments, &args); len(issues) != 0 {
return imcp.NewToolResultZogIssueMap(issues), nil
}
return readSheet(args.FileAbsolutePath, args.SheetName, args.Range, args.ShowFormula, args.ShowStyle)
}
func readSheet(fileAbsolutePath string, sheetName string, valueRange string, showFormula bool, showStyle bool) (*mcp.CallToolResult, error) {
config, issues := LoadConfig()
if issues != nil {
return imcp.NewToolResultZogIssueMap(issues), nil
}
workbook, release, err := excel.OpenFile(fileAbsolutePath)
if err != nil {
return nil, err
}
defer release()
worksheet, err := workbook.FindSheet(sheetName)
if err != nil {
return imcp.NewToolResultInvalidArgumentError(err.Error()), nil
}
defer worksheet.Release()
// ページング戦略の初期化
strategy, err := worksheet.GetPagingStrategy(config.EXCEL_MCP_PAGING_CELLS_LIMIT)
if err != nil {
return nil, err
}
pagingService := excel.NewPagingRangeService(strategy)
// 利用可能な範囲を取得
allRanges := pagingService.GetPagingRanges()
if len(allRanges) == 0 {
return imcp.NewToolResultInvalidArgumentError("no range available to read"), nil
}
// 現在の範囲を決定
currentRange := valueRange
if currentRange == "" && len(allRanges) > 0 {
currentRange = allRanges[0]
}
// Find next paging range if current range matches a paging range
nextRange := pagingService.FindNextRange(allRanges, currentRange)
// Validate the current range against the used range
usedRange, err := worksheet.GetDimention()
if err != nil {
return nil, err
}
if err := validateRangeWithinUsedRange(currentRange, usedRange); err != nil {
return imcp.NewToolResultInvalidArgumentError(err.Error()), nil
}
// 範囲を解析
startCol, startRow, endCol, endRow, err := excel.ParseRange(currentRange)
if err != nil {
return nil, err
}
// HTMLテーブルの生成
var table *string
if showStyle {
if showFormula {
table, err = CreateHTMLTableOfFormulaWithStyle(worksheet, startCol, startRow, endCol, endRow)
} else {
table, err = CreateHTMLTableOfValuesWithStyle(worksheet, startCol, startRow, endCol, endRow)
}
} else {
if showFormula {
table, err = CreateHTMLTableOfFormula(worksheet, startCol, startRow, endCol, endRow)
} else {
table, err = CreateHTMLTableOfValues(worksheet, startCol, startRow, endCol, endRow)
}
}
if err != nil {
return nil, err
}
result := "<h2>Read Sheet</h2>\n"
result += *table + "\n"
result += "<h2>Metadata</h2>\n"
result += "<ul>\n"
result += fmt.Sprintf("<li>backend: %s</li>\n", workbook.GetBackendName())
result += fmt.Sprintf("<li>sheet name: %s</li>\n", html.EscapeString(sheetName))
result += fmt.Sprintf("<li>read range: %s</li>\n", currentRange)
result += "</ul>\n"
result += "<h2>Notice</h2>\n"
if nextRange != "" {
result += "<p>This sheet has more ranges.</p>\n"
result += "<p>To read the next range, you should specify 'range' argument as follows.</p>\n"
result += fmt.Sprintf("<code>{ \"range\": \"%s\" }</code>\n", nextRange)
} else {
result += "<p>This is the last range or no more ranges available.</p>\n"
}
return mcp.NewToolResultText(result), nil
}
func validateRangeWithinUsedRange(targetRange, usedRange string) error {
// Parse target range
targetStartCol, targetStartRow, targetEndCol, targetEndRow, err := excel.ParseRange(targetRange)
if err != nil {
return fmt.Errorf("failed to parse target range: %w", err)
}
// Parse used range
usedStartCol, usedStartRow, usedEndCol, usedEndRow, err := excel.ParseRange(usedRange)
if err != nil {
return fmt.Errorf("failed to parse used range: %w", err)
}
// Check if target range is within used range
if targetStartCol < usedStartCol || targetStartRow < usedStartRow ||
targetEndCol > usedEndCol || targetEndRow > usedEndRow {
return fmt.Errorf("range is outside of used range: %s is not within %s", targetRange, usedRange)
}
return nil
}
```
--------------------------------------------------------------------------------
/docs/design/excel-style-schema.md:
--------------------------------------------------------------------------------
```markdown
# MCP Excel Style Structure Definition
This document presents JsonSchema definitions for exchanging Excel styles through MCP (Model Context Protocol), based on the Excelize library's style API.
## Target Style Elements
- Border
- Font
- Fill
- NumFmt (Number Format)
- DecimalPlaces
## JsonSchema Definition
### ExcelStyle Structure
```json
{
"$schema": "http://json-schema.org/draft-07/schema#",
"type": "object",
"title": "ExcelStyle",
"description": "Excel cell style configuration",
"properties": {
"border": {
"type": "array",
"description": "Border configuration for cell edges",
"items": {
"$ref": "#/definitions/Border"
}
},
"font": {
"$ref": "#/definitions/Font",
"description": "Font configuration"
},
"fill": {
"$ref": "#/definitions/Fill",
"description": "Fill pattern and color configuration"
},
"numFmt": {
"type": "string",
"description": "Custom number format string",
"example": "#,##0.00"
},
"decimalPlaces": {
"type": "integer",
"description": "Number of decimal places (0-30)",
"minimum": 0,
"maximum": 30
}
},
"definitions": {
"Border": {
"type": "object",
"description": "Border style configuration",
"properties": {
"type": {
"type": "string",
"description": "Border position",
"enum": ["left", "right", "top", "bottom", "diagonalDown", "diagonalUp"]
},
"color": {
"type": "string",
"description": "Border color in hex format",
"pattern": "^#[0-9A-Fa-f]{6}$",
"example": "#000000"
},
"style": {
"type": "string",
"description": "Border style",
"enum": ["none", "continuous", "dash", "dashDot", "dashDotDot", "dot", "double", "hair", "medium", "mediumDash", "mediumDashDot", "mediumDashDotDot", "slantDashDot", "thick"]
}
},
"required": ["type"],
"additionalProperties": false
},
"Font": {
"type": "object",
"description": "Font style configuration",
"properties": {
"bold": {
"type": "boolean",
"description": "Bold text"
},
"italic": {
"type": "boolean",
"description": "Italic text"
},
"underline": {
"type": "string",
"description": "Underline style",
"enum": ["none", "single", "double", "singleAccounting", "doubleAccounting"]
},
"size": {
"type": "number",
"description": "Font size in points",
"minimum": 1,
"maximum": 409
},
"strike": {
"type": "boolean",
"description": "Strikethrough text"
},
"color": {
"type": "string",
"description": "Font color in hex format",
"pattern": "^#[0-9A-Fa-f]{6}$",
"example": "#000000"
},
"vertAlign": {
"type": "string",
"description": "Vertical alignment",
"enum": ["baseline", "superscript", "subscript"]
}
},
"additionalProperties": false
},
"Fill": {
"type": "object",
"description": "Fill pattern and color configuration",
"properties": {
"type": {
"type": "string",
"description": "Fill type",
"enum": ["gradient", "pattern"]
},
"pattern": {
"type": "string",
"description": "Pattern style",
"enum": ["none", "solid", "mediumGray", "darkGray", "lightGray", "darkHorizontal", "darkVertical", "darkDown", "darkUp", "darkGrid", "darkTrellis", "lightHorizontal", "lightVertical", "lightDown", "lightUp", "lightGrid", "lightTrellis", "gray125", "gray0625"]
},
"color": {
"type": "array",
"description": "Fill colors in hex format",
"items": {
"type": "string",
"pattern": "^#[0-9A-Fa-f]{6}$",
"example": "#FFFFFF"
}
},
"shading": {
"type": "string",
"description": "Gradient shading direction",
"enum": ["horizontal", "vertical", "diagonalDown", "diagonalUp", "fromCenter", "fromCorner"]
}
},
"additionalProperties": false
}
}
}
```
## Usage Examples
### Basic Style Configuration
```json
{
"font": {
"bold": true,
"size": 12,
"color": "#000000"
},
"fill": {
"type": "pattern",
"pattern": "solid",
"color": ["#FFFF00"]
}
}
```
### Style with Borders
```json
{
"border": [
{
"type": "top",
"style": "continuous",
"color": "#000000"
},
{
"type": "bottom",
"style": "continuous",
"color": "#000000"
}
],
"font": {
"size": 10
}
}
```
### Style with Number Format
```json
{
"numFmt": "#,##0.00",
"decimalPlaces": 2,
"font": {
"size": 11
}
}
```
## Implementation Notes
1. **Required Fields**: Only Border's `type` field is required; all others are optional
2. **Color Format**: Hexadecimal color codes (#RRGGBB format)
3. **Numeric Limits**:
- `decimalPlaces`: Range 0-30
- `border.style`: String identifiers (none, continuous, dash, etc.)
- `fill.pattern`: String identifiers (none, solid, mediumGray, etc.)
- `fill.shading`: String identifiers (horizontal, vertical, etc.)
- `font.size`: Range 1-409
4. **Testing**: After implementation, test with actual Excel files
## Correspondence with Excelize
This definition is based on the style structure of `github.com/xuri/excelize/v2 v2.9.0` and maintains compatibility with Excelize's API specifications.
```
--------------------------------------------------------------------------------
/internal/tools/excel_format_range.go:
--------------------------------------------------------------------------------
```go
package tools
import (
"context"
"fmt"
"regexp"
z "github.com/Oudwins/zog"
"github.com/mark3labs/mcp-go/mcp"
"github.com/mark3labs/mcp-go/server"
"github.com/negokaz/excel-mcp-server/internal/excel"
imcp "github.com/negokaz/excel-mcp-server/internal/mcp"
"github.com/xuri/excelize/v2"
)
type ExcelFormatRangeArguments struct {
FileAbsolutePath string `zog:"fileAbsolutePath"`
SheetName string `zog:"sheetName"`
Range string `zog:"range"`
Styles [][]*excel.CellStyle `zog:"styles"`
}
var colorPattern, _ = regexp.Compile("^#[0-9A-Fa-f]{6}$")
var excelFormatRangeArgumentsSchema = z.Struct(z.Shape{
"fileAbsolutePath": z.String().Test(AbsolutePathTest()).Required(),
"sheetName": z.String().Required(),
"range": z.String().Required(),
"styles": z.Slice(z.Slice(
z.Ptr(z.Struct(z.Shape{
"border": z.Slice(z.Struct(z.Shape{
"type": z.StringLike[excel.BorderType]().OneOf(excel.BorderTypeValues()).Required(),
"color": z.String().Match(colorPattern).Default("#000000"),
"style": z.StringLike[excel.BorderStyle]().OneOf(excel.BorderStyleValues()).Default(excel.BorderStyleContinuous),
})).Default([]excel.Border{}),
"font": z.Ptr(z.Struct(z.Shape{
"bold": z.Ptr(z.Bool()),
"italic": z.Ptr(z.Bool()),
"underline": z.Ptr(z.StringLike[excel.FontUnderline]().OneOf(excel.FontUnderlineValues())),
"size": z.Ptr(z.Int().GTE(1).LTE(409)),
"strike": z.Ptr(z.Bool()),
"color": z.Ptr(z.String().Match(colorPattern)),
"vertAlign": z.Ptr(z.StringLike[excel.FontVertAlign]().OneOf(excel.FontVertAlignValues())),
})),
"fill": z.Ptr(z.Struct(z.Shape{
"type": z.StringLike[excel.FillType]().OneOf(excel.FillTypeValues()).Default(excel.FillTypePattern),
"pattern": z.StringLike[excel.FillPattern]().OneOf(excel.FillPatternValues()).Default(excel.FillPatternSolid),
"color": z.Slice(z.String().Match(colorPattern)).Default([]string{}),
"shading": z.Ptr(z.StringLike[excel.FillShading]().OneOf(excel.FillShadingValues())),
})),
"numFmt": z.Ptr(z.String()),
"decimalPlaces": z.Ptr(z.Int().GTE(0).LTE(30)),
}),
))).Required(),
})
func AddExcelFormatRangeTool(server *server.MCPServer) {
server.AddTool(mcp.NewTool("excel_format_range",
mcp.WithDescription("Format cells in the Excel sheet with style information"),
mcp.WithString("fileAbsolutePath",
mcp.Required(),
mcp.Description("Absolute path to the Excel file"),
),
mcp.WithString("sheetName",
mcp.Required(),
mcp.Description("Sheet name in the Excel file"),
),
mcp.WithString("range",
mcp.Required(),
mcp.Description("Range of cells in the Excel sheet (e.g., \"A1:C3\")"),
),
mcp.WithArray("styles",
mcp.Required(),
mcp.Description("2D array of style objects for each cell. If a cell does not change style, use null. The number of items of the array must match the range size."),
mcp.Items(map[string]any{
"type": "array",
"items": map[string]any{
"anyOf": []any{
map[string]any{
"type": "object",
"description": "Style object for the cell",
"properties": map[string]any{
"border": map[string]any{
"type": "array",
"items": map[string]any{
"type": "object",
"properties": map[string]any{
"type": map[string]any{
"type": "string",
"enum": excel.BorderTypeValues(),
},
"color": map[string]any{
"type": "string",
"pattern": colorPattern.String(),
},
"style": map[string]any{
"type": "string",
"enum": excel.BorderStyleValues(),
},
},
"required": []string{"type"},
},
},
"font": map[string]any{
"type": "object",
"properties": map[string]any{
"bold": map[string]any{"type": "boolean"},
"italic": map[string]any{"type": "boolean"},
"underline": map[string]any{
"type": "string",
"enum": excel.FontUnderlineValues(),
},
"size": map[string]any{
"type": "number",
"minimum": 1,
"maximum": 409,
},
"strike": map[string]any{"type": "boolean"},
"color": map[string]any{
"type": "string",
"pattern": colorPattern.String(),
},
"vertAlign": map[string]any{
"type": "string",
"enum": excel.FontVertAlignValues(),
},
},
},
"fill": map[string]any{
"type": "object",
"properties": map[string]any{
"type": map[string]any{
"type": "string",
"enum": []string{"gradient", "pattern"},
},
"pattern": map[string]any{
"type": "string",
"enum": excel.FillPatternValues(),
},
"color": map[string]any{
"type": "array",
"items": map[string]any{
"type": "string",
"pattern": colorPattern.String(),
},
},
"shading": map[string]any{
"type": "string",
"enum": excel.FillShadingValues(),
},
},
"required": []string{"type", "pattern", "color"},
},
"numFmt": map[string]any{
"type": "string",
"description": "Custom number format string",
},
"decimalPlaces": map[string]any{
"type": "integer",
"minimum": 0,
"maximum": 30,
},
},
},
map[string]any{
"type": "null",
"description": "No style applied to this cell",
},
},
},
}),
),
), handleFormatRange)
}
func handleFormatRange(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
args := ExcelFormatRangeArguments{}
issues := excelFormatRangeArgumentsSchema.Parse(request.Params.Arguments, &args)
if len(issues) != 0 {
return imcp.NewToolResultZogIssueMap(issues), nil
}
return formatRange(args.FileAbsolutePath, args.SheetName, args.Range, args.Styles)
}
func formatRange(fileAbsolutePath string, sheetName string, rangeStr string, styles [][]*excel.CellStyle) (*mcp.CallToolResult, error) {
workbook, closeFn, err := excel.OpenFile(fileAbsolutePath)
if err != nil {
return nil, err
}
defer closeFn()
startCol, startRow, endCol, endRow, err := excel.ParseRange(rangeStr)
if err != nil {
return imcp.NewToolResultInvalidArgumentError(err.Error()), nil
}
// Check data consistency
rangeRowSize := endRow - startRow + 1
if len(styles) != rangeRowSize {
return imcp.NewToolResultInvalidArgumentError(fmt.Sprintf("number of style rows (%d) does not match range size (%d)", len(styles), rangeRowSize)), nil
}
// Get worksheet
worksheet, err := workbook.FindSheet(sheetName)
if err != nil {
return imcp.NewToolResultInvalidArgumentError(err.Error()), nil
}
defer worksheet.Release()
// Apply styles to each cell
for i, styleRow := range styles {
rangeColumnSize := endCol - startCol + 1
if len(styleRow) != rangeColumnSize {
return imcp.NewToolResultInvalidArgumentError(fmt.Sprintf("number of style columns in row %d (%d) does not match range size (%d)", i, len(styleRow), rangeColumnSize)), nil
}
for j, style := range styleRow {
cell, err := excelize.CoordinatesToCellName(startCol+j, startRow+i)
if err != nil {
return nil, err
}
if style != nil {
if err := worksheet.SetCellStyle(cell, style); err != nil {
return nil, fmt.Errorf("failed to set style for cell %s: %w", cell, err)
}
}
}
}
if err := workbook.Save(); err != nil {
return nil, err
}
// Create response HTML
html := "<h2>Formatted Range</h2>\n"
html += fmt.Sprintf("<p>Successfully applied styles to range %s in sheet %s</p>\n", rangeStr, sheetName)
html += "<h2>Metadata</h2>\n"
html += "<ul>\n"
html += fmt.Sprintf("<li>backend: %s</li>\n", workbook.GetBackendName())
html += fmt.Sprintf("<li>sheet name: %s</li>\n", sheetName)
html += fmt.Sprintf("<li>formatted range: %s</li>\n", rangeStr)
html += fmt.Sprintf("<li>cells processed: %d</li>\n", (endRow-startRow+1)*(endCol-startCol+1))
html += "</ul>\n"
html += "<h2>Notice</h2>\n"
html += "<p>Cell styles applied successfully.</p>\n"
return mcp.NewToolResultText(html), nil
}
```
--------------------------------------------------------------------------------
/internal/excel/excel.go:
--------------------------------------------------------------------------------
```go
package excel
import (
"github.com/xuri/excelize/v2"
)
type Excel interface {
// GetBackendName returns the backend used to manipulate the Excel file.
GetBackendName() string
// GetSheets returns a list of all worksheets in the Excel file.
GetSheets() ([]Worksheet, error)
// FindSheet finds a sheet by its name and returns a Worksheet.
FindSheet(sheetName string) (Worksheet, error)
// CreateNewSheet creates a new sheet with the specified name.
CreateNewSheet(sheetName string) error
// CopySheet copies a sheet from one to another.
CopySheet(srcSheetName, destSheetName string) error
// Save saves the Excel file.
Save() error
}
type Worksheet interface {
// Release releases the worksheet resources.
Release()
// Name returns the name of the worksheet.
Name() (string, error)
// GetTable returns a tables in this worksheet.
GetTables() ([]Table, error)
// GetPivotTable returns a pivot tables in this worksheet.
GetPivotTables() ([]PivotTable, error)
// SetValue sets a value in the specified cell.
SetValue(cell string, value any) error
// SetFormula sets a formula in the specified cell.
SetFormula(cell string, formula string) error
// GetValue gets the value from the specified cell.
GetValue(cell string) (string, error)
// GetFormula gets the formula from the specified cell.
GetFormula(cell string) (string, error)
// GetDimention gets the dimension of the worksheet.
GetDimention() (string, error)
// GetPagingStrategy returns the paging strategy for the worksheet.
// The pageSize parameter is used to determine the max size of each page.
GetPagingStrategy(pageSize int) (PagingStrategy, error)
// CapturePicture returns base64 encoded image data of the specified range.
CapturePicture(captureRange string) (string, error)
// AddTable adds a table to this worksheet.
AddTable(tableRange, tableName string) error
// GetCellStyle gets style information for the specified cell.
GetCellStyle(cell string) (*CellStyle, error)
// SetCellStyle sets style for the specified cell.
SetCellStyle(cell string, style *CellStyle) error
}
type Table struct {
Name string
Range string
}
type PivotTable struct {
Name string
Range string
}
type CellStyle struct {
Border []Border `yaml:"border,omitempty"`
Font *FontStyle `yaml:"font,omitempty"`
Fill *FillStyle `yaml:"fill,omitempty"`
NumFmt *string `yaml:"numFmt,omitempty"`
DecimalPlaces *int `yaml:"decimalPlaces,omitempty"`
}
type Border struct {
Type BorderType `yaml:"type"`
Style BorderStyle `yaml:"style,omitempty"`
Color string `yaml:"color,omitempty"`
}
type FontStyle struct {
Bold *bool `yaml:"bold,omitempty"`
Italic *bool `yaml:"italic,omitempty"`
Underline *FontUnderline `yaml:"underline,omitempty"`
Size *int `yaml:"size,omitempty"`
Strike *bool `yaml:"strike,omitempty"`
Color *string `yaml:"color,omitempty"`
VertAlign *FontVertAlign `yaml:"vertAlign,omitempty"`
}
type FillStyle struct {
Type FillType `yaml:"type,omitempty"`
Pattern FillPattern `yaml:"pattern,omitempty"`
Color []string `yaml:"color,omitempty"`
Shading *FillShading `yaml:"shading,omitempty"`
}
// OpenFile opens an Excel file and returns an Excel interface.
// It first tries to open the file using OLE automation, and if that fails,
// it tries to using the excelize library.
func OpenFile(absoluteFilePath string) (Excel, func(), error) {
ole, releaseFn, err := NewExcelOle(absoluteFilePath)
if err == nil {
return ole, releaseFn, nil
}
// If OLE fails, try Excelize
workbook, err := excelize.OpenFile(absoluteFilePath)
if err != nil {
return nil, func() {}, err
}
excelize := NewExcelizeExcel(workbook)
return excelize, func() {
workbook.Close()
}, nil
}
// BorderType represents border direction
type BorderType string
const (
BorderTypeLeft BorderType = "left"
BorderTypeRight BorderType = "right"
BorderTypeTop BorderType = "top"
BorderTypeBottom BorderType = "bottom"
BorderTypeDiagonalDown BorderType = "diagonalDown"
BorderTypeDiagonalUp BorderType = "diagonalUp"
)
func (b BorderType) String() string {
return string(b)
}
func (b BorderType) MarshalText() ([]byte, error) {
return []byte(b.String()), nil
}
func BorderTypeValues() []BorderType {
return []BorderType{
BorderTypeLeft,
BorderTypeRight,
BorderTypeTop,
BorderTypeBottom,
BorderTypeDiagonalDown,
BorderTypeDiagonalUp,
}
}
// BorderStyle represents border style constants
type BorderStyle string
const (
BorderStyleNone BorderStyle = "none"
BorderStyleContinuous BorderStyle = "continuous"
BorderStyleDash BorderStyle = "dash"
BorderStyleDot BorderStyle = "dot"
BorderStyleDouble BorderStyle = "double"
BorderStyleDashDot BorderStyle = "dashDot"
BorderStyleDashDotDot BorderStyle = "dashDotDot"
BorderStyleSlantDashDot BorderStyle = "slantDashDot"
BorderStyleMediumDashDot BorderStyle = "mediumDashDot"
BorderStyleMediumDashDotDot BorderStyle = "mediumDashDotDot"
)
func (b BorderStyle) String() string {
return string(b)
}
func (b BorderStyle) MarshalText() ([]byte, error) {
return []byte(b.String()), nil
}
func BorderStyleValues() []BorderStyle {
return []BorderStyle{
BorderStyleNone,
BorderStyleContinuous,
BorderStyleDash,
BorderStyleDot,
BorderStyleDouble,
BorderStyleDashDot,
BorderStyleDashDotDot,
BorderStyleSlantDashDot,
BorderStyleMediumDashDot,
BorderStyleMediumDashDotDot,
}
}
// FontUnderline represents underline styles for font
type FontUnderline string
const (
FontUnderlineNone FontUnderline = "none"
FontUnderlineSingle FontUnderline = "single"
FontUnderlineDouble FontUnderline = "double"
FontUnderlineSingleAccounting FontUnderline = "singleAccounting"
FontUnderlineDoubleAccounting FontUnderline = "doubleAccounting"
)
func (f FontUnderline) String() string {
return string(f)
}
func (f FontUnderline) MarshalText() ([]byte, error) {
return []byte(f.String()), nil
}
func FontUnderlineValues() []FontUnderline {
return []FontUnderline{
FontUnderlineNone,
FontUnderlineSingle,
FontUnderlineDouble,
FontUnderlineSingleAccounting,
FontUnderlineDoubleAccounting,
}
}
// FontVertAlign represents vertical alignment options for font styles
type FontVertAlign string
const (
FontVertAlignBaseline FontVertAlign = "baseline"
FontVertAlignSuperscript FontVertAlign = "superscript"
FontVertAlignSubscript FontVertAlign = "subscript"
)
func (v FontVertAlign) String() string {
return string(v)
}
func (v FontVertAlign) MarshalText() ([]byte, error) {
return []byte(v.String()), nil
}
func FontVertAlignValues() []FontVertAlign {
return []FontVertAlign{
FontVertAlignBaseline,
FontVertAlignSuperscript,
FontVertAlignSubscript,
}
}
// FillType represents fill types for cell styles
type FillType string
const (
FillTypeGradient FillType = "gradient"
FillTypePattern FillType = "pattern"
)
func (f FillType) String() string {
return string(f)
}
func (f FillType) MarshalText() ([]byte, error) {
return []byte(f.String()), nil
}
func FillTypeValues() []FillType {
return []FillType{
FillTypeGradient,
FillTypePattern,
}
}
// FillPattern represents fill pattern constants
type FillPattern string
const (
FillPatternNone FillPattern = "none"
FillPatternSolid FillPattern = "solid"
FillPatternMediumGray FillPattern = "mediumGray"
FillPatternDarkGray FillPattern = "darkGray"
FillPatternLightGray FillPattern = "lightGray"
FillPatternDarkHorizontal FillPattern = "darkHorizontal"
FillPatternDarkVertical FillPattern = "darkVertical"
FillPatternDarkDown FillPattern = "darkDown"
FillPatternDarkUp FillPattern = "darkUp"
FillPatternDarkGrid FillPattern = "darkGrid"
FillPatternDarkTrellis FillPattern = "darkTrellis"
FillPatternLightHorizontal FillPattern = "lightHorizontal"
FillPatternLightVertical FillPattern = "lightVertical"
FillPatternLightDown FillPattern = "lightDown"
FillPatternLightUp FillPattern = "lightUp"
FillPatternLightGrid FillPattern = "lightGrid"
FillPatternLightTrellis FillPattern = "lightTrellis"
FillPatternGray125 FillPattern = "gray125"
FillPatternGray0625 FillPattern = "gray0625"
)
func (f FillPattern) String() string {
return string(f)
}
func (f FillPattern) MarshalText() ([]byte, error) {
return []byte(f.String()), nil
}
func FillPatternValues() []FillPattern {
return []FillPattern{
FillPatternNone,
FillPatternSolid,
FillPatternMediumGray,
FillPatternDarkGray,
FillPatternLightGray,
FillPatternDarkHorizontal,
FillPatternDarkVertical,
FillPatternDarkDown,
FillPatternDarkUp,
FillPatternDarkGrid,
FillPatternDarkTrellis,
FillPatternLightHorizontal,
FillPatternLightVertical,
FillPatternLightDown,
FillPatternLightUp,
FillPatternLightGrid,
FillPatternLightTrellis,
FillPatternGray125,
FillPatternGray0625,
}
}
// FillShading represents fill shading constants
type FillShading string
const (
FillShadingHorizontal FillShading = "horizontal"
FillShadingVertical FillShading = "vertical"
FillShadingDiagonalDown FillShading = "diagonalDown"
FillShadingDiagonalUp FillShading = "diagonalUp"
FillShadingFromCenter FillShading = "fromCenter"
FillShadingFromCorner FillShading = "fromCorner"
)
func (f FillShading) String() string {
return string(f)
}
func (f FillShading) MarshalText() ([]byte, error) {
return []byte(f.String()), nil
}
func FillShadingValues() []FillShading {
return []FillShading{
FillShadingHorizontal,
FillShadingVertical,
FillShadingDiagonalDown,
FillShadingDiagonalUp,
FillShadingFromCenter,
FillShadingFromCorner,
}
}
```
--------------------------------------------------------------------------------
/internal/tools/common.go:
--------------------------------------------------------------------------------
```go
package tools
import (
"crypto/md5"
"fmt"
"html"
"path/filepath"
"slices"
"strconv"
"strings"
"github.com/goccy/go-yaml"
"github.com/xuri/excelize/v2"
"github.com/negokaz/excel-mcp-server/internal/excel"
z "github.com/Oudwins/zog"
)
type StyleRegistry struct {
// Border styles
borderStyles map[string]string // styleID -> YAML string
borderHashToID map[string]string // styleHash -> styleID
borderCounter int
// Font styles
fontStyles map[string]string // styleID -> YAML string
fontHashToID map[string]string // styleHash -> styleID
fontCounter int
// Fill styles
fillStyles map[string]string // styleID -> YAML string
fillHashToID map[string]string // styleHash -> styleID
fillCounter int
// Number format styles
numFmtStyles map[string]string // styleID -> NumFmt
numFmtHashToID map[string]string // styleHash -> styleID
numFmtCounter int
// Decimal places styles
decimalStyles map[string]string // styleID -> YAML string
decimalHashToID map[string]string // styleHash -> styleID
decimalCounter int
}
func NewStyleRegistry() *StyleRegistry {
return &StyleRegistry{
borderStyles: make(map[string]string),
borderHashToID: make(map[string]string),
borderCounter: 0,
fontStyles: make(map[string]string),
fontHashToID: make(map[string]string),
fontCounter: 0,
fillStyles: make(map[string]string),
fillHashToID: make(map[string]string),
fillCounter: 0,
numFmtStyles: make(map[string]string),
numFmtHashToID: make(map[string]string),
numFmtCounter: 0,
decimalStyles: make(map[string]string),
decimalHashToID: make(map[string]string),
decimalCounter: 0,
}
}
func (sr *StyleRegistry) RegisterStyle(cellStyle *excel.CellStyle) []string {
if cellStyle == nil || sr.isEmptyStyle(cellStyle) {
return []string{}
}
var styleIDs []string
// Register border style
if len(cellStyle.Border) > 0 {
if borderID := sr.RegisterBorderStyle(cellStyle.Border); borderID != "" {
styleIDs = append(styleIDs, borderID)
}
}
// Register font style
if cellStyle.Font != nil {
if fontID := sr.RegisterFontStyle(cellStyle.Font); fontID != "" {
styleIDs = append(styleIDs, fontID)
}
}
// Register fill style
if cellStyle.Fill != nil && cellStyle.Fill.Type != "" {
if fillID := sr.RegisterFillStyle(cellStyle.Fill); fillID != "" {
styleIDs = append(styleIDs, fillID)
}
}
// Register number format style
if cellStyle.NumFmt != nil && *cellStyle.NumFmt != "" {
if numFmtID := sr.RegisterNumFmtStyle(*cellStyle.NumFmt); numFmtID != "" {
styleIDs = append(styleIDs, numFmtID)
}
}
// Register decimal places style
if cellStyle.DecimalPlaces != nil && *cellStyle.DecimalPlaces != 0 {
if decimalID := sr.RegisterDecimalStyle(*cellStyle.DecimalPlaces); decimalID != "" {
styleIDs = append(styleIDs, decimalID)
}
}
return styleIDs
}
func (sr *StyleRegistry) isEmptyStyle(style *excel.CellStyle) bool {
if len(style.Border) > 0 || style.Font != nil || (style.NumFmt != nil && *style.NumFmt != "") || (style.DecimalPlaces != nil && *style.DecimalPlaces != 0) {
return false
}
if style.Fill != nil && style.Fill.Type != "" {
return false
}
return true
}
// calculateYamlHash calculates a hash for a YAML string
func calculateYamlHash(yaml string) string {
if yaml == "" {
return ""
}
hash := md5.Sum([]byte(yaml))
return fmt.Sprintf("%x", hash)[:8]
}
// Individual style element registration methods
func (sr *StyleRegistry) RegisterBorderStyle(borders []excel.Border) string {
if len(borders) == 0 {
return ""
}
yamlStr := convertToYAMLFlow(borders)
if yamlStr == "" {
return ""
}
styleHash := calculateYamlHash(yamlStr)
if styleHash == "" {
return ""
}
if existingID, exists := sr.borderHashToID[styleHash]; exists {
return existingID
}
sr.borderCounter++
styleID := fmt.Sprintf("b%d", sr.borderCounter)
sr.borderStyles[styleID] = yamlStr
sr.borderHashToID[styleHash] = styleID
return styleID
}
func (sr *StyleRegistry) RegisterFontStyle(font *excel.FontStyle) string {
if font == nil {
return ""
}
yamlStr := convertToYAMLFlow(font)
if yamlStr == "" {
return ""
}
styleHash := calculateYamlHash(yamlStr)
if styleHash == "" {
return ""
}
if existingID, exists := sr.fontHashToID[styleHash]; exists {
return existingID
}
sr.fontCounter++
styleID := fmt.Sprintf("f%d", sr.fontCounter)
sr.fontStyles[styleID] = yamlStr
sr.fontHashToID[styleHash] = styleID
return styleID
}
func (sr *StyleRegistry) RegisterFillStyle(fill *excel.FillStyle) string {
if fill == nil || fill.Type == "" {
return ""
}
yamlStr := convertToYAMLFlow(fill)
if yamlStr == "" {
return ""
}
styleHash := calculateYamlHash(yamlStr)
if styleHash == "" {
return ""
}
if existingID, exists := sr.fillHashToID[styleHash]; exists {
return existingID
}
sr.fillCounter++
styleID := fmt.Sprintf("l%d", sr.fillCounter)
sr.fillStyles[styleID] = yamlStr
sr.fillHashToID[styleHash] = styleID
return styleID
}
func (sr *StyleRegistry) RegisterNumFmtStyle(numFmt string) string {
if numFmt == "" {
return ""
}
styleHash := calculateYamlHash(numFmt)
if styleHash == "" {
return ""
}
if existingID, exists := sr.numFmtHashToID[styleHash]; exists {
return existingID
}
sr.numFmtCounter++
styleID := fmt.Sprintf("n%d", sr.numFmtCounter)
sr.numFmtStyles[styleID] = numFmt
sr.numFmtHashToID[styleHash] = styleID
return styleID
}
func (sr *StyleRegistry) RegisterDecimalStyle(decimal int) string {
if decimal == 0 {
return ""
}
yamlStr := convertToYAMLFlow(decimal)
if yamlStr == "" {
return ""
}
styleHash := calculateYamlHash(yamlStr)
if styleHash == "" {
return ""
}
if existingID, exists := sr.decimalHashToID[styleHash]; exists {
return existingID
}
sr.decimalCounter++
styleID := fmt.Sprintf("d%d", sr.decimalCounter)
sr.decimalStyles[styleID] = yamlStr
sr.decimalHashToID[styleHash] = styleID
return styleID
}
func (sr *StyleRegistry) GenerateStyleDefinitions() string {
totalCount := len(sr.borderStyles) + len(sr.fontStyles) + len(sr.fillStyles) + len(sr.numFmtStyles) + len(sr.decimalStyles)
if totalCount == 0 {
return ""
}
var result strings.Builder
result.WriteString("<h2>Style Definitions</h2>\n")
result.WriteString("<div class=\"style-definitions\">\n")
// Generate border style definitions
result.WriteString(sr.generateStyleDefTag(sr.borderStyles, "border"))
// Generate font style definitions
result.WriteString(sr.generateStyleDefTag(sr.fontStyles, "font"))
// Generate fill style definitions
result.WriteString(sr.generateStyleDefTag(sr.fillStyles, "fill"))
// Generate number format style definitions
result.WriteString(sr.generateStyleDefTag(sr.numFmtStyles, "numFmt"))
// Generate decimal places style definitions
result.WriteString(sr.generateStyleDefTag(sr.decimalStyles, "decimalPlaces"))
result.WriteString("</div>\n\n")
return result.String()
}
func (sr *StyleRegistry) generateStyleDefTag(styles map[string]string, styleLabel string) string {
if len(styles) == 0 {
return ""
}
var styleIDs []string
for styleID := range styles {
styleIDs = append(styleIDs, styleID)
}
sortStyleIDs(styleIDs)
var result strings.Builder
for _, styleID := range styleIDs {
yamlStr := styles[styleID]
if yamlStr != "" {
result.WriteString(fmt.Sprintf("<code class=\"style language-yaml\" id=\"%s\">%s: %s</code>\n", styleID, styleLabel, html.EscapeString(yamlStr)))
}
}
return result.String()
}
func sortStyleIDs(styleIDs []string) {
slices.SortFunc(styleIDs, func(a, b string) int {
// styleID must have number suffix after prefix
ai, _ := strconv.Atoi(a[1:])
bi, _ := strconv.Atoi(b[1:])
return ai - bi
})
}
// Common function to convert any value to YAML flow format
func convertToYAMLFlow(value any) string {
if value == nil {
return ""
}
yamlBytes, err := yaml.MarshalWithOptions(value, yaml.Flow(true), yaml.OmitEmpty())
if err != nil {
return ""
}
yamlStr := strings.TrimSpace(strings.ReplaceAll(string(yamlBytes), "\"", ""))
return yamlStr
}
func CreateHTMLTableOfValues(worksheet excel.Worksheet, startCol int, startRow int, endCol int, endRow int) (*string, error) {
return createHTMLTable(startCol, startRow, endCol, endRow, func(cellRange string) (string, error) {
return worksheet.GetValue(cellRange)
})
}
func CreateHTMLTableOfFormula(worksheet excel.Worksheet, startCol int, startRow int, endCol int, endRow int) (*string, error) {
return createHTMLTable(startCol, startRow, endCol, endRow, func(cellRange string) (string, error) {
return worksheet.GetFormula(cellRange)
})
}
// CreateHTMLTable creates a table data in HTML format
func createHTMLTable(startCol int, startRow int, endCol int, endRow int, extractor func(cellRange string) (string, error)) (*string, error) {
return createHTMLTableWithStyle(startCol, startRow, endCol, endRow, extractor, nil)
}
func CreateHTMLTableOfValuesWithStyle(worksheet excel.Worksheet, startCol int, startRow int, endCol int, endRow int) (*string, error) {
return createHTMLTableWithStyle(startCol, startRow, endCol, endRow,
func(cellRange string) (string, error) {
return worksheet.GetValue(cellRange)
},
func(cellRange string) (*excel.CellStyle, error) {
return worksheet.GetCellStyle(cellRange)
})
}
func CreateHTMLTableOfFormulaWithStyle(worksheet excel.Worksheet, startCol int, startRow int, endCol int, endRow int) (*string, error) {
return createHTMLTableWithStyle(startCol, startRow, endCol, endRow,
func(cellRange string) (string, error) {
return worksheet.GetFormula(cellRange)
},
func(cellRange string) (*excel.CellStyle, error) {
return worksheet.GetCellStyle(cellRange)
})
}
func createHTMLTableWithStyle(startCol int, startRow int, endCol int, endRow int, extractor func(cellRange string) (string, error), styleExtractor func(cellRange string) (*excel.CellStyle, error)) (*string, error) {
registry := NewStyleRegistry()
// データとスタイルを収集
var result strings.Builder
result.WriteString("<table>\n<tr><th></th>")
// 列アドレスの出力
for col := startCol; col <= endCol; col++ {
name, _ := excelize.ColumnNumberToName(col)
result.WriteString(fmt.Sprintf("<th>%s</th>", name))
}
result.WriteString("</tr>\n")
// データの出力とスタイル登録
for row := startRow; row <= endRow; row++ {
result.WriteString("<tr>")
result.WriteString(fmt.Sprintf("<th>%d</th>", row))
for col := startCol; col <= endCol; col++ {
axis, _ := excelize.CoordinatesToCellName(col, row)
value, _ := extractor(axis)
var tdTag string
if styleExtractor != nil {
cellStyle, err := styleExtractor(axis)
if err == nil && cellStyle != nil {
styleIDs := registry.RegisterStyle(cellStyle)
if len(styleIDs) > 0 {
tdTag = fmt.Sprintf("<td style-ref=\"%s\">", strings.Join(styleIDs, " "))
} else {
tdTag = "<td>"
}
} else {
tdTag = "<td>"
}
} else {
tdTag = "<td>"
}
result.WriteString(fmt.Sprintf("%s%s</td>", tdTag, strings.ReplaceAll(html.EscapeString(value), "\n", "<br>")))
}
result.WriteString("</tr>\n")
}
result.WriteString("</table>")
// スタイル定義とテーブルを結合
var finalResult strings.Builder
styleDefinitions := registry.GenerateStyleDefinitions()
if styleDefinitions != "" {
finalResult.WriteString(styleDefinitions)
}
finalResult.WriteString("<h2>Sheet Data</h2>\n")
finalResult.WriteString(result.String())
finalResultStr := finalResult.String()
return &finalResultStr, nil
}
func AbsolutePathTest() z.Test[*string] {
return z.Test[*string]{
Func: func(path *string, ctx z.Ctx) {
if !filepath.IsAbs(*path) {
ctx.AddIssue(ctx.Issue().SetMessage(fmt.Sprintf("Path '%s' is not absolute", *path)))
}
},
}
}
```
--------------------------------------------------------------------------------
/internal/excel/excel_excelize.go:
--------------------------------------------------------------------------------
```go
package excel
import (
"fmt"
"os"
"path/filepath"
"strings"
"github.com/xuri/excelize/v2"
)
type ExcelizeExcel struct {
file *excelize.File
}
func NewExcelizeExcel(file *excelize.File) Excel {
return &ExcelizeExcel{file: file}
}
func (e *ExcelizeExcel) GetBackendName() string {
return "excelize"
}
func (e *ExcelizeExcel) FindSheet(sheetName string) (Worksheet, error) {
index, err := e.file.GetSheetIndex(sheetName)
if err != nil {
return nil, fmt.Errorf("sheet not found: %s", sheetName)
}
if index < 0 {
return nil, fmt.Errorf("sheet not found: %s", sheetName)
}
return &ExcelizeWorksheet{file: e.file, sheetName: sheetName}, nil
}
func (e *ExcelizeExcel) CreateNewSheet(sheetName string) error {
_, err := e.file.NewSheet(sheetName)
if err != nil {
return fmt.Errorf("failed to create new sheet: %w", err)
}
return nil
}
func (e *ExcelizeExcel) CopySheet(srcSheetName string, destSheetName string) error {
srcIndex, err := e.file.GetSheetIndex(srcSheetName)
if srcIndex < 0 {
return fmt.Errorf("source sheet not found: %s", srcSheetName)
}
if err != nil {
return err
}
destIndex, err := e.file.NewSheet(destSheetName)
if err != nil {
return fmt.Errorf("failed to create destination sheet: %w", err)
}
if err := e.file.CopySheet(srcIndex, destIndex); err != nil {
return fmt.Errorf("failed to copy sheet: %w", err)
}
srcNext := e.file.GetSheetList()[srcIndex+1]
if srcNext != srcSheetName {
e.file.MoveSheet(destSheetName, srcNext)
}
return nil
}
func (e *ExcelizeExcel) GetSheets() ([]Worksheet, error) {
sheetList := e.file.GetSheetList()
worksheets := make([]Worksheet, len(sheetList))
for i, sheetName := range sheetList {
worksheets[i] = &ExcelizeWorksheet{file: e.file, sheetName: sheetName}
}
return worksheets, nil
}
// SaveExcelize saves the Excel file to the specified path.
// Excelize's Save method restricts the file path length to 207 characters,
// but since this limitation has been relaxed in some environments,
// we ignore this restriction.
// https://github.com/qax-os/excelize/blob/v2.9.0/file.go#L71-L73
func (w *ExcelizeExcel) Save() error {
file, err := os.OpenFile(filepath.Clean(w.file.Path), os.O_WRONLY|os.O_TRUNC|os.O_CREATE, os.ModePerm)
if err != nil {
return err
}
defer file.Close()
return w.file.Write(file)
}
type ExcelizeWorksheet struct {
file *excelize.File
sheetName string
}
func (w *ExcelizeWorksheet) Release() {
// No resources to release in excelize
}
func (w *ExcelizeWorksheet) Name() (string, error) {
return w.sheetName, nil
}
func (w *ExcelizeWorksheet) GetTables() ([]Table, error) {
tables, err := w.file.GetTables(w.sheetName)
if err != nil {
return nil, fmt.Errorf("failed to get tables: %w", err)
}
tableList := make([]Table, len(tables))
for i, table := range tables {
tableList[i] = Table{
Name: table.Name,
Range: NormalizeRange(table.Range),
}
}
return tableList, nil
}
func (w *ExcelizeWorksheet) GetPivotTables() ([]PivotTable, error) {
pivotTables, err := w.file.GetPivotTables(w.sheetName)
if err != nil {
return nil, fmt.Errorf("failed to get pivot tables: %w", err)
}
pivotTableList := make([]PivotTable, len(pivotTables))
for i, pivotTable := range pivotTables {
pivotTableList[i] = PivotTable{
Name: pivotTable.Name,
Range: NormalizeRange(pivotTable.PivotTableRange),
}
}
return pivotTableList, nil
}
func (w *ExcelizeWorksheet) SetValue(cell string, value any) error {
if err := w.file.SetCellValue(w.sheetName, cell, value); err != nil {
return err
}
if err := w.updateDimension(cell); err != nil {
return fmt.Errorf("failed to update dimension: %w", err)
}
return nil
}
func (w *ExcelizeWorksheet) SetFormula(cell string, formula string) error {
if err := w.file.SetCellFormula(w.sheetName, cell, formula); err != nil {
return err
}
if err := w.updateDimension(cell); err != nil {
return fmt.Errorf("failed to update dimension: %w", err)
}
return nil
}
func (w *ExcelizeWorksheet) GetValue(cell string) (string, error) {
value, err := w.file.GetCellValue(w.sheetName, cell)
if err != nil {
return "", err
}
if value == "" {
// try to get calculated value
formula, err := w.file.GetCellFormula(w.sheetName, cell)
if err != nil {
return "", fmt.Errorf("failed to get formula: %w", err)
}
if formula != "" {
return w.file.CalcCellValue(w.sheetName, cell)
}
}
return value, nil
}
func (w *ExcelizeWorksheet) GetFormula(cell string) (string, error) {
formula, err := w.file.GetCellFormula(w.sheetName, cell)
if err != nil {
return "", fmt.Errorf("failed to get formula: %w", err)
}
if formula == "" {
// fallback
return w.GetValue(cell)
}
if !strings.HasPrefix(formula, "=") {
formula = "=" + formula
}
return formula, nil
}
func (w *ExcelizeWorksheet) GetDimention() (string, error) {
return w.file.GetSheetDimension(w.sheetName)
}
func (w *ExcelizeWorksheet) GetPagingStrategy(pageSize int) (PagingStrategy, error) {
return NewExcelizeFixedSizePagingStrategy(pageSize, w)
}
func (w *ExcelizeWorksheet) CapturePicture(captureRange string) (string, error) {
return "", fmt.Errorf("CapturePicture is not supported in Excelize")
}
func (w *ExcelizeWorksheet) AddTable(tableRange, tableName string) error {
enable := true
if err := w.file.AddTable(w.sheetName, &excelize.Table{
Range: tableRange,
Name: tableName,
StyleName: "TableStyleMedium2",
ShowColumnStripes: true,
ShowFirstColumn: false,
ShowHeaderRow: &enable,
ShowLastColumn: false,
ShowRowStripes: &enable,
}); err != nil {
return err
}
return nil
}
func (w *ExcelizeWorksheet) GetCellStyle(cell string) (*CellStyle, error) {
styleID, err := w.file.GetCellStyle(w.sheetName, cell)
if err != nil {
return nil, fmt.Errorf("failed to get cell style: %w", err)
}
style, err := w.file.GetStyle(styleID)
if err != nil {
return nil, fmt.Errorf("failed to get style details: %w", err)
}
return convertExcelizeStyleToCellStyle(style), nil
}
func (w *ExcelizeWorksheet) SetCellStyle(cell string, style *CellStyle) error {
excelizeStyle := convertCellStyleToExcelizeStyle(style)
styleID, err := w.file.NewStyle(excelizeStyle)
if err != nil {
return fmt.Errorf("failed to create style: %w", err)
}
if err := w.file.SetCellStyle(w.sheetName, cell, cell, styleID); err != nil {
return fmt.Errorf("failed to set cell style: %w", err)
}
return nil
}
func convertCellStyleToExcelizeStyle(style *CellStyle) *excelize.Style {
result := &excelize.Style{}
// Border
if len(style.Border) > 0 {
borders := make([]excelize.Border, len(style.Border))
for i, border := range style.Border {
excelizeBorder := excelize.Border{
Type: border.Type.String(),
}
if border.Color != "" {
excelizeBorder.Color = strings.TrimPrefix(border.Color, "#")
}
excelizeBorder.Style = borderStyleNameToInt(border.Style)
borders[i] = excelizeBorder
}
result.Border = borders
}
// Font
if style.Font != nil {
font := &excelize.Font{}
if style.Font.Bold != nil {
font.Bold = *style.Font.Bold
}
if style.Font.Italic != nil {
font.Italic = *style.Font.Italic
}
if style.Font.Underline != nil {
font.Underline = style.Font.Underline.String()
}
if style.Font.Size != nil && *style.Font.Size > 0 {
font.Size = float64(*style.Font.Size)
}
if style.Font.Strike != nil {
font.Strike = *style.Font.Strike
}
if style.Font.Color != nil && *style.Font.Color != "" {
font.Color = strings.TrimPrefix(*style.Font.Color, "#")
}
if style.Font.VertAlign != nil {
font.VertAlign = style.Font.VertAlign.String()
}
result.Font = font
}
// Fill
if style.Fill != nil {
fill := excelize.Fill{}
if style.Fill.Type != "" {
fill.Type = style.Fill.Type.String()
}
fill.Pattern = fillPatternNameToInt(style.Fill.Pattern)
if len(style.Fill.Color) > 0 {
colors := make([]string, len(style.Fill.Color))
for i, color := range style.Fill.Color {
colors[i] = strings.TrimPrefix(color, "#")
}
fill.Color = colors
}
if style.Fill.Shading != nil {
fill.Shading = fillShadingNameToInt(*style.Fill.Shading)
}
result.Fill = fill
}
// NumFmt
if style.NumFmt != nil && *style.NumFmt != "" {
result.CustomNumFmt = style.NumFmt
}
// DecimalPlaces
if style.DecimalPlaces != nil && *style.DecimalPlaces > 0 {
result.DecimalPlaces = style.DecimalPlaces
}
return result
}
func convertExcelizeStyleToCellStyle(style *excelize.Style) *CellStyle {
result := &CellStyle{}
// Border
if len(style.Border) > 0 {
var borders []Border
for _, border := range style.Border {
borderStyle := Border{
Type: BorderType(border.Type),
}
if border.Color != "" {
borderStyle.Color = "#" + strings.ToUpper(border.Color)
}
if border.Style != 0 {
borderStyle.Style = intToBorderStyleName(border.Style)
}
borders = append(borders, borderStyle)
}
if len(borders) > 0 {
result.Border = borders
}
}
// Font
if style.Font != nil {
font := &FontStyle{}
if style.Font.Bold {
font.Bold = &style.Font.Bold
}
if style.Font.Italic {
font.Italic = &style.Font.Italic
}
if style.Font.Underline != "" {
underline := FontUnderline(style.Font.Underline)
font.Underline = &underline
}
if style.Font.Size > 0 {
size := int(style.Font.Size)
font.Size = &size
}
if style.Font.Strike {
font.Strike = &style.Font.Strike
}
if style.Font.Color != "" {
color := "#" + strings.ToUpper(style.Font.Color)
font.Color = &color
}
if style.Font.VertAlign != "" {
vertAlign := FontVertAlign(style.Font.VertAlign)
font.VertAlign = &vertAlign
}
if font.Bold != nil || font.Italic != nil || font.Underline != nil || font.Size != nil || font.Strike != nil || font.Color != nil || font.VertAlign != nil {
result.Font = font
}
}
// Fill
if style.Fill.Type != "" || style.Fill.Pattern != 0 || len(style.Fill.Color) > 0 {
fill := &FillStyle{}
if style.Fill.Type != "" {
fill.Type = FillType(style.Fill.Type)
}
if style.Fill.Pattern != 0 {
fill.Pattern = intToFillPatternName(style.Fill.Pattern)
}
if len(style.Fill.Color) > 0 {
var colors []string
for _, color := range style.Fill.Color {
if color != "" {
colors = append(colors, "#"+strings.ToUpper(color))
}
}
if len(colors) > 0 {
fill.Color = colors
}
}
if style.Fill.Shading != 0 {
shading := intToFillShadingName(style.Fill.Shading)
fill.Shading = &shading
}
if fill.Type != "" || fill.Pattern != FillPatternNone || len(fill.Color) > 0 || fill.Shading != nil {
result.Fill = fill
}
}
// NumFmt
if style.CustomNumFmt != nil && *style.CustomNumFmt != "" {
result.NumFmt = style.CustomNumFmt
}
// DecimalPlaces
if style.DecimalPlaces != nil && *style.DecimalPlaces != 0 {
result.DecimalPlaces = style.DecimalPlaces
}
return result
}
func intToBorderStyleName(style int) BorderStyle {
styles := map[int]BorderStyle{
0: BorderStyleNone,
1: BorderStyleContinuous,
2: BorderStyleContinuous,
3: BorderStyleDash,
4: BorderStyleDot,
5: BorderStyleContinuous,
6: BorderStyleDouble,
7: BorderStyleContinuous,
8: BorderStyleDashDot,
9: BorderStyleDashDotDot,
10: BorderStyleSlantDashDot,
11: BorderStyleContinuous,
12: BorderStyleMediumDashDot,
13: BorderStyleMediumDashDotDot,
}
if name, exists := styles[style]; exists {
return name
}
return BorderStyleContinuous
}
func intToFillPatternName(pattern int) FillPattern {
patterns := map[int]FillPattern{
0: FillPatternNone,
1: FillPatternSolid,
2: FillPatternMediumGray,
3: FillPatternDarkGray,
4: FillPatternLightGray,
5: FillPatternDarkHorizontal,
6: FillPatternDarkVertical,
7: FillPatternDarkDown,
8: FillPatternDarkUp,
9: FillPatternDarkGrid,
10: FillPatternDarkTrellis,
11: FillPatternLightHorizontal,
12: FillPatternLightVertical,
13: FillPatternLightDown,
14: FillPatternLightUp,
15: FillPatternLightGrid,
16: FillPatternLightTrellis,
17: FillPatternGray125,
18: FillPatternGray0625,
}
if name, exists := patterns[pattern]; exists {
return name
}
return FillPatternNone
}
func intToFillShadingName(shading int) FillShading {
shadings := map[int]FillShading{
0: FillShadingHorizontal,
1: FillShadingVertical,
2: FillShadingDiagonalDown,
3: FillShadingDiagonalUp,
4: FillShadingFromCenter,
5: FillShadingFromCorner,
}
if name, exists := shadings[shading]; exists {
return name
}
return FillShadingHorizontal
}
func borderStyleNameToInt(style BorderStyle) int {
styles := map[BorderStyle]int{
BorderStyleNone: 0,
BorderStyleContinuous: 1,
BorderStyleDash: 3,
BorderStyleDot: 4,
BorderStyleDouble: 6,
BorderStyleDashDot: 8,
BorderStyleDashDotDot: 9,
BorderStyleSlantDashDot: 10,
BorderStyleMediumDashDot: 12,
BorderStyleMediumDashDotDot: 13,
}
if value, exists := styles[style]; exists {
return value
}
return 1
}
func fillPatternNameToInt(pattern FillPattern) int {
patterns := map[FillPattern]int{
FillPatternNone: 0,
FillPatternSolid: 1,
FillPatternMediumGray: 2,
FillPatternDarkGray: 3,
FillPatternLightGray: 4,
FillPatternDarkHorizontal: 5,
FillPatternDarkVertical: 6,
FillPatternDarkDown: 7,
FillPatternDarkUp: 8,
FillPatternDarkGrid: 9,
FillPatternDarkTrellis: 10,
FillPatternLightHorizontal: 11,
FillPatternLightVertical: 12,
FillPatternLightDown: 13,
FillPatternLightUp: 14,
FillPatternLightGrid: 15,
FillPatternLightTrellis: 16,
FillPatternGray125: 17,
FillPatternGray0625: 18,
}
if value, exists := patterns[pattern]; exists {
return value
}
return 0
}
func fillShadingNameToInt(shading FillShading) int {
shadings := map[FillShading]int{
FillShadingHorizontal: 0,
FillShadingVertical: 1,
FillShadingDiagonalDown: 2,
FillShadingDiagonalUp: 3,
FillShadingFromCenter: 4,
FillShadingFromCorner: 5,
}
if value, exists := shadings[shading]; exists {
return value
}
return 0
}
// updateDimention updates the dimension of the worksheet after a cell is updated.
func (w *ExcelizeWorksheet) updateDimension(updatedCell string) error {
dimension, err := w.file.GetSheetDimension(w.sheetName)
if err != nil {
return err
}
startCol, startRow, endCol, endRow, err := ParseRange(dimension)
if err != nil {
return err
}
updatedCol, updatedRow, err := excelize.CellNameToCoordinates(updatedCell)
if err != nil {
return err
}
if startCol > updatedCol {
startCol = updatedCol
}
if endCol < updatedCol {
endCol = updatedCol
}
if startRow > updatedRow {
startRow = updatedRow
}
if endRow < updatedRow {
endRow = updatedRow
}
startRange, err := excelize.CoordinatesToCellName(startCol, startRow)
if err != nil {
return err
}
endRange, err := excelize.CoordinatesToCellName(endCol, endRow)
if err != nil {
return err
}
updatedDimension := fmt.Sprintf("%s:%s", startRange, endRange)
return w.file.SetSheetDimension(w.sheetName, updatedDimension)
}
```
--------------------------------------------------------------------------------
/internal/excel/excel_ole.go:
--------------------------------------------------------------------------------
```go
package excel
import (
"bufio"
"bytes"
"encoding/base64"
"fmt"
"io"
"path/filepath"
"regexp"
"runtime"
"strings"
"github.com/go-ole/go-ole"
"github.com/go-ole/go-ole/oleutil"
"github.com/skanehira/clipboard-image"
)
type OleExcel struct {
application *ole.IDispatch
workbook *ole.IDispatch
}
type OleWorksheet struct {
excel *OleExcel
worksheet *ole.IDispatch
}
func NewExcelOle(absolutePath string) (*OleExcel, func(), error) {
runtime.LockOSThread()
ole.CoInitializeEx(0, ole.COINIT_APARTMENTTHREADED)
unknown, err := oleutil.GetActiveObject("Excel.Application")
if err != nil {
return nil, func() {}, err
}
excel, err := unknown.QueryInterface(ole.IID_IDispatch)
if err != nil {
return nil, func() {}, err
}
oleutil.MustPutProperty(excel, "ScreenUpdating", false)
oleutil.MustPutProperty(excel, "EnableEvents", false)
workbooks := oleutil.MustGetProperty(excel, "Workbooks").ToIDispatch()
c := oleutil.MustGetProperty(workbooks, "Count").Val
for i := 1; i <= int(c); i++ {
workbook := oleutil.MustGetProperty(workbooks, "Item", i).ToIDispatch()
fullName := oleutil.MustGetProperty(workbook, "FullName").ToString()
name := oleutil.MustGetProperty(workbook, "Name").ToString()
if strings.HasPrefix(fullName, "https:") && name == filepath.Base(absolutePath) {
// If a workbook is opened through a WOPI URL, its absolute file path cannot be retrieved.
// If the absolutePath is not writable, it assumes that the workbook has opened by WOPI.
if FileIsNotWritable(absolutePath) {
return &OleExcel{application: excel, workbook: workbook}, func() {
oleutil.MustPutProperty(excel, "EnableEvents", true)
oleutil.MustPutProperty(excel, "ScreenUpdating", true)
workbook.Release()
workbooks.Release()
excel.Release()
ole.CoUninitialize()
runtime.UnlockOSThread()
}, nil
} else {
// This workbook might not be specified with the absolutePath
}
} else if normalizePath(fullName) == normalizePath(absolutePath) {
return &OleExcel{application: excel, workbook: workbook}, func() {
oleutil.MustPutProperty(excel, "EnableEvents", true)
oleutil.MustPutProperty(excel, "ScreenUpdating", true)
workbook.Release()
workbooks.Release()
excel.Release()
ole.CoUninitialize()
runtime.UnlockOSThread()
}, nil
}
}
return nil, func() {}, fmt.Errorf("workbook not found: %s", absolutePath)
}
func NewExcelOleWithNewObject(absolutePath string) (*OleExcel, func(), error) {
runtime.LockOSThread()
ole.CoInitializeEx(0, ole.COINIT_APARTMENTTHREADED)
unknown, err := oleutil.CreateObject("Excel.Application")
if err != nil {
return nil, func() {}, err
}
excel, err := unknown.QueryInterface(ole.IID_IDispatch)
if err != nil {
return nil, func() {}, err
}
workbooks := oleutil.MustGetProperty(excel, "Workbooks").ToIDispatch()
workbook, err := oleutil.CallMethod(workbooks, "Open", absolutePath)
if err != nil {
return nil, func() {}, err
}
w := workbook.ToIDispatch()
return &OleExcel{application: excel, workbook: w}, func() {
w.Release()
workbooks.Release()
excel.Release()
oleutil.CallMethod(excel, "Close")
ole.CoUninitialize()
runtime.UnlockOSThread()
}, nil
}
func (o *OleExcel) GetBackendName() string {
return "ole"
}
func (o *OleExcel) GetSheets() ([]Worksheet, error) {
worksheets := oleutil.MustGetProperty(o.workbook, "Worksheets").ToIDispatch()
defer worksheets.Release()
count := int(oleutil.MustGetProperty(worksheets, "Count").Val)
worksheetList := make([]Worksheet, count)
for i := 1; i <= count; i++ {
worksheet := oleutil.MustGetProperty(worksheets, "Item", i).ToIDispatch()
worksheetList[i-1] = &OleWorksheet{
excel: o,
worksheet: worksheet,
}
}
return worksheetList, nil
}
func (o *OleExcel) FindSheet(sheetName string) (Worksheet, error) {
worksheets := oleutil.MustGetProperty(o.workbook, "Worksheets").ToIDispatch()
defer worksheets.Release()
count := int(oleutil.MustGetProperty(worksheets, "Count").Val)
for i := 1; i <= count; i++ {
worksheet := oleutil.MustGetProperty(worksheets, "Item", i).ToIDispatch()
name := oleutil.MustGetProperty(worksheet, "Name").ToString()
if name == sheetName {
return &OleWorksheet{
excel: o,
worksheet: worksheet,
}, nil
}
}
return nil, fmt.Errorf("sheet not found: %s", sheetName)
}
func (o *OleExcel) CreateNewSheet(sheetName string) error {
activeWorksheet := oleutil.MustGetProperty(o.workbook, "ActiveSheet").ToIDispatch()
defer activeWorksheet.Release()
activeWorksheetIndex := oleutil.MustGetProperty(activeWorksheet, "Index").Val
worksheets := oleutil.MustGetProperty(o.workbook, "Worksheets").ToIDispatch()
defer worksheets.Release()
_, err := oleutil.CallMethod(worksheets, "Add", nil, activeWorksheet)
if err != nil {
return err
}
worksheet := oleutil.MustGetProperty(worksheets, "Item", activeWorksheetIndex+1).ToIDispatch()
defer worksheet.Release()
_, err = oleutil.PutProperty(worksheet, "Name", sheetName)
if err != nil {
return err
}
return nil
}
func (o *OleExcel) CopySheet(srcSheetName string, dstSheetName string) error {
worksheets := oleutil.MustGetProperty(o.workbook, "Worksheets").ToIDispatch()
defer worksheets.Release()
srcSheetVariant, err := oleutil.GetProperty(worksheets, "Item", srcSheetName)
if err != nil {
return fmt.Errorf("faild to get sheet: %w", err)
}
srcSheet := srcSheetVariant.ToIDispatch()
defer srcSheet.Release()
srcSheetIndex := oleutil.MustGetProperty(srcSheet, "Index").Val
_, err = oleutil.CallMethod(srcSheet, "Copy", nil, srcSheet)
if err != nil {
return err
}
dstSheetVariant, err := oleutil.GetProperty(worksheets, "Item", srcSheetIndex+1)
if err != nil {
return fmt.Errorf("failed to get copied sheet: %w", err)
}
dstSheet := dstSheetVariant.ToIDispatch()
defer dstSheet.Release()
_, err = oleutil.PutProperty(dstSheet, "Name", dstSheetName)
if err != nil {
return err
}
return nil
}
func (o *OleExcel) Save() error {
_, err := oleutil.CallMethod(o.workbook, "Save")
if err != nil {
return err
}
return nil
}
func (o *OleWorksheet) Release() {
o.worksheet.Release()
}
func (o *OleWorksheet) Name() (string, error) {
name := oleutil.MustGetProperty(o.worksheet, "Name").ToString()
return name, nil
}
func (o *OleWorksheet) GetTables() ([]Table, error) {
tables := oleutil.MustGetProperty(o.worksheet, "ListObjects").ToIDispatch()
defer tables.Release()
count := int(oleutil.MustGetProperty(tables, "Count").Val)
tableList := make([]Table, count)
for i := 1; i <= count; i++ {
table := oleutil.MustGetProperty(tables, "Item", i).ToIDispatch()
defer table.Release()
name := oleutil.MustGetProperty(table, "Name").ToString()
defer table.Release()
tableRange := oleutil.MustGetProperty(table, "Range").ToIDispatch()
defer tableRange.Release()
tableList[i-1] = Table{
Name: name,
Range: NormalizeRange(oleutil.MustGetProperty(tableRange, "Address").ToString()),
}
}
return tableList, nil
}
func (o *OleWorksheet) GetPivotTables() ([]PivotTable, error) {
pivotTables := oleutil.MustGetProperty(o.worksheet, "PivotTables").ToIDispatch()
defer pivotTables.Release()
count := int(oleutil.MustGetProperty(pivotTables, "Count").Val)
pivotTableList := make([]PivotTable, count)
for i := 1; i <= count; i++ {
pivotTable := oleutil.MustGetProperty(pivotTables, "Item", i).ToIDispatch()
defer pivotTable.Release()
name := oleutil.MustGetProperty(pivotTable, "Name").ToString()
pivotTableRange := oleutil.MustGetProperty(pivotTable, "TableRange1").ToIDispatch()
defer pivotTableRange.Release()
pivotTableList[i-1] = PivotTable{
Name: name,
Range: NormalizeRange(oleutil.MustGetProperty(pivotTableRange, "Address").ToString()),
}
}
return pivotTableList, nil
}
func (o *OleWorksheet) SetValue(cell string, value any) error {
range_ := oleutil.MustGetProperty(o.worksheet, "Range", cell).ToIDispatch()
defer range_.Release()
_, err := oleutil.PutProperty(range_, "Value", value)
return err
}
func (o *OleWorksheet) SetFormula(cell string, formula string) error {
range_ := oleutil.MustGetProperty(o.worksheet, "Range", cell).ToIDispatch()
defer range_.Release()
_, err := oleutil.PutProperty(range_, "Formula", formula)
return err
}
func (o *OleWorksheet) GetValue(cell string) (string, error) {
range_ := oleutil.MustGetProperty(o.worksheet, "Range", cell).ToIDispatch()
defer range_.Release()
value := oleutil.MustGetProperty(range_, "Text").Value()
switch v := value.(type) {
case string:
return v, nil
case nil:
return "", nil
default: // Handle other types as needed
return "", fmt.Errorf("unsupported type: %T", v)
}
}
func (o *OleWorksheet) GetFormula(cell string) (string, error) {
range_ := oleutil.MustGetProperty(o.worksheet, "Range", cell).ToIDispatch()
defer range_.Release()
formula := oleutil.MustGetProperty(range_, "Formula").ToString()
return formula, nil
}
func (o *OleWorksheet) GetDimention() (string, error) {
range_ := oleutil.MustGetProperty(o.worksheet, "UsedRange").ToIDispatch()
defer range_.Release()
dimension := oleutil.MustGetProperty(range_, "Address").ToString()
return NormalizeRange(dimension), nil
}
func (o *OleWorksheet) GetPagingStrategy(pageSize int) (PagingStrategy, error) {
return NewOlePagingStrategy(1000, o)
}
func (o *OleWorksheet) PrintArea() (string, error) {
v, err := oleutil.GetProperty(o.worksheet, "PageSetup")
if err != nil {
return "", err
}
pageSetup := v.ToIDispatch()
defer pageSetup.Release()
printArea := oleutil.MustGetProperty(pageSetup, "PrintArea").ToString()
return printArea, nil
}
func (o *OleWorksheet) HPageBreaks() ([]int, error) {
v, err := oleutil.GetProperty(o.worksheet, "HPageBreaks")
if err != nil {
return nil, err
}
hPageBreaks := v.ToIDispatch()
defer hPageBreaks.Release()
count := int(oleutil.MustGetProperty(hPageBreaks, "Count").Val)
pageBreaks := make([]int, count)
for i := 1; i <= count; i++ {
pageBreak := oleutil.MustGetProperty(hPageBreaks, "Item", i).ToIDispatch()
defer pageBreak.Release()
location := oleutil.MustGetProperty(pageBreak, "Location").ToIDispatch()
defer location.Release()
row := oleutil.MustGetProperty(location, "Row").Val
pageBreaks[i-1] = int(row)
}
return pageBreaks, nil
}
func (o *OleWorksheet) CapturePicture(captureRange string) (string, error) {
r := oleutil.MustGetProperty(o.worksheet, "Range", captureRange).ToIDispatch()
defer r.Release()
_, err := oleutil.CallMethod(
r,
"CopyPicture",
int(1), // xlScreen (https://learn.microsoft.com/ja-jp/dotnet/api/microsoft.office.interop.excel.xlpictureappearance?view=excel-pia)
int(2), // xlBitmap (https://learn.microsoft.com/ja-jp/dotnet/api/microsoft.office.interop.excel.xlcopypictureformat?view=excel-pia)
)
if err != nil {
return "", err
}
// Read the image from the clipboard
buf := new(bytes.Buffer)
bufWriter := bufio.NewWriter(buf)
clipboardReader, err := clipboard.ReadFromClipboard()
if err != nil {
return "", fmt.Errorf("failed to read from clipboard: %w", err)
}
if _, err := io.Copy(bufWriter, clipboardReader); err != nil {
return "", fmt.Errorf("failed to copy clipboard data: %w", err)
}
if err := bufWriter.Flush(); err != nil {
return "", fmt.Errorf("failed to flush buffer: %w", err)
}
return base64.StdEncoding.EncodeToString(buf.Bytes()), nil
}
func (o *OleWorksheet) AddTable(tableRange string, tableName string) error {
tables := oleutil.MustGetProperty(o.worksheet, "ListObjects").ToIDispatch()
defer tables.Release()
// https://learn.microsoft.com/ja-jp/office/vba/api/excel.listobjects.add
tableVar, err := oleutil.CallMethod(
tables,
"Add",
int(1), // xlSrcRange (https://learn.microsoft.com/ja-jp/office/vba/api/excel.xllistobjectsourcetype)
tableRange,
nil,
int(0), // xlYes (https://learn.microsoft.com/ja-jp/office/vba/api/excel.xlyesnoguess)
)
if err != nil {
return err
}
table := tableVar.ToIDispatch()
defer table.Release()
_, err = oleutil.PutProperty(table, "Name", tableName)
if err != nil {
return err
}
return err
}
func (o *OleWorksheet) GetCellStyle(cell string) (*CellStyle, error) {
rng := oleutil.MustGetProperty(o.worksheet, "Range", cell).ToIDispatch()
defer rng.Release()
style := &CellStyle{}
// Get Font information
normalStyle := oleutil.MustGetProperty(o.excel.workbook, "Styles", "Normal").ToIDispatch()
defer normalStyle.Release()
normalFont := oleutil.MustGetProperty(normalStyle, "Font").ToIDispatch()
defer normalFont.Release()
font := oleutil.MustGetProperty(rng, "Font").ToIDispatch()
defer font.Release()
normalFontSize := int(oleutil.MustGetProperty(normalFont, "Size").Value().(float64))
normalFontBold := oleutil.MustGetProperty(normalFont, "Bold").Value().(bool)
normalFontItalic := oleutil.MustGetProperty(normalFont, "Italic").Value().(bool)
normalFontColor := oleutil.MustGetProperty(normalFont, "Color").Value().(float64)
fontSize := int(oleutil.MustGetProperty(font, "Size").Value().(float64))
fontBold := oleutil.MustGetProperty(font, "Bold").Value().(bool)
fontItalic := oleutil.MustGetProperty(font, "Italic").Value().(bool)
fontColor := oleutil.MustGetProperty(font, "Color").Value().(float64)
if fontSize != normalFontSize || fontBold != normalFontBold || fontItalic != normalFontItalic || fontColor != normalFontColor {
colorStr := bgrToRgb(fontColor)
style.Font = &FontStyle{
Bold: &fontBold,
Italic: &fontItalic,
Size: &fontSize,
Color: &colorStr,
}
}
// Get Interior (fill) information
interior := oleutil.MustGetProperty(rng, "Interior").ToIDispatch()
defer interior.Release()
interiorPattern := excelPatternToFillPattern(oleutil.MustGetProperty(interior, "Pattern").Value().(int32))
if interiorPattern != FillPatternNone {
interiorColor := oleutil.MustGetProperty(interior, "Color").Value().(float64)
style.Fill = &FillStyle{
Type: "pattern",
Pattern: interiorPattern,
Color: []string{bgrToRgb(interiorColor)},
}
}
// Get Border information
var borderStyles []Border
// Get borders for each direction: Left(7), Top(8), Bottom(9), Right(10)
borderPositions := []struct {
index int
position BorderType
}{
{7, BorderTypeLeft},
{8, BorderTypeTop},
{9, BorderTypeBottom},
{10, BorderTypeRight},
}
borders := oleutil.MustGetProperty(rng, "Borders").ToIDispatch()
defer borders.Release()
bordersLineStyle := oleutil.MustGetProperty(borders, "LineStyle")
if bordersLineStyle.VT == ole.VT_NULL {
// If Borders.LineStyle is null, the borders have different styles
for _, pos := range borderPositions {
border := oleutil.MustGetProperty(borders, "Item", pos.index).ToIDispatch()
defer border.Release()
borderLineStyle := excelBorderStyleToName(oleutil.MustGetProperty(border, "LineStyle").Value().(int32))
if borderLineStyle != BorderStyleNone {
borderColor := oleutil.MustGetProperty(border, "Color").Value().(float64)
borderStyle := Border{
Type: pos.position,
Style: borderLineStyle,
Color: bgrToRgb(borderColor),
}
borderStyles = append(borderStyles, borderStyle)
}
}
} else {
// If Borders.LineStyle is not null, all borders have the same style
lineStyle := excelBorderStyleToName(bordersLineStyle.Value().(int32))
if lineStyle != BorderStyleNone {
for _, pos := range borderPositions {
border := oleutil.MustGetProperty(borders, "Item", pos.index).ToIDispatch()
borderColor := oleutil.MustGetProperty(border, "Color").Value().(float64)
borderStyle := Border{
Type: pos.position,
Style: lineStyle,
Color: bgrToRgb(borderColor),
}
borderStyles = append(borderStyles, borderStyle)
}
}
}
style.Border = borderStyles
// Get NumberFormat information
generalNumberFormat := oleutil.MustGetProperty(o.excel.application, "International", 26).Value().(string) // xlGeneralFormatName
numberFormat := oleutil.MustGetProperty(rng, "NumberFormat").ToString()
if numberFormat != generalNumberFormat && numberFormat != "@" {
style.NumFmt = &numberFormat
}
// Extract decimal places from number format if it's a numeric format
decimalPlaces := extractDecimalPlacesFromFormat(numberFormat)
style.DecimalPlaces = &decimalPlaces
return style, nil
}
// bgrToRgb converts BGR color format to RGB hex string
func bgrToRgb(bgrColor float64) string {
bgrColorInt := int32(bgrColor)
// Extract RGB components from BGR format
r := (bgrColorInt >> 0) & 0xFF
g := (bgrColorInt >> 8) & 0xFF
b := (bgrColorInt >> 16) & 0xFF
return fmt.Sprintf("#%02X%02X%02X", r, g, b)
}
// excelBorderStyleToName converts Excel border style constant to BorderStyleName
func excelBorderStyleToName(excelStyle int32) BorderStyle {
switch excelStyle {
case 1: // xlContinuous
return BorderStyleContinuous
case -4115: // xlDash
return BorderStyleDash
case -4118: // xlDot
return BorderStyleDot
case -4119: // xlDouble
return BorderStyleDouble
case 4: // xlDashDot
return BorderStyleDashDot
case 5: // xlDashDotDot
return BorderStyleDashDotDot
case 13: // xlSlantDashDot
return BorderStyleSlantDashDot
case -4142: // xlLineStyleNone
return BorderStyleNone
default:
return BorderStyleNone
}
}
// excelPatternToFillPattern converts Excel XlPattern constant to FillPatternName
func excelPatternToFillPattern(excelPattern int32) FillPattern {
switch excelPattern {
case -4142: // xlPatternNone
return FillPatternNone
case 1: // xlPatternSolid
return FillPatternSolid
case -4125: // xlPatternGray75
return FillPatternDarkGray
case -4124: // xlPatternGray50
return FillPatternMediumGray
case -4126: // xlPatternGray25
return FillPatternLightGray
case -4121: // xlPatternGray16
return FillPatternGray125
case -4127: // xlPatternGray8
return FillPatternGray0625
case 9: // xlPatternHorizontal
return FillPatternLightHorizontal
case 12: // xlPatternVertical
return FillPatternLightVertical
case 10: // xlPatternDown
return FillPatternLightDown
case 11: // xlPatternUp
return FillPatternLightUp
case 16: // xlPatternGrid
return FillPatternLightGrid
case 17: // xlPatternCrissCross
return FillPatternLightTrellis
case 5: // xlPatternLightHorizontal
return FillPatternLightHorizontal
case 6: // xlPatternLightVertical
return FillPatternLightVertical
case 7: // xlPatternLightDown
return FillPatternLightDown
case 8: // xlPatternLightUp
return FillPatternLightUp
case 15: // xlPatternLightGrid
return FillPatternLightGrid
case 18: // xlPatternLightTrellis
return FillPatternLightTrellis
case 13: // xlPatternSemiGray75
return FillPatternDarkHorizontal
case 2: // xlPatternDarkHorizontal
return FillPatternDarkHorizontal
case 3: // xlPatternDarkVertical
return FillPatternDarkVertical
case 4: // xlPatternDarkDown
return FillPatternDarkDown
case 14: // xlPatternDarkUp
return FillPatternDarkUp
case -4162: // xlPatternDarkGrid
return FillPatternDarkGrid
case -4166: // xlPatternDarkTrellis
return FillPatternDarkTrellis
default:
return FillPatternNone
}
}
var extractDecimalPlacesRegexp = regexp.MustCompile(`\.([0#]+)`)
// extractDecimalPlacesFromFormat extracts decimal places count from Excel number format string
func extractDecimalPlacesFromFormat(format string) int {
// Handle common numeric formats
// Examples: "0.00" -> 2, "#,##0.000" -> 3, "0" -> 0
matches := extractDecimalPlacesRegexp.FindStringSubmatch(format)
if len(matches) > 1 {
return len(matches[1])
}
return 0
}
func (o *OleWorksheet) SetCellStyle(cell string, style *CellStyle) error {
rng := oleutil.MustGetProperty(o.worksheet, "Range", cell).ToIDispatch()
defer rng.Release()
// Apply Font styles
if style.Font != nil {
font := oleutil.MustGetProperty(rng, "Font").ToIDispatch()
defer font.Release()
if style.Font.Bold != nil {
oleutil.PutProperty(font, "Bold", *style.Font.Bold)
}
if style.Font.Italic != nil {
oleutil.PutProperty(font, "Italic", *style.Font.Italic)
}
if style.Font.Size != nil && *style.Font.Size > 0 {
oleutil.PutProperty(font, "Size", *style.Font.Size)
}
if style.Font.Color != nil && *style.Font.Color != "" {
colorValue := rgbToBgr(*style.Font.Color)
oleutil.PutProperty(font, "Color", colorValue)
}
if style.Font.Strike != nil && *style.Font.Strike {
oleutil.PutProperty(font, "Strikethrough", true)
}
}
// Apply Fill styles
if style.Fill != nil {
interior := oleutil.MustGetProperty(rng, "Interior").ToIDispatch()
defer interior.Release()
if style.Fill.Pattern != FillPatternNone {
oleutil.PutProperty(interior, "Pattern", fillPatternToExcelPattern(style.Fill.Pattern))
}
if len(style.Fill.Color) > 0 && style.Fill.Color[0] != "" {
colorValue := rgbToBgr(style.Fill.Color[0])
oleutil.PutProperty(interior, "Color", colorValue)
}
}
// Apply Border styles
if len(style.Border) > 0 {
borders := oleutil.MustGetProperty(rng, "Borders").ToIDispatch()
defer borders.Release()
for _, borderStyle := range style.Border {
borderIndex := borderTypeToIndex(borderStyle.Type)
if borderIndex > 0 {
border := oleutil.MustGetProperty(borders, "Item", borderIndex).ToIDispatch()
defer border.Release()
oleutil.PutProperty(border, "LineStyle", borderStyleNameToExcel(borderStyle.Style))
if borderStyle.Color != "" {
colorValue := rgbToBgr(borderStyle.Color)
oleutil.PutProperty(border, "Color", colorValue)
}
}
}
}
// Apply Number Format
if style.NumFmt != nil && *style.NumFmt != "" {
oleutil.PutProperty(rng, "NumberFormat", *style.NumFmt)
}
return nil
}
// rgbToBgr converts RGB hex string to BGR color format
func rgbToBgr(rgbColor string) int32 {
if len(rgbColor) != 7 || rgbColor[0] != '#' {
return 0
}
r := hexToByte(rgbColor[1:3])
g := hexToByte(rgbColor[3:5])
b := hexToByte(rgbColor[5:7])
return int32(r) | (int32(g) << 8) | (int32(b) << 16)
}
// hexToByte converts hex string to byte
func hexToByte(hex string) byte {
var result byte
for _, char := range hex {
result *= 16
if char >= '0' && char <= '9' {
result += byte(char - '0')
} else if char >= 'A' && char <= 'F' {
result += byte(char - 'A' + 10)
} else if char >= 'a' && char <= 'f' {
result += byte(char - 'a' + 10)
}
}
return result
}
// borderTypeToIndex converts border type string to Excel border index
func borderTypeToIndex(borderType BorderType) int {
switch borderType {
case BorderTypeLeft:
return 7
case BorderTypeTop:
return 8
case BorderTypeBottom:
return 9
case BorderTypeRight:
return 10
case BorderTypeDiagonalDown:
return 5
case BorderTypeDiagonalUp:
return 6
default:
return 0
}
}
// borderStyleNameToExcel converts BorderStyleName to Excel constant
func borderStyleNameToExcel(style BorderStyle) int32 {
switch style {
case BorderStyleContinuous:
return 1 // xlContinuous
case BorderStyleDash:
return -4115 // xlDash
case BorderStyleDot:
return -4118 // xlDot
case BorderStyleDouble:
return -4119 // xlDouble
case BorderStyleDashDot:
return 4 // xlDashDot
case BorderStyleDashDotDot:
return 5 // xlDashDotDot
case BorderStyleSlantDashDot:
return 13 // xlSlantDashDot
case BorderStyleNone:
return -4142 // xlLineStyleNone
default:
return -4142 // xlLineStyleNone
}
}
// fillPatternToExcelPattern converts FillPatternName to Excel pattern constant
func fillPatternToExcelPattern(pattern FillPattern) int32 {
switch pattern {
case FillPatternSolid:
return 1 // xlPatternSolid
case FillPatternMediumGray:
return -4124 // xlPatternGray50
case FillPatternDarkGray:
return -4125 // xlPatternGray75
case FillPatternLightGray:
return -4126 // xlPatternGray25
case FillPatternGray125:
return -4121 // xlPatternGray16
case FillPatternGray0625:
return -4127 // xlPatternGray8
case FillPatternLightHorizontal:
return 5 // xlPatternLightHorizontal
case FillPatternLightVertical:
return 6 // xlPatternLightVertical
case FillPatternLightDown:
return 7 // xlPatternLightDown
case FillPatternLightUp:
return 8 // xlPatternLightUp
case FillPatternLightGrid:
return 15 // xlPatternLightGrid
case FillPatternLightTrellis:
return 18 // xlPatternLightTrellis
case FillPatternDarkHorizontal:
return 2 // xlPatternDarkHorizontal
case FillPatternDarkVertical:
return 3 // xlPatternDarkVertical
case FillPatternDarkDown:
return 4 // xlPatternDarkDown
case FillPatternDarkUp:
return 14 // xlPatternDarkUp
case FillPatternDarkGrid:
return -4162 // xlPatternDarkGrid
case FillPatternDarkTrellis:
return -4166 // xlPatternDarkTrellis
case FillPatternNone:
return -4142 // xlPatternNone
default:
return -4142 // xlPatternNone
}
}
func normalizePath(path string) string {
// Normalize the volume name to uppercase
vol := filepath.VolumeName(path)
if vol == "" {
return path
}
rest := path[len(vol):]
return filepath.Clean(strings.ToUpper(vol) + rest)
}
```