A .NET library for reading, parsing, and validating Excel files with type-safe operations, data annotation validation, and Entity Framework integration. Supports batch processing with comprehensive error handling. See GitHub for full documentation and examples.
Get Started
$ dotnet add package SimplyWorks.ExcelImportReadme
SimplyWorks ExcelImport
A .NET library for reading, parsing, and validating Excel files with type-safe operations and comprehensive error handling.
Features
- Excel File Reading: Read Excel files (.xlsx, .xls) using ExcelDataReader
- Type-Safe Parsing: Parse Excel data into strongly-typed C# objects
- Data Validation: Built-in validation using data annotations
- Batch Processing: Support for batch imports with error tracking
- Sheet Mapping: Map Excel sheets to different types with flexible column mapping
- Query Interface: Query imported Excel data with filtering and pagination
- Entity Framework Integration: Store and query Excel data using Entity Framework
Packages
This library consists of two NuGet packages:
- SimplyWorks.ExcelImport: Core library with Excel reading and parsing functionality
- SimplyWorks.ExcelImport.Extensions: ASP.NET Core dependency injection extensions
Installation
Install the core package:
dotnet add package SimplyWorks.ExcelImport
For ASP.NET Core applications, also install the extensions package:
dotnet add package SimplyWorks.ExcelImport.Extensions
Quick Start
1. Define Your Data Model
public class Order
{
[Required]
public string OrderId { get; set; }
[Required]
public string CustomerName { get; set; }
[Range(0, double.MaxValue)]
public decimal Amount { get; set; }
public DateTime OrderDate { get; set; }
}
2. Configure Services (ASP.NET Core)
public void ConfigureServices(IServiceCollection services)
{
services.AddExcelImport();
// Configure your DbContext
services.AddDbContext<YourDbContext>(options =>
options.UseSqlServer(connectionString));
}
3. Import Excel Data
public class OrderImportService
{
private readonly ExcelService _excelService;
public OrderImportService(ExcelService excelService)
{
_excelService = excelService;
}
public async Task ImportOrders(string excelFileUrl)
{
var options = new TypedParseToJsonOptions
{
TypeAssemblyQualifiedName = typeof(Order).AssemblyQualifiedName,
NamingStrategy = JsonNamingStrategy.SnakeCase
};
// Load and validate Excel file
var container = await _excelService.LoadExcelFileInfo(excelFileUrl, options);
// Check for validation errors
if (container.Sheets.Any(sheet => sheet.HasErrors()))
{
// Handle validation errors
return;
}
// Import valid data
await _excelService.Import(excelFileUrl, options);
}
}
4. Query Imported Data
public class OrderQueryService
{
private readonly IExcelQueryable _excelQueryable;
public OrderQueryService(IExcelQueryable excelQueryable)
{
_excelQueryable = excelQueryable;
}
public async Task<IEnumerable<Order>> GetValidOrders(string reference, int pageIndex = 0, int pageSize = 100)
{
var options = new ExcelQueryValidatedOptions
{
Reference = reference,
PageIndex = pageIndex,
PageSize = pageSize,
RowStatus = QueryRowStatus.Valid
};
return await _excelQueryable.Get<Order>(options);
}
}
Core Components
ExcelService
Main service for importing Excel files with validation and error handling.
IExcelReader
Interface for reading Excel files and sheets with support for:
- Multiple sheet processing
- Column mapping
- Row-by-row reading
ExcelRepo
Repository pattern implementation for storing Excel import metadata and results.
SheetReader
Generic sheet reader for type-safe Excel data parsing.
Data Validation
Built-in support for:
- Data annotation validation
- Type conversion validation
- Custom validation rules
Configuration Options
TypedParseToJsonOptions
TypeAssemblyQualifiedName: Target type for parsingNamingStrategy: JSON naming strategy (SnakeCase, CamelCase, etc.)SheetsOptions: Configuration for multiple sheets
SheetMappingOptions
- Column mapping configuration
- Sheet selection options
- Validation rules
Entity Framework Integration
The library includes Entity Framework entities for tracking:
ExcelFileRecord: Excel file metadataSheetRecord: Individual sheet informationRowRecord: Row-level data and validation resultsCellRecord: Cell-level dataBatchandBatchItem: Batch processing tracking
Dependencies
- ExcelDataReader: For reading Excel files
- Newtonsoft.Json: JSON serialization
- Entity Framework Core: Data persistence
- SimplyWorks Libraries: Additional utilities and extensions
Target Framework
- .NET Standard 2.1 (Core library)
- .NET Core 3.1 (Extensions package)
License
This project is licensed under the MIT License - see the LICENSE file for details.
Contributing
Please read CONTRIBUTING.md for details on our code of conduct and the process for submitting pull requests.
Support
For issues and questions:
- Create an issue on GitHub
- Check existing documentation
Related Packages
This library is part of the SimplyWorks ecosystem: