pi-excel

Preview Pi/PITOT extension for creating, inspecting, validating, and patching Excel workbooks from a Linux/Docker Pi agent.

This package is intentionally split into:

TypeScript Pi extension -> tool registration, validation, orchestration
Python worker           -> Excel/OpenXML/VBA-heavy work

The preview focuses on the workflow where Pi generates an .xlsm workbook and the user later opens/tests it in desktop Microsoft Excel.

Current scope

Works toward prompts like:

Take this Excel, add this SQL as a dynamic table, make a refresh button with a macro, and another button to export PDF.

The preview can generate/patch a workbook with:

• SQL/server/database config sheet.
• generated VBA module source for:
  • EnsurePowerQuery
  • EnsureQueryTable
  • RefreshData
  • ExportPdf
  • SetupWorkbook
• a VBA bootstrap approach where Excel itself creates/updates the Power Query when a macro workbook is requested.
• macro-free .xlsx Power Query M updates using excel-datamashup when the workbook already contains a DataMashup part.
• static validation of workbook OpenXML parts, with warnings for embedded Power Query metadata.
• Power Query M text generation for safe manual paste into desktop Excel.
• preview VBA module listing/add/replace through pyOpenVBA when installed.

It does not execute Excel, macros, Power Query, or PDF export inside Linux/Docker.

Install/load

From this workspace, load directly:

pi -e ./pi-excel

Or install/copy as a Pi package according to your PITOT extension workflow.

Pi package entry:

{
  "pi": {
    "extensions": ["./extensions/excel.ts"]
  }
}

Python dependencies

The TypeScript extension calls:

python/excel_worker.py

Install Python support inside the Pi agent container. For Alpine-based PITOT agent images:

RUN apk add --no-cache python3 py3-pip

Then install worker dependencies:

pip install -r requirements.txt

Recommended packages:

XlsxWriter
openpyxl
pyOpenVBA
oletools

If pyOpenVBA is missing, the SQL report tool still writes generated VBA source beside the workbook as a .bas fallback, but cannot inject it into vbaProject.bin.

Check availability in Pi:

/excel-python-check

Config/data path

Development convention path:

~/.pi/agent/pi-excel/

This preview currently stores no persistent config, accounts, tokens, or credentials. It does not support env-var, project-local, or legacy config in this development version.

Commands

/excel-python-check

Checks that the Python worker is reachable and reports optional Python package availability.

Tools

excel_create_sql_report

Create or patch an .xlsm workbook with SQL/Power Query configuration and generated VBA macros.

Important behavior:

• Does not run SQL.
• Does not refresh Power Query.
• Does not execute macros.
• Does not export PDF in Docker.
• Generates a workbook for later desktop Excel testing.
• Refuses to patch templates with existing embedded Power Query by default; use excel_generate_powerquery_text for those workbooks.

Example parameters:

{
  "outputPath": "reports/sales-report.xlsm",
  "server": "MY-SQL-SERVER",
  "database": "SalesDW",
  "queryName": "SalesQuery",
  "tableName": "SalesTable",
  "dataSheet": "Data",
  "reportSheet": "Report",
  "sql": "SELECT TOP 100 * FROM dbo.Sales",
  "allowPowerQueryTemplatePatch": false
}

Desktop Excel flow after generation:

1. Open the workbook.
2. Enable macros.
3. Run SetupWorkbook to create buttons.
4. Click Refresh Data to create/update the Power Query table.
5. Click Export PDF to export the report sheet.

excel_inspect_workbook

Statically inspects an .xlsx/.xlsm workbook:

• sheets, when openpyxl is available
• tables, when openpyxl is available
• xl/vbaProject.bin
• xl/connections.xml
• customXml/*
• query table parts
• pivot/data model hints

excel_validate_workbook

Static package validation for .xlsx/.xlsm files. Checks required OpenXML package parts and macro/Power Query related hints.

Important: this is ZIP/OpenXML validation only. A workbook can pass this check and still be rejected by Excel if embedded Power Query metadata is inconsistent.

excel_write_powerquery

Write SQL Power Query M directly into an existing macro-free .xlsx by updating its existing DataMashup Section1.m with excel-datamashup.

Use this when the user wants: .xlsx, Power Query, no VBA/macros.

Current constraints:

• The input .xlsx must already contain at least one Power Query/DataMashup part.
• The tool replaces the workbook's Power Query formula section with one shared QueryName = ... query.
• It updates M code only. If the query is loaded to a sheet/table, open in desktop Excel and Refresh so Excel regenerates table/queryTable/range metadata.
• Creating a brand-new DataMashup from a completely blank .xlsx is not supported yet.

excel_generate_powerquery_text

Generate the exact Power Query M text for a SQL Server query without modifying the workbook. Use this as a fallback/manual workflow:

1. Open the original workbook in desktop Excel.
2. Data > Queries & Connections > right-click query > Edit.
3. Advanced Editor > paste the generated M.
4. Close & Load / Refresh, then save the workbook so Excel regenerates DataMashup, query table, table, range, and cache metadata.

excel_inspect_powerpivot

Statically inspects an .xlsx/.xlsm workbook for Power Pivot / Data Model hints, including:

• xl/model/item.data
• other xl/model/* parts
• relationship files mentioning model parts
• content type entries mentioning model parts
• workbook connections that mention model/model connections
• pivot cache/table parts that mention model/data model/OLAP

This does not edit the embedded Data Model. Direct offline editing of Power Pivot binary internals is intentionally out of scope.

excel_list_vba_modules

Lists VBA modules using pyOpenVBA when available. If pyOpenVBA is missing, it falls back to detecting whether xl/vbaProject.bin exists.

excel_create_macro_workbook

Creates a new blank macro-enabled .xlsm workbook using pyOpenVBA.

If no VBA code is provided, it creates a default macro:

Public Sub HelloWorldRandomNumber()
    ' Shows a Windows Excel MsgBox with hello world and a random number.
End Sub

Use this when the user asks for a blank workbook with a macro. Do not use .xlsx for macro workbooks; .xlsx cannot contain VBA.

excel_add_or_replace_vba_module

Adds or replaces a VBA module in an existing .xlsm workbook using pyOpenVBA.

This tool edits source code only. It cannot run or test the macro in Linux/Docker. It intentionally rejects .xlsx inputs because plain .xlsx files cannot contain VBA projects.

Security notes

Generated .xlsm files contain macros and will be unsigned. Excel may block them depending on Trust Center and Mark-of-the-Web settings.

Do not embed SQL passwords in generated workbooks. Prefer:

• Windows/Excel managed credentials.
• Integrated authentication.
• user-specific connection prompts.
• placeholders in the workbook config sheet.

The generated Power Query uses:

Sql.Database("SERVER", "DATABASE", [Query="..."])

Desktop Excel will handle actual authentication and refresh.

Power Query strategy

Expected behavior:

• If the user asks for a macro workbook/report/buttons/PDF automation, generate .xlsm + VBA bootstrap.
• If the user asks for a macro-free .xlsx and only needs Power Query, use excel_write_powerquery and do not add macros.

Important detail: Power Query is not plain XML. It is stored in customXml/item*.xml as a base64 DataMashup/MS-QDEFF binary stream. excel_write_powerquery uses excel-datamashup to update that stream instead of hand-editing unrelated workbook XML.

What is still unsafe:

• Manually expanding xl/tables/table*.xml, xl/queryTables/queryTable*.xml, worksheet ranges, shared strings, and defined names to fake a refreshed output table.
• Assuming basic ZIP/OpenXML validation proves Excel will accept the Power Query/table metadata.

Safe workflows:

1. For macro-free existing Power Query .xlsx: update the M with excel_write_powerquery, then open Excel and Refresh.
2. For generated macro reports: create .xlsm with the VBA bootstrap macros and let desktop Excel create/update the query.
3. If direct DataMashup update is not possible, use excel_generate_powerquery_text and paste the M code in Excel's Advanced Editor.

Development

npm install
npm run typecheck
npm test
npm run python:check

Limitations in this preview

• No server-side macro execution.
• No server-side Power Query refresh.
• No server-side PDF export through Excel.
• Existing workbook button insertion is handled by generated SetupWorkbook macro, not offline VML patching.
• pyOpenVBA is new; test generated files in real desktop Excel.