diff --git a/ROADMAP.md b/ROADMAP.md --- a/ROADMAP.md +++ b/ROADMAP.md @@ -2,7 +2,7 @@ **Stage: alpha** · next: beta, ~25 points of "ready for real work" and ~160–240 h away -> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-25 · **Change:** minor (AutoSum over a selected row puts its total to the right, and fills a selection's empty last row and column) · **Target:** Microsoft Excel (Microsoft 365) +> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-26 · **Change:** minor (Excel open check script; AutoSum over a selected row puts its total to the right, and fills a selection's empty last row and column) · **Target:** Microsoft Excel (Microsoft 365) GridCraft aims at full Microsoft Excel parity, and at being better in places: faster, open (XLSX is the native format), fully scriptable by agents (MCP, control channel, CLI), and on the web. @@ -135,6 +135,7 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | What landed | |---|---| +| 2026-10-26 | Excel open check (`packaging/windows/excel-open-check`): saves workbooks with `gridcraft-cli`, opens each in real Excel through COM so a repair counts as a failure, and records pass or the failure per file; 0.12.1 over 18 public fixtures and 7 feature scenarios: 24 of 25 open cleanly, the other is the legacy-formula slowness of #410 | | 2026-10-25 | AutoSum over a selection does what Excel does: a selected row gets one total to its right (in its last cell if that is empty), not a one-cell total under each cell; a block whose last column is empty gets each row's total there, one whose last row is empty each column's, and with both the corner gets the total of the block; otherwise totals go under each column as before | | 2026-10-24 | Go To lists the named ranges of every sheet (`Sheet2!Name` for a name scoped to another sheet; hidden names, constants and formulas left out) and the references entered before, a double-click goes to an entry, and the Reference box takes several names or references separated by commas (`Price, Type`, `B14:C22,F19:G30`) | | 2026-10-24 | Status bar: a right-click opens a menu that sets which selection statistics show (Average, Count and Sum by default; Numerical Count, Minimum and Maximum are new), kept with the preferences (`view.statusBar`) | @@ -242,6 +243,7 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | Change | Summary | |---|---|---| +| 2026-10-26 | minor | Progress log: Excel open check | | 2026-10-25 | minor | Progress log: AutoSum over a selected row, and a selection's empty last row and column | | 2026-10-23 | minor | Progress log: accounting negatives in number text | | 2026-10-23 | minor | Progress log: INDEX with one index on a 2-D reference is #REF! | diff --git a/docs/gaps.md b/docs/gaps.md --- a/docs/gaps.md +++ b/docs/gaps.md @@ -1,6 +1,6 @@ # Gaps -> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-24 · **Change:** minor (§8: PivotTable Enable show details; earlier: PivotTable Show Details) · **Target:** Microsoft Excel (Microsoft 365) +> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-26 · **Change:** minor (§1: repeatable Excel open check; §8: PivotTable Enable show details; earlier: PivotTable Show Details) · **Target:** Microsoft Excel (Microsoft 365) Every known shortfall against Excel, one entry each, ranked by user impact (what blocks someone using GridCraft for real work first). This is the work list: pick from the top, check the open @@ -22,6 +22,7 @@ No alpha blockers: every core workflow passes the [alpha gate](roadmap.md#alpha- - **Evidence:** round-trip tests cover our own files only (`crates/xlsx/src/tests/`); PivotTable round-trip noted as "unverified in real Excel" since 10-07; #64 (dynamic-array flag) shows the class of bug. +- **Check:** `packaging/windows/excel-open-check/check.ps1` saves workbooks with `gridcraft-cli` and opens each result in real Excel through COM with `CorruptLoad` = normal load, so a repair is a failure. The 0.12.1 run (18 public synthetic fixtures + 7 feature scenarios: charts, pivots with pivot chart and slicer, CF, validation, tables and names, shapes and pictures, sheets and protection) gave 24 of 25 saved files opening cleanly; the 25th (`gc_legacy_to_array`) is not a repair but Excel taking over 200 s on legacy formulas saved as whole-column array formulas (#410, open PRs #417, #1367, #1660). Still open: a run over a large real-world corpus. - **Impact:** the main format; a repair prompt or silent loss destroys trust. - **Estimate:** 15–25 h, plus owner time in Excel. [file-format-parity.md](file-format-parity.md) @@ -183,6 +184,7 @@ No alpha blockers: every core workflow passes the [alpha gate](roadmap.md#alpha- | Date | Change | Summary | |---|---|---| +| 2026-10-26 | minor | §1: `packaging/windows/excel-open-check` opens saved workbooks in real Excel and records pass or the exact failure; first run: 24 of 25 open cleanly | | 2026-10-24 | minor | §8: PivotTable Enable show details: `pivot.options` `enableShowDetails` turns Show Details off for a PivotTable, read and written in XLSX as `enableDrill` | | 2026-10-23 | minor | §8: PivotTable Show Details: double-clicking a value cell lists its source records on a new sheet (#2364) | | 2026-10-22 | minor | §20: a native window per workbook window, New Window on the same workbook and Arrange All on the desktop; left: Arrange All across monitors, the web's one window, pen pressure (estimate 10–14 h → 5–8 h) | diff --git a/packaging/windows/excel-open-check/README.md b/packaging/windows/excel-open-check/README.md new file mode 100644 --- /dev/null +++ b/packaging/windows/excel-open-check/README.md @@ -0,0 +1,13 @@ +# Excel open check + +Saves workbooks with `gridcraft-cli` and opens each result in real Excel (COM), recording per file `PASS` or the +exact failure. Windows with Excel only. See `check.ps1` for the options. + +```powershell +.\check.ps1 -Cli C:\gridcraft\gridcraft-cli.exe -Inputs C:\workbooks -Csv result.csv +``` + +Inputs are any `.xlsx`/`.xlsm` (the synthetic `gridcraft/` fixtures in a public fixtures repo are a good start); each +`*.jsonl` here is a feature scenario run on the Sales sample. Add a scenario by dropping in another `.jsonl` +(`gridcraft-cli run --script` format). Results: `Original` is the input opened in Excel (so a file Excel itself +rejects or loads slowly isn't blamed on GridCraft), `Saved` is the GridCraft-saved copy. diff --git a/packaging/windows/excel-open-check/charts.jsonl b/packaging/windows/excel-open-check/charts.jsonl new file mode 100644 --- /dev/null +++ b/packaging/windows/excel-open-check/charts.jsonl @@ -0,0 +1,12 @@ +# One chart of each common kind over the sales table +insert.chart={"range":"B4:F8","type":"bar","at":"K36"} +insert.chart={"range":"B4:F8","type":"line","subtype":"markers","at":"S3"} +insert.chart={"range":"B4:F8","type":"area","subtype":"stacked","at":"S19"} +insert.chart={"range":"C5:D8","type":"scatter","at":"S36"} +insert.chart={"range":"B4:F8","type":"radar","at":"AA3"} +insert.chart={"range":"B4:C8","type":"doughnut","at":"AA19"} +insert.chart={"range":"B4:C8","type":"waterfall","at":"AA36"} +insert.chart={"range":"B4:C8","type":"treemap","at":"AI3"} +insert.chart={"range":"B4:C8","type":"histogram","at":"AI19"} +insert.sparkline={"range":"C5:F5","location":"J5","type":"line"} +insert.sparkline={"range":"C6:F6","location":"J6","type":"column"} diff --git a/packaging/windows/excel-open-check/check.ps1 b/packaging/windows/excel-open-check/check.ps1 new file mode 100644 --- /dev/null +++ b/packaging/windows/excel-open-check/check.ps1 @@ -0,0 +1,125 @@ +<# +Excel open check: saves workbooks with GridCraft, opens each result in real Excel (COM) and records per +file whether Excel loads it as it is or has to repair it (docs/file-format-parity.md, "Excel open check"). + + check.ps1 -Cli C:\gridcraft\gridcraft-cli.exe [-Inputs C:\workbooks] [-Out C:\out] [-Csv result.csv] + [-TimeoutSec 120] + +Files saved: + - every .xlsx / .xlsm in -Inputs, opened and saved with `gridcraft-cli run --in --out` (the original + is opened in Excel too, so a file Excel itself dislikes or loads slowly is told apart from a + GridCraft regression); + - every *.jsonl scenario next to this script (one feature each), run on the Sales sample. + +Each file is opened read-only with CorruptLoad = xlNormalLoad (0), so a part Excel would repair is a +failure instead of a silent fix, with DisplayAlerts off and VBA macros disabled so nothing waits on a prompt. +AutomationSecurity does not cover Excel 4.0 (XLM) macros, which would still prompt, so a workbook that +holds XLM macro sheets is never opened: it is reported as SKIP-XLM and the run does not pass. +Excel is a fresh instance per file; a file that hangs or kills Excel is recorded as TIMEOUT or CRASH and +that instance (found by its window's process id) is stopped. Nothing is written next to the inputs; any other +Excel, such as one the person starts meanwhile, is never touched. Exit code 1 when any saved file is not PASS. +#> +param( + [Parameter(Mandatory = $true)][string]$Cli, + [string]$Inputs, + [string]$Out = (Join-Path ([IO.Path]::GetTempPath()) 'gridcraft-excel-open-check'), + [string]$Csv, + [int]$TimeoutSec = 120 +) +$ErrorActionPreference = 'Stop' + +# Runs in a child PowerShell so a hang can be killed after $TimeoutSec without taking this script with it. +$probe = { + param($path) + $x = $null + try { + # Compiled before Excel starts, so nothing between the start and the PID line can fail or stall on it. + Add-Type -Namespace Win -Name U -MemberDefinition '[DllImport("user32.dll")] public static extern uint GetWindowThreadProcessId(IntPtr h, out uint pid);' + $x = New-Object -ComObject Excel.Application + # Report this instance's process id so the caller can stop exactly it and nothing else. + $xlPid = [uint32]0 + [void][Win.U]::GetWindowThreadProcessId([IntPtr]$x.Hwnd, [ref]$xlPid) + "PID`t$xlPid" + $x.Visible = $false + $x.DisplayAlerts = $false + $x.AutomationSecurity = 3 # msoAutomationSecurityForceDisable: no VBA macros (XLM is screened by Test-HasXlm) + # Named arguments: PowerShell can't skip the optional ones in between positionally. + $wb = [System.__ComObject].InvokeMember('Open', [Reflection.BindingFlags]::InvokeMethod, $null, $x.Workbooks, + @($path, 0, $true, 0), $null, $null, @('Filename', 'UpdateLinks', 'ReadOnly', 'CorruptLoad')) + $sheets = $wb.Worksheets.Count + $wb.Chartsheets.Count + $wb.Close($false) + "PASS`t$sheets sheets" + } catch { + "FAIL`t" + ($_.Exception.Message -replace '\s+', ' ') + } finally { + # Also covers a failure before the PID line: this process's own instance is quit, no other is touched. + if ($x) { try { $x.Quit() } catch {} } + } +} + +# True when the package holds Excel 4.0 macro sheets, which ForceDisable does not stop and Excel would prompt for. +function Test-HasXlm([string]$path) { + Add-Type -AssemblyName System.IO.Compression.FileSystem + # Not a zip at all: nothing to screen, Excel gets to report it. + try { $zip = [IO.Compression.ZipFile]::OpenRead($path) } catch { return $false } + try { return [bool]($zip.Entries | Where-Object { $_.FullName -like 'xl/macrosheets/*' }) } finally { $zip.Dispose() } +} + +function Test-InExcel([string]$path) { + if (Test-HasXlm $path) { return [pscustomobject]@{ State = 'SKIP-XLM'; Seconds = 0; Detail = 'Excel 4.0 macro sheets; not opened' } } + $clock = [Diagnostics.Stopwatch]::StartNew() + $job = Start-Job -ScriptBlock $probe -ArgumentList $path + $done = Wait-Job $job -Timeout $TimeoutSec + $lines = @(Receive-Job $job -Keep -ErrorAction SilentlyContinue) + $failed = $job.State -eq 'Failed' + if (-not $done) { Stop-Job $job } + Remove-Job $job -Force + $xlPid = ($lines | Where-Object { $_ -like "PID`t*" } | Select-Object -First 1) -replace '^PID\t', '' + $out = $lines | Where-Object { $_ -notlike "PID`t*" } | Select-Object -Last 1 + if (-not $done) { $out = "TIMEOUT`tno answer in $TimeoutSec s" } elseif ($failed -or -not $out) { $out = "CRASH`tno result (Excel died)" } + if ($xlPid) { + # A clean Quit lets Excel exit by itself; give it a moment, then stop only this instance. + Wait-Process -Id ([int]$xlPid) -Timeout 15 -ErrorAction SilentlyContinue + Stop-Process -Id ([int]$xlPid) -Force -ErrorAction SilentlyContinue + } + $state, $detail = $out -split "`t", 2 + [pscustomobject]@{ State = $state; Seconds = [math]::Round($clock.Elapsed.TotalSeconds, 1); Detail = $detail } +} + +$saved = Join-Path $Out 'saved' +Remove-Item -LiteralPath $saved -Recurse -Force -ErrorAction SilentlyContinue +New-Item -ItemType Directory -Path $saved -Force | Out-Null + +# (name, original or $null, saved) per file +$work = @() +if ($Inputs) { + foreach ($f in Get-ChildItem -LiteralPath $Inputs -File | Where-Object { $_.Extension -in '.xlsx', '.xlsm' } | Sort-Object Name) { + $work += , @($f.Name, $f.FullName, (Join-Path $saved $f.Name), @('run', '--in', $f.FullName, '--quiet')) + } +} +foreach ($s in Get-ChildItem -Path $PSScriptRoot -Filter *.jsonl | Sort-Object Name) { + $work += , @("scenario:$($s.BaseName)", $null, (Join-Path $saved "$($s.BaseName).xlsx"), @('run', '--sample', 'sales', '--script', $s.FullName, '--quiet')) +} + +$rows = @() +foreach ($w in $work) { + $name, $orig, $dest, $cliArgs = $w + # The CLI reports on stderr, which Stop would turn into an exception. + Remove-Item -LiteralPath $dest -Force -ErrorAction SilentlyContinue + $ErrorActionPreference = 'Continue' + & $Cli @cliArgs --out $dest *> $null + $code = $LASTEXITCODE + $ErrorActionPreference = 'Stop' + $base = if ($orig) { (Test-InExcel $orig) } else { $null } + $r = if ($code -eq 0 -and (Test-Path -LiteralPath $dest)) { Test-InExcel $dest } else { [pscustomobject]@{ State = 'SAVE-FAIL'; Seconds = 0; Detail = "gridcraft-cli exit code $code" } } + $rows += [pscustomobject]@{ + File = $name; Original = if ($base) { $base.State } else { '-' }; Saved = $r.State + Seconds = $r.Seconds; Detail = $r.Detail + } +} +$rows | Format-Table -AutoSize -Wrap | Out-String -Width 220 | Write-Output +if ($Csv) { $rows | Export-Csv -NoTypeInformation -Path $Csv } +$bad = @($rows | Where-Object { $_.Saved -ne 'PASS' -or $_.Original -eq 'SKIP-XLM' }).Count +$savedBad = @($rows | Where-Object Saved -ne 'PASS').Count +"{0} of {1} saved files pass" -f ($rows.Count - $savedBad), $rows.Count +if ($bad) { exit 1 } diff --git a/packaging/windows/excel-open-check/conditional-formats.jsonl b/packaging/windows/excel-open-check/conditional-formats.jsonl new file mode 100644 --- /dev/null +++ b/packaging/windows/excel-open-check/conditional-formats.jsonl @@ -0,0 +1,8 @@ +# Colour scale, data bar, icon set, top 10, formula and text rules +home.conditionalFormat={"range":"C5:F8","rule":{"type":"colorScale"}} +home.conditionalFormat={"range":"C5:C8","rule":{"type":"dataBar"}} +home.conditionalFormat={"range":"D5:D8","rule":{"type":"iconSet"}} +home.conditionalFormat={"range":"E5:E8","rule":{"type":"top10","rank":2}} +home.conditionalFormat={"range":"F5:F8","rule":{"type":"cellIs","operator":"greaterThan","value":"60000","preset":"lightRedFill"}} +home.conditionalFormat={"range":"B5:B8","rule":{"type":"containsText","text":"th","preset":"yellowFill"}} +home.conditionalFormat={"range":"C5:F8","rule":{"type":"expression","formula":"=C5>AVERAGE($C$5:$F$8)","preset":"greenFill"}} diff --git a/packaging/windows/excel-open-check/pivots.jsonl b/packaging/windows/excel-open-check/pivots.jsonl new file mode 100644 --- /dev/null +++ b/packaging/windows/excel-open-check/pivots.jsonl @@ -0,0 +1,4 @@ +# PivotTable on the sales table, a pivot chart, a table slicer +insert.pivotTable={"source":"Sales","destination":"Sales!K40","rows":["Region"],"values":[{"field":"Q1","func":"sum"}]} +insert.pivotChart={"pivot":"Sales!K42","type":"column","at":"S50"} +insert.slicer={"table":"Sales","column":"Region","at":"AA50"} diff --git a/packaging/windows/excel-open-check/protection-sheets.jsonl b/packaging/windows/excel-open-check/protection-sheets.jsonl new file mode 100644 --- /dev/null +++ b/packaging/windows/excel-open-check/protection-sheets.jsonl @@ -0,0 +1,9 @@ +# Extra sheets (renamed, coloured, hidden), grouping and a protected sheet +home.insertSheet={} +sheet.rename={"name":"Second"} +sheet.tabColor={"color":"#FF0000"} +cell.set={"cell":"A1","input":"=Sales!B5&\" \"&Sales!C5"} +home.insertSheet={} +sheet.hide={} +sheet.activate={"sheet":"Sales"} +review.protectSheet={"formatCells":true} diff --git a/packaging/windows/excel-open-check/shapes-pictures.jsonl b/packaging/windows/excel-open-check/shapes-pictures.jsonl new file mode 100644 --- /dev/null +++ b/packaging/windows/excel-open-check/shapes-pictures.jsonl @@ -0,0 +1,5 @@ +# Shapes with text and a picture from base64 (a 1x1 PNG) +insert.shape={"kind":"rectangle","at":"K30","width":120,"height":40,"text":"Box","fill":"#FFC000"} +insert.shape={"kind":"ellipse","at":"O30","width":80,"height":40} +insert.shape={"kind":"arrow","at":"K33","width":80,"height":30} +insert.picture={"base64":"iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mP8z8BQDwAEhQGAhKmMIQAAAABJRU5ErkJggg==","mime":"image/png","at":"B20","width":60,"height":60,"alt":"dot"} diff --git a/packaging/windows/excel-open-check/tables-names.jsonl b/packaging/windows/excel-open-check/tables-names.jsonl new file mode 100644 --- /dev/null +++ b/packaging/windows/excel-open-check/tables-names.jsonl @@ -0,0 +1,13 @@ +# Table style and total row, defined names, merged cells, frozen panes, hyperlink-free cell notes +selection.set={"cell":"C5"} +table.totalRow={} +table.style={"style":"TableStyleLight9"} +formulas.defineName={"name":"NorthQ1","refersTo":"=Sales!$C$5"} +formulas.defineName={"name":"AllRegions","refersTo":"=Sales!$B$5:$B$8"} +formulas.defineName={"name":"Rate","refersTo":"=0.2","comment":"tax rate"} +cell.set={"cell":"K14","input":"=SUM(AllRegions)"} +cell.set={"cell":"K15","input":"=NorthQ1*Rate"} +home.mergeCells={"range":"K16:M16"} +view.freezePanes={"cell":"C5"} +review.newNote={"cell":"C5","text":"checked"} +review.newComment={"cell":"D5","text":"threaded"} diff --git a/packaging/windows/excel-open-check/validation.jsonl b/packaging/windows/excel-open-check/validation.jsonl new file mode 100644 --- /dev/null +++ b/packaging/windows/excel-open-check/validation.jsonl @@ -0,0 +1,8 @@ +# One rule of each kind +data.validation={"range":"K2:K5","type":"list","formula1":"North,South,East,West"} +data.validation={"range":"K6:K8","type":"whole","operator":"between","formula1":"1","formula2":"100","inputTitle":"Count","inputMessage":"1 to 100","errorStyle":"warning"} +data.validation={"range":"L2:L5","type":"decimal","operator":"greaterThan","formula1":"0"} +data.validation={"range":"L6:L8","type":"date","operator":"greaterOrEqual","formula1":"2026-01-01"} +data.validation={"range":"M2:M5","type":"textLength","operator":"lessOrEqual","formula1":"10"} +data.validation={"range":"M6:M8","type":"custom","formula1":"=ISNUMBER(M6)"} +data.validation={"range":"N2:N5","type":"list","formula1":"=$B$5:$B$8"}