PanoramicData.SheetMagic 3.1.140

PanoramicData.SheetMagic

Easily save/load data to/from Excel (XLSX) documents using strongly-typed C# classes.

Requirements

• .NET 10.0 - This library targets .NET 10.0 only
• DocumentFormat.OpenXml 3.x - required from 3.1 onwards

Versioning

The major.minor prefix comes from version.json and the patch number is the Nerdbank.GitVersioning commit height. The prefix sat at 3.0 from 2020 to 2026 across 53 releases and is not used to signal API breaks - check the package dependencies and release notes rather than inferring compatibility from the version alone.

The height restarts from zero whenever the prefix changes, which would have dropped the patch number from 136 to 1 when the prefix moved to 3.1. versionHeightOffset in version.json compensates for that, so numbering continues from where the 3.0 series left off rather than restarting. Do not remove it: without it the next build would be numbered below releases that are already published.

One such change is worth calling out, because the package metadata states it but the version number does not: only one DocumentFormat.OpenXml assembly can load per process, and from 3.1 SheetMagic requires OpenXml 3.x. An application that must stay on OpenXml 2.x should remain on 3.0.136 or earlier.

Installation

dotnet add package PanoramicData.SheetMagic

Features

• ? Strongly-typed - Work with your own C# classes
• ? Simple API - Easy to read and write XLSX files
• ? Multiple sheets - Add and read multiple worksheets
• ? Styling support - Apply table styles to your data
• ? Extended properties - Support for dynamic properties via Extended<T>
• ? Streams and files - Work with both FileInfo and Stream objects
• ? Type safe - Full support for common .NET types including nullable types

Quick Start

Writing to a file

using PanoramicData.SheetMagic;

// Define your class
public class Thing
{
    public string PropertyA { get; set; }
    public int PropertyB { get; set; }
}

// Create some data
var things = new List<Thing>
{
    new Thing { PropertyA = "Value 1", PropertyB = 1 },
    new Thing { PropertyA = "Value 2", PropertyB = 2 },
};

// Write to Excel file
var fileInfo = new FileInfo($"Output {DateTime.UtcNow:yyyyMMddTHHmmss}Z.xlsx");
using var workbook = new MagicSpreadsheet(fileInfo);
workbook.AddSheet(things);
workbook.Save();

Reading from a file

using PanoramicData.SheetMagic;

// Read from Excel file
using var workbook = new MagicSpreadsheet(fileInfo);
workbook.Load();

// Read from default worksheet (first sheet)
var cars = workbook.GetList<Car>();

// Read from a specific worksheet by name
var animals = workbook.GetList<Animal>("Animals");

Advanced Features

Working with Streams

// Write to a stream
using var stream = new MemoryStream();
using (var workbook = new MagicSpreadsheet(stream))
{
    workbook.AddSheet(data);
    workbook.Save();
}

// Read from a stream
stream.Position = 0;
using var workbook = new MagicSpreadsheet(stream);
workbook.Load();
var items = workbook.GetList<MyClass>();

Multiple Sheets

using var workbook = new MagicSpreadsheet(fileInfo);
workbook.AddSheet(cars, "Cars");
workbook.AddSheet(animals, "Animals");
workbook.AddSheet(products, "Products");
workbook.Save();

Applying Table Styles

var options = new AddSheetOptions
{
    TableOptions = new TableOptions
    {
        Name = "MyTable",
        DisplayName = "MyTable1",
        XlsxTableStyle = XlsxTableStyle.TableStyleMedium2,
      ShowRowStripes = true,
        ShowColumnStripes = false,
        ShowFirstColumn = false,
   ShowLastColumn = false
    }
};

workbook.AddSheet(data, "StyledSheet", options);

Conditional Formatting

Conditional formatting is configured through AddSheetOptions.ConditionalFormats. The object model is intentionally close to Excel's own configuration model:

• ConditionalFormat selects one or more output columns, or all columns when ColumnNames is omitted.
• ConditionalFormatRule describes one Excel rule such as CellIs, ContainsBlanks, or ContainsErrors.
• ConditionalFormatStyle defines the differential format Excel applies when the rule matches.

ColumnNames must match the final header text written to Excel. If PropertyHeaders is set, use those values. Otherwise use the Description attribute value or the property name.

using System.Drawing;
using PanoramicData.SheetMagic;

var rows = new[]
{
    new ReportRow { Name = null, Description = "Missing name", Score = null },
    new ReportRow { Name = "Bravo", Description = "High score", Score = 9 },
    new ReportRow { Name = "Charlie", Description = null, Score = 4 }
}.ToList();

var options = new AddSheetOptions
{
    ConditionalFormats =
    [
        new ConditionalFormat
        {
            ColumnNames = ["Name", "Description"],
            Rules =
            [
                new ConditionalFormatRule
                {
                    RuleType = ConditionalFormatRuleType.ContainsBlanks,
                    Style = new ConditionalFormatStyle
                    {
                        BackgroundColor = Color.Red
                    }
                }
            ]
        },
        new ConditionalFormat
        {
            ColumnNames = ["Score"],
            Rules =
            [
                new ConditionalFormatRule
                {
                    RuleType = ConditionalFormatRuleType.ContainsBlanks,
                    Style = new ConditionalFormatStyle
                    {
                        BackgroundColor = Color.Red
                    }
                },
                new ConditionalFormatRule
                {
                    RuleType = ConditionalFormatRuleType.CellIs,
                    Operator = ConditionalFormatOperator.GreaterThan,
                    Formula = "5",
                    Style = new ConditionalFormatStyle
                    {
                        FontColor = Color.Green,
                        FontWeight = FontWeight.Bold
                    }
                }
            ]
        },
        new ConditionalFormat
        {
            Rules =
            [
                new ConditionalFormatRule
                {
                    RuleType = ConditionalFormatRuleType.ContainsErrors,
                    Style = new ConditionalFormatStyle
                    {
                        FontWeight = FontWeight.Bold
                    }
                }
            ]
        }
    ]
};

using var workbook = new MagicSpreadsheet(new FileInfo("ConditionalFormatting.xlsx"));
workbook.AddSheet(rows, "Report", options);
workbook.Save();

public sealed class ReportRow
{
    public string? Name { get; set; }

    public string? Description { get; set; }

    public int? Score { get; set; }
}

Supported rule types currently include:

• CellIs
• Expression
• ContainsBlanks
• NotContainsBlanks
• ContainsErrors
• NotContainsErrors
• ContainsText
• NotContainsText
• BeginsWith
• EndsWith
• DuplicateValues
• UniqueValues
• Top10
• AboveAverage

Custom Property Headers

Use the Description attribute to customize column headers:

using System.ComponentModel;

public class Employee
{
    public int Id { get; set; }
    
    [Description("Full Name")]
    public string Name { get; set; }
    
    [Description("Hire Date")]
    public DateTime HireDate { get; set; }
}

Property Filtering

// Include only specific properties
var options = new AddSheetOptions
{
 IncludeProperties = new[] { "Name", "Age", "City" }
};
workbook.AddSheet(people, "Filtered", options);

// Exclude specific properties
var options = new AddSheetOptions
{
    ExcludeProperties = new[] { "InternalId", "Password" }
};
workbook.AddSheet(users, "Public", options);

Extended Properties (Dynamic Properties)

Work with objects that have both strongly-typed and dynamic properties:

var extendedData = new List<Extended<MyClass>>
{
    new Extended<MyClass>(
        new MyClass { Id = 1, Name = "Item 1" },
    new Dictionary<string, object?>
        {
    { "DynamicProp1", "Value1" },
        { "DynamicProp2", 42 }
 }
    )
};

workbook.AddSheet(extendedData);
workbook.Save();

// Reading extended properties
var loadedData = workbook.GetExtendedList<MyClass>();
foreach (var item in loadedData)
{
    Console.WriteLine($"{item.Item.Name}");
    foreach (var prop in item.Properties)
    {
        Console.WriteLine($"  {prop.Key}: {prop.Value}");
    }
}

Supported Types

• Primitives: int, long, short, uint, ulong, ushort
• Floating point: float, double, decimal
• Boolean: bool
• Dates: DateTime, DateTimeOffset
• Strings: string
• Enums (stored as text)
• Lists: List<string> (with configurable delimiter)
• All nullable versions of the above

Options

Configure behavior with the Options class:

var options = new Options
{
    StopProcessingOnFirstEmptyRow = true,
IgnoreUnmappedProperties = true,
    EmptyRowInterpretedAsNull = false,
  LoadNullExtendedProperties = true,
    ListSeparator = ";"
};

using var workbook = new MagicSpreadsheet(fileInfo, options);

Known Limitations

• JObject Support: Direct JObject serialization is not yet supported. Use Extended<object> instead.
• Nested Complex Objects: Properties of type List<ComplexType> cannot be loaded from Excel (though they can be saved as delimited strings).
• Large Integer Precision: Excel stores all numbers as doubles, so very large Int64/UInt64 values (near MaxValue) may lose precision.
• Special Values: double.NaN and null nullable types are stored as empty strings in Excel.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

See the LICENSE file for details.