\r \r

Excel Column Compare\r

\r

Overview\r

\r Compare two specified columns in an Excel/CSV file to identify differences and commonalities between them. The skill produces a formatted Excel report showing values unique to each column, values shared by both, and a summary of the comparison.\r \r

When to Use\r

\r

• User asks to compare two columns in an Excel file\r
• User wants to find values that exist in one column but not another\r
• User needs to identify differences or overlaps between two sets of data in a spreadsheet\r
• User says things like "对比这两列的不同", "比较Excel两列数据", "find differences between columns", "compare column A and B"\r \r

Workflow\r

\r

Step 1: Understand the User's Request\r

\r Identify from the user's message:\r

• The Excel/CSV file to compare (file path)\r
• The two columns to compare (by column name, letter like A/B/C, or index)\r
• Optional: specific sheet name, comparison mode, or output path\r \r If the user does not specify column identifiers, read the file first to display available columns and ask the user which two to compare.\r \r

Step 2: Preview the File (if needed)\r

\r If the column identifiers are unclear, read the file to show available columns:\r \r

import pandas as pd\r
df = pd.read_excel(input_file, nrows=5)\r
print(df.columns.tolist())\r
print(df.head())\r
```\r
\r
### Step 3: Run the Comparison Script\r
\r
Execute the bundled script `scripts/compare_columns.py`:\r
\r
```bash\r
python \x3Cskill_path>/scripts/compare_columns.py \x3Cinput_file> \x3Ccolumn_a> \x3Ccolumn_b> [--sheet \x3Csheet>] [--output \x3Coutput>] [--mode \x3Cmode>]\r
```\r
\r
**Parameters:**\r
- `input_file`: Path to the Excel (.xlsx, .xls) or CSV file\r
- `column_a`: Column name (e.g., "姓名"), letter (e.g., A, B), or 0-based index\r
- `column_b`: Column name, letter, or 0-based index for the second column\r
- `--sheet`: Sheet name or index (default: first sheet)\r
- `--output`: Output file path (default: `\x3Cinput>_comparison_result.xlsx`)\r
- `--mode`: Comparison mode:\r
  - `full` (default): Show all categories — only in A, only in B, common\r
  - `diff`: Show only differences (not in both)\r
  - `unique_a`: Show only items unique to column A\r
  - `unique_b`: Show only items unique to column B\r
  - `common`: Show only items common to both columns\r
\r
**Examples:**\r
\r
```bash\r
# Compare by column names\r
python scripts/compare_columns.py data.xlsx "姓名" "名字"\r
\r
# Compare by column letters\r
python scripts/compare_columns.py data.xlsx A B\r
\r
# Compare specific sheet, only show differences\r
python scripts/compare_columns.py data.xlsx "Email" "邮箱" --sheet "Sheet2" --mode diff\r
\r
# Specify output path\r
python scripts/compare_columns.py data.xlsx C D --output result.xlsx\r
```\r
\r
### Step 4: Present the Results\r
\r
After the script runs:\r
1. Display the console summary (unique counts for each category)\r
2. Open the generated output Excel file for the user to review\r
3. Summarize key findings: how many values are unique to each column, how many are shared\r
\r
## Output Format\r
\r
The output Excel file contains the following sheets:\r
\r
| Sheet | Content |\r
|-------|---------|\r
| Summary | Overview with column names, counts, and statistics |\r
| Only in [Column A] | Values found only in the first column, with row numbers |\r
| Only in [Column B] | Values found only in the second column, with row numbers |\r
| Common Values | Values present in both columns |\r
\r
Sheets are color-coded: blue for Column A, orange for Column B, green for common values, purple for summary.\r
\r
## Troubleshooting\r
\r
- **Column not found**: The script prints available column names. Suggest the correct column identifier to the user.\r
- **Same column selected**: The script will error if both references resolve to the same column. Ask the user to choose two different columns.\r
- **Large files**: For files with many rows, the comparison still works efficiently using set operations. Row number display is limited to first 10 occurrences per unique value.\r
- **Missing dependencies**: The script auto-installs `pandas` and `openpyxl` if not present.\r
\r
## Resources\r
\r
### scripts/\r
\r
- `compare_columns.py` — Main comparison script that reads an Excel/CSV file, compares two specified columns, and generates a formatted Excel report with the comparison results.\r