MiniExcel Documentation

To query data from specific sheets, first retrieve all sheet names using MiniExcel.GetSheetNames(path), then iterate through them using the sheetName parameter in the Query method.

var sheets = MiniExcel.GetSheetNames(path);
foreach (var sheet in sheets)
{
    Console.WriteLine($"sheet name : {sheet} ");
    var rows = MiniExcel.Query(path, useHeaderRow: true, sheetName: sheet);
    Console.WriteLine(rows);
}

MiniExcel supports filling Excel templates using syntax similar to Vue templates: {{variable_name}} for single values and {{collection_name.field_name}} for collections. Collections can be IEnumerable, DataTable, or DapperRow.

Basic Fill

Use MiniExcel.SaveAsByTemplate(path, templatePath, value) to fill a template using a POCO or a Dictionary<string, object>.

Collection Rendering

Use MiniExcel.Templaters.GetOpenXmlTemplater().ApplyTemplate(path, templatePath, value) to render collections. The first IEnumerable of the same column in the template serves as the basis for filling.

Complex and Grouped Data

• Complex Data: Supports nested objects and multiple collections.
• Grouped Data: Use the @group tag in the template to group rows. You can also use the @header tag to control header rendering within groups.

To create Excel or CSV documents, use an exporter. You can export collections of strong types, anonymous types, or IDictionary<string, object> collections.

Exporters support both synchronous and asynchronous operations.

// 1. Get an exporter
var exporter = MiniExcel.Exporters.GetOpenXmlExporter();
// or var exporter = MiniExcel.Exporters.GetCsvExporter();

// 2. Prepare data (Strong type, Anonymous type, or Dictionary)
var values = new[] 
{
    new { Column1 = "MiniExcel", Column2 = 1 },
    new { Column1 = "Github", Column2 = 2 }
};

// 3. Export
exporter.Export(outputPath, values);

// Or use asynchronous export
await exporter.ExportAsync(outputPath, values);

To handle large datasets or dynamic logic (like i18n or role-based permissions) without loading everything into memory, return an IEnumerable<Dictionary<string, object>> using the yield return keyword. This allows MiniExcel.SaveAs to stream the data directly to the file.

private IEnumerable<Dictionary<string, object>> GetOrders(string lang, string role, Order[] orders)
{
    foreach (var order in orders)
    {
        var newOrder = new Dictionary<string, object>();
        // ... logic to populate dictionary ...
        yield return newOrder;
    }
}

// Usage
MiniExcel.SaveAs(path, GetOrders(lang, role, value));

You can use @if, @elseif, @else, and @endif statements inside template cells to implement conditional logic.

Rules for statements:

1. Each statement must be on a new line.
2. Add a single space before and after operators.
3. No new lines allowed inside a single statement.
4. Supported Types & Operators:
   • DateTime, double, int: Supports ==, !=, >, >=, <, <=.
   • string: Supports ==, !=.

Example Template Syntax:

@if(name == Jack)
{{employees.name}}
@elseif(name == Neo)
Test {{employees.name}}
@else
{{employees.department}}
@endif

While MiniExcel attempts to detect if a file is .xlsx or .csv via the extension, it may be inaccurate (especially with Streams). You should manually specify the excelType using ExcelType.CSV or ExcelType.XLSX when calling SaveAs or Query.

stream.SaveAs(excelType:ExcelType.CSV);
//or
stream.SaveAs(excelType:ExcelType.XLSX);
//or
stream.Query(excelType:ExcelType.CSV);
//or
stream.Query(excelType:ExcelType.XLSX);

You can embed conditional logic directly within template cells using @if, @elseif, and @else syntax.

Rules:

1. Supports DateTime, Double, and Int with ==, !=, >, >=, <, <= operators.
2. Supports String with ==, != operators.
3. Each statement must be on a new line.
4. Add a single space before and after operators.
5. Do not use new lines inside a single statement.
6. The cell must follow the exact format shown below.

@if(name == Jack)
{{employees.name}}
@elseif(name == Neo)
Test {{employees.name}}
@else
{{employees.department}}
@endif

The SaveAsByTemplate method allows you to fill data into an existing .xlsx template.

Key features include:

• Support for various data sources: Works with IEnumerable<IDictionary<string, object>>, DapperRows, or DataTable (v0.13.1).
• Template Formulas: Supports template formulas (v1.33.0).
• Conditional Formatting: Supports conditional formatting within the Excel template (v1.40.0).
• Parameter Handling: By default, it ignores missing parameter keys in the template, but this can be controlled via OpenXmlConfiguration.IgnoreTemplateParameterMissing (v1.24.0).

To avoid processing empty rows (often caused by accidental trailing whitespace in Excel), you can filter the IEnumerable results by checking if any key in the row dictionary contains a non-null value.

public static IEnumerable<dynamic> QueryWithoutEmptyRow(Stream stream, bool useHeaderRow, string sheetName, ExcelType excelType, string startCell, IConfiguration configuration)
{
    var rows = stream.Query(useHeaderRow, sheetName, excelType, startCell, configuration);
    foreach (IDictionary<string, object> row in rows)
    {
        if (row.Keys.Any(key => row[key] != null))
            yield return row;
    }
}

To include formulas in a template that dynamically adjust to the size of an enumerable, prefix the formula with $ and use $enumrowstart and $enumrowend as placeholders. When rendered, the $ is removed and the placeholders are replaced with the actual start and end row numbers.

Common patterns:

• Sum: $=SUM(C{{$enumrowstart}}:C{{$enumrowend}})
• Count: COUNT(C{{$enumrowstart}}:C{{$enumrowend}})
• Range: $=MAX(C{{$enumrowstart}}:C{{$enumrowend}}) - MIN(C{{$enumrowstart}}:C{{$enumrowend}})