DHI.Services.MCLite for Spreadsheets — Internal Developer Guide¶

A database-backed spreadsheet repository for the Spreadsheets core & Web API. Instead of reading .xlsx files from disk, the MCLite provider stores Excel workbooks as blobs inside an MC/MCLite database (PostgreSQL, SQL Server, or SQLite) using SpreadsheetGear under the hood.

If you’re looking for the MC/Lite database primitives, see MCLite Providers.

What you get¶

A concrete repository: DHI.Services.Provider.MCLite.SpreadsheetRepository : BaseSpreadsheetRepository<string>, IGroupedUpdatableRepository
Works with SpreadsheetService and with the Web API controller you wired earlier.
Full support for:
- Groups (hierarchical folders) and group queries
- Cell, Range, UsedRange, Named ranges
- Type inference and format hints (Number/Text/DateTime/Boolean)
- Binary streaming of the native Excel file (xlsx / xls)
- Add/Update/Delete including RemoveByGroup
Transparent versioning & caching so reads are fast but immediately invalidated after updates.

How the MCLite repo stores spreadsheets¶

Each spreadsheet is persisted as a real Excel workbook (SpreadsheetGear) in the DB’s Blob table.
On read, we open a cached stream if the DB version GUID hasn’t changed; otherwise we refetch from DB.
On write, we build a workbook from your Spreadsheet<TId>.Data (list of object[,]) and optional SheetNames, save to a temp file, upload it in blocks, and bump the version.

Metadata populated

Metadata["SheetNames"] – all worksheet names (on reads)
Metadata["IsPublic"] – if column exists in your schema
Metadata["MetaData"] – parsed XML metadata (dictionary) if present

A signed SpreadsheetGear license is applied internally; you don’t need to configure licensing.

Connection strings¶

Create the repo with a single connection string:

var repo = new DHI.Services.Provider.MCLite.SpreadsheetRepository(
    "database=mc2014.2;host=localhost;port=5432;username=dss_admin;password=secretdss_admin;workspace=workspace1;dbflavour=PostgreSQL");

Supported flavours: PostgreSQL (default), SqlServer, SQLite.

Recognized keys (case-insensitive):

Key	Default	Notes
`database`	required	DB name (PostgreSQL/SQL Server) or file path (SQLite).
`dbflavour`	`PostgreSQL`	`PostgreSQL` / `SqlServer` / `SQLite`.
`host`	`localhost`	Not used for SQLite.
`port`	`5432`	Not used for SQLite.
`username`	`dss_admin`	Not used for SQLite.
`password`	`secretdss_admin`	Not used for SQLite.
`workspace`	`workspace1`	Resolves the schema to use (via `master.workspace`).
`cachemode`	`All`	Provider-level feature cache mode (not critical for spreadsheets).

All other key/values are passed through to the underlying ADO.NET provider.

SQLite tip database= must be a valid file or an exception is thrown:

database=[AppData]MCSQLiteTest.sqlite;dbflavour=SQLite

Wiring the provider¶

Option A — Manual (ServiceLocator)¶

using DHI.Services;
using DHI.Services.Spreadsheets;

var repo = new DHI.Services.Provider.MCLite.SpreadsheetRepository(
    "database=[AppData]MCSQLiteTest.sqlite;dbflavour=SQLite");

var service = new SpreadsheetService(repo);

// the key "mclite-ws1-sps" becomes your {connectionId}
ServiceLocator.Register(service, "mclite-ws1-sps");

A tidy pattern you can copy:

var spreadsheetRepository = new DHI.Services.Provider.MCLite.SpreadsheetRepository("database=mc2014.2;dbflavour=PostgreSQL");
var spreadsheetService    = new SpreadsheetService(spreadsheetRepository);
ServiceLocator.Register(spreadsheetService, "mc-ws1-sps");

Option B — Connections module (config-driven)¶

Add this entry to your connections.json (or equivalent):

"mclite-ws1-sps": {
  "$type": "DHI.Services.Spreadsheets.WebApi.SpreadsheetServiceConnection, DHI.Services.Spreadsheets.WebApi",
  "ConnectionString": "database=[AppData]MCSQLiteTest.sqlite;dbflavour=SQLite",
  "RepositoryType": "DHI.Services.Provider.MCLite.SpreadsheetRepository, DHI.Services.Provider.MCLite",
  "Name": "Local MC Lite spreadsheet connection to workspace1",
  "Id": "mclite-ws1-sps"
}

Your Web API routes will then use mclite-ws1-sps as {connectionId}.

Using the service¶

Create or update from in-memory data¶

var sheet = new object[,]
{
    { "Name", "Value", "When" },      // header row
    { "Alpha", 1.0,   DateTime.UtcNow },
    { "Beta",  2.5,   DateTime.UtcNow }
};

var sps = new Spreadsheet<string>("/projects/demo/parameters", "parameters", "/projects/demo");
sps.Data.Add(sheet);
sps.Metadata["SheetNames"] = new List<string> { "parameters" };

var service = ServiceLocator.Get<SpreadsheetService>("mclite-ws1-sps");
service.Add(sps);              // creates if not exists
// service.Update(sps);        // replaces if exists

Read ranges & cells¶

var used = service.GetUsedRange("/projects/demo/parameters", "parameters");
// object[,] with header at [0,*]

var cell = service.GetCellValue("/projects/demo/parameters", "parameters", new Cell(1, 2));
// DateTime if the underlying cell is formatted as date/time in Excel

var sub = service.GetRange("/projects/demo/parameters", "parameters",
                           new Range(new Cell(0,0), new Cell(1,1))); // header + first data row

var formats = service.GetUsedRangeFormats("/projects/demo/parameters", "parameters");
// CellFormat[,] with Number/Text/DateTime/Boolean

Named ranges (provider supported)¶

var nr = service.GetNamedRange("/projects/demo/parameters", "parameters", "MyNamedRange");

Stream the workbook (for downloads)¶

var (stream, fileType, fileName) = service.GetStream("/projects/demo/parameters");
// fileType: "xlsx" or "xls"; fileName: "parameters"

Groups & queries¶

Spreadsheets live under hierarchical groups (/group/subgroup/name). The repo supports several tree modifiers on group paths:

Recursive (default) — include all descendants.
;nonrecursive — only the direct group (no descendants).
;groupsonly — list subgroups rather than spreadsheets (used by GetFullNames).
;nonrecursive;groupsonly — both modifiers.

Examples:

// All spreadsheets under /workspace1 (recursively)
var all = service.GetByGroup("/workspace1");

// Only direct children of /workspace1
var direct = service.GetByGroup("/workspace1;nonrecursive");

// Group existence checks with the same modifiers
var exists = service.ContainsGroup("/workspace1;nonrecursive");

To mass-delete:

// Remove all spreadsheets in the group tree and then delete the groups
var repo = new DHI.Services.Provider.MCLite.SpreadsheetRepository("database=mc2014.2");
repo.RemoveByGroup("/workspace1");

Data & type behavior¶

Dates & times: When reading, any cell with ValueType.Number and a NumberFormatType of Date, DateTime, or Time is converted to a DateTime with workbook.NumberToDateTime(...).
UsedRange: We expand from $A$1 to the end of UsedRange so you always get a rectangular object[,] with the header row at [0,*].
Formats: GetUsedRangeFormats maps SpreadsheetGear types to CellFormat.Number | Text | DateTime | Boolean.

If a worksheet or named range doesn’t exist, the repo throws (the Web API maps that to a 404/400 depending on your middleware).

Interop with the Web API¶

Once registered (manually or via Connections), the same HTTP surface applies (see the Web API guide you created). For example:

POST /api/spreadsheets/mclite-ws1-sps – create/update from JSON
GET /api/spreadsheets/mclite-ws1-sps/{id}/{sheet}/usedrange – read data
GET /api/spreadsheets/mclite-ws1-sps/stream/{id} – download Excel (xlsx/xls)

Remember to URL-encode IDs: replace / with | and escape | as ||. Example: /projects/demo/parameters → projects|demo|parameters.

Troubleshooting & tips¶

SQLite file not found → ensure database= points to a real file (consider [AppData]).
Wrong sheet name → throws; check Metadata["SheetNames"] or inspect via Get/GetList.
Stale reads → the repo uses version GUIDs; calling Update/AddStream bumps the version and invalidates cache.
Named ranges → only if present in the workbook; otherwise you’ll get an exception.
Streaming content type → the Web API maps xlsx to application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, xls to application/vnd.ms-excel.

Appendix: quick wiring snippets¶

Manual (clean pattern)

var spreadsheetRepository = new DHI.Services.Provider.MCLite.SpreadsheetRepository(
    "database=mc2014.2;host=localhost;port=5432;username=dss_admin;password=secretdss_admin;workspace=workspace1;dbflavour=PostgreSQL");
var spreadsheetService    = new SpreadsheetService(spreadsheetRepository);
ServiceLocator.Register(spreadsheetService, "mc-ws1-sps");

Connections module (JSON)

"mclite-ws1-sps": {
  "$type": "DHI.Services.Spreadsheets.WebApi.SpreadsheetServiceConnection, DHI.Services.Spreadsheets.WebApi",
  "ConnectionString": "database=[AppData]MCSQLiteTest.sqlite;dbflavour=SQLite",
  "RepositoryType": "DHI.Services.Provider.MCLite.SpreadsheetRepository, DHI.Services.Provider.MCLite",
  "Name": "Local MC Lite spreadsheet connection to workspace1",
  "Id": "mclite-ws1-sps"
}

That’s everything you need to store Excel workbooks in MC/Lite and access them through the Spreadsheets service/Web API with strong typing and range semantics.