PTSC.ImportExcel 1.4.0

PTSC.ImportExcel

A high-performance Excel/DataTable import library for .NET 8 with:

• 4 Import Strategies: Override, Grid, OnlyInsert, OnlyUpdate
• Validation Pipeline: Chain of Responsibility with 9+ built-in rules
• Fluent Builder API: Clean configuration chaining
• Batch SQL Execution: Auto-splitting for SQL Server 2100 param limit
• Error Observer: Structured error collection with Observer pattern
• Progress Reporting: Real-time progress via abstracted reporter
• Zero app-specific dependencies: No IClientContext, no ProgressServer, no EF Core — only abstractions

Dependencies

No dependencies on: EF Core, or any app-specific interfaces (IClientContext, ProgressServer, etc.).

Quick Start

1. Install

dotnet add package PTSC.ImportExcel

2. Register in Program.cs

Minimal — connection string only (no SignalR progress)

using PTSC.ImportExcel;

builder.Services.AddImportExcel(
    builder.Configuration.GetConnectionString("DefaultConnection")
);

With SignalR progress — pass your Hub type

using PTSC.ImportExcel;

builder.Services.AddImportExcel<ChatHub>(
    builder.Configuration.GetConnectionString("DefaultConnection")
);

Full — SignalR progress + user context + column config

using PTSC.ImportExcel;
using Microsoft.AspNetCore.Http;

// ── Step 1: Register ImportUserContext factory (BEFORE AddImportExcel) ──
// This must be registered first so the package's TryAddTransient<ImportUserContext>
// detects it and skips the default (empty) registration.
builder.Services.AddTransient<ImportUserContext>(sp =>
{
    var httpContextAccessor = sp.GetRequiredService<IHttpContextAccessor>();
    var ctx = new ImportUserContext();
    var httpContext = httpContextAccessor.HttpContext;
    if (httpContext != null)
    {
        // Read user/session info from your app's HttpContext.Items, Claims, etc.
        ctx.UserName     = httpContext.Items["UserSession"]?.ToString() ?? "";
        ctx.DataCode     = httpContext.Items["DataCode"]?.ToString() ?? "";
        ctx.ProjectCode  = httpContext.Items["ProjectCode"]?.ToString() ?? "";
        ctx.FacilityCode = httpContext.Items["FacilityCode"]?.ToString() ?? "";
        ctx.Language     = httpContext.Items["Language"]?.ToString() ?? "";
        ctx.ConnectionId = httpContext.Items["ConnectionId"]?.ToString() ?? "";
    }
    return ctx;
});

// ── Step 2: Register ImportExcel with SignalR + column config ──
builder.Services.AddImportExcel<ChatHub>(
    builder.Configuration.GetConnectionString("DefaultConnection"),
    columns => columns
        .SetColumnId("Id")
        .SetColumnCreatedBy("CreatebyUser")
        .SetColumnCreatedDate("DateCreated", "CreateDate")
        .SetColumnModifiedBy("LastedModifyUser", "LastModifiedbyUser")
        .SetColumnModifiedDate("LastedModifyDate", "LastModifiedDate")
        .SetColumnFromClientContext("DataCode", "ProjectCode", "FacilityCode")
);

Important: Register ImportUserContext factory before calling AddImportExcel. The package uses TryAddTransient<ImportUserContext>() internally — it will skip its default (empty) registration if it finds yours already registered.

3. Built-in SignalR Progress Reporter (optional)

When using AddImportExcel<THub>(...), the library auto-registers ImportProgressReporter<THub>. When using AddImportExcel(...) without a Hub type, a NullProgressReporter (no-op) is registered instead.

• Reads ConnectionId from ImportUserContext.ConnectionId
• Sends progress via IHubContext<THub>.Clients.Client(connectionId).SendAsync("OnRunProgressBar", ...)
• Includes percent deduplication (only sends when percent changes)

4. Use in your service/controller

var result = _importExcel.Override.CreateInstance(dataTable, typeof(MyModel))
    .WithPrimaryKeys("ProjectCode", "TagNo")
    .WithNotAllowNulls("Name", "Code")
    .WithMaxLength("Name", 100)
    .WithUnique("Email")
    .WithUpdateSystemField(false)
    .WithTransaction(transaction)
    .GetResult();

// Check errors (typed)
if (result.ErrorCollector.HasErrors)
{
    foreach (var error in result.ErrorCollector.Errors)
    {
        Console.WriteLine($"Row {error.RowIndex}: {error.ErrorMessage}");
    }
}

// Execute SQL
await _importToDatabase.Execute(result);

Abstractions

ImportExcelCore depends only on these abstractions — no concrete app classes:

ImportUserContext (POCO)

Simple data carrier resolved per-request via factory delegate at DI registration time. No interface needed — the library receives a populated POCO, not an abstraction to implement.

public class ImportUserContext
{
    public string UserName { get; set; } = "";
    public string DataCode { get; set; } = "";
    public string ProjectCode { get; set; } = "";
    public string FacilityCode { get; set; } = "";
    public string Language { get; set; } = "";
    public string ConnectionId { get; set; } = "";  // SignalR connection ID for progress
}

How it works: Register a Transient<ImportUserContext> factory before calling AddImportExcel. The factory runs per-request, reads user info from your app (e.g., IHttpContextAccessor → HttpContext.Items), and returns a populated ImportUserContext. The engine never touches HttpContext or IClientContext directly. The ConnectionId is used by the built-in ImportProgressReporter<THub> to send SignalR progress messages.

IImportProgressReporter (Interface)

Built-in ImportProgressReporter<THub> handles progress via SignalR automatically. You only need to implement this interface if you want custom behavior (e.g., console logging, file output).

public interface IImportProgressReporter
{
    Task SendAsync(string message = "");
    Task SendAsync(string message, int current, int total);
}

ImportSystemColumnConfig (Fluent Config)

Configure system column names that the engine auto-populates during import.

columns => columns
    .SetColumnId("Id")                                        // Primary key column
    .SetColumnCreatedBy("CreatebyUser")                       // Created-by user column
    .SetColumnCreatedDate("DateCreated", "CreateDate")        // Created date columns
    .SetColumnModifiedBy("LastedModifyUser", "LastModifiedbyUser")
    .SetColumnModifiedDate("LastedModifyDate", "LastModifiedDate")
    .SetColumnFromClientContext("DataCode", "ProjectCode", "FacilityCode")  // Auto-filled from ImportUserContext

Custom Validation Rules

public class EmailFormatRule : IImportColumnValidationRule
{
    public void Validate(DataColumnChangeEventArgs e)
    {
        if (e.Column.ExtendedProperties.ContainsKey("RuleEmail"))
        {
            if (!e.ProposedValue.ToString().Contains("@"))
                throw new Exception("Invalid email format");
        }
    }
}

Architecture

PTSC.ImportExcel/
├── Abstractions/            ← ImportUserContext, IImportProgressReporter, ImportSystemColumnConfig
├── ImportProgressReporter   ← Built-in SignalR progress reporter (generic <THub>)
├── ImportSqlHelper   ← Built-in SQL utilities (no external dependencies)
├── Engine/           ← Core processing pipeline
├── Strategy/         ← 4 import strategies
├── Validation/       ← Validation pipeline
│   ├── Rules/        ← Built-in rules
│   └── Error/        ← Error collection (Observer pattern)
└── Extensions/       ← Fluent builder + type helpers

Decoupled Dependencies

The library has been systematically decoupled from all app-specific dependencies:

License Key

The library requires a valid license key at registration time. Without a valid key, AddPTSCImportExcel throws InvalidOperationException at startup.

Generate a key (admin only)

using PTSC.ImportExcel.License;

// Never expires
string key = ImportLicenseGenerator.GenerateKey("PTSC_PMS_WEB_ENTERPRISE");

// Expires on a specific date
string key = ImportLicenseGenerator.GenerateKey("MyProject", expiresAt: new DateTime(2026, 12, 31));

Key format

{base64url_payload}.{base64url_signature} — HMAC-SHA256 signed.

Payload contains:

• Subject — project/client identifier
• ExpiresAt — expiry date (null = never)
• Issuer — "PTSCMC"

Validation behavior

Publish to NuGet

# 1. Pack (Release mode)
dotnet pack PTSC.ImportExcel/PTSC.ImportExcel.csproj -c Release

# 2. Push to nuget.org
dotnet nuget push PTSC.ImportExcel/nupkg/PTSC.ImportExcel.1.0.0.nupkg \
    --api-key YOUR_NUGET_API_KEY \
    --source https://api.nuget.org/v3/index.json

# OR push to a private GitHub Packages feed
dotnet nuget push PTSC.ImportExcel/nupkg/PTSC.ImportExcel.1.0.0.nupkg \
    --api-key YOUR_GITHUB_PAT \
    --source https://nuget.pkg.github.com/PTSCMC/index.json