DHI.Services.Spreadsheets - Internal Developer Guide¶

This guide explains the Spreadsheets module in DHI.Services.Spreadsheets: what it does, the core types, how grouping & the data model work, how ranges/cells are addressed, how to use the service and repositories, and how to wire JSON converters so your models serialize cleanly.

1) What the Spreadsheets module does¶

At a glance:

Store and retrieve tabular sheets as domain entities (Spreadsheet<TId>).
Group spreadsheets (folder/tenant/etc.) and list by group.
Read cells, ranges, used ranges, and format hints from provider backends.
Upload/download raw spreadsheet files via streams (provider-dependent).
Swap persistence by changing the repository (Markdown files, Excel/OpenXML, cloud…).

The module follows the standard Domain Services pattern:

[Entity] Spreadsheet<TId>  ->  [Repository] ISpreadsheetRepository<TId>  ->  [Service] SpreadsheetService<TId>

2) Key concepts¶

2.1 Spreadsheet identity & grouping¶

Spreadsheet<TId> inherits BaseGroupedEntity<TId> so every spreadsheet has:

Id (TId) – stable key the repo uses to address the sheet (e.g., "projectA/tables/parameters").
Name (string) – display name of the spreadsheet (e.g., "parameters").
Group (string?) – logical folder/tenant; FullName = "{Group}/{Name}".
Metadata (IDictionary<string,object?>) and Permissions (from BaseEntity).

2.2 Data model (multi-sheet)¶

A spreadsheet’s tabular content lives in:

public List<object[,]> Data { get; }    // one 2D array per sheet

Each object[,] is a rectangular matrix.
Row 0 must be the header row (column names as strings).
Rows 1..N are the data rows (cells may be null).
Values are typed (int, double, bool, DateTime, or string) - JSON converters map them back and forth.

Don’t need to stuff all data in Data? You don’t have to. Some providers can read directly from the native file via GetRange/GetUsedRange without populating Data.

2.3 Sheets vs. Data vs. `Sheet`¶

Repositories speak in sheet names (e.g., "parameters"). In-memory, you can also convert a single object[,] into a more ergonomic row-object view using Sheet:

object[,] data = ...;           // [0,*] = headers, [1..,*] = row values
Sheet rows = data.ToSheet();    // extension method
var v = rows.GetValue(0, "Name");   // first data row’s value of "Name"

2.4 Cells, ranges & formats¶

Cell(row, col) and Range(upperLeft, lowerRight) are 0-based indices.
Rows/cols in Cell/Range refer to the data body, not the header row, unless a provider states otherwise.
CellFormat (Number, Text, DateTime, Boolean) is a hint that some providers can return for the used range.

Provider differences exist (e.g., Markdown vs. Excel). See the provider docs for exact behavior; the service keeps the signatures uniform.

3) Core types overview¶

3.1 Entity¶

[Serializable]
public class Spreadsheet<TId> : BaseGroupedEntity<TId>
{
    public Spreadsheet(TId id, string name, string group) : base(id, name, group) {}
    public List<object[,]> Data { get; } = new();   // optional, one entry per sheet
    public bool ShouldSerializeData() => Data.Count > 0;
}

3.2 Repository contract¶

public interface ISpreadsheetRepository<TId> :
    IRepository<Spreadsheet<TId>, TId>,
    IDiscreteRepository<Spreadsheet<TId>, TId>,
    IUpdatableRepository<Spreadsheet<TId>, TId>,
    IGroupedRepository<Spreadsheet<TId>>,
    IStreamableRepository<TId>
{
    // Sheet-centric reads
    Maybe<object>       GetCellValue(TId id, string sheetName, Cell cell, ClaimsPrincipal user = null);
    Maybe<object[,]>    GetNamedRange(TId id, string sheetName, string rangeName, ClaimsPrincipal user = null);
    Maybe<object[,]>    GetRange(TId id, string sheetName, Range range, ClaimsPrincipal user = null);
    Maybe<object[,]>    GetUsedRange(TId id, string sheetName, ClaimsPrincipal user = null);
    Maybe<CellFormat[,]>GetUsedRangeFormats(TId id, string sheetName, ClaimsPrincipal user = null);
    bool                ContainsSheet(TId id, string sheetName, ClaimsPrincipal user = null);

    // Raw file ingress/egress
    void AddStream(TId id, string name, string group, Stream stream, ClaimsPrincipal user = null);
    // from IStreamableRepository<TId>:
    (Maybe<Stream>, string fileType, string fileName) GetStream(TId id, ClaimsPrincipal user = null);
}

Base helper: BaseSpreadsheetRepository<TId> implements the discrete/group listing boilerplate you can inherit in file-based providers.

3.3 Service¶

public class SpreadsheetService<TId> :
    BaseGroupedUpdatableDiscreteService<Spreadsheet<TId>, TId>,
    ISpreadsheetService<TId>
{
    // Query helpers wrap Maybe<T> -> T? and throw where appropriate
    public object        GetCellValue(TId id, string sheet, Cell cell, ClaimsPrincipal user = null);
    public object[,]     GetNamedRange(TId id, string sheet, string rangeName, ClaimsPrincipal user = null);
    public object[,]     GetRange(TId id, string sheet, Range range, ClaimsPrincipal user = null);
    public object[,]     GetUsedRange(TId id, string sheet, ClaimsPrincipal user = null);
    public CellFormat[,] GetUsedRangeFormats(TId id, string sheet, ClaimsPrincipal user = null);
    public bool          SheetExists(TId id, string sheet, ClaimsPrincipal user = null);

    // Streams
    public void          AddStream(TId id, string name, string group, Stream stream, ClaimsPrincipal user = null);
    public (Stream stream, string fileType, string fileName) GetStream(TId id, ClaimsPrincipal user = null);

    // Provider discovery (plugin scanning)
    public static Type[] GetRepositoryTypes();
    public static Type[] GetRepositoryTypes(string path);
    public static Type[] GetRepositoryTypes(string path, string searchPattern);
}

Being a BaseGroupedUpdatableDiscreteService, you also get:

CRUD (Add, Update, Remove, AddOrUpdate), list (GetAll, GetByGroup, …), and Exists/Count.
Lifecycle events: Adding/Added, Updating/Updated, Deleting/Deleted, plus group variants (DeletingGroup/DeletedGroup).
Automatic stamping of Added / Updated when the entity implements ITraceableEntity<TId>.

3.4 JSON converters (important)¶

The module ships converters so spreadsheets serialize predictably via System.Text.Json:

TwoDimensionalArrayConverter<T> - serializes T[,] to JSON arrays of arrays and back.
DoubleConverter - writes numbers with a trailing .0 when they look like integers to avoid 1/1.0 instability.
SpreadsheetConverter<TId> - controls the JSON shape of Spreadsheet<TId> (Id/Name/Group/Data/Metadata/Permissions), using the converters above and core converters (PermissionConverter, ObjectToInferredTypeConverter).

Registering converters in your host:

builder.Services.AddControllers()
    .AddJsonOptions(o =>
    {
        o.JsonSerializerOptions.Converters.Add(new JsonStringEnumConverter());
        o.JsonSerializerOptions.Converters.Add(new DHI.Services.Spreadsheets.Converters.SpreadsheetConverter<string>());
        o.JsonSerializerOptions.Converters.Add(new DHI.Services.Spreadsheets.Converters.TwoDimensionalArrayConverter<object>());
        o.JsonSerializerOptions.Converters.Add(new DHI.Services.Spreadsheets.Converters.DoubleConverter());
        o.JsonSerializerOptions.Converters.Add(new DHI.Services.Converters.ObjectToInferredTypeConverter());
        o.JsonSerializerOptions.Converters.Add(new DHI.Services.Converters.PermissionConverter());
    });

JSON shape (example)

{
  "id": "projectA/tables/parameters",
  "name": "parameters",
  "group": "projectA/tables",
  "data": [
    [
      ["Name","Value","Active"],
      ["Alpha",1.0,true],
      ["Beta",2.5,false]
    ]
  ],
  "metadata": { "SheetNames": ["parameters"], "DefinedNames": ["values"] },
  "permissions": [ { "principals":["Editors"], "operation":"update", "type":"Allowed" } ]
}

4) Built-in file provider (Markdown) - quick peek¶

While providers get their own docs, there’s a lightweight Markdown provider inside this package that treats *.md tables as sheets:

Each heading becomes a sheet name.
The first table after a heading becomes the sheet’s data.
Markdown round-trips using MarkdownDocument + MarkdownParser/MarkdownBuilder.
Repository: DHI.Services.Spreadsheets.Markdown.MarkdownRepository (TId = string, IDs look like "group/file.md" without the .md is fine; it’s appended).

Use it for simple file-backed scenarios and tests; see provider doc for details.

5) Typical tasks (with code)¶

The snippets below use string IDs for simplicity. Swap in your provider and TId as needed.

5.1 Create & save a spreadsheet (in-memory data)¶

// Build the entity
var id    = "projects/demo/parameters";
var name  = "parameters";
var group = "projects/demo";
var s = new Spreadsheet<string>(id, name, group);

// one sheet called "parameters": header row + 2 data rows
s.Data.Add(new object[,]
{
    { "Name", "Value", "Active" },
    { "Alpha", 1.0, true },
    { "Beta",  2.5, false }
});

// Persist with your repository (example uses Markdown file repo)
ISpreadsheetRepository<string> repo = new DHI.Services.Spreadsheets.Markdown.MarkdownRepository(@"C:\AppData\md");
var svc = new SpreadsheetService<string>(repo);
svc.Add(s);

5.2 List, group, and fetch¶

foreach (var sheet in svc.GetAll())
    Console.WriteLine(sheet.FullName);

foreach (var sheet in svc.GetByGroup("projects/demo"))
    Console.WriteLine($"{sheet.Name}  ->  {sheet.Metadata.Count} meta items");

5.3 Read a cell / a range / the used range¶

// First data row, first column (0-based, data body only)
var cell = svc.GetCellValue(id, "parameters", new Cell(0, 0));  // "Alpha"

// A 2x2 body range (rows 0..1, cols 0..1)
var range = svc.GetRange(id, "parameters",
              new Range(new Cell(0,0), new Cell(1,1)));

// The whole table as a rectangular array with header row at [0,*]
var used = svc.GetUsedRange(id, "parameters");

// Optional format hints for the used range
var formats = svc.GetUsedRangeFormats(id, "parameters");

5.4 Work with rows as objects¶

object[,] data = svc.GetUsedRange(id, "parameters");
Sheet rows = data.ToSheet();

foreach (var row in rows)
    Console.WriteLine($"{row["Name"]} = {row["Value"]} (Active={row["Active"]})");

5.5 Upload/download native files (provider-specific)¶

// Add as raw file (e.g., an .md or .xlsx depending on the repo)
using var stream = File.OpenRead(@"C:\temp\parameters.md");
svc.AddStream("projects/demo/parameters", "parameters", "projects/demo", stream);

// Download for clients
var (fileStream, fileType, fileName) = svc.GetStream("projects/demo/parameters");
using var dest = File.Create($@"C:\out\{fileName}");
fileStream.CopyTo(dest);

5.6 Remove by group (group-aware repos)¶

// From BaseGroupedUpdatableDiscreteService: remove all sheets under a group
svc.RemoveByGroup("projects/demo");

6) Events & interception¶

Hook into lifecycle events to validate, veto, or audit:

svc.Adding += (_, e) =>
{
    var ss = e.Item;
    if (ss.Data.Count == 0) e.Cancel = true;  // require at least one sheet
};

svc.Updating += (_, e) =>
{
    var hasHeader = e.Item.Data.All(d => d.GetLength(0) >= 1);
    if (!hasHeader) e.Cancel = true;
};

Grouped variants:

DeletingGroup / DeletedGroup fire around RemoveByGroup(group).

7) Provider discovery & connections¶

7.1 Discover repository providers (plugins)¶

// Scan the current base directory for assemblies implementing ISpreadsheetRepository<string>
var types = SpreadsheetService<string>.GetRepositoryTypes(AppContext.BaseDirectory, "dhi*.dll");

Use this to populate UI drop-downs or to validate configuration.

7.2 Construct from a connection object¶

SpreadsheetServiceConnection<TId> lets you create services from config:

var conn = new SpreadsheetServiceConnection<string>("sheets-md", "Markdown sheets")
{
    RepositoryType = "DHI.Services.Spreadsheets.Markdown.MarkdownRepository, DHI.Services.Spreadsheets",
    ConnectionString = @"C:\AppData\md"
};
var svc = (ISpreadsheetService<string>)conn.Create();
ServiceLocator.Register(svc, conn.Id);

SpreadsheetServiceConnection<TId>.CreateConnectionType<TConnection>(path) is available to discover provider types for tooling (e.g., an admin UI).

8) Security & permissions¶

Entities carry Permissions : IList<Permission> and helpers like IsAllowed(ClaimsPrincipal, op).
All repository methods accept an optional ClaimsPrincipal user - providers may enforce access control based on it.
The core service does not perform AuthZ by default; enforce at the API layer or in your provider.

9) Guarding inputs (quick recap)¶

Use Guard.Against.* at module boundaries:

Null, NullOrEmpty, NullOrWhiteSpace, NegativeOrZero, etc.
Cell and Range ctors throw on negative indices.
SpreadsheetService.SheetExists(id, name) throws KeyNotFoundException if the spreadsheet itself is missing.

10) Errors & edge cases¶

SpreadsheetService.GetStream(id) throws KeyNotFoundException if the repo returns no stream.
Indices are 0-based. For Cell / Range, row/col refer to the data body (i.e., row 0 in Cell = first data row). Header handling is provider-specific when reading from native files; the JSON Dataalways encodes headers at [0,*].
Sheet throws if the header row contains duplicate column names.
TwoDimensionalArrayConverter<T> expects rectangular data. Keep your arrays rectangular.
DoubleConverter emits trailing .0 for integer-looking doubles (JSON stability). If your consumer relies on pure integers, store them as int not double.
Named ranges may be unsupported by some providers (e.g., Markdown throws NotSupportedException).

11) Quick reference¶

Need…	Use…	Notes
Store grouped tabular data	`Spreadsheet<TId>` with `group` + `name` + `Data`	One `object[,]` per sheet; row 0 is headers
Read a cell / range / used range	`SpreadsheetService.GetCellValue/GetRange/GetUsedRange`	Indices are 0-based; refer to the data body
List by group / full names	`GetByGroup(...)`, `GetFullNames(...)`	From `IGroupedRepository`
Upload/download native sheet files	`AddStream(...)` / `GetStream(...)`	File type/name come back with the stream
CRUD + events	`BaseGroupedUpdatableDiscreteService` methods + `Adding/Updating/...`	Events are cancelable (`CancelEventArgs<T>`)
JSON stability for 2D arrays	Register `TwoDimensionalArrayConverter<object>` + `DoubleConverter`	See §3.4
Pluggable storage	Implement `ISpreadsheetRepository<TId>` or use an existing provider	Discover via `SpreadsheetService.GetRepositoryTypes(...)`
Row-object view for a single sheet	`Sheet rows = data.ToSheet()`	`rows[i]["ColName"]`

12) Minimal wiring (in-process)¶

// Choose a repo (Markdown for local files; swap for Excel/OpenXML or cloud provider later)
ISpreadsheetRepository<string> repo = new DHI.Services.Spreadsheets.Markdown.MarkdownRepository(@"C:\AppData\md");
var svc = new SpreadsheetService<string>(repo);

// Register for lookups by id (used by Web API packages)
ServiceLocator.Register(svc, "sheets-file");

// Create and add a spreadsheet
var sheet = new Spreadsheet<string>("projects/demo/parameters", "parameters", "projects/demo");
sheet.Data.Add(new object[,] { { "Name","Value" }, { "Alpha", 1.0 }, { "Beta", 2.5 } });
svc.Add(sheet);

13) How the Markdown utils fit (only if you use the Markdown provider)¶

The Markdown sub-namespace provides:

MarkdownDocument - object model (headings, text blocks, tables).
MarkdownParser / MarkdownBuilder - parse/build .md text.
ExtensionMethods - map between MarkdownDocument and Spreadsheet<string> (sheet names come from headings; first table after a heading is the sheet).

You don’t need these for non-Markdown providers; they’re used internally by MarkdownRepository.