Download the PHP package wpdev/phpspreadsheet-odata without Composer

phpspreadsheet-odata

Framework-agnostic OData v4 read-only feed for PhpSpreadsheet workbooks.

Each worksheet becomes an OData entity set. The first row defines property names; remaining rows are exposed as entities with a synthetic RowIndex key.

Requirements

• PHP >= 7.4
• Composer

Installation

Quick Start

Run the included example:

Then open:

• Feed index: http://localhost:8080/odata
• Metadata: http://localhost:8080/odata/employees/$metadata
• Entity set: http://localhost:8080/odata/employees/Employees
• Single entity: http://localhost:8080/odata/employees/Employees(1)

Supported Endpoints

Single feed (Phase 1 — backward compatible)

Pass a Spreadsheet directly to ODataServer:

Multi-feed / multi-tenant (Phase 2)

Inject a FeedResolverInterface to serve many feeds from one endpoint:

feedId must be URL-safe ([A-Za-z0-9_-]+).

Feed Resolution

The core package is database-agnostic. Provide a FeedResolverInterface to map feedId → Spreadsheet:

Optional PDO-backed resolver

For database-backed feeds, use PdoFeedResolver with an injected PDO and a loader callback. The resolver only looks up a source_ref — loading the spreadsheet is your responsibility:

Authentication is checked before feed resolution: invalid auth → 401; valid auth + unknown feedId → 404.

Query Options

Supported filter operators: eq, ne, gt, lt, ge, le. Multiple conditions can be combined with and.

Authentication

Invalid or missing credentials return 401 Unauthorized with a WWW-Authenticate challenge header (Basic realm="OData" or Bearer realm="OData").

Excel credential prompt

When paired with wpdev/odata-feed, the workbook stores only the OData URL — no password in the file. On Data → Refresh All, Power Query requests $metadata first; a 401 with WWW-Authenticate: Basic triggers Excel's native username/password dialog. Credentials are saved in the OS credential store (Keychain on macOS), keyed by the data source URL.

If a feed was previously refreshed anonymously, clear or edit stored credentials via Data → Get Data → Data Source Settings → Edit Permissions.

Use HTTPS in production when enabling Basic auth.

Architecture

• ODataServer — main PSR-7 entry point
• FeedResolverInterface — resolves feedId to a Spreadsheet
• InMemoryFeedResolver — array-backed resolver for tests and small setups
• PdoFeedResolver — optional PDO example for DB-backed feed lookup
• EntitySetBuilder — maps worksheets to entities
• MetadataBuilder — generates $metadata EDMX
• QueryProcessor — applies OData query options
• ResponseFormatter — formats OData JSON responses
• Router — routes requests to handlers (with optional feedId segment)

Production deployment

• Set serviceRoot to the public URL of your OData endpoint (no trailing slash required).
• Enable authentication before exposing feeds to the internet.
• Use HTTPS via your web server or reverse proxy.
• For multi-tenant setups, implement FeedResolverInterface to load workbooks from your storage layer.
• Query limits are enforced: $top ≤ 1000, $skip ≤ 100000.

Development

License

GNU General Public License v2.0 or later — see LICENSE.