Minimax XLSX

• The final output must be an Excel file, possibly multiple files based on task requirements, but the delivery must contain at least one .xlsx file
• Keep the overall output succinct, and avoid providing additional files beyond what the user specified, particularly readme documentation, since this consumes excessive context.

Creating Excel Files: Python + openpyxl/pandas

✅ MANDATORY Technology Foundation for Excel Generation:

• Runtime Environment: Python 3
• Core Library: openpyxl (for Excel file generation, formatting, formulas)
• Data Manipulation: pandas (for data processing, subsequently exporting through openpyxl)
• Execution Method: Utilize ipython tool for Python scripts

✅ Formula Recalculation:

• Engine: LibreOffice (headless mode)
• Script: recalc.py (automatically sets up LibreOffice macro, recalculates all formulas, reports errors)
• Execution Method: Utilize shell tool to run python ./scripts/recalc.py <file>

✅ Verification & PivotTable Utilities:

• Utility: MiniMaxXlsx (consolidated CLI tool for validation, recheck, pivot, etc.)
• Execution Method: Utilize shell tool for CLI instructions

🔧 Runtime Environment:

• Employ ipython tool for Excel generation via openpyxl/pandas
• Employ shell tool for recalculation and verification instructions

Python Excel Generation Template:

from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font, Border, Side, Alignment
import pandas as pd

# Create workbook
wb = Workbook()
ws = wb.active
ws.title = "Data"

# Add data
ws['A1'] = "Header1"
ws['B1'] = "Header2"

# Apply styling
ws['A1'].font = Font(bold=True, color="FFFFFF")
ws['A1'].fill = PatternFill(start_color="333333", end_color="333333", fill_type="solid")

# Save
wb.save('output.xlsx')

</Technology Stack>

When generating Excel files containing externally retrieved data:

Source Attribution (COMPULSORY):

• Every piece of external data MUST include source attribution in the final Excel
• 🚨 Applicable to ALL external utilities: datasource, web_search, API requests, or any retrieved data
• Employ two distinct columns: Source Name | Source URL
• Avoid using HYPERLINK function (utilize plain text to prevent formula issues)
• ⛔ PROHIBITED: Delivering Excel containing external data without source attribution
• Example:

• When per-row attribution is impractical, establish a dedicated "Sources" sheet

</External Data in Excel>

1. Python (openpyxl/pandas) - For Excel file generation, formatting, formulas, charts 2. recalc.py (Python + LibreOffice) - For formula recalculation and computed value generation 3. MiniMaxXlsx CLI Utility - For verification, error detection, and PivotTable generation

The MiniMaxXlsx utility offers 6 commands invokable via the shell tool:

⚠️ Path Convention: Every relative path in this document (e.g., ./scripts/, ./pivot-table.md) is relative to the skill directory containing this SKILL.md.

Executable Location: ./scripts/MiniMaxXlsx

Base Invocation: ./scripts/MiniMaxXlsx <command> [arguments]

1. recheck ⚠️ EXECUTE FIRST for formula issues

• description：This utility identifies:

  • Formula issues: #VALUE!, #DIV/0!, #REF!, #NAME?, #NULL!, #NUM!, #N/A
  • Zero-result cells: Formula cells yielding 0 (frequently signals reference issues)
  • Implicit array formulas: Formulas functioning in LibreOffice but displaying #N/A in MS Excel (e.g., MATCH(TRUE(), range>0, 0))

• Implicit Array Formula Identification:

  • Patterns such as MATCH(TRUE(), range>0, 0) necessitate CSE (Ctrl+Shift+Enter) in MS Excel
  • LibreOffice processes these automatically, thus they succeed in LibreOffice recalculation but fail in Excel
  • Upon detection, reconstruct the formula using alternatives:
    • ❌ =MATCH(TRUE(), A1:A10>0, 0) → displays #N/A in Excel
    • ✅ =SUMPRODUCT((A1:A10>0)*ROW(A1:A10))-ROW(A1)+1 → functions across all Excel versions
    • ✅ Alternatively employ helper column containing explicit TRUE/FALSE values

• how to use:

./scripts/MiniMaxXlsx recheck output.xlsx

2. reference-check (alias: refcheck)

• description: This utility serves to Identify potential reference issues and pattern irregularities in Excel formulas. It recognizes 4 typical problems when AI produces formulas:

Out-of-range references - Formulas reference a range substantially exceeding the actual data row count. Header row references - The initial row (typically the header) is mistakenly included in the computation. Insufficient aggregate function range - Functions such as SUM/AVERAGE only span ≤2 cells. Inconsistent formula patterns - Certain formulas in the same column diverge from the dominant pattern ("isolated" formulas).

• how to use:

./scripts/MiniMaxXlsx reference-check output.xlsx

3. inspect

• description: This command examines Excel file structure and produces JSON describing all sheets, tables, headers, and data ranges. Employ this to comprehend an Excel file's structure prior to processing.
• how to use:

# Examine and produce JSON
./scripts/MiniMaxXlsx inspect input.xlsx --pretty

4. pivot 🚨 NECESSITATES pivot-table.md

• description: Generate PivotTable with optional chart utilizing pure OpenXML SDK. This constitutes the SOLE supported approach for PivotTable generation. Automatically generates a chart (bar/line/pie) alongside the PivotTable.
• ⚠️ ESSENTIAL: Prior to utilizing this command, you MUST consult ./pivot-table.md for complete documentation.
• required parameters:
  • input.xlsx - Source Excel file (positional)
  • output.xlsx - Destination Excel file (positional)
  • --source "Sheet!A1:Z100" - Source data range
  • --location "Sheet!A3" - PivotTable placement location
  • --values "Field:sum" - Value fields with aggregation (sum/count/avg/max/min)
• optional parameters:
  • --rows "Field1,Field2" - Row fields
  • --cols "Field1" - Column fields
  • --filters "Field1" - Filter/page fields
  • --name "PivotName" - PivotTable designation (default: PivotTable1)
  • --style "monochrome" - Style theme: monochrome (default) or finance
  • --chart "bar" - Chart variety: bar (default), line, or pie
• how to use:

# First: inspect to obtain sheet names and headers
./scripts/MiniMaxXlsx inspect data.xlsx --pretty

# Then: generate PivotTable with chart
./scripts/MiniMaxXlsx pivot \
    data.xlsx output.xlsx \
    --source "Sales!A1:F100" \
    --rows "Product,Region" \
    --values "Revenue:sum,Units:count" \
    --location "Summary!A3" \
    --chart "bar"

5. chart-verify

• description: Confirm that all charts contain actual data. Employ this following chart generation to verify they are not empty.
• how to use:

./scripts/MiniMaxXlsx chart-verify output.xlsx

• exit codes:
  • 0 = All charts contain data, safe for delivery
  • 1 = Charts are empty or defective - MUST RECTIFY

6. validate ⚠️ COMPULSORY - MUST EXECUTE PRIOR TO DELIVERY

• description: OpenXML structure verification. Files failing this verification CANNOT be opened in Microsoft Excel. You MUST execute this command prior to delivering any Excel file.

• Verification scope:

  • OpenXML schema compliance (Office 2013 standard)
  • PivotTable and Chart structure integrity
  • Incompatible functions (FILTER, UNIQUE, XLOOKUP, etc. - unsupported in Excel 2019 and earlier)
  • .rels file path format (absolute paths trigger Excel crashes)

• exit codes:

  • 0 = Verification succeeded, safe for delivery
  • Non-zero = Verification failed - DO NOT DELIVER, regenerate the file

• how to use:

./scripts/MiniMaxXlsx validate output.xlsx

• Upon verification failure: Do NOT attempt to "repair" the file. Regenerate it entirely with corrected code.

Additionally, the following Python script is available for formula recalculation:

7. recalc.py 🔄 FORMULA RECALCULATION (COMPULSORY WHEN FILE CONTAINS FORMULAS)

• description: Recalculate all formula values in an Excel file using LibreOffice (headless mode). Excel files generated or modified by openpyxl contain formulas as text strings but NO computed values. This script invokes LibreOffice to evaluate every formula and write the calculated results back into the file. It then scans ALL cells for Excel errors and returns a JSON report.

• Why this is necessary:

  • openpyxl writes formulas (e.g., =SUM(A1:A10)) but does NOT compute their values
  • Without recalculation, opening the file shows formulas without results until Excel recalculates
  • The recheck command needs computed values to detect errors accurately
  • Running recalc.py BEFORE recheck ensures error detection works on actual computed results

• What it does:

  • Automatically configures LibreOffice macro on first execution
  • Recalculates all formulas across all sheets via LibreOffice
  • Scans ALL cells for Excel errors (#VALUE!, #DIV/0!, #REF!, #NAME?, #NULL!, #NUM!, #N/A)
  • Returns JSON with detailed error locations and counts
  • Compatible with both Linux and macOS

• how to use:

python ./scripts/recalc.py output.xlsx [timeout_seconds]

• Parameters:

  • output.xlsx - The Excel file to recalculate (required)
  • timeout_seconds - Maximum wait time for recalculation (optional, default: 30)

• Output format (JSON):

{
  "status": "success",
  "total_errors": 0,
  "total_formulas": 42,
  "error_summary": {}
}

• When errors are found:

{
  "status": "errors_found",
  "total_errors": 2,
  "total_formulas": 42,
  "error_summary": {
    "#REF!": {
      "count": 2,
      "locations": ["Sheet1!B5", "Sheet1!C10"]
    }
  }
}

• When to use: ALWAYS execute after wb.save() and BEFORE running recheck, whenever the file contains formulas.
• When to skip: Only skip if the file contains NO formulas (pure static data).

</Tool script list>

<Excel Creation Workflow - MUST FOLLOW>

📋 Excel Generation Workflow (Per-Sheet Verification)

🚨 ESSENTIAL: Verify EACH sheet immediately following creation, NOT after all sheets are completed!

For each sheet in workbook:
    1. PLAN   → Design this sheet's structure, formulas, references
    2. CREATE → Write data, formulas, styling for this sheet
    3. SAVE   → Save the workbook (wb.save())
    4. RECALC → Run recalc.py to compute formula values (if sheet has formulas)
    5. CHECK  → Run recheck + reference-check → Fix until 0 errors
    6. NEXT   → Only proceed to next sheet after current sheet has 0 errors

After ALL sheets pass:
    7. VALIDATE → Run `validate` command → Fix until exit code 0
    8. DELIVER  → Only deliver files that passed ALL validations

Per-Sheet Verification Commands

# After creating/modifying EACH sheet, save and run:
python ./scripts/recalc.py output.xlsx          # Recalculate formula values
./scripts/MiniMaxXlsx recheck output.xlsx        # Check for formula errors
./scripts/MiniMaxXlsx reference-check output.xlsx # Check for reference errors
# Fix ALL errors before creating the next sheet!

Final Verification (after all sheets complete)

./scripts/MiniMaxXlsx validate output.xlsx

Rationale for Per-Sheet Verification?

• Issues in Sheet 1 propagate to Sheet 2, Sheet 3... triggering cascading failures
• Resolving 3 issues per sheet is simpler than resolving 30 issues at conclusion
• Cross-sheet references can be verified immediately

</Excel Creation Workflow - MUST FOLLOW>

⚠️ ESSENTIAL: Excel Formulas Are INVARIABLY the Primary Choice

For ANY analysis task, employing Excel formulas is the default and preferred methodology. Wherever a formula CAN be employed, it MUST be employed.

✅ CORRECT - Employ Excel formulas:

ws['C2'] = '=A2+B2'           # Sum
ws['D2'] = '=C2/B2*100'       # Percentage
ws['E2'] = '=SUM(A2:A100)'    # Aggregation

❌ PROHIBITED - Pre-compute in Python and insert static values:

result = value_a + value_b
ws['C2'] = result    # BAD: Static value, not a formula

Only employ static values when:

• Data is retrieved from external sources (web search, API)
• Values are constants that remain unchanged
• Formula would generate circular reference

Adhere to this workflow::

Sheet 1: Plan (compose detailed design) → Create → Save → Recalc → Run Recheck → Run ReferenceCheck → Fix errors → Zero errors ✓
Sheet 2: Plan (compose detailed design) → Create → Save → Recalc → Run Recheck → Run ReferenceCheck → Fix errors → Zero errors ✓
Sheet 3: Plan (compose detailed design) → Create → Save → Recalc → Run Recheck → Run ReferenceCheck → Fix errors → Zero errors ✓
...

🚨 ESSENTIAL: Recheck Results Are CONCLUSIVE - NO EXCEPTIONS

The recheck command identifies formula issues (#VALUE!, #DIV/0!, #REF!, #NAME?, #N/A, etc.) and zero-result cells. You MUST adhere to these rules rigorously:

1. ZERO TOLERANCE for issues: If recheck reports ANY issues, you MUST resolve them prior to delivery. There are NO exceptions.

2. DO NOT presume issues will "self-resolve":

   • ❌ INCORRECT: "These issues will vanish when the user opens the file in Excel"
   • ❌ INCORRECT: "Excel will recalculate and rectify these issues automatically"
   • ✅ CORRECT: Resolve ALL issues reported by recheck until error_count = 0

3. Issues identified = Issues to resolve:

   • If recheck displays error_count: 5, you have 5 issues to resolve
   • If recheck displays zero_value_count: 3, you have 3 suspicious cells to examine
   • Only when error_count: 0 can you advance to the next step

4. Typical mistakes to circumvent:

   • ❌ "The #REF! issue occurs because openpyxl doesn't evaluate formulas" - INCORRECT, resolve it!
   • ❌ "The #VALUE! will resolve when opened in Excel" - INCORRECT, resolve it!
   • ❌ "Zero values are anticipated" - EXAMINE each one, many are reference issues!

5. Delivery threshold: Files containing ANY recheck issues CANNOT be delivered to users.

Prohibited Patterns ❌:

1. Create Sheet 1 → Create Sheet 2 → Create Sheet 3 → Run Recheck once at conclusion
   ❌ INCORRECT: Issues accumulate, debugging becomes exponentially more difficult
   ✅ CORRECT: Verify after EACH sheet, resolve before proceeding to next

2. Omit planning for any sheet
   ❌ INCORRECT: Causes 80%+ of reference issues
   ✅ CORRECT: Plan each sheet's structure prior to creating it

3. Recheck displays issues → Disregard and deliver regardless
   ❌ ABSOLUTELY PROHIBITED - issues must be resolved, not disregarded!

4. Recheck displays issues → Proceed to create next sheet regardless
   ❌ INCORRECT: Issues in Sheet 1 will cascade to Sheet 2, 3...
   ✅ CORRECT: Resolve ALL issues in current sheet prior to creating next sheet

</Analyze loop>

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE) — lookup column MUST be leftmost in table_array Best Practices: Employ FALSE for exact match; Lock range with $A$2:$D$100; Wrap with IFERROR(...,"N/A"); Cross-sheet: Sheet2!$A$2:$C$100 Issues: #N/A=not found; #REF!=col_index exceeds columns. Alternative: INDEX/MATCH when lookup column not leftmost

ws['D2'] = '=IFERROR(VLOOKUP(A2,$G$2:$I$50,3,FALSE),"N/A")'

</VLOOKUP Usage Rules>

🚨 ESSENTIAL: PivotTable Generation Necessitates Reading pivot-table.md

Activation Conditions: Identify ANY of these user intentions:

• User explicitly requests "pivot table", "data pivot", "数据透视表"
• Task necessitates data summarization by categories
• Keywords: summarize, aggregate, group by, categorize, breakdown, statistics, distribution, count by, total by
• Dataset contains 50+ rows with grouping requirements
• Cross-tabulation or multi-dimensional analysis required

⚠️ COMPULSORY ACTION: Upon detecting PivotTable requirement, you MUST:

1. CONSULT ./pivot-table.md FIRST
2. Adhere to the execution sequence and workflow in that document
3. Employ the pivot command (NOT manual code construction)

Rationale for This Requirement:

• PivotTable generation employs pure OpenXML SDK (C# tool)
• The pivot command delivers stable, verified implementation
• Manual pivot construction in openpyxl is NOT supported and prohibited
• Chart varieties (bar/line/pie) are automatically generated alongside PivotTable

Quick Reference (Details in pivot-table.md):

# Step 1: Examine data structure
./scripts/MiniMaxXlsx inspect data.xlsx --pretty

# Step 2: Generate PivotTable with chart
./scripts/MiniMaxXlsx pivot \
    data.xlsx output.xlsx \
    --source "Sheet!A1:F100" \
    --rows "Category" \
    --values "Revenue:sum" \
    --location "Summary!A3" \
    --chart "bar"

# Step 3: Verify
./scripts/MiniMaxXlsx validate output.xlsx

⛔ PROHIBITED:

• Generating PivotTable manually via openpyxl code
• Bypassing the inspect step
• Neglecting to consult pivot-table.md prior to generating PivotTable
• 🚨 NEVER modify pivot output file using openpyxl - openpyxl will corrupt pivotCache paths!

⚠️ ESSENTIAL: Workflow Sequence for PivotTable When you need to append additional sheets (Cover, Summary, etc.) to a file that will contain PivotTable:

1. FIRST: Generate ALL sheets using openpyxl (data sheets, cover sheet, styling, etc.)
2. THEN: Execute pivot command as the FINAL STEP
3. NEVER: Open the pivot output file using openpyxl again - this corrupts the file!

✅ CORRECT SEQUENCE:
   openpyxl generates base.xlsx (with Cover, Data sheets)
   → pivot command: base.xlsx → final.xlsx (appends PivotTable)
   → validate final.xlsx
   → DELIVER final.xlsx (do NOT modify subsequently)

❌ INCORRECT SEQUENCE (WILL CORRUPT FILE):
   pivot command generates pivot.xlsx
   → openpyxl opens pivot.xlsx to append Cover sheet  ← CORRUPTS FILE!
   → File cannot be opened in MS Excel

</PivotTable Module>

🚨 PROHIBITED FUNCTIONS (Incompatible with earlier Excel versions):

The following functions are NOT supported in Excel 2019 and earlier. Files employing these functions will FAIL to open in earlier Excel versions. Employ traditional alternatives instead.

Rationale for prohibition:

• These are Excel 365/2021+ dynamic array functions or Google Sheets functions
• Earlier Excel versions (2019, 2016, etc.) cannot interpret these formulas
• The file will crash or exhibit errors when opened in earlier Excel
• The validate command will identify and reject files employing these functions

Example - Converting FILTER to INDEX-MATCH:

❌ INCORRECT: =FILTER(A2:C100, B2:B100="Active")
✅ CORRECT: Employ AutoFilter on the data range, or generate a PivotTable

⚠️ Off-By-One Prevention: Prior to saving, confirm each formula references accurate cells. Execute reference-check tool. Typical issues: referencing headers, incorrect row/column offset. If result is 0 or unexpected → verify references first.

💰 Financial Values: Store in smallest unit (15000000 not 1.5M). Employ Excel format for display: "¥#,##0". Never employ scaled units necessitating conversion in formulas.

</Baseline error>

</Analyze rule>