DHI.Services.Spreadsheets.WebApi - Internal Developer Guide¶

Expose spreadsheet upload/update/query & range access over HTTP. This guide tracks the controller behavior in DHI.Services.Spreadsheets.WebApi exactly, so you can wire a host quickly and know what payloads to expect.

Hands-on sample host

1) What you get¶

Route base: api/spreadsheets/{connectionId} (API v1)
Controller: SpreadsheetsController
- Create/Update (POST, PUT)
- Read/Query (GET …/{id}, cell, range, usedrange, formats, count, lists)
- Delete (DELETE …/{id}, DELETE …/group/{group})
- Download native file (GET …/stream/{id})
JSON converters preconfigured for multidimensional arrays (object[,], double[,], CellFormat[,]) and Spreadsheet<string>.

The backing store & parsing logic come from the connection you bind (OpenXML/Excel, MCLite, Markdown, etc.). Pick the repo that fits your deployment; the HTTP surface stays the same.

2) DTOs & JSON (read this first)¶

2.1 SpreadsheetDTO (request model for POST/PUT)¶

public class SpreadsheetDTO
{
    [Required] public string FullName { get; set; }       // "group/path/name" (see §3)
    [Required] public IList<object[,]> Data { get; set; } // one 2D matrix per sheet; row 0 = headers
    public IList<string> SheetNames { get; set; }         // optional, becomes Metadata["SheetNames"]

    public Spreadsheet<string> ToSpreadsheet(); // splits FullName to Id/Name/Group
}

Data conventions

Each object[,] is rectangular.
Row 0 = header row (column names as strings).
Rows 1..N = data rows (values are int, double, bool, DateTime or string).

2.2 JSON converters used by the API¶

The package ships SerializerOptionsDefault.Options which includes:

MultidimensionalArrayConverterFactory (generic T[,])
SpreadsheetConverter<string>
TwoDimensionalArrayConverter<object/double/CellFormat>
DoubleConverter (writes 1.0 rather than 1 for doubles that look integer-like)
ObjectToInferredTypeConverter
NumberHandling = AllowReadingFromString

Your host should add these (see §7).

3) IDs, groups & path encoding¶

To safely place IDs and groups into URLs, the controller uses FullNameString.FromUrl(...):

Encode slashes / as | in URL segments. Example: projects/demo/parameters → projects|demo|parameters
Escape a literal | as ||.

Both {id} and {group} route parameters expect this encoding.

4) Cells, ranges & addressing¶

Cells are R1C1-style tokens but 0-based in this API:
- Example: R0C0 (first data row, first column)
Ranges are two cells separated by a comma:
- Example: R0C0,R10C3
Routes use semicolons (;) to attach these tokens:
- /api/spreadsheets/{conn}/{id}/{sheet}/cell;R0C0
- /api/spreadsheets/{conn}/{id}/{sheet}/range;R0C0,R10C3

If the token cannot be parsed, the controller throws ArgumentException (see §6 for error mapping).

5) Endpoints (quick matrix)¶

Base: /api/spreadsheets/{connectionId}

Method	Path	Query/body	Auth	Response	Notes
POST	`/`	`SpreadsheetDTO` (JSON)	`EditorsOnly`	`201 Created` + `Spreadsheet<string>`	Body `Data` is a list of 2D arrays; optional `SheetNames` stored in metadata.
PUT	`/`	`SpreadsheetDTO` (JSON)	`EditorsOnly`	`200 OK` + updated entity	Replaces the spreadsheet with same `FullName`.
DELETE	`/{id}`	-	`EditorsOnly`	`204 No Content`	`{id}` uses `\|` encoding.
DELETE	`/group/{group}`	-	`EditorsOnly`	`204 No Content`	Removes all spreadsheets under group.
GET	`/{id}`	-	`[Authorize]`	`Spreadsheet<string>`	Returns the entity (including `Data` if persisted by the repo).
GET	`/count`	-	`[Authorize]`	`int`	Total spreadsheets.
GET	`/fullnames`	`group=...` (optional)	`[Authorize]`	`string[]`	Full names (`group/name`) across repo or within a group.
GET	`/`	`group=...` (optional)	`[Authorize]`	`Spreadsheet<string>[]`	List all or by group.
GET	`/{id}/{sheet}/cell;{cell}`	-	`[Authorize]`	`object`	Single cell value.
GET	`/{id}/{sheet}/namedrange;{name}`	-	`[Authorize]`	`object[,]`	Named range values (provider-dependent).
GET	`/{id}/{sheet}/range;{range}`	-	`[Authorize]`	`object[,]`	Arbitrary 2D range values.
GET	`/{id}/{sheet}/usedrange`	-	`[Authorize]`	`object[,]`	Entire used area for the sheet (header at `[0,*]`).
GET	`/{id}/{sheet}/formats/usedrange`	-	`[Authorize]`	`CellFormat[,]`	Format hints for the used range.
GET	`/stream/{id}`	-	`[Authorize]`	File stream	Excel only: content types `xlsx` or `xls`. Other file types throw `NotSupportedException`.

Streaming: The controller maps xlsx → application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, xls → application/vnd.ms-excel. Any other fileType returned by your repository will cause a 500 (unsupported). Use the OpenXML/Excel repo for this endpoint.

6) Errors & status codes¶

The controller largely throws and expects your host’s exception middleware to translate:

KeyNotFoundException → 404 (missing spreadsheet/sheet)
ArgumentException (bad cell/range token, etc.) → 400
NotSupportedException (unsupported file type in /stream) → 500

We recommend the DHI exception mapping middleware (UseExceptionHandlingWithLogging()), which the sample host uses (see §7).

7) Minimal host wiring¶

Below is a trimmed Program.cs showing MVC, versioning, Swagger, auth, and JSON converter setup.

var builder = WebApplication.CreateBuilder(args);

// 1) MVC + versioning + Swagger
builder.Services.AddControllers()
    .AddJsonOptions(o =>
    {
        // Use the package defaults (multidimensional arrays, spreadsheet converter, inferred types, etc.)
        foreach (var c in DHI.Services.Spreadsheets.WebApi.SerializerOptionsDefault.Options.Converters)
            o.JsonSerializerOptions.Converters.Add(c);

        o.JsonSerializerOptions.DefaultIgnoreCondition =
            DHI.Services.Spreadsheets.WebApi.SerializerOptionsDefault.Options.DefaultIgnoreCondition;
        o.JsonSerializerOptions.NumberHandling =
            DHI.Services.Spreadsheets.WebApi.SerializerOptionsDefault.Options.NumberHandling;
    });

builder.Services.AddApiVersioning();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// 2) AuthN/AuthZ (sample)
builder.Services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme).AddJwtBearer();
builder.Services.AddAuthorization(options =>
{
    options.AddPolicy("EditorsOnly",
        p => p.RequireClaim(System.Security.Claims.ClaimTypes.GroupSid, "Editors"));
});

// 3) Optional: set [AppData] for your process (handy for file providers)
AppDomain.CurrentDomain.SetData("DataDirectory",
    Path.Combine(builder.Environment.ContentRootPath, "App_Data"));

var app = builder.Build();

// 4) Centralized exception mapping
app.UseExceptionHandlingWithLogging();

app.UseSwagger(); app.UseSwaggerUI();

app.UseRouting();
app.UseAuthentication();
app.UseAuthorization();
app.MapControllers();

// 5) Register at least one spreadsheet service (see §8)
ServiceLocator.Register(
    new SpreadsheetService(
        new DHI.Services.Provider.OpenXML.SpreadsheetRepository("[AppData]".Resolve())),
    "xlsx"
);

app.Run();

8) Wiring services¶

You can bind services manually (direct registration) or via the Connections module.

8.1 Manual (ServiceLocator)¶

Pick a repository and register a service with an ID (that ID becomes {connectionId} in your URLs):

// OpenXML/Excel repository (files under [AppData])
var repo = new DHI.Services.Provider.OpenXML.SpreadsheetRepository("[AppData]".Resolve());
var svc  = new SpreadsheetService(repo);
ServiceLocator.Register(svc, "xlsx");

8.2 Connections module (config-driven)¶

Drop entries like this into your connections.json (or equivalent), then resolve by {connectionId}:

{
  "$type": "System.Collections.Generic.Dictionary`2[[System.String, mscorlib],[DHI.Services.IConnection, DHI.Services]], mscorlib",
  "mc-ws1-sps": {
    "$type": "DHI.Services.Spreadsheets.WebApi.SpreadsheetServiceConnection, DHI.Services.Spreadsheets.WebApi",
    "ConnectionString": "database=mc2014.2",
    "RepositoryType": "DHI.Services.Provider.MCLite.SpreadsheetRepository, DHI.Services.Provider.MCLite",
    "Name": "Local MC spreadsheet connection to workspace1",
    "Id": "mc-ws1-sps"
  },
  "xlsx": {
    "$type": "DHI.Services.Spreadsheets.WebApi.SpreadsheetServiceConnection, DHI.Services.Spreadsheets.WebApi",
    "ConnectionString": "[AppData]",
    "RepositoryType": "DHI.Services.Provider.OpenXML.SpreadsheetRepository, DHI.Services.Provider.OpenXML",
    "Name": "spreadsheet connection to Microsoft Excel worksheet repository",
    "Id": "xlsx"
  },
  "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"
  }
}

The WebApi connection variant resolves [AppData] and [env:VAR] placeholders: new SpreadsheetServiceConnection(...).Create() returns a fully constructed SpreadsheetService.

9) Example requests¶

9.1 Create a spreadsheet (POST)¶

POST /api/spreadsheets/xlsx
Content-Type: application/json

{
  "fullName": "projects|demo|parameters",
  "sheetNames": ["parameters"],
  "data": [
    [
      ["Name","Value","Active"],
      ["Alpha",1.0,true],
      ["Beta",2.5,false]
    ]
  ]
}

201 Created → Location: /api/spreadsheets/xlsx/projects|demo|parameters

9.2 Get a cell¶

GET /api/spreadsheets/xlsx/projects|demo|parameters/parameters/cell;R0C0

200 OK

"Alpha"

9.3 Get a range¶

GET /api/spreadsheets/xlsx/projects|demo|parameters/parameters/range;R0C0,R1C1

200 OK

[
  ["Name","Value"],
  ["Alpha",1.0],
  ["Beta",2.5]
]

9.4 Download native file¶

GET /api/spreadsheets/xlsx/stream/projects|demo|parameters

200 OK with Content-Type application/vnd.openxmlformats-officedocument.spreadsheetml.sheet (for xlsx).

10) Security model (defaults)¶

Controller-level [Authorize] for reads.
POST/PUT/DELETE/DELETE group → EditorsOnly policy.
Bring your own JWT/claims setup and map appropriate groups/roles.

11) Common pitfalls¶

ID encoding: remember to replace / with | and escape | as || in URL segments.
Zero-based indices: R0C0 really is the first data row, first column.
Rectangular arrays: object[,] must be rectangular; JSON is arrays-of-arrays.
Streaming: only xls/xlsx are supported by the built-in controller. For Markdown or other file types, skip /stream and read data via the range endpoints.
Named ranges: supported only if the provider implements them (e.g., OpenXML). Markdown repo will throw NotSupportedException.

12) Quick reference¶

Need…	Use…	Notes
Create/update spreadsheets via JSON	`POST /` and `PUT /` with `SpreadsheetDTO`	`Data` = list of `object[,]` (row 0 headers)
Read cells / ranges / used area	`GET …/cell;R0C0`, `…/range;R0C0,R10C3`, `…/usedrange`	Zero-based indices; values are strongly typed (`int/double/bool/…`).
Format hints (Number/Text/DateTime/…)	`GET …/formats/usedrange`	Provider-dependent; OpenXML provides good coverage.
List & count	`GET /`, `GET /fullnames`, `GET /count`	`group` query optional.
Download native file	`GET /stream/{id}`	Only `xlsx`/`xls`.
Wire providers	ServiceLocator (manual) or Connections module (config)	See §8.
JSON serializers	`SerializerOptionsDefault.Options`	Adds multidimensional array + spreadsheet converters automatically.

That’s it - wire a provider, pick a {connectionId}, and you’re ready to POST sheets and query them with R1C1-style routes.