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

usingPanoramicData.SheetMagic;// Define your classpublicclassThing{publicstringPropertyA{get;set;}publicintPropertyB{get;set;}}// Create some datavarthings=newList<Thing>{newThing{PropertyA="Value 1",PropertyB=1},newThing{PropertyA="Value 2",PropertyB=2},};// Write to Excel filevarfileInfo=newFileInfo($"Output {DateTime.UtcNow:yyyyMMddTHHmmss}Z.xlsx");usingvarworkbook=newMagicSpreadsheet(fileInfo);workbook.AddSheet(things);workbook.Save();

Reading from a file

usingPanoramicData.SheetMagic;// Read from Excel fileusingvarworkbook=newMagicSpreadsheet(fileInfo);workbook.Load();// Read from default worksheet (first sheet)varcars=workbook.GetList<Car>();// Read from a specific worksheet by namevaranimals=workbook.GetList<Animal>("Animals");

Advanced Features

Working with Streams

// Write to a streamusingvarstream=newMemoryStream();using(varworkbook=newMagicSpreadsheet(stream)){workbook.AddSheet(data);workbook.Save();}// Read from a streamstream.Position=0;usingvarworkbook=newMagicSpreadsheet(stream);workbook.Load();varitems=workbook.GetList<MyClass>();

Multiple Sheets

usingvarworkbook=newMagicSpreadsheet(fileInfo);workbook.AddSheet(cars,"Cars");workbook.AddSheet(animals,"Animals");workbook.AddSheet(products,"Products");workbook.Save();

Applying Table Styles

varoptions=newAddSheetOptions{TableOptions=newTableOptions{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.

usingSystem.Drawing;usingPanoramicData.SheetMagic;varrows=new[]{newReportRow{Name=null,Description="Missing name",Score=null},newReportRow{Name="Bravo",Description="High score",Score=9},newReportRow{Name="Charlie",Description=null,Score=4}}.ToList();varoptions=newAddSheetOptions{ConditionalFormats=[newConditionalFormat{ColumnNames=["Name","Description"],Rules=[newConditionalFormatRule{RuleType=ConditionalFormatRuleType.ContainsBlanks,Style=newConditionalFormatStyle{BackgroundColor=Color.Red}}]},newConditionalFormat{ColumnNames=["Score"],Rules=[newConditionalFormatRule{RuleType=ConditionalFormatRuleType.ContainsBlanks,Style=newConditionalFormatStyle{BackgroundColor=Color.Red}},newConditionalFormatRule{RuleType=ConditionalFormatRuleType.CellIs,Operator=ConditionalFormatOperator.GreaterThan,Formula="5",Style=newConditionalFormatStyle{FontColor=Color.Green,FontWeight=FontWeight.Bold}}]},newConditionalFormat{Rules=[newConditionalFormatRule{RuleType=ConditionalFormatRuleType.ContainsErrors,Style=newConditionalFormatStyle{FontWeight=FontWeight.Bold}}]}]};usingvarworkbook=newMagicSpreadsheet(newFileInfo("ConditionalFormatting.xlsx"));workbook.AddSheet(rows,"Report",options);workbook.Save();publicsealedclassReportRow{publicstring?Name{get;set;}publicstring?Description{get;set;}publicint?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:

usingSystem.ComponentModel;publicclassEmployee{publicintId{get;set;}[Description("Full Name")]publicstringName{get;set;}[Description("Hire Date")]publicDateTimeHireDate{get;set;}}

Property Filtering

// Include only specific propertiesvaroptions=newAddSheetOptions{IncludeProperties=new[]{"Name","Age","City"}};workbook.AddSheet(people,"Filtered",options);// Exclude specific propertiesvaroptions=newAddSheetOptions{ExcludeProperties=new[]{"InternalId","Password"}};workbook.AddSheet(users,"Public",options);

Extended Properties (Dynamic Properties)

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

varextendedData=newList<Extended<MyClass>>{newExtended<MyClass>(newMyClass{Id=1,Name="Item 1"},newDictionary<string,object?>{{"DynamicProp1","Value1"},{"DynamicProp2",42}})};workbook.AddSheet(extendedData);workbook.Save();// Reading extended propertiesvarloadedData=workbook.GetExtendedList<MyClass>();foreach(variteminloadedData){Console.WriteLine($"{item.Item.Name}");foreach(varpropinitem.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:

varoptions=newOptions{StopProcessingOnFirstEmptyRow=true,IgnoreUnmappedProperties=true,EmptyRowInterpretedAsNull=false,LoadNullExtendedProperties=true,ListSeparator=";"};usingvarworkbook=newMagicSpreadsheet(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.