ExcelBridgeNext

Proof-of-concept for the Excel-on-OneDrive ETL pipeline.

Extract    : Graph download bytes               -> byte[]
Transform  : Proxy over interface               -> typed ChangeRecord journal (cells + table ops)
Load actor : journal -> Graph workbook session  -> PATCH cells / append+update+delete table rows
Verify     : Graph download                     -> formulas recalculated, table rows landed

Projects

Project	Purpose
ExcelBridgeNext.Abstractions	[Cell], [Table], [Column] attributes; polymorphic ChangeRecord (CellWrite/TableAppend/TableRowUpdate/TableDelete); ITableList<T>; Actions (JSON-serializable).
ExcelBridgeNext.Core	WorkbookFactory + DispatchProxy stack: WorkbookProxy, TableListProxy, AttachedRowProxy, PendingRowProxy. In-memory ClosedXML workbook + journal.
ExcelBridgeNext.Graph	MSAL auth, HttpClient wrapper for Graph workbook + table endpoints, GraphLoadActor with type dispatch.
ExcelBridgeNext.Poc	Console runner that exercises the round-trip end-to-end.

What you write

public interface IHolding
{
    [Column("Ticker")]   string Ticker { get; set; }
    [Column("BidPrice")] double BidPrice { get; set; }
    [Column("Qty")]      int    Quantity { get; set; }
}

public interface ITestBook
{
    [Cell]                 double Input { get; set; }
    [Cell]                 double Double { get; }                 // formula =Input*2
    [Cell("Sheet1!A5")]    string? Label { get; set; }
    [Table("Holdings")]    ITableList<IHolding> Holdings { get; }
}

// usage
var actions = new Actions();
var wb = WorkbookFactory.Create<ITestBook>(bytes, actions);

wb.Input = 5;                                  // -> CellWrite
wb.Holdings[0].BidPrice = 30.5;                // -> TableRowUpdate (positional)
wb.Holdings.Add(h =>                           // -> TableAppend
{
    h.Ticker = "NVDA";
    h.BidPrice = 1100;
    h.Quantity = 10;
});
wb.Holdings[^1].Quantity = 11;                 // folds into the pending TableAppend
wb.Holdings.RemoveAt(2);                       // -> TableDelete (tables only)

await new GraphLoadActor(graph).ApplyAsync(item, actions);

Setup

You need three things: an Azure AD app registration, a test workbook on OneDrive, and a populated appsettings.Local.json.

1. Register an Azure AD app (app-only client-credentials auth)

This is the work/school path. App-only auth has no user context; the app authenticates as itself with a client secret. Requires tenant admin to grant consent.

1. https://entra.microsoft.com -> Applications -> App registrations -> New registration.
2. Name it (e.g. ExcelBridgeNext POC). Supported account types: "Accounts in this organizational directory only".
3. Skip the redirect URI (not used for client-credentials).
4. Register, then note Application (client) ID and Directory (tenant) ID.
5. API permissions -> Add a permission -> Microsoft Graph -> Application permissions:
   • Files.ReadWrite.All (Remove any previously-added Delegated permissions if you want; not required.)
6. Click Grant admin consent for [tenant] and confirm. The Status column should show a green check. This requires a tenant admin — if you aren't one, ask an admin to do this step.
7. Certificates & secrets -> Client secrets -> New client secret:
   • Description: e.g. POC
   • Expires: e.g. 6 months
   • Click Add
   • Immediately copy the Value column (not the Secret ID). It is shown once and never again.

Production note: Files.ReadWrite.All (Application) grants access to every user's files in the tenant. For production switch to Sites.Selected + per-site grants. Fine for the POC.

2. Create the test workbook

Create ebn-test.xlsx in your OneDrive. Add:

Scalar named ranges:

1. Pick a cell (say B2), type 0. Select it; in the Name Box (top-left) type Input -> Enter.
2. Pick another cell (say B3), type =Input*2. Name it Double.

A Holdings Excel Table:

3. Somewhere on Sheet1 (say starting at D1), enter the headers in a row:

   Ticker | BidPrice | Qty
   

4. Add at least one data row, e.g.:

   AAPL   | 175      | 5
   

5. Select the whole block including header (D1:F2), press Ctrl+T (Cmd+T on Mac), confirm My table has headers = Yes.
6. With the table selected, in the Table Design tab set Table Name = Holdings. (Default would be Table1.)
7. Save.

The minimum data row ensures Holdings.DataRange is non-null on first load. The POC handles an empty table too, but having a row to update makes the verification meaningful.

3. Find the DriveId and ItemId

App-only auth has no /me context, so you must supply both DriveId and ItemId explicitly.

Sign into Graph Explorer (https://developer.microsoft.com/graph/graph-explorer) as the owner of the workbook (your normal user, not the app). Then:

GET https://graph.microsoft.com/v1.0/me/drive

Copy the id field from the response -> that's your DriveId.

GET https://graph.microsoft.com/v1.0/me/drive/root:/ebn-test.xlsx

Copy the id field -> that's your ItemId. (If the file is in a subfolder, adjust the path, e.g. /me/drive/root:/Documents/ebn-test.xlsx.)

For SharePoint: replace /me/drive with /sites/{site-id}/drive to find that library's drive id.

4. Fill config

cp src/ExcelBridgeNext.Poc/appsettings.Local.json.sample src/ExcelBridgeNext.Poc/appsettings.Local.json

Edit it:

{
  "ExcelBridge": {
    "ClientId": "<client-id-from-step-1>",
    "TenantId": "<tenant-guid-from-step-1>",
    "ClientSecret": "<the-secret-Value-you-copied>",
    "DriveId": "<drive-id-from-step-3>",
    "ItemId": "<item-id-from-step-3>"
  }
}

• ClientSecret: the Value column from Certificates & secrets, not the Secret ID.
• DriveId is required for app-only auth (no /me context).

appsettings.Local.json is gitignored.

5. Run

dotnet run --project src/ExcelBridgeNext.Poc

No browser; the app authenticates itself via client credentials and acquires a token directly from Azure AD.

Expected end:

PASS - formula recalculated AND Holdings append (with pending-row update) landed correctly.

What this POC proves

• Interface + attribute + DispatchProxy API shape (wb.Input = 5, wb.Holdings.Add(...)).
• Setter on a scalar property emits CellWrite. Setter on an existing table row emits TableRowUpdate. Add emits TableAppend.
• Pending-row folding: mutations on a just-appended row update the pending TableAppend.Values in place, no orphan TableRowUpdate referencing an unstable index.
• Named-range convention + [Cell] / [Table] / [Column] overrides.
• Actions round-trips through STJ with $kind polymorphic discriminator (cross-actor transport ready).
• Excel Online recalculates formulas server-side when writes land via a workbook session with persistChanges=true.
• Graph's tables/{name}/rows/add endpoint resolves server-side row positions (concurrent appends safe by design).

Known gaps (deliberate v2 scope)

• Tables only. Range-backed lists throw at EnsureTable. v2.1 will add a range fallback (single-writer-only).
• No per-row PATCH coalescing. Multiple updates to the same row produce multiple Graph calls; coalescing to one range PATCH is a v2.1 optimization.
• No retry/backoff on Graph 429s.
• No eTag/conflict detection on the workbook itself (table appends are race-free server-side; cell writes and table row updates are not).
• Client secret in appsettings.Local.json. Fine for dev; for prod move to a managed-identity-or-Key-Vault-resolved configuration, or switch to certificate-based auth.
• Files.ReadWrite.All is broader than ideal. For production switch to Sites.Selected.
• No tests. A dotnet test project should land before layering further features.

Next milestones

1. Range fallback for non-table lists (v2.1).
2. Per-row PATCH coalescing for multi-column updates on the same row.
3. Certificate-based auth instead of client secret; Key Vault / managed identity for prod.
4. Retry + throttling handler (HttpMessageHandler chain).
5. eTag on GET /content and If-Match on session close.
6. Integration into an ETL Transform/Load actor pipeline.