best way to read excel file in c#
Learn the best way to read Excel files in C# using EPPlus or ClosedXML. This guide covers setup, code examples, type handling, performance tips, and robust mapping to domain models for reliable data loading.
The best way to read excel file in c# is to use a modern OpenXML-friendly library such as EPPlus or ClosedXML. These libraries hide the low-level OpenXML details and expose a clean API to load a workbook, loop through worksheets, and read rows and cells with strong typing. Install via NuGet, load the workbook, iterate sheets and rows, and map values to your domain objects.
Why this topic matters for modern .NET apps
According to XLS Library, reading excel files efficiently in C# starts with selecting a library that abstracts the OpenXML layer and provides a robust object model. In practice, developers choose EPPlus or ClosedXML because they offer simple APIs to access worksheets, rows, and cells without parsing every row manually. This section sets the stage for a production-grade approach, emphasizing reliability, maintainability, and clear data mapping.
// EPPlus example (read a single cell)
using OfficeOpenXml;
using System.IO;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
var file = new FileInfo(@"data\\sample.xlsx");
using (var package = new ExcelPackage(file))
{
var ws = package.Workbook.Worksheets[0];
var value = ws.Cells[2, 1].Text; // read A2 as string
Console.WriteLine(value);
}Notes:
- EPPlus and ClosedXML both support .xlsx files; they differ in licensing and API shape.
- For large files, consider streaming options or a reader-based approach as an alternative.
codeFenceLanguageGroupsForSection1Block1TitleBulletPointsNotesInLineToSupportNarrowerUse
Steps
Estimated time: 1-2 hours
- 1
Choose library and set up project
Decide between EPPlus or ClosedXML based on licensing and API preferences. Create a new console project or use an existing one.
Tip: Document your chosen library's licensing terms before production use. - 2
Add package references
Install EPPlus or ClosedXML via dotnet CLI and restore to fetch dependencies.
Tip: Prefer dotnet add package over manual NuGet copy for reliability. - 3
Define your domain model
Create simple POCOs that map Excel columns to properties, e.g., Name, Age, DateOfBirth.
Tip: Keep properties nullable where input data may be missing. - 4
Read data from the worksheet
Iterate rows, read cells using GetValue<T>, and handle missing or malformed cells.
Tip: Validate input and clamp values to expected ranges. - 5
Map to objects and test
Transform rows into domain objects; write unit/integration tests with sample Excel data.
Tip: Test edge cases: empty rows, extra columns, and default values.
Prerequisites
Required
- Required
- C# knowledge (classes, generics, collections)Required
- VS Code or Visual Studio 2022+Required
- NuGet package management (built-in or CLI)Required
Optional
- Sample Excel file (xlsx) for testingOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Open command palette (readability & navigation)In VS Code | Ctrl+⇧+P |
| Format documentFormat code in the current file | Ctrl+K Ctrl+D |
| Comment selected linesComment out code blocks during examples | Ctrl+K Ctrl+C |
| Build solutionBuild to verify changes | Ctrl+⇧+B |
| Search in fileFind specific cells or values | Ctrl+F |
People Also Ask
Which library should I choose for reading Excel in C#?
EPPlus and ClosedXML are the most popular options. EPPlus offers a compact API and strong performance, while ClosedXML provides a friendlier object model. For commercial use, verify licensing terms before selecting.
EPPlus and ClosedXML are the go-to choices; pick based on licensing and API style.
Can I read legacy .xls files with these libraries?
EPPlus and ClosedXML target .xlsx formats. If you need to read .xls files, consider libraries like NPOI or ExcelDataReader that support legacy formats.
No, use a library that supports .xls, such as NPOI or ExcelDataReader.
Is EPPlus free for commercial use?
EPPlus moved to a new licensing model. Review the official docs for whether you need a commercial license or use the non-commercial path for development.
Licensing can vary; check EPPlus documentation for current terms.
How do I read a date cell correctly?
Read dates with GetValue<DateTime?> and validate that the value is not null before using it. If the cell contains a string, consider DateTime.TryParse as a fallback.
Use GetValue<DateTime?> and validate; fall back to TryParse if needed.
What about performance on large sheets?
For large sheets, stream data or read in chunks rather than loading the entire sheet. Consider ExcelDataReader for streaming and minimize memory usage.
Stream data when possible to keep memory usage low.
The Essentials
- Choose a library that fits licensing and API needs
- Read cells with strong typing for fewer runtime errors
- Map rows to domain models for clean data handling
- Test with realistic Excel samples to catch edge cases