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:

• Reading & Viewing: view, headers, sheets, select, slice

• Searching & Filtering: search, filter (SQL-like queries)

• Editing: set (cell values), insert (rows/columns), delete (rows/columns), copy

• Conversion: to-csv, from-csv

• Analysis: stats, count

• Formulas: get-formula, eval

• 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

• Always check sheet names first: Use xlsx sheets file.xlsx to see available sheets

• View headers before filtering: Use xlsx headers file.xlsx to know column names

• Use --limit for large files: Prevent overwhelming output with --limit N

• Quote column names with spaces: Use brackets: [First Name] not First Name

• Use appropriate output formats:

• --format table for human viewing (default)

• --format json for programmatic processing

• --format csv for data export

• Test filters first: Use --limit 10 when testing filter expressions

• 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:

• File not found: Verify the file path is correct

• Sheet not found: Use xlsx sheets file.xlsx to list available sheets

• Invalid cell reference: Check that cell/range format is correct (e.g., A1, B5:B10)

• 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