SmartExcelKit 1.0.1

SmartExcelKit

SmartExcelKit is a modern, high-performance, low-allocation .NET Standard 2.0 library designed for reading, writing, and evaluating spreadsheet files (XLSX, XLS, CSV, TSV, HTML, XML Spreadsheet 2003, JSON).

Designed with ClosedXML and EPPlus compatibility in mind, SmartExcelKit offers an intuitive API surface with near-zero learning curve for enterprise developers while optimizing memory footprint through lazy enumerations, struct address types, cached range bounds, and forward-only streaming.

Target Frameworks

• SmartExcelKit: .NET Standard 2.0 (Supports .NET Core 2.0+, .NET 5+, .NET 6/7/8/9/10+, and .NET Framework 4.6.1+).

Installation

dotnet add package SmartExcelKit

Complete API Guide & Examples

1. Open Workbook

Load workbooks seamlessly from file paths, streams, or raw byte arrays with automatic format detection.

using SmartExcelKit;

// 1. Open from file path (auto-detects extension & signature)
using var workbook1 = ExcelWorkbook.Open("data.xlsx");

// 2. Open asynchronously from file path
using var workbook2 = await ExcelWorkbook.OpenAsync("data.xlsx");

// 3. Open from a stream
using var stream = File.OpenRead("data.csv");
using var workbook3 = ExcelWorkbook.Open(stream);

// 4. Open from byte array
byte[] fileBytes = File.ReadAllBytes("legacy.xls");
using var workbook4 = ExcelWorkbook.Open(fileBytes);

2. Create Workbook

Create new workbooks, add worksheets, set cell values and formulas, apply styles, and save to any supported format.

using SmartExcelKit;
using SmartExcelKit.Styles;

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

// Set cell values
sheet.Cell("A1").Value = "Product Name";
sheet.Cell("B1").Value = "Unit Price";
sheet.Cell("C1").Value = "Quantity";
sheet.Cell("D1").Value = "Total Value";

sheet.Cell("A2").Value = "Mechanical Keyboard";
sheet.Cell("B2").Value = 129.99;
sheet.Cell("C2").Value = 15;
sheet.Cell("D2").Formula = "B2*C2";

// Create & apply styled range
var headerStyle = new ExcelStyle(
    font: new ExcelFont(name: "Segoe UI", size: 11, bold: true, color: "FFFFFF"),
    fill: new ExcelFill(ExcelFillPatternType.Solid, backgroundColor: "1F4E79"),
    alignment: new ExcelAlignment(horizontal: ExcelHorizontalAlignment.Center)
);
sheet.Range("A1:D1").Style = headerStyle;

// Save workbook (format auto-detected via extension)
workbook.Save("inventory.xlsx");

3. Worksheet Navigation

Access sheets by name, 0-based index, or active worksheet reference.

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

// Access worksheet by name via indexer
ExcelWorksheet sheetByName = workbook["Inventory"];

// Access worksheet by 0-based index
ExcelWorksheet sheetByIndex = workbook[0];

// Access or set ActiveWorksheet
ExcelWorksheet activeSheet = workbook.ActiveWorksheet;
workbook.ActiveWorksheet = sheetByName;

4. Rows Enumeration

Lazily iterate all populated rows or access individual rows by 1-based index.

var sheet = workbook.ActiveWorksheet;

// Lazy enumeration over all populated rows
foreach (ExcelRow row in sheet.Rows)
{
    Console.WriteLine($"Row {row.Index} - Height: {row.Height}, Hidden: {row.Hidden}, IsEmpty: {row.IsEmpty}");
}

// Access specific row by index
ExcelRow thirdRow = sheet.Row(3);
thirdRow.Height = 28.0;
thirdRow.Hidden = false;

5. Columns Enumeration

Lazily iterate all columns, query column letters, or modify widths and visibility.

var sheet = workbook.ActiveWorksheet;

// Lazy enumeration over all populated columns
foreach (ExcelColumn col in sheet.Columns)
{
    Console.WriteLine($"Column {col.Letter} ({col.Index}) - Width: {col.Width}, IsEmpty: {col.IsEmpty}");
}

// Access specific column by index
ExcelColumn colB = sheet.Column(2); // Column 'B'
colB.Width = 22.5;
colB.AutoFit();

6. Cells Enumeration

Iterate cells sequentially over a range or worksheet without intermediate array allocations.

var range = sheet.Range("A1:C10");

// Enumerates all cells in range row-by-row
foreach (ExcelCell cell in range.Cells)
{
    Console.WriteLine($"{cell.Address} = {cell.Value}");
}

7. UsedRange

Retrieve the bounding box of all populated cells in O(1) cached time.

ExcelRange usedRange = sheet.UsedRange;

Console.WriteLine($"Used Range Address : {usedRange.Address.Address}");
Console.WriteLine($"Used Rows Count    : {sheet.UsedRowCount}");
Console.WriteLine($"Used Columns Count : {sheet.UsedColumnCount}");
Console.WriteLine($"Max Row            : {sheet.MaxRow}");
Console.WriteLine($"Max Column         : {sheet.MaxColumn}");

8. CellsUsed

Lazily enumerate only cells that contain actual values, formulas, comments, hyperlinks, or non-default styles.

// Zero-allocation iteration over used cells only
foreach (ExcelCell cell in sheet.CellsUsed())
{
    Console.WriteLine($"Cell {cell.Address} ({cell.RowNumber}, {cell.ColumnNumber}) = {cell.Value}");
}

9. RowsUsed

Find the first, last, or all rows containing populated data.

ExcelRow? firstRow = sheet.FirstRowUsed();
ExcelRow? lastRow  = sheet.LastRowUsed();

Console.WriteLine($"First row with data: {firstRow?.Index}");
Console.WriteLine($"Last row with data : {lastRow?.Index}");

// Iterate populated rows only
foreach (ExcelRow row in sheet.RowsUsed())
{
    Console.WriteLine($"Row {row.Index} contains {row.CellsUsed().Count()} used cells.");
}

10. ColumnsUsed

Find the first, last, or all columns containing populated data.

ExcelColumn? firstCol = sheet.FirstColumnUsed();
ExcelColumn? lastCol  = sheet.LastColumnUsed();

Console.WriteLine($"First col with data: {firstCol?.Letter}");
Console.WriteLine($"Last col with data : {lastCol?.Letter}");

// Iterate populated columns only
foreach (ExcelColumn col in sheet.ColumnsUsed())
{
    Console.WriteLine($"Column {col.Letter} contains data.");
}

11. Range Operations

Perform bulk styling, value assignment, clear, merge, and copy operations across ranges.

var range = sheet.Range("A1:D10");

// Bulk value assignment
range.Value = "Default Text";

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

// Clear contents or styles
range.ClearContents(); // Clears values and formulas only
range.ClearStyles();   // Resets formatting to default
range.Clear();          // Clears everything (values, formulas, styles, comments, hyperlinks)

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

12. Cell Access

Intuitive cell access via functions or indexers on worksheets, ranges, rows, and columns.

// Method call access
sheet.Cell("A1").Value = "Title";
sheet.Cell(1, 1).Value = "Title";

// Indexer access on worksheet
sheet["A1"].Value = "Title";
sheet[1, 1].Value = "Title";

// Access via Row or Column wrappers
sheet.Row(1)[1].Value = "Title";
sheet.Column(1)[1].Value = "Title";

13. Typed Value Reading

Type-safe, null-safe helper methods and generic converters.

ExcelCell cell = sheet.Cell("B2");

// Strongly typed convenience getters
int intVal       = cell.GetInt32();
long longVal     = cell.GetInt64();
double doubleVal = cell.GetDouble();
decimal decVal   = cell.GetDecimal();
bool boolVal     = cell.GetBoolean();
DateTime dateVal = cell.GetDateTime();
string strVal    = cell.GetString();

// Generic type conversion
if (cell.HasValue)
{
    double price = cell.GetValue<double>();
    
    if (cell.TryGetValue<int>(out int count))
    {
        Console.WriteLine($"Parsed count: {count}");
    }
}

14. Import POCO

Import strongly typed objects into worksheet rows with automatic header generation.

public class Product
{
    public string Name { get; set; } = string.Empty;
    public double Price { get; set; }
    public int Quantity { get; set; }
}

var products = new List<Product>
{
    new() { Name = "Laptop", Price = 999.99, Quantity = 5 },
    new() { Name = "Mouse", Price = 25.50, Quantity = 40 }
};

var sheet = workbook.AddWorksheet("Products");
sheet.Import(products, startRow: 1, startColumn: 1);

15. Export POCO

Export sheet rows back to a strongly typed collection of POCOs matching header names.

// Export worksheet rows back to POCO objects
List<Product> items = sheet.Export<Product>(startRow: 1, startColumn: 1).ToList();

foreach (var item in items)
{
    Console.WriteLine($"{item.Name}: ${item.Price} (Qty: {item.Quantity})");
}

16. Import DataTable

Import DataTable structures directly or export sheet content back to DataTable.

using System.Data;

var dt = new DataTable("Orders");
dt.Columns.Add("OrderID", typeof(int));
dt.Columns.Add("Customer", typeof(string));
dt.Rows.Add(101, "Acme Corp");

// Import DataTable to sheet
sheet.Import(dt, startRow: 1, startColumn: 1, includeHeader: true);

// Export sheet content back to DataTable
DataTable exportedDt = sheet.ExportToDataTable(startRow: 1, startColumn: 1, hasHeader: true);

17. Formula Engine

Parse and evaluate Excel formulas with dependency cycle protection.

using SmartExcelKit.Formula;

sheet.Cell("A1").Value = 50;
sheet.Cell("A2").Value = 150;
sheet.Cell("A3").Formula = "SUM(A1:A2)"; // Built-in formula linking

// Direct formula evaluation
object? result = FormulaEvaluator.Evaluate("AVERAGE(A1:A2) * 2", sheet);
Console.WriteLine($"Evaluated Result: {result}"); // 200

18. Worksheet Protection

Protect worksheets with password hashing compatible with standard Excel XOR protection.

// Protect sheet with password
sheet.Protect("SecretPassword123");
Console.WriteLine(sheet.IsProtected); // True
Console.WriteLine(sheet.ProtectionPasswordHash); // HEX Hash e.g. "DAA7"

// Unprotect sheet
sheet.Unprotect();

19. Streaming Reader

Low-memory forward-only XML reader for processing massive files with millions of rows.

using SmartExcelKit.Streaming;

using var fs = File.OpenRead("large_data.xlsx");
using var reader = new ExcelStreamingReader(fs);

foreach (string sheetName in reader.GetSheets())
{
    foreach (object?[] rowValues in reader.ReadRows(sheetName))
    {
        Console.WriteLine($"Row: {string.Join(", ", rowValues)}");
    }
}

20. Streaming Writer

Flat-memory forward-only ZIP stream writer that avoids string table allocations.

using SmartExcelKit.Streaming;

using var fs = File.Create("large_export.xlsx");
using var writer = new ExcelStreamingWriter(fs);

writer.BeginSheet("Data");
writer.WriteRow(new object?[] { "ID", "Name", "Date" });

for (int i = 1; i <= 1000000; i++)
{
    writer.WriteRow(new object?[] { i, $"User {i}", DateTime.UtcNow });
}

writer.EndSheet();

21. CSV Engine

High-performance delimited parser utilizing pooled character memory (ArrayPool<char>).

using SmartExcelKit.Csv;

// Write CSV
var rows = new List<List<string>>
{
    new() { "ID", "Description" },
    new() { "1", "Keyboard, Wireless" }
};
using var writeStream = File.Create("output.csv");
CsvEngine.Write(writeStream, rows, delimiter: ',');

// Read CSV with auto-detected encoding & delimiter
using var readStream = File.OpenRead("output.csv");
var encoding = CsvEngine.DetectEncoding(readStream);
char delimiter = CsvEngine.DetectDelimiter(readStream, encoding);

foreach (List<string> rowFields in CsvEngine.ReadStreaming(readStream, delimiter, encoding))
{
    Console.WriteLine($"Field 0: {rowFields[0]}, Field 1: {rowFields[1]}");
}

22. Auto Detect File Format

Automatically detect format from file extension or content magic signatures.

using var stream = File.OpenRead("unknown_file");
ExcelFileFormat format = ExcelWorkbook.DetectFormat(stream, "unknown_file.xlsx");
Console.WriteLine($"Detected format: {format}"); // XLSX, XLS, CSV, JSON, XML2003, etc.

23. Bulk Operations

Matrix value manipulation, row/column insertion, deletion, and cell shifting.

// 1. Bulk matrix assignment
var matrix = new object?[,]
{
    { "ID", "Name", "Score" },
    { 1, "Alice", 95.5 },
    { 2, "Bob", 88.0 }
};
sheet.SetValues(matrix, startRow: 1, startColumn: 1);

// 2. Retrieve matrix from region
object?[,] dataMatrix = sheet.GetValues(startRow: 1, startColumn: 1, endRow: 3, endColumn: 3);

// 3. Row & Column Shifting
sheet.InsertRows(startRow: 2, count: 1);   // Inserts 1 blank row at row 2
sheet.DeleteRows(startRow: 2, count: 1);   // Deletes 1 row at row 2

sheet.InsertColumns(startColumn: 2, count: 1); // Inserts 1 blank column at column B
sheet.DeleteColumns(startColumn: 2, count: 1); // Deletes 1 column at column B

24. Performance Best Practices

To achieve optimal throughput and minimal GC pressure when working with large Excel files:

1. Use CellsUsed(), RowsUsed(), ColumnsUsed(): Avoid looping empty cells in large worksheets; CellsUsed() yields only populated coordinates.
2. Prefer Lazy Enumeration: Methods like sheet.Rows and sheet.Columns utilize yield return and do not allocate intermediate List<T> arrays.
3. Use ExcelStreamingWriter & ExcelStreamingReader: For datasets exceeding 100,000 rows, streaming reduces memory from hundreds of megabytes to a flat ~10 MB buffer.
4. Cached Range Bounds: sheet.UsedRange, sheet.MaxRow, and sheet.MaxColumn use internal O(1) bounds tracking, avoiding full dictionary scans on repeat calls.

License

This project is licensed under the MIT License.