Use xlsx binary for Excel file manipulation including viewing, SQL-like filtering, cell editing, conversion to/from CSV, and data analysis operations.
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.
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
**List all sheets:**
```bash
xlsx sheets file.xlsx
```
**View sheet contents:**
```bash
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:**
```bash
xlsx headers file.xlsx
xlsx headers file.xlsx --sheet "Sales Data"
```
**Select specific columns:**
```bash
xlsx select file.xlsx "A,C,E"
xlsx select file.xlsx "A-E" --sheet "Data"
```
**Slice rows:**
```bash
xlsx slice file.xlsx 10 20 # rows 10-20
```
**Basic search:**
```bash
xlsx search file.xlsx "pattern"
xlsx search file.xlsx "pattern" --ignore-case
xlsx search file.xlsx "pattern" --regex --sheet "Sheet1"
```
**SQL-like filtering (powerful):**
```bash
xlsx filter file.xlsx --where "Status = 'Active'"
xlsx filter file.xlsx --where "Age > 30"
xlsx filter file.xlsx --where "Email LIKE '%@example.com'"
xlsx filter file.xlsx --where "Department = 'Sales' AND Active = TRUE"
xlsx filter file.xlsx --where "(Status = 'Active' OR Status = 'Pending') AND Age > 25"
xlsx filter file.xlsx --where "[First Name] = 'John'"
xlsx filter file.xlsx --where "[Job Title] LIKE '%Engineer%'"
xlsx filter file.xlsx --where "Status IN ('Active', 'Pending', 'Review')"
xlsx filter file.xlsx --where "Age BETWEEN 25 AND 35"
xlsx filter file.xlsx --where "Manager IS NULL"
xlsx filter file.xlsx --where "Email IS NOT NULL"
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
```
**Set cell value:**
```bash
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
xlsx set file.xlsx B5:B10 "Same Value"
```
**Insert rows/columns:**
```bash
xlsx insert file.xlsx row 5
xlsx insert file.xlsx column C --sheet "Sheet1"
```
**Delete rows/columns:**
```bash
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
```
**Convert XLSX to CSV:**
```bash
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:**
```bash
xlsx from-csv input.csv output.xlsx
xlsx from-csv input.csv output.xlsx --sheet "ImportedData"
```
**Count rows and columns:**
```bash
xlsx count file.xlsx
xlsx count file.xlsx --sheet "Sheet2"
```
**Calculate statistics:**
```bash
xlsx stats file.xlsx
xlsx stats file.xlsx --sheet "Data"
```
**Get cell formula:**
```bash
xlsx get-formula file.xlsx A1
xlsx get-formula file.xlsx C10 --sheet "Calculations"
```
**Evaluate formula:**
```bash
xlsx eval "=SUM(1,2,3)"
xlsx eval "=IF(A1>10, 'High', 'Low')"
```
All commands support `--date-format` for consistent date handling:
**Presets:**
**Custom format:**
```bash
xlsx view file.xlsx --date-format "%Y-%m-%d"
xlsx to-csv file.xlsx output.csv --date-format "%m/%d/%Y %H:%M"
```
By default, `xlsx` creates backups before editing operations. Use `--no-backup` to skip:
```bash
xlsx set file.xlsx A1 "value" --no-backup
```
```bash
xlsx filter data.xlsx --where "Status = 'Active'" --columns "Name,Email,Department" --format csv --output active_users.csv
```
```bash
xlsx set report.xlsx A1 "Name"
xlsx set report.xlsx B1 "Email"
xlsx set report.xlsx C1 "Status"
xlsx set report.xlsx A2 "John Doe"
xlsx set report.xlsx B2 "[email protected]"
xlsx set report.xlsx C2 "Active"
```
```bash
xlsx search data.xlsx "error" --ignore-case
xlsx stats data.xlsx --sheet "Results"
xlsx count data.xlsx
```
```bash
xlsx to-csv input.xlsx temp.csv
xlsx from-csv processed.csv output.xlsx --sheet "Results"
```
```bash
xlsx view data.xlsx --limit 20
xlsx view data.xlsx --sheet "Orders" --format json
xlsx headers data.xlsx
```
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
6. **Test filters first**: Use `--limit 10` when testing filter expressions
7. **Backups are your friend**: Don't use `--no-backup` unless you're sure
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
```bash
xlsx filter sales.xlsx --where "Amount > 1000" --format table
xlsx stats sales.xlsx --sheet "Revenue"
xlsx filter customers.xlsx --where "Status = 'Active'" | wc -l
```
```bash
xlsx filter users.xlsx --where "Email IS NULL"
xlsx search data.xlsx "ERROR" --ignore-case
xlsx filter data.xlsx --where "Age < 0 OR Age > 150"
```
```bash
xlsx select report.xlsx "A,E,F" --limit 10
xlsx to-csv monthly_report.xlsx summary.csv --sheet "Summary"
xlsx view report.xlsx --sheet "Dashboard" --limit 50
```
```bash
xlsx set data.xlsx A1:E1 "ID,Name,Email,Status,Date"
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
```
The xlsx tool works well with standard Unix tools:
```bash
xlsx filter data.xlsx --where "Status = 'Active'" --format csv | wc -l
xlsx view data.xlsx --format json | jq '.[] | select(.age > 30)'
xlsx to-csv input.xlsx - | grep "pattern" | xlsx from-csv - output.xlsx
```
```bash
xlsx sheets <file> # list sheets
xlsx view <file> # view data
xlsx headers <file> # show headers
xlsx search <file> <pattern> # search
xlsx filter <file> --where <expr> # SQL-like filter
xlsx set <file> <cell> <value> # set cell
xlsx insert <file> row <n> # insert row
xlsx delete <file> row <n> # delete row
xlsx to-csv <xlsx> <csv> # to CSV
xlsx from-csv <csv> <xlsx> # from CSV
xlsx count <file> # count rows/cols
xlsx stats <file> # statistics
```
Leave a review
No reviews yet. Be the first to review this skill!