Spreadsheets Providers – Guide & Recipes

A compact guide to wire spreadsheets into your service — DB-backed or file-based — with the same service API.

---

See also (deep dives)

• Spreadsheets MCLite
• Spreadsheets OpenXML

---

Which provider should I use?

Provider	Use when	Storage	Creates storage?	Highlights
MCLite (DHI.Services.Provider.MCLite.SpreadsheetRepository)	You want spreadsheets inside MC/Lite DB (PG/SQL Server/SQLite)	Blob table in MC/Lite workspace	Uses existing MCLite DB	Groups, Named ranges, UsedRange, formats, stream xlsx/xls, RemoveByGroup, smart versioning cache
OpenXML (DHI.Services.Provider.OpenXML.SpreadsheetRepository)	You want .xlsx files on disk	File system	N/A	Simple, durable .xlsx, same service surface, FileShare.ReadWrite

Both plug into SpreadsheetService, so your app/web API code doesn’t change when you switch.

---

5-minute wiring

A) MCLite (DB-backed workbooks)

using DHI.Services;
using DHI.Services.Spreadsheets;
using DHI.Services.Provider.MCLite;

// One connection string; workspace resolves to a schema via master.workspace
var repo = new SpreadsheetRepository(
  "database=mc2014.2;host=localhost;port=5432;username=dss_admin;password=secretdss_admin;workspace=workspace1;dbflavour=PostgreSQL");

var svc = new SpreadsheetService(repo);
ServiceLocator.Register(svc, "mclite-ws1-sps"); // {connectionId} for Web API

Good to know

• Stores real Excel (via SpreadsheetGear) as blobs.
• Version GUID invalidates the internal stream cache on updates.
• Metadata auto-populated: SheetNames, optional IsPublic, parsed XML MetaData.

---

B) OpenXML (filesystem)

using DHI.Services;
using DHI.Services.Spreadsheets;
using DHI.Services.Provider.OpenXML;

var repo = new DHI.Services.Provider.OpenXML.SpreadsheetRepository("[AppData]"); // root folder
var svc  = new SpreadsheetService(repo);
ServiceLocator.Register(svc, "openxml-sps");

ID model

• IDs include .xlsx (e.g., /reports/finance.xlsx).
• Group = folder path (e.g., /reports).

---

IDs, groups & routing (applies to both)

• A spreadsheet is identified by FullName: "/group/subgroup/name" (OpenXML: include .xlsx).
• Groups are hierarchical; GetByGroup(group) is recursive by default.
• Tree modifiers on group:
  • ;nonrecursive → only direct children
  • ;groupsonly → list subgroups instead of spreadsheets
  • Combine as ;nonrecursive;groupsonly
• Web API ID encoding: / → | and escape | as ||. Example: /projects/demo/parameters → projects|demo|parameters.

---

Common tasks

Create / update from in-memory data

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

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

var svc = ServiceLocator.Get<SpreadsheetService>("mclite-ws1-sps");
svc.Add(sps);     // create (or svc.Update(sps) to replace)

Read cells, ranges, used range, formats

var used   = svc.GetUsedRange("/projects/demo/parameters", "parameters").Value;  // object[,]
var cell   = svc.GetCellValue("/projects/demo/parameters", "parameters", new Cell(1, 2)).Value; // typed
var sub    = svc.GetRange("/projects/demo/parameters", "parameters",
                          new Range(new Cell(0,0), new Cell(1,1))).Value;       // object[,]
var fmts   = svc.GetUsedRangeFormats("/projects/demo/parameters", "parameters").Value; // CellFormat[,]

Named ranges

var named = svc.GetNamedRange("/projects/demo/parameters", "parameters", "MyNamedRange").Value; // object[,]

Download the workbook

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

Group queries & cleanup

var allInTree   = svc.GetByGroup("/workspace1");
var directOnly  = svc.GetByGroup("/workspace1;nonrecursive");
var hasChildren = svc.ContainsGroup("/workspace1;groupsonly");

// MCLite-only: bulk delete by group tree
var mcliteRepo = (DHI.Services.Provider.MCLite.SpreadsheetRepository)svc.Repository;
mcliteRepo.RemoveByGroup("/workspace1");

---

Type & format behavior (at a glance)

• UsedRange returns a rectangular object[,] starting at A1; header row at [0,*].
• Dates/Times: cells with numeric value and a date/time number format are converted to DateTime.
• Formats: GetUsedRangeFormats returns CellFormat.Number | Text | DateTime | Boolean.
• Named ranges: must exist; otherwise you’ll get an error (Web API → 4xx).

---

Web API pairing (same surface for both providers)

Register your service with a {connectionId}, then call:

POST /api/spreadsheets/{connectionId}
GET  /api/spreadsheets/{connectionId}/{id}/{sheet}/usedrange
GET  /api/spreadsheets/{connectionId}/stream/{id}

Example (MCLite via Connections module)

"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"
}

Example (OpenXML via Connections module)

"openxml-sps": {
  "$type": "DHI.Services.Spreadsheets.WebApi.SpreadsheetServiceConnection, DHI.Services.Spreadsheets.WebApi",
  "ConnectionString": "[AppData]",
  "RepositoryType": "DHI.Services.Provider.OpenXML.SpreadsheetRepository, DHI.Services.Provider.OpenXML",
  "Name": "OpenXML spreadsheets",
  "Id": "openxml-sps"
}

---

Provider-specific notes & foot-guns

MCLite

• Connection string keys: database (req), workspace (schema), dbflavour=PostgreSQL|SqlServer|SQLite, host/port/username/password.
• SQLite: database= must point to an existing file, or you’ll get an exception.
• Versioning cache: reads are fast; any write bumps version and invalidates cache.
• Throws on missing worksheet / named range (check Metadata["SheetNames"]).

OpenXML

• IDs must include .xlsx.
• Sheet name lookup is case-insensitive.
• GetUsedRange trims trailing empty rows/columns.
• Helpers available for quick writes: DataSet.SaveAsXlsx(...), "Name".AsValidSheetName("_").

---

Quick reference

Need…	Use…	Notes
Store Excel in DB	MCLite repo	Real workbooks as blobs; fast reads via version cache
Keep files on disk	OpenXML repo	.xlsx only; simple and durable
Read row/col block	GetRange / GetUsedRange	Typed object[,]
Cell & format info	GetCellValue / GetUsedRangeFormats	Dates recognized by number format
Named ranges	GetNamedRange	Throws if missing
Download XLSX/XLS	GetStream(id)	xlsx / xls
Group tree ops	GetByGroup, ContainsGroup, RemoveByGroup	Tree modifiers: ;nonrecursive, ;groupsonly
Web API IDs	Encode / as |, escape |as| |	Same for both providers

---

Bottom line: Pick MCLite to centralize spreadsheets in your MC/Lite database with instant caching and group tooling; pick OpenXML when plain .xlsx files on disk are the goal. Either way, you get the same ergonomic SpreadsheetService API and Web API surface.