Linger.Excel.Contracts 1.4.2

Linger Excel Framework

A unified, efficient, and extensible Excel operation framework that supports multiple Excel library implementations

🚀 Features Overview

• Unified Interface - Multiple underlying implementations, developers don't need to worry about specific details
• Automatic Type Mapping - Seamless data conversion between Excel and objects
• DataSet Support - Import/export entire workbook as DataSet, supports multi-sheet operations
• Dependency Injection Friendly - Supports .NET Core/ASP.NET Core dependency injection
• High-Performance Design - Batch processing, parallel processing, and performance monitoring
• True Async Support - Async file I/O + Task.Run for CPU-intensive operations
• Flexible Configuration - Rich options configuration system
• Extensibility - Easy to customize and extend
• Cross-Platform Compatible - Supports .NET Framework 4.7.2+, .NET Standard 2.0+, .NET 8+, .NET 9+, .NET 10+

📦 Supported Excel Implementations

🏗️ Architecture Design

┌─────────────────┐
│ IExcelService │ ◄──── Non-generic interface, provides basic Excel operations
└────────┬────────┘
 │
 │inherits
 ▼
┌─────────────────────┐
│ IExcel<TWorksheet> │ ◄──── Generic interface, inherits IExcelService and provides advanced operations (with Action delegates)
└────────┬────────────┘
 │
 │implements
 ▼
┌─────────────────────────────┐
│AbstractExcelService<T1,T2> │ ◄──── Abstract base class, implements common logic and backward compatibility methods
└────────────┬────────────────┘
 │
 │inherits
 ▼
┌─────────────────────────────┐
│ ExcelBase<TWorkbook,TSheet> │ ◄──── Excel implementation base, more common logic
└────────────┬────────────────┘
 │
 │inherits
 ▼
┌─────────────────────────────┐
│ Concrete Implementation │ ◄──── Specific Excel library implementation
│(NpoiExcel, EPPlusExcel, etc)│ 
└─────────────────────────────┘

Design Highlights:

• Interface Inheritance: IExcel<TWorksheet> inherits from IExcelService, automatically gaining all basic methods
• Method Overloading: Export methods provide advanced customization via Action<TWorksheet> parameters without affecting basic usage
• Clear Hierarchy: Basic operations (IExcelService) → Advanced operations (IExcel) → Concrete implementations

🚀 Quick Start

1. Install Packages

# Install core interface package
dotnet add package Linger.Excel.Contracts

# Install your chosen implementation
dotnet add package Linger.Excel.Npoi
# or
dotnet add package Linger.Excel.ClosedXML

2. Register Services

// In Startup.cs or Program.cs
// NPOI implementation
services.AddExcelNpoi(options =>
{
 options.DefaultDateFormat = "yyyy-MM-dd";
 options.EnableFormulaEvaluation = true;
});

// Or use ClosedXML implementation
services.AddExcelClosedXML(options =>
{
 options.DefaultDateFormat = "yyyy-MM-dd";
 options.AutoFitColumns = true;
});

3. Use in Services

public class ExcelReportService
{
 private readonly IExcelService _excelService;

 public ExcelReportService(IExcelService excelService)
 {
 _excelService = excelService;
 }

 public List<User> ImportUsers(string filePath)
 {
 return _excelService.ExcelToList<User>(filePath) ?? new List<User>();
 }

 public string ExportUsers(List<User> users, string filePath)
 {
 return _excelService.CollectionToExcel(users, filePath, "UserList");
 }
 
 public async Task<string> ExportUsersAsync(List<User> users, string filePath)
 {
 // True async file I/O
 return await _excelService.CollectionToExcelAsync(users, filePath, "UserList");
 }
}

4. AOT-Friendly Imports

Use the import extension overloads when you want to avoid reflection during object materialization:

using Linger.Excel.Contracts;
using Linger.Extensions.Data;

var users = excelService.ExcelToList(
 filePath,
 row => new User
 {
 Id = Convert.ToInt32(row["Id"]),
 Name = row["Name"]?.ToString() ?? string.Empty
 });

var imported = await excelService.ExcelToListAsync(
 filePath,
 () => new ImportUser("excel"),
 new Dictionary<string, Action<ImportUser, object?>>
 {
 ["Id"] = DataTableExtensions.CreateColumnSetter<ImportUser, int>((user, value) => user.Id = value),
 ["Name"] = DataTableExtensions.CreateColumnSetter<ImportUser, string?>((user, value) => user.Name = value)
 });

These overloads first import Excel into a DataTable, then reuse the existing AOT-friendly DataTable mapping APIs.

5. AOT-Friendly Exports

Use explicit columns when you want to avoid reflection during collection export and template generation:

using Linger.Excel.Contracts;

var columns = new[]
{
 new ExcelExportColumn<User>("User Id", user => user.Id, typeof(int)),
 new ExcelExportColumn<User>("Name", user => user.Name, typeof(string)),
 new ExcelExportColumn<User>("Department", user => user.Department, typeof(string))
};

await excelService.CollectionToExcelAsync(users, columns, filePath, "Users");

using var template = excelService.CreateExcelTemplate(columns, "Users");

These overloads build a DataTable from explicit column definitions and then reuse the existing DataTable export pipeline, so no property reflection is required.

📝 Core Interfaces

IExcelService - Basic Interface

Provides all fundamental Excel import/export functionality:

public interface IExcelService
{
 #region Import Functions
 
 // Import single worksheet as DataTable
 DataTable? ExcelToDataTable(string filePath, string? sheetName = null, int headerRowIndex = 0, bool addEmptyRow = false);
 DataTable? StreamToDataTable(Stream stream, string? sheetName = null, int headerRowIndex = 0, bool addEmptyRow = false);
 
 // Import single worksheet as object list
 List<T>? ExcelToList<T>(string filePath, string? sheetName = null, int headerRowIndex = 0, bool addEmptyRow = false) where T : class, new();
 List<T>? StreamToList<T>(Stream stream, string? sheetName = null, int headerRowIndex = 0, bool addEmptyRow = false) where T : class, new();
 
 // Import entire workbook as DataSet (multiple overloads)
 DataSet? ExcelToDataSet(string filePath, int headerRowIndex = 0, bool addEmptyRow = false);
 DataSet? ExcelToDataSet(string filePath, IEnumerable<string>? sheetNames, int headerRowIndex = 0, bool addEmptyRow = false);
 DataSet? ExcelToDataSet(string filePath, Func<string, int?> headerRowIndexSelector, bool addEmptyRow = false);
 DataSet? StreamToDataSet(Stream stream, int headerRowIndex = 0, bool addEmptyRow = false);
 DataSet? StreamToDataSet(Stream stream, IEnumerable<string>? sheetNames, int headerRowIndex = 0, bool addEmptyRow = false);
 DataSet? StreamToDataSet(Stream stream, Func<string, int?> headerRowIndexSelector, bool addEmptyRow = false);
 
 // Async imports - True async file I/O
 Task<DataTable?> ExcelToDataTableAsync(string filePath, string? sheetName = null, int headerRowIndex = 0, bool addEmptyRow = false);
 Task<List<T>?> ExcelToListAsync<T>(string filePath, string? sheetName = null, int headerRowIndex = 0, bool addEmptyRow = false) where T : class, new();
 Task<DataSet?> ExcelToDataSetAsync(string filePath, int headerRowIndex = 0, bool addEmptyRow = false);
 Task<DataSet?> ExcelToDataSetAsync(string filePath, IEnumerable<string>? sheetNames, int headerRowIndex = 0, bool addEmptyRow = false);
 Task<DataSet?> ExcelToDataSetAsync(string filePath, Func<string, int?> headerRowIndexSelector, bool addEmptyRow = false);
 
 // Async Stream processing - Virtual methods, subclasses can override for true async
 Task<DataTable?> StreamToDataTableAsync(Stream stream, string? sheetName = null, int headerRowIndex = 0, bool addEmptyRow = false);
 Task<List<T>?> StreamToListAsync<T>(Stream stream, string? sheetName = null, int headerRowIndex = 0, bool addEmptyRow = false) where T : class, new();
 Task<DataSet?> StreamToDataSetAsync(Stream stream, int headerRowIndex = 0, bool addEmptyRow = false);
 Task<DataSet?> StreamToDataSetAsync(Stream stream, IEnumerable<string>? sheetNames, int headerRowIndex = 0, bool addEmptyRow = false);
 Task<DataSet?> StreamToDataSetAsync(Stream stream, Func<string, int?> headerRowIndexSelector, bool addEmptyRow = false);
 
 #endregion

 #region Export Functions
 
 // Export to Excel file
 string DataTableToExcel(DataTable dataTable, string fullFileName, string sheetsName = "Sheet1", string title = "");
 string DataSetToExcel(DataSet dataSet, string fullFileName, string defaultSheetName = "Sheet");
 string CollectionToExcel<T>(List<T> list, string fullFileName, string sheetsName = "Sheet1", string title = "") where T : class;
 
 // Export to memory stream
 MemoryStream CollectionToMemoryStream<T>(List<T> list, string sheetsName = "Sheet1", string title = "") where T : class;
 MemoryStream DataTableToMemoryStream(DataTable dataTable, string sheetsName = "Sheet1", string title = "");
 
 // Async exports
 Task<string> DataTableToExcelAsync(DataTable dataTable, string fullFileName, string sheetsName = "Sheet1", string title = "");
 Task<string> CollectionToExcelAsync<T>(List<T> list, string fullFileName, string sheetsName = "Sheet1", string title = "") where T : class;
 
 // Create template
 MemoryStream CreateExcelTemplate<T>() where T : class, new();
 
 #endregion
}

IExcel<TWorksheet> - Advanced Interface

Inherits from IExcelService and provides advanced customization with Action<TWorksheet> delegates:

public interface IExcel<out TWorksheet> : IExcelService where TWorksheet : class
{
 #region Advanced Export Functions - Support Custom Operations
 
 // Export with custom cell and style operations
 string DataTableToExcel(DataTable dataTable, string fullFileName, string sheetsName = "Sheet1", string title = "",
 Action<TWorksheet, DataColumnCollection, DataRowCollection>? action = null, 
 Action<TWorksheet>? styleAction = null);
 
 string DataSetToExcel(DataSet dataSet, string fullFileName, string defaultSheetName = "Sheet",
 Action<TWorksheet, DataColumnCollection, DataRowCollection>? action = null, 
 Action<TWorksheet>? styleAction = null);
 
 string CollectionToExcel<T>(List<T> list, string fullFileName, string sheetsName = "Sheet1", string title = "",
 Action<TWorksheet, PropertyInfo[]>? action = null, 
 Action<TWorksheet>? styleAction = null) where T : class;
 
 MemoryStream CollectionToMemoryStream<T>(List<T> list, string sheetsName = "Sheet1", string title = "",
 Action<TWorksheet, PropertyInfo[]>? action = null, 
 Action<TWorksheet>? styleAction = null) where T : class;
 
 MemoryStream DataTableToMemoryStream(DataTable dataTable, string sheetsName = "Sheet1", string title = "",
 Action<TWorksheet, DataColumnCollection, DataRowCollection>? action = null, 
 Action<TWorksheet>? styleAction = null);
 
 // Async exports
 Task<string> DataTableToExcelAsync(DataTable dataTable, string fullFileName, string sheetsName = "Sheet1", string title = "",
 Action<TWorksheet, DataColumnCollection, DataRowCollection>? action = null, 
 Action<TWorksheet>? styleAction = null);
 
 Task<string> CollectionToExcelAsync<T>(List<T> list, string fullFileName, string sheetsName = "Sheet1", string title = "",
 Action<TWorksheet, PropertyInfo[]>? action = null, 
 Action<TWorksheet>? styleAction = null) where T : class;
 
 #endregion
}

Interface Usage Recommendations:

• Basic Scenarios: Use IExcelService for most import/export needs
• Advanced Customization: Use IExcel<TWorksheet> when you need custom cell styles, merged cells, etc.
• Dependency Injection: Both interfaces can be injected; IExcel<TWorksheet> instances can be upcast to IExcelService

Async Implementation Notes:

• ✅ File I/O: Uses true async (FileStream with useAsync: true)
• ⚠️ Excel Processing: Uses Task.Run to wrap sync methods (library limitation)
• 🔧 Extensible: Subclasses can override StreamToXXXAsync methods for custom async implementations

🎨 Advanced Features

Import Entire Workbook as DataSet

ExcelToDataSet allows you to import an entire Excel workbook or specified worksheets as a DataSet, where each worksheet corresponds to a DataTable:

// 1. Import all worksheets with uniform header row (row 0)
DataSet? allSheets = excelService.ExcelToDataSet("workbook.xlsx", headerRowIndex: 0);

// 2. Import only specified worksheets
var selectedSheets = new[] { "UserData", "OrderData", "ProductData" };
DataSet? specificSheets = excelService.ExcelToDataSet("workbook.xlsx", selectedSheets, headerRowIndex: 0);

// 3. Specify different header rows for each worksheet
DataSet? flexibleHeaders = excelService.ExcelToDataSet("workbook.xlsx", sheetName =>
{
 return sheetName switch
 {
 "UserData" => 0, // Row 1 is header
 "Financial" => 2, // Row 3 is header (skip first 2 rows)
 "RawData" => null, // No header, all rows are data rows
 _ => 0 // Default: row 1 is header
 };
});

// 4. Async import
DataSet? result = await excelService.ExcelToDataSetAsync("large-workbook.xlsx", headerRowIndex: 0);

// 5. Backward compatibility mode - compatible with old NPOIHelper.ImportExcelToDs method
// Supports comma-separated worksheet name strings
DataSet? compatibleResult = excelService.ExcelToDataSet("workbook.xlsx", "Sheet1,Sheet2,Sheet3", headerRowIndex: 0);
DataSet? asyncCompatible = await excelService.ExcelToDataSetAsync("workbook.xlsx", "UserData, OrderData", headerRowIndex: 1);

// Access imported data
if (result is not null)
{
 foreach (DataTable table in result.Tables)
 {
 Console.WriteLine($"Worksheet: {table.TableName}, Rows: {table.Rows.Count}, Columns: {table.Columns.Count}");
 }
}

Parameter Description:

• headerRowIndex: Header row index (0-based). All worksheets use the same header row
• sheetNames:
  • IEnumerable<string>? - List of worksheet names to import. Pass null or empty collection to import all worksheets
  • string? - Comma-separated worksheet name string (backward compatible). Automatically handles whitespace, pass null to import all worksheets
• headerRowSelector: Specify different header rows for each worksheet. Pass worksheet name, return header row index for that worksheet
• addEmptyRow: Whether to include empty rows. Default is false

Backward Compatibility: The framework provides fully compatible method signatures with the legacy NPOIHelper.ImportExcelToDs, allowing migration without modifying existing code.

Export DataSet as Multi-Sheet Excel

DataSet dataSet = new DataSet();
dataSet.Tables.Add(CreateUserTable());
dataSet.Tables.Add(CreateOrderTable());
dataSet.Tables.Add(CreateProductTable());

// Export DataSet, each DataTable becomes a worksheet
string filePath = excelService.DataSetToExcel(dataSet, "multi-sheet-workbook.xlsx");

Advanced Export - Custom Cell Operations

Use the advanced overload methods in IExcel<TWorksheet> interface to customize cell operations during export:

// Inject IExcel<TWorksheet> instead of IExcelService
public class AdvancedExcelService
{
 private readonly IExcel<ISheet> _npoiExcel; // NPOI's worksheet type is ISheet

 public AdvancedExcelService(IExcel<ISheet> npoiExcel)
 {
 _npoiExcel = npoiExcel;
 }

 public string ExportWithCustomStyle(List<User> users, string filePath)
 {
 return _npoiExcel.CollectionToExcel(users, filePath, "UserList", "User Data Report",
 // Custom cell operations
 action: (sheet, properties) =>
 {
 // Access native ISheet object for advanced operations
 var row = sheet.GetRow(0);
 row.Height = 500; // Set header row height
 
 // Merge cells
 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length - 1));
 },
 // Custom style operations
 styleAction: (sheet) =>
 {
 // Set column widths
 sheet.SetColumnWidth(0, 3000);
 sheet.SetColumnWidth(1, 5000);
 
 // Freeze panes
 sheet.CreateFreezePane(0, 2);
 }
 );
 }
}

Custom Excel Column Mapping

Use attributes to control how object properties map to Excel columns:

public class User
{
 [ExcelColumn(Index = 0, Name = "User ID")]
 public int Id { get; set; }

 [ExcelColumn(Index = 1, Name = "Username")]
 public string Username { get; set; }

 [ExcelColumn(Index = 2, Name = "Email Address", Width = 30)]
 public string Email { get; set; }

 [ExcelColumn(Index = 3, Name = "Registration Date", Format = "yyyy-MM-dd")]
 public DateTime RegistrationDate { get; set; }

 [ExcelColumn(Ignore = true)]
 public string Password { get; set; }
}

Excel Style Customization

// Use IExcel<TWorksheet> advanced overloads for style customization
public class StyledExcelService
{
 private readonly IExcel<ISheet> _npoiExcel;

 public StyledExcelService(IExcel<ISheet> npoiExcel)
 {
 _npoiExcel = npoiExcel;
 }

 public string ExportWithStyles(List<User> users, string filePath)
 {
 return _npoiExcel.CollectionToExcel(users, filePath, "UserData", "User Information",
 styleAction: (sheet) =>
 {
 // Create styles
 var workbook = sheet.Workbook;
 var headerStyle = workbook.CreateCellStyle();
 var headerFont = workbook.CreateFont();
 
 // Header style
 headerFont.IsBold = true;
 headerFont.FontHeightInPoints = 12;
 headerFont.Color = IndexedColors.White.Index;
 headerStyle.SetFont(headerFont);
 headerStyle.FillForegroundColor = IndexedColors.Blue.Index;
 headerStyle.FillPattern = FillPattern.SolidForeground;
 
 // Apply to header row
 var headerRow = sheet.GetRow(1); // Row 2 is data header
 for (int i = 0; i < headerRow.LastCellNum; i++)
 {
 headerRow.GetCell(i)?.SetCellStyle(headerStyle);
 }
 
 // Auto-size columns
 for (int i = 0; i < headerRow.LastCellNum; i++)
 {
 sheet.AutoSizeColumn(i);
 }
 }
 );
 }
}

Large Data Processing

// Async export of large datasets
var largeDataList = GetLargeDataSet(); // Assume 100k records

// Use true async I/O
string filePath = await excelService.CollectionToExcelAsync(
 largeDataList, 
 "huge_data.xlsx", 
 "DataExport"
);

Console.WriteLine($"Export completed: {filePath}");

Excel Template Filling

Note: Template functionality implementation depends on the chosen Excel library. Please refer to the specific implementation library's documentation.

🧩 Extensibility

Custom Excel Implementation

public class MyCustomExcel : AbstractExcelService<MyWorkbook, MyWorksheet>
{
 public MyCustomExcel(ExcelOptions? options = null) : base(options) { }

 // Implement abstract methods...
 protected override MyWorkbook CreateWorkbookInternal() { /* ... */ }
 protected override MyWorksheet GetWorksheetInternal(MyWorkbook workbook, string sheetName) { /* ... */ }
 // More method implementations...
}

// Register custom implementation
services.AddSingleton<IExcelService, MyCustomExcel>();
services.AddSingleton<IExcel<MyWorksheet>, MyCustomExcel>();