xlsx（XLSX 表格处理）

XLSX Skill - Excel File Manipulation

You are an Excel file manipulation specialist using the xlsx command-line tool. This skill enables you to read, write, edit, query, and analyze.xlsx files without requiring Python or Node.js libraries.

Core Capabilities

The xlsx binary provides comprehensive Excel file manipulation:

1. Reading & Viewing: view, headers, sheets, select, slice
2. Searching & Filtering: search, filter (SQL-like queries)
3. Editing: set (cell values), insert (rows/columns), delete (rows/columns), copy
4. Conversion: to-csv, from-csv
5. Analysis: stats, count
6. Formulas: get-formula, eval
7. Advanced: format, validate, names (named ranges), pivot, chart

Common Operations

1. Viewing Data

List all sheets:

xlsx sheets file.xlsx

View sheet contents:

xlsx view file.xlsx
xlsx view file.xlsx --sheet "Sheet2"
xlsx view file.xlsx --sheet "Sheet2" --limit 50
xlsx view file.xlsx --format json
xlsx view file.xlsx --format csv

Show headers:

xlsx headers file.xlsx
xlsx headers file.xlsx --sheet "Sales Data"

Select specific columns:

xlsx select file.xlsx "A,C,E"
xlsx select file.xlsx "A-E" --sheet "Data"

Slice rows:

xlsx slice file.xlsx 10 20  # rows 10-20

2. Searching Data

Basic search:

xlsx search file.xlsx "pattern"
xlsx search file.xlsx "pattern" --ignore-case
xlsx search file.xlsx "pattern" --regex --sheet "Sheet1"

SQL-like filtering (powerful):

# Basic comparison
xlsx filter file.xlsx --where "Status = 'Active'"
xlsx filter file.xlsx --where "Age > 30"

# Pattern matching
xlsx filter file.xlsx --where "Email LIKE '%@example.com'"

# Multiple conditions
xlsx filter file.xlsx --where "Department = 'Sales' AND Active = TRUE"
xlsx filter file.xlsx --where "(Status = 'Active' OR Status = 'Pending') AND Age > 25"

# Column names with spaces (use brackets)
xlsx filter file.xlsx --where "[First Name] = 'John'"
xlsx filter file.xlsx --where "[Job Title] LIKE '%Engineer%'"

# Membership testing
xlsx filter file.xlsx --where "Status IN ('Active', 'Pending', 'Review')"

# Range testing
xlsx filter file.xlsx --where "Age BETWEEN 25 AND 35"

# Null checks
xlsx filter file.xlsx --where "Manager IS NULL"
xlsx filter file.xlsx --where "Email IS NOT NULL"

# Output formatting and column selection
xlsx filter file.xlsx --where "Department = 'Sales'" --columns "Name,Email,Phone"
xlsx filter file.xlsx --where "Age > 30" --format csv --output results.csv
xlsx filter file.xlsx --where "Active = TRUE" --format json

3. Editing Data

Set cell value:

xlsx set file.xlsx A1 "Hello"
xlsx set file.xlsx B5 42 --value-type number
xlsx set file.xlsx C1 "2025-01-15" --value-type date
xlsx set file.xlsx D1 "=SUM(A1:A10)" --value-type formula

# Set range of cells
xlsx set file.xlsx B5:B10 "Same Value"

Insert rows/columns:

xlsx insert file.xlsx row 5
xlsx insert file.xlsx column C --sheet "Sheet1"

Delete rows/columns:

xlsx delete file.xlsx row 5
xlsx delete file.xlsx row 5:10  # delete rows 5-10
xlsx delete file.xlsx column C
xlsx delete file.xlsx column C:E  # delete columns C through E

4. Conversion

Convert XLSX to CSV:

xlsx to-csv input.xlsx output.csv
xlsx to-csv input.xlsx output.csv --sheet "Data"
xlsx to-csv input.xlsx output.csv --date-format iso8601

Convert CSV to XLSX:

xlsx from-csv input.csv output.xlsx
xlsx from-csv input.csv output.xlsx --sheet "ImportedData"

5. Analysis

Count rows and columns:

xlsx count file.xlsx
xlsx count file.xlsx --sheet "Sheet2"

Calculate statistics:

xlsx stats file.xlsx
xlsx stats file.xlsx --sheet "Data"

6. Formulas

Get cell formula:

xlsx get-formula file.xlsx A1
xlsx get-formula file.xlsx C10 --sheet "Calculations"

Evaluate formula:

xlsx eval "=SUM(1,2,3)"
xlsx eval "=IF(A1>10, 'High', 'Low')"

Date Formatting

All commands support --date-format for consistent date handling:

Presets:

• iso8601 (default): 2025-01-15T10:30:00
• mdy or us: 1/15/2025
• us-padded: 01/15/2025
• us-time: 1/15/2025 10:30 AM
• unix: Unix timestamp
• excel-serial: Excel serial number
• excel: Excel date format

Custom format:

xlsx view file.xlsx --date-format "%Y-%m-%d"
xlsx to-csv file.xlsx output.csv --date-format "%m/%d/%Y %H:%M"

Backup Protection

By default, xlsx creates backups before editing operations. Use --no-backup to skip:

xlsx set file.xlsx A1 "value" --no-backup

Common Workflows

Workflow 1: Extract specific data to CSV

# Filter data and export to CSV
xlsx filter data.xlsx --where "Status = 'Active'" --columns "Name,Email,Department" --format csv --output active_users.csv

Workflow 2: Update multiple cells

# Set header values
xlsx set report.xlsx A1 "Name"
xlsx set report.xlsx B1 "Email"
xlsx set report.xlsx C1 "Status"

# Set data values
xlsx set report.xlsx A2 "John Doe"
xlsx set report.xlsx B2 "john@example.com"
xlsx set report.xlsx C2 "Active"

Workflow 3: Search and analyze

# Find all instances of a pattern
xlsx search data.xlsx "error" --ignore-case

# Get statistics on the data
xlsx stats data.xlsx --sheet "Results"

# Count total rows
xlsx count data.xlsx

Workflow 4: Convert and process

# Convert XLSX to CSV for processing
xlsx to-csv input.xlsx temp.csv

# Process the CSV (using other tools)
# ... do work ...

# Convert back to XLSX
xlsx from-csv processed.csv output.xlsx --sheet "Results"

Workflow 5: Read and display data

# Quick view of first 20 rows
xlsx view data.xlsx --limit 20

# View specific sheet as JSON for processing
xlsx view data.xlsx --sheet "Orders" --format json

# Get column headers to understand structure
xlsx headers data.xlsx

Best Practices

1. Always check sheet names first: Use xlsx sheets file.xlsx to see available sheets
2. View headers before filtering: Use xlsx headers file.xlsx to know column names
3. Use --limit for large files: Prevent overwhelming output with --limit N
4. Quote column names with spaces: Use brackets: [First Name] not First Name
5. Use appropriate output formats:

- --format table for human viewing (default) - --format json for programmatic processing - --format csv for data export

1. Test filters first: Use --limit 10 when testing filter expressions
2. Backups are your friend: Don't use --no-backup unless you're sure

Important Notes

• All xlsx commands modify files in-place for editing operations (with backup unless --no-backup)
• Row numbers are 1-based (first row is 1)
• Column letters are A-Z, AA-ZZ, etc.
• Sheet names are case-sensitive
• Default sheet is always the first sheet if --sheet is not specified
• Filter WHERE clauses are case-insensitive by default (use --case-sensitive to change)

Error Handling

When operations fail:

1. File not found: Verify the file path is correct
2. Sheet not found: Use xlsx sheets file.xlsx to list available sheets
3. Invalid cell reference: Check that cell/range format is correct (e.g., A1, B5:B10)
4. Permission denied: Ensure the file is not open in Excel or locked

Examples by Use Case

Data Analysis

# Find all high-value transactions
xlsx filter sales.xlsx --where "Amount > 1000" --format table

# Get statistics on revenue
xlsx stats sales.xlsx --sheet "Revenue"

# Count records by criteria
xlsx filter customers.xlsx --where "Status = 'Active'" | wc -l

Data Cleaning

# Find null emails
xlsx filter users.xlsx --where "Email IS NULL"

# Search for invalid data
xlsx search data.xlsx "ERROR" --ignore-case

# View problematic rows
xlsx filter data.xlsx --where "Age < 0 OR Age > 150"

Reporting

# Extract executive summary data
xlsx select report.xlsx "A,E,F" --limit 10

# Convert to CSV for email
xlsx to-csv monthly_report.xlsx summary.csv --sheet "Summary"

# Get formatted view
xlsx view report.xlsx --sheet "Dashboard" --limit 50

Bulk Operations

# Set header row
xlsx set data.xlsx A1:E1 "ID,Name,Email,Status,Date"

# Insert calculated column
xlsx insert data.xlsx column F
xlsx set data.xlsx F1 "Total" --value-type string
xlsx set data.xlsx F2 "=SUM(D2:E2)" --value-type formula

Integration with Other Tools

The xlsx tool works well with standard Unix tools:

# Count matching rows
xlsx filter data.xlsx --where "Status = 'Active'" --format csv | wc -l

# Process with jq
xlsx view data.xlsx --format json | jq '.[] | select(.age > 30)'

# Chain operations
xlsx to-csv input.xlsx - | grep "pattern" | xlsx from-csv - output.xlsx

When to Use Each Command

• view: Quick inspection, data exploration, exporting to different formats
• filter: Complex queries, SQL-like data extraction, conditional exports
• search: Finding text patterns, debugging data issues
• set: Updating cell values, bulk updates, formula insertion
• to-csv/from-csv: Integration with other tools, data exchange
• headers: Understanding data structure before operations
• sheets: Multi-sheet workbook navigation
• select: Column-focused extraction
• stats/count: Quick data analysis and validation

Quick Reference

# View
xlsx sheets <file>                    # list sheets
xlsx view <file>                      # view data
xlsx headers <file>                   # show headers

# Search
xlsx search <file> <pattern>          # search
xlsx filter <file> --where <expr>     # SQL-like filter

# Edit
xlsx set <file> <cell> <value>        # set cell
xlsx insert <file> row <n>            # insert row
xlsx delete <file> row <n>            # delete row

# Convert
xlsx to-csv <xlsx> <csv>              # to CSV
xlsx from-csv <csv> <xlsx>            # from CSV

# Analyze
xlsx count <file>                     # count rows/cols
xlsx stats <file>                     # statistics