Skip to content

DHI.Services.OpenXML for Spreadsheets — Internal Developer Guide (Summary)

Storage provider for spreadsheets on disk using the Open XML SDK. For full details see the OpenXML Provider Guide.

When to use

  • You want simple, durable .xlsx files on disk as your backing store.
  • You need to read cells/ranges/used range/named ranges and write new workbooks.
  • You want to download the raw file via the Web API.

ID & group model

  • ID: relative path including .xlsx (e.g., /reports/finance.xlsx).
  • Group: subfolder (e.g., /reports → <root>/reports/). GetByGroup is recursive.

Common calls

// Create
var wb = new Spreadsheet<string>("/reports/finance.xlsx", "finance.xlsx", "/reports");
wb.Data = new List<object[,]> { new object[,] { { "A", 1 }, { "B", 2 } } };
wb.Metadata["SheetNames"] = new List<string> { "Summary" };
service.Add(wb);

// Read
var cell = service.GetCellValue("/reports/finance.xlsx", "Summary", new Cell(0,1)).Value;
var used = service.GetUsedRange("/reports/finance.xlsx", "Summary").Value;
var fmts = service.GetUsedRangeFormats("/reports/finance.xlsx", "Summary").Value;

// List & delete
var all     = service.GetAll();
var inGroup = service.GetByGroup("/reports");
service.Remove("/reports/finance.xlsx");

// Stream
var (ms, type, name) = service.GetStream("/reports/finance.xlsx");

Helpers (direct write):

var ds = new DataSet();
// ... fill DataTables ...
ds.SaveAsXlsx(Path.Combine(root, "quick/orders.xlsx"), includeFieldNames:true);

"Finance:Q1/2025".AsValidSheetName("_"); // safe worksheet name

Notes

  • .xlsx only (IDs must include the extension).
  • Sheet names are case-insensitive in lookups.
  • Values are typed (bool, int, double, DateTime, or string) based on Excel number formats (built-ins 14–22, 45–47, 49, etc.).
  • GetUsedRange trims trailing empty rows/columns.
  • Files are read with FileShare.ReadWrite.

Web API example

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