Skip to content

About

High-performance, lightweight .NET Standard 2.0 library for reading, writing, and streaming Excel and spreadsheet files (XLSX, XLS, XLSM, XLSB, CSV, TSV, HTML, XML 2003, JSON). Features native BIFF8 XLS support, zero-allocation streaming, automatic format detection, and a built-in Excel formula engine.

Topics

Resources

Stars

2 stars

Watchers

1 watching

Forks

Repository files navigation

SmartExcelKit

NuGet Version License

SmartExcelKit is a lightweight, ultra-fast, low-allocation .NET Standard 2.0 library for reading, writing, streaming, and evaluating spreadsheet files across multiple formats (XLSX, XLS, CSV, TSV, HTML Table, XML Spreadsheet 2003, JSON).

Designed with performance as the top priority, SmartExcelKit uses pooled buffers (ArrayPool<T>), lazy enumerations, O(1) bounds caching, and forward-only streaming to deliver maximum throughput with minimal GC allocation.


Target Frameworks

  • .NET Standard 2.0: Compatible with .NET Core 2.0+, .NET 5+, .NET 6/7/8/9/10+, and .NET Framework 4.6.1+.

Installation

dotnet add package SmartExcelKit

Supported Formats

Format Extension Read Write Description
XLSX .xlsx ✅ ✅ Standard Microsoft Excel OpenXML Workbook
XLS .xls ✅ ❌ Legacy Microsoft Excel Binary Format (BIFF8 / OLE2)
CSV .csv ✅ ✅ Comma-Separated Values
TSV .tsv ✅ ✅ Tab-Separated Values
HTML .html, .htm ✅ ✅ HTML Table Spreadsheet Markup
XML 2003 .xml ✅ ✅ Microsoft Excel 2003 XML SpreadsheetML
JSON .json ✅ ✅ Structured JSON Array / Document

Developer Guide & Complete Code Examples

1. Open Workbooks (Auto-Format Detection)

Load workbooks from file paths, streams, or byte arrays. Format is auto-detected from magic byte signatures or extensions.

using SmartExcelKit;
using SmartExcelKit.Core;

// 1. Open from file path (sync & async)
using var workbook1 = ExcelWorkbook.Open("data.xlsx");
using var workbook2 = await ExcelWorkbook.OpenAsync("data.csv");

// 2. Open from stream or byte array
using var stream = File.OpenRead("legacy.xls");
using var workbook3 = ExcelWorkbook.Open(stream);

byte[] bytes = File.ReadAllBytes("data.json");
using var workbook4 = ExcelWorkbook.Open(bytes);

// 3. Detect format explicitly
ExcelFileFormat format = ExcelWorkbook.DetectFormat(stream, "data.xlsx");

2. Create & Save Workbooks

Create workbooks, set active sheets, apply protection, and save to files or streams.

using SmartExcelKit;

using var workbook = new ExcelWorkbook();
var sheet = workbook.AddWorksheet("MainSheet");

// Set active worksheet
workbook.ActiveWorksheet = sheet;

// Workbook protection
workbook.Protect("WorkbookPassword123");
bool isProtected = workbook.IsProtected;
workbook.Unprotect();

// Save to file or stream (sync & async)
workbook.Save("output.xlsx");
await workbook.SaveAsync("output.xlsx");

using var outStream = File.Create("output.csv");
workbook.Save(outStream, ExcelFileFormat.Csv);

3. Worksheet Management & Navigation

Access sheets by name or 0-based index, remove sheets, or protect individual worksheets.

var sheet1 = workbook["MainSheet"];       // Access by name
var sheet2 = workbook[0];                 // Access by 0-based index

// Add / Remove worksheets
var newSheet = workbook.AddWorksheet("Draft");
workbook.RemoveWorksheet("Draft");

// Worksheet protection
sheet1.Protect("SheetPassword123");
sheet1.Unprotect();

4. Row & Column Operations

Manage heights, widths, visibility, grouping, auto-fit, and insert/delete operations.

var sheet = workbook.ActiveWorksheet;

// Row properties
ExcelRow row = sheet.Row(1);
row.Height = 30.0;
row.Hidden = false;

// Column properties
ExcelColumn col = sheet.Column(1); // Column 'A'
col.Width = 25.0;
col.AutoFit();                     // Auto-fit column width based on content

// Insert & Delete Rows / Columns
sheet.InsertRows(startRow: 2, count: 5);
sheet.DeleteRows(startRow: 10, count: 2);

sheet.InsertColumns(startColumn: 2, count: 1);
sheet.DeleteColumns(startColumn: 5, count: 1);

// Group & Ungroup Rows / Columns
sheet.GroupRows(fromRow: 2, toRow: 10);
sheet.UngroupRows(fromRow: 2, toRow: 10);

sheet.GroupColumns(fromColumn: 1, toColumn: 3);
sheet.UngroupColumns(fromColumn: 1, toColumn: 3);

// First & Last Used Row / Column
ExcelRow? firstRow = sheet.FirstRowUsed();
ExcelRow? lastRow  = sheet.LastRowUsed();
ExcelColumn? firstCol = sheet.FirstColumnUsed();
ExcelColumn? lastCol  = sheet.LastColumnUsed();

5. Cell Reading & Writing (Type-Safe Getters)

Set and read cell values with type safety and fallback default values.

// Cell assignment via address string or row/column numbers
sheet.Cell("A1").Value = "Product Name";
sheet.Cell(1, 2).Value = 199.99;
sheet["A3"].Value = DateTime.UtcNow;
sheet[1, 4].Value = true;

// Type-safe getters
ExcelCell cell = sheet.Cell("B1");

string strVal  = cell.GetString();
int intVal     = cell.GetInt32();
long longVal   = cell.GetInt64();
double dblVal  = cell.GetDouble();
decimal decVal = cell.GetDecimal();
bool boolVal   = cell.GetBoolean();
DateTime dtVal = cell.GetDateTime();

// Generic getter & TryGetValue with fallback
if (cell.HasValue)
{
    double val = cell.GetValue<double>();
    
    if (cell.TryGetValue<int>(out int parsedCount))
    {
        Console.WriteLine($"Count: {parsedCount}");
    }
}

int fallback = cell.GetValueOrDefault<int>(defaultValue: 0);

6. Range Operations & Bulk Matrix Access

Perform bulk formatting, matrix assignments, clearing, merging, and copying.

var range = sheet.Range("A1:D10");
// Or range by coordinates: sheet.Range(startRow: 1, startColumn: 1, endRow: 10, endColumn: 4);

// Merge & Unmerge
range.Merge();
range.Unmerge();

// Clear Operations
range.ClearContents(); // Clears values and formulas
range.ClearStyles();   // Resets styles to default
range.Clear();         // Clears values, formulas, styles, comments, hyperlinks

// Bulk Copy
var targetRange = sheet.Range("F1:I10");
range.CopyTo(targetRange);

// Bulk Matrix Value Assignment & Extraction
object?[,] matrix = new object?[,]
{
    { "ID", "Name", "Price" },
    { 101, "Keyboard", 89.99 },
    { 102, "Mouse", 29.99 }
};
sheet.SetValues(matrix, startRow: 1, startColumn: 1);

object?[,] extracted = sheet.GetValues(startRow: 1, startColumn: 1, endRow: 3, endColumn: 3);

7. Cell Styling & Number Formatting

Customize fonts, fills, borders, alignments, and number format strings.

using SmartExcelKit.Styles;

var customStyle = new ExcelStyle(
    font: new ExcelFont(name: "Arial", size: 12, bold: true, italic: false, underline: true, color: "1F4E79"),
    fill: new ExcelFill(ExcelFillPatternType.Solid, backgroundColor: "EBF1F5"),
    border: new ExcelBorder(
        left: new ExcelBorderItem(ExcelBorderStyle.Thin, color: "000000"),
        right: new ExcelBorderItem(ExcelBorderStyle.Thin, color: "000000"),
        top: new ExcelBorderItem(ExcelBorderStyle.Medium, color: "1F4E79"),
        bottom: new ExcelBorderItem(ExcelBorderStyle.Medium, color: "1F4E79")
    ),
    alignment: new ExcelAlignment(
        horizontal: ExcelHorizontalAlignment.Center,
        vertical: ExcelVerticalAlignment.Center,
        wrapText: true,
        indent: 1
    ),
    numberFormat: new ExcelNumberFormat("$#,##0.00")
);

// Apply style to cell or range
sheet.Cell("B2").Style = customStyle;
sheet.Range("A1:D1").Style = customStyle;

8. Excel Tables (ListObject) & Totals Row

Create native Excel Tables with custom styles and totals row aggregations.

using SmartExcelKit.Tables;

var table = sheet.Tables.Add("A1:C10", "SalesTable");

// Table Properties
table.StyleName = "TableStyleMedium9";
table.ShowHeaderRow = true;
table.ShowTotalsRow = true;
table.ShowStripedRows = true;
table.ShowStripedColumns = false;

// Configure Totals Row Functions for Columns
table["Price"].TotalsRowFunction = TotalsRowFunction.Average;
table["Quantity"].TotalsRowFunction = TotalsRowFunction.Sum;
table["ID"].TotalsRowFunction = TotalsRowFunction.Count;

9. AutoFilter & Multi-Column Sorting

Enable AutoFilters and perform multi-column, culture-aware sorting.

// Enable AutoFilter over specific range or used range
sheet.AutoFilter("A1:D100");
sheet.ClearAutoFilter();

// Multi-column sorting: sort rows 2-100 by Column 2 (ascending)
sheet.Sort(
    startRow: 2, startColumn: 1,
    endRow: 100, endColumn: 4,
    sortColumn: 2,
    ascending: true,
    culture: System.Globalization.CultureInfo.CurrentCulture
);

10. Conditional Formatting

Apply color scales, data bars, value comparisons, or formula rules.

using SmartExcelKit.Formatting;
using SmartExcelKit.Styles;

// 1. Cell Value Rule (Highlight values > 500 in red)
var alertStyle = new ExcelStyle(font: new ExcelFont(bold: true, color: "FF0000"));
sheet.ConditionalFormatting.AddCellValueRule("C2:C100", ConditionalFormattingOperator.GreaterThan, "500", alertStyle);

// 2. Data Bar Gradient Visualization
sheet.ConditionalFormatting.AddDataBar("D2:D100", colorHex: "00FF00");

// 3. 2-Color & 3-Color Scales
sheet.ConditionalFormatting.AddTwoColorScale("E2:E100", minColorHex: "FFFFFF", maxColorHex: "0000FF");
sheet.ConditionalFormatting.AddThreeColorScale("F2:F100", minColorHex: "FF0000", midColorHex: "FFFF00", maxColorHex: "00FF00");

// 4. Formula Rule
sheet.ConditionalFormatting.AddFormulaRule("A2:A100", "ISODD(ROW())", alertStyle);

11. Data Validation Rules

Add drop-down lists, numeric ranges, text length limits, date constraints, or custom formulas.

using SmartExcelKit.Validation;

// 1. List Validation (Drop-down menu)
sheet.DataValidations.AddListValidation("A2:A100", new[] { "Approved", "Pending", "Rejected" });

// 2. Whole Number & Decimal Validation
sheet.DataValidations.AddWholeNumberValidation("B2:B100", ValidationOperator.Between, min: 1, max: 100);
sheet.DataValidations.AddDecimalValidation("C2:C100", ValidationOperator.GreaterThan, min: 0.0);

// 3. Date & Text Length Validation
sheet.DataValidations.AddDateValidation