Sheetly.Excel 1.1.0

🌟 Why Sheetly?

Sheetly brings the Entity Framework Core developer experience to Google Sheets and Excel. If you know EF Core, you already know Sheetly.

public class Product 
{
    public int Id { get; set; }
    
    [MaxLength(100)]
    public string Name { get; set; } = string.Empty;
    
    public decimal Price { get; set; }
    
    public int CategoryId { get; set; }
    public Category Category { get; set; }
}

public class AppContext : SheetsContext
{
    public SheetsSet<Product> Products { get; set; }
    public SheetsSet<Category> Categories { get; set; }

    protected override void OnConfiguring(SheetsOptions options)
    {
        options.UseGoogleSheets("your-spreadsheet-id", "credentials.json");
        // or: options.UseExcel("data.xlsx");
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasSheetName("Products");
            entity.Property(p => p.Name).HasMaxLength(100);
            entity.Property(p => p.Price).IsRequired();
        });
    }
}

// Use it like EF Core
await using var context = new AppContext();
await context.InitializeAsync();

context.Products.Add(new Product { Name = "Laptop", Price = 1200 });
await context.SaveChangesAsync();

var products = await context.Products
    .Include(p => p.Category)
    .ToListAsync();

✨ Key Features

🎯 EF Core-Style API

• SheetsContext and SheetsSet<T> — familiar patterns
• Add(), Update(), Remove(), SaveChangesAsync()
• Automatic change tracking — modify entities and call SaveChangesAsync() without explicit Update()
• Include() with string and expression-based overloads (Include(p => p.Category))
• AsNoTracking() for read-only queries
• FindAsync(), FirstOrDefaultAsync(), Where(), CountAsync(), AnyAsync()
• CancellationToken support on SaveChangesAsync()
• IAsyncDisposable — use await using for automatic cleanup

🔄 Code-First Migrations

• C# migration files with Up/Down methods
• Automatic model change detection
• Migration history tracking
• Schema synchronization checks at startup

dotnet sheetly migrations add InitialCreate
dotnet sheetly database update

✅ Constraint Validation

• Primary Keys (auto-detected, auto-increment)
• Foreign Keys (auto-detected from navigation properties)
• Required/Nullable (IsRequired())
• Max/Min Length (HasMaxLength(), HasMinLength())
• Range validation (HasRange())
• Default values (HasDefaultValue())
• Column mapping (HasColumnName())
• Local validation before API calls

🛡️ Schema Tracking & Performance

• Hidden __SheetlySchema__ sheet stores all metadata
• Hidden __SheetlyMigrationsHistory__ tracks applied migrations
• Batch operations — adding N entities uses a single API call
• In-memory sheet metadata cache — SheetExistsAsync costs 0 API calls after init
• Optimized FindAsync — scans only the PK column instead of full data
• Automatic retry with exponential backoff on rate limits
• Multiple credentials rotation — distribute API quota across service accounts

🧰 Professional CLI

dotnet tool install -g dotnet-sheetly

dotnet sheetly migrations add MyMigration
dotnet sheetly migrations list
dotnet sheetly migrations remove
dotnet sheetly database update
dotnet sheetly database drop
dotnet sheetly scaffold

📦 Packages

dotnet add package Sheetly.Core

# Pick your provider:
dotnet add package Sheetly.Google   # Google Sheets (online)
dotnet add package Sheetly.Excel    # Excel .xlsx (local)

# For ASP.NET Core apps
dotnet add package Sheetly.DependencyInjection

# CLI tool
dotnet tool install -g dotnet-sheetly

🚀 Quick Start

Option A: Google Sheets (Online)

1. Setup Google Sheets API

1. Go to Google Cloud Console
2. Create a new project
3. Enable Google Sheets API
4. Create credentials (Service Account)
5. Download credentials.json
6. Share your spreadsheet with the service account email

2. Configure

protected override void OnConfiguring(SheetsOptions options)
{
    options.UseGoogleSheets("your-spreadsheet-id", "credentials.json");
}

Option B: Excel (Local .xlsx)

dotnet add package Sheetly.Excel

protected override void OnConfiguring(SheetsOptions options)
{
    options.UseExcel("C:/data/myapp.xlsx");
}

No API keys, no internet — all data stays on disk.

2. Create Your Models

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public List<Product> Products { get; set; } = [];
}

public class Product 
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public string? Description { get; set; }
    
    public int CategoryId { get; set; }
    public Category Category { get; set; }
}

Primary keys (Id) and foreign keys (CategoryId → Category) are auto-detected by convention.

3. Create Your Context

using Sheetly.Core;
using Sheetly.Google;

public class MyAppContext : SheetsContext
{
    public SheetsSet<Product> Products { get; set; }
    public SheetsSet<Category> Categories { get; set; }

    protected override void OnConfiguring(SheetsOptions options)
    {
        options.UseGoogleSheets("your-spreadsheet-id", "credentials.json");
        // or: options.UseExcel("mydata.xlsx");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasSheetName("Products");
            entity.Property(p => p.Title).HasMaxLength(200).IsRequired();
            entity.Property(p => p.Price).IsRequired();
        });
            
        modelBuilder.Entity<Category>(entity =>
        {
            entity.HasSheetName("Categories");
            entity.Property(c => c.Name).HasMaxLength(100);
        });
    }
}

4. Create & Apply Migration

dotnet sheetly migrations add InitialCreate
dotnet sheetly database update

5. Use Your Context

await using var context = new MyAppContext();
await context.InitializeAsync();

// Create
var category = new Category { Name = "Electronics" };
context.Categories.Add(category);
await context.SaveChangesAsync();

var product = new Product 
{ 
    Title = "Laptop", 
    Price = 1200,
    CategoryId = category.Id
};
context.Products.Add(product);
await context.SaveChangesAsync();

// Query with Include
var products = await context.Products.Include(p => p.Category).ToListAsync();

foreach (var p in products)
    Console.WriteLine($"{p.Title} - ${p.Price} - {p.Category.Name}");

// Update (auto change tracking — no explicit Update() needed)
product.Price = 1100;
await context.SaveChangesAsync();

// Delete
context.Products.Remove(product);
await context.SaveChangesAsync();

🎓 Advanced Features

Fluent API Configuration

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>(entity =>
    {
        entity.HasSheetName("Products");
        
        entity.Property(p => p.Title)
            .HasMaxLength(200)
            .HasMinLength(2)
            .HasColumnName("Product_Title");
            
        entity.Property(p => p.Price)
            .IsRequired()
            .HasRange(0, 999999);
            
        entity.Property(p => p.Stock)
            .HasDefaultValue(0);
    });
}

ASP.NET Core Integration

// Parameterless constructor (classic)
builder.Services.AddSheetsContext<MyAppContext>(options =>
    options.UseGoogleSheets("spreadsheet-id", "credentials.json"));

// Options constructor (EF Core-style)
public class MyAppContext : SheetsContext
{
    public MyAppContext(SheetsContextOptions<MyAppContext> options) : base(options) { }
    public SheetsSet<Product> Products { get; set; }
}

builder.Services.AddSheetsContext<MyAppContext>(options =>
    options.UseGoogleSheets("spreadsheet-id", "credentials.json"));

AsNoTracking

var products = await context.Products.AsNoTracking().ToListAsync();

Queries

var product = await context.Products.FindAsync(1);
var first = await context.Products.FirstOrDefaultAsync(p => p.Price > 100);
var filtered = await context.Products.Where(p => p.CategoryId == 1);
var count = await context.Products.CountAsync();
var any = await context.Products.AnyAsync(p => p.Price > 0);

Expression-Based Include

// Type-safe — compile-time validation
var products = await context.Products.Include(p => p.Category).ToListAsync();
var categories = await context.Categories.Include(c => c.Products).ToListAsync();

// String-based still supported
var products2 = await context.Products.Include("Category").ToListAsync();

Automatic Change Tracking

var products = await context.Products.ToListAsync();
products.First().Price = 999;

// No need for context.Products.Update(product) — changes are auto-detected
await context.SaveChangesAsync();

Multiple Credentials (API Quota Rotation)

// credentials.json can be a single object or an array:
// [{ "type": "service_account", ... }, { "type": "service_account", ... }]
// Each API call rotates to the next credential (round-robin)
// Effective limit: N accounts × 60 req/min = N×60 req/min
options.UseGoogleSheets("spreadsheet-id", "credentials.json");

CancellationToken Support

var cts = new CancellationTokenSource(TimeSpan.FromSeconds(30));
await context.SaveChangesAsync(cts.Token);

🏗️ Architecture

Sheetly/
├── Sheetly.Core                  # Core: context, sets, migrations, validation
├── Sheetly.Google                # Google Sheets API provider (online)
├── Sheetly.Excel                 # Excel .xlsx provider (local)
├── Sheetly.DependencyInjection   # ASP.NET Core DI extensions
└── dotnet-sheetly (CLI)          # Command-line migration tool

📊 How It Works

Sheetly creates hidden sheets in your spreadsheet (Google Sheets or local .xlsx):

Workflow

Define models → Create migrations → Apply to Sheets → Use context
     ↓                ↓                    ↓               ↓
  C# classes    .cs migration files   Google Sheets    CRUD + queries

🤝 Contributing

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

📄 License

This project is licensed under the MIT License — see the LICENSE file for details.

Created with ❤️ by Muqimjon Mamadaliyev

Give it a ⭐ if you like it!