pi-excel

Pi extension for comprehensive Microsoft Excel automation on Windows using win32com.client and the real desktop Excel application.

Requirements

• Windows
• Microsoft Excel installed
• Python 3
• pywin32

For VBA editing, enable:

Excel > File > Options > Trust Center > Trust Center Settings > Macro Settings > Trust access to the VBA project object model

Install

pi install git:git.otmatas.com/pi-extensions/pi-excel.git
cd "$HOME\.pi\agent\git\git.otmatas.com\pi-extensions\pi-excel"
python -m pip install -r requirements.txt

Restart Pi and verify Python integration:

/excel-python-check

The extension tries python, py, and then python3, which avoids preferring the broken Windows Microsoft Store python3 alias.

Workflow

1. Analyze a workbook

Use excel_analyze with an .xlsx or .xlsm path. Excel opens the workbook read-only and creates an analysis directory containing editable representations such as:

analysis_report/
  summary.json
  macros_manifest.json
  connections.json
  power_queries.json
  workbook_info.json
  buttons.json
  charts.json
  worksheets.json
  formulas.json
  macros/
  power_queries/

2. Edit the analysis files

Pi can edit these files to change or create:

• VBA modules
• Power Query definitions
• OLEDB/ODBC connections
• buttons and assigned macros
• query output tables
• pivot tables
• charts
• conditional formatting
• formulas and values
• range formatting
• worksheets

3. Apply changes

Use excel_apply with the original workbook and analysis directory. By default it creates *_modified.xlsx or *_modified.xlsm; specifying a separate output path is recommended.

Set refresh: true only when external connections and Power Query should refresh before saving. Refresh can contact external systems and may require credentials.

Tools

The extension exposes every high-level Excel capability as a Pi tool:

• excel_analyze
• excel_set_macro_code
• excel_append_macro_code
• excel_set_power_query
• excel_create_sql_power_query
• excel_add_query_table
• excel_update_button
• excel_duplicate_button
• excel_add_chart
• excel_add_conditional_format
• excel_add_worksheet_action
• excel_set_cell
• excel_format_range
• excel_add_pivot_table
• excel_direct
• excel_apply

Editing tools queue definitions in the analysis folder. Run excel_apply afterward to perform the queued changes through desktop Excel.

excel_direct supports immediate range reads/writes, refresh, macro execution, PDF export, deletion of sheets/shapes/queries/connections, and Save As. Risky operations require confirm: true; modifying the original requires overwrite: true.

excel_analyze

Uses desktop Excel to inspect a workbook and produce editable analysis files.

Parameters:

{
  "path": "C:\\Reports\\report.xlsm",
  "outputDir": "C:\\Reports\\analysis_report"
}

excel_apply

Uses desktop Excel to apply all definitions found in the analysis directory.

{
  "path": "C:\\Reports\\report.xlsm",
  "analysisDir": "C:\\Reports\\analysis_report",
  "outputPath": "C:\\Reports\\report_modified.xlsm",
  "refresh": false
}

Implementation

All workbook inspection and modification is performed through win32com.client against Microsoft Excel's COM object model. The extension no longer uses OpenXML, openpyxl, XlsxWriter, pyOpenVBA, oletools, or DataMashup editing libraries.

The Python standard library is still used for JSON files, paths, temporary analysis data, and safe output copies.

Safety

• Prefer creating a new output workbook instead of overwriting the original.
• Excel may prompt for credentials, protected-view decisions, links, or macro permissions.
• Unsigned macros may be blocked by Trust Center or Mark-of-the-Web settings.
• Do not embed database passwords in workbooks or analysis files.
• The worker closes the workbook and Excel application after each operation.

Development

npm install
npm run typecheck
npm test
python -m py_compile python/excel_worker.py python/excel_features/*.py