XLSight.Query 0.1.0

XLSight.Query

A streaming, single-pass query layer for XLSight: answer "sum of X by Y where Z" over a region of a sheet without materializing the sheet, without a database, and without adding any dependency beyond the core reader.

using XLSight;
using XLSight.Query;
using static XLSight.Query.QueryAggregates;

using var workbook = ExcelWorkbook.Open("sales.xlsx");

QueryResult result = workbook
    .QueryRange("Sheet1", "A6:F2410", headerRow: 6)    // headers from row 6
    .Where("Region", QueryOperator.Equals, "EMEA")     // AND-combined filters
    .GroupBy("Month")
    .Select(Sum("NetSales"), Count())
    .Execute();

foreach (QueryResultRow row in result.Rows)
{
    Console.WriteLine($"{row.Values[0]}: {row.Values[1]} ({row.Values[2]} rows)");
}

The same query can also be executed from the XLSight Query DSL when a host application or agent needs a portable text contract instead of compiled C#:

QueryResult result = workbook.ExecuteQuery("""
    FROM "Sheet1"!A6:F2410 HEADER ROW 6
    SELECT SUM(NetSales), COUNT()
    WHERE Region = "EMEA"
    GROUP BY Month
    """);

What it does

• Single pass, bounded memory. Filters, group-by, and aggregates are fused into one scan over borrowed rows; memory scales with group cardinality, never row count.
• Operators: Where (Equals/NotEquals/LessThan/LessThanOrEqual/GreaterThan/ GreaterThanOrEqual over text, number, date, boolean literals), GroupBy (one column), Select (Sum/Count/Min/Max/Average), Take, and a DistinctValues(column, top) terminal returning frequency-ordered value counts for filter discovery.
• Row queries. Without aggregates, Execute() returns the matching rows; with a Take, the scan stops as soon as enough rows matched.
• Dirty data never throws. Cells that don't coerce to an aggregate's input type are skipped and reported per column in QueryResult.Unaggregatable, with sample row indices.
• Stats pruning. Pass AnalyzeSheet column profiles via WithStats(...): a numeric filter no value can satisfy returns an empty result without opening the sheet.
• Guard rails. Group/distinct cardinality is capped (default 10,000, configurable via WithGroupLimit); exceeding it throws TooManyGroupsException instead of exhausting memory.
• Runtime Query DSL. ExecuteQuery(...) parses a fixed-order SQL-like DSL into a safe SheetQuerySpec, then executes it through the same row-oriented query engine.

Query DSL

Use the fluent API when writing .NET code directly. Use the DSL when queries need to cross a process, config, prompt, or tool boundary without compiling C#.

FROM "Sheet1"!A6:F2410 HEADER ROW 6
SELECT *
WHERE Region = "EMEA" AND Units > 10
LIMIT 100

FROM "Sheet1"!A6:F2410 HEADER ROW 6
SELECT SUM(NetSales), COUNT()
WHERE Region = "EMEA" AND Units > 10
GROUP BY Month
LIMIT 100

Supported DSL features:

• FROM <sheet>!<bounded-range> with bare or quoted sheet names.
• HEADER AUTO or HEADER ROW <number>.
• SELECT * for row results.
• SELECT COUNT(), SUM(column), AVG(column), MIN(column), MAX(column) for aggregate results.
• WHERE predicates joined by AND, using =, !=, <, <=, >, >=.
• Text, number, DATE "yyyy-MM-dd", and boolean literals.
• One GROUP BY column.
• Optional positive integer LIMIT.

For lower-level host validation, parse without executing:

SheetQuerySpec spec = SheetQuerySpec.Parse(queryText);
QueryResult result = workbook.ExecuteQuery(spec);

HEADER COLUMN is reserved for transposed tables. The parser recognizes it, but execution rejects it until the engine has a dedicated transposed scan strategy.

Using with AI agents

The Query DSL is designed to be the interface between an agent and an Excel file. The agent receives a bounded, read-only query grammar, no arbitrary code, no writes, no file system access beyond the single file, which makes it safe to expose as a tool without a code sandbox. The host validates and executes the DSL; the agent never touches the file directly.

A minimal tool set covers three operations: workbook discovery, sheet profiling, and querying. Wire them up with AIFunctionFactory.Create and register them with your agent:

using System.ComponentModel;
using XLSight;
using XLSight.Query;

// ── 1. workbook overview ───────────────────────────────────────────────────
[Description("List sheets and workbook-level metadata for an Excel file.")]
static string GetWorkbook(
    [Description("Absolute path to the .xlsx, .xlsm, or .xlsb file.")] string path)
{
    using var wb = ExcelWorkbook.Open(path);
    WorkbookInfo info = wb.Analyze(AnalysisLevel.Fast);
    // Reccomended: Don't naivly serialize the WorkbookInfo object to JSON
    // format it into something like a consise markdown with just what is needed
    // {Your own formatting function here}
    return FormatWorkbookInfo(info);
}

// ── 2. sheet profile ────────────────────────────────────────────────────────
[Description(
    "Profile one sheet: column names, dominant types, value ranges, and (for low-cardinality " +
    "columns) the exact distinct values. Call this before querying to discover column names " +
    "and filter values.")]
static string GetSheetOverview(
    [Description("Absolute path to the file.")] string path,
    [Description("Exact sheet name.")] string sheet)
{
    using var wb = ExcelWorkbook.Open(path);
    SheetInfo info = wb.AnalyzeSheet(sheet, AnalysisLevel.Full);
    // {Your own formatting function here}
    return FormatSheetInfo(info);
}

// ── 3. query ────────────────────────────────────────────────────────────────
[Description(
    "Run a read-only query against one sheet using the XLSight Query DSL. " +
    "Returns aggregate or row results. Requires column names — call GetSheetOverview first.")]
static string QuerySheet(
    [Description("Absolute path to the file.")] string path,
    [Description(
        "XLSight Query DSL. Examples:\n" +
        "  FROM Sales!A1:F500 HEADER AUTO SELECT SUM(Revenue), COUNT() WHERE Region = \"EMEA\" GROUP BY Month\n" +
        "  FROM Sheet1!A1:D200 HEADER ROW 1 SELECT * WHERE Status = \"Open\" LIMIT 50")]
    string query)
{
    using var wb = ExcelWorkbook.Open(path);
    QueryResult result = wb.ExecuteQuery(query);
    // convert result.Rows / result.Unaggregatable to a string the model can read
    // {Your own formatting function here}
    return FormatQueryResult(result);
}

Register the tools and run the agent loop with your chosen provider:

using Microsoft.Agents.AI;
using Microsoft.Extensions.AI;

IList<AITool> tools =
[
    AIFunctionFactory.Create(GetWorkbook),
    AIFunctionFactory.Create(GetSheetOverview),
    AIFunctionFactory.Create(QuerySheet),
];

// pass tools to your IChatClient / AIAgent as usual

Stats pruning (optional optimisation). If GetSheetOverview has already been called, pass the column profiles into the query via WithStats(...) on the fluent API. A numeric filter that no value in the profiled min/max range can satisfy returns an empty result without opening the sheet at all:

SheetInfo info = wb.AnalyzeSheet(sheet, AnalysisLevel.Full);

QueryResult result = wb
    .QueryRange(sheet, "A1:F500")
    .Where("Units", QueryOperator.GreaterThan, 1000)
    .Select(Sum("Revenue"))
    .WithStats(info.Columns!)   // skips the scan when no column value can match
    .Execute();

For filter discovery beyond what GetSheetOverview returns, add a fourth tool that calls QueryRange(...).DistinctValues("ColumnName") and returns the top-N values with their frequencies.