You can read Excel files to parse the data from rows and columns of Excel worksheets in XLSX XLS or other related formats in C#. It is helpful to extract data from Excel files and use it in your C# applications. Accordingly, this blog post explains how to read Excel files in C# step by step.
Why Read Excel Files in C#?
Excel files are a prevalent means of storing tabular data due to their user-friendly interface and flexibility. In the realm of C# programming, extracting data from Excel files offers opportunities to automate tasks, perform data analysis, and integrate with numerous applications. This process becomes crucial in scenarios where data needs to be extracted from these files for reporting, analysis, or further processing.
Read Excel File in C#
You can easily read data from Excel worksheets. Simply follow the steps below to read Excel files in C#:
- Configure Conholdate.Total for .NET in your environment.
- Load the source Excel file with an object of the Workbook class.
- Access all the worksheets with the Worksheets property.
- Iterate through each worksheet with a For loop.
- Iterate each row and extract values in each column.
- Print the cell value to the console or use it based on your requirements.
The sample code below demonstrates how to read an Excel file in C#:
// Load Excel file | |
Workbook wb = new Workbook("excel.xlsx"); | |
// Get all worksheets | |
WorksheetCollection collection = wb.Worksheets; | |
// Loop through all the worksheets | |
for (int worksheetIndex = 0; worksheetIndex < collection.Count; worksheetIndex++) | |
{ | |
// Get worksheet using its index | |
Worksheet worksheet = collection[worksheetIndex]; | |
// Print worksheet name | |
Console.WriteLine("Worksheet: " + worksheet.Name); | |
// Get number of rows and columns | |
int rows = worksheet.Cells.MaxDataRow; | |
int cols = worksheet.Cells.MaxDataColumn; | |
// Loop through rows | |
for (int i = 0; i < rows; i++) | |
{ | |
// Loop through each column in selected row | |
for (int j = 0; j < cols; j++) | |
{ | |
// Parsing cell value | |
Console.Write(worksheet.Cells[i, j].Value + " | "); | |
} | |
// Print line break | |
Console.WriteLine(" "); | |
} | |
} |
Read Excel Data from a Specific Worksheet in C#
Sometimes you may need to extract specific data from a worksheet instead of processing the whole Workbook containing multiple sheets. Please follow the steps below to read data from an Excel file in a particular worksheet using C#:
- Install Conholdate.Total for .NET in your system.
- Create an instance of the Workbook class.
- Access any worksheet while specifying its name or zero-based index.
- Get the last populated row and column with MaxDataRow and MaxDataColumn properties.
- Loop through each row and column.
- Get the cell value.
The following code sample shows how to read data from a specific worksheet in Excel using C#:
// Load Excel file | |
Workbook wb = new Workbook("excel.xlsx"); | |
// Get worksheet using its index | |
Worksheet worksheet = wb.Worksheets[0]; | |
// Print worksheet name | |
Console.WriteLine("Worksheet: " + worksheet.Name); | |
// Get number of rows and columns | |
int rows = worksheet.Cells.MaxDataRow; | |
int cols = worksheet.Cells.MaxDataColumn; | |
// Loop through rows | |
for (int i = 0; i < rows; i++) | |
{ | |
// Loop through each column in selected row | |
for (int j = 0; j < cols; j++) | |
{ | |
// Parsing cell value | |
Console.Write(worksheet.Cells[i, j].Value + " | "); | |
} | |
// Print line break | |
Console.WriteLine(" "); | |
} |
Best Practices for Reading Excel Files in C#
Error Handling: Always handle exceptions that might occur while reading Excel files, such as file not found, file format issues, or access permission problems.
Memory Management: Dispose of objects properly to avoid memory leaks.
Performance Optimization: Use appropriate methods to read data efficiently, especially when dealing with large Excel files.
Data Validation: Validate and sanitize the data read from Excel to ensure its integrity and reliability.
Free Evaluation License
You can get a free temporary license to evaluate the API features to their full capacity.
Wrapping Up
In the world of software development, managing and manipulating data is a fundamental task. Excel files, with their widespread use in storing and organizing data, are a common format encountered in various applications. This blog post has covered different approaches to reading Excel files in C# like parsing all the data from the spreadsheet or working with a specific worksheet as per your requirements. In case of any queries, please write to us at the forum.
FAQs
Can I use C# to read both older .xls and newer .xlsx Excel file formats?
Yes, C# libraries like Conholdate.Total can handle both the older .xls and newer .xlsx formats. However, it’s recommended to work with .xlsx files whenever possible due to their improved features and performance.
Is it possible to read Excel files without Microsoft Excel installed on the system?
Yes, you do not need to install Microsoft Excel to read data from Excel worksheets in C#.
Can I read data from specific worksheets and cells in an Excel file using C#?
Yes, you can read data from specific worksheets and cells in an Excel file. Simply extract data from specific worksheets and cells by their names or coordinates.
How do I validate data read from an Excel file to ensure its integrity?
To validate data, you can perform checks on the data types, ranges, and constraints according to your application’s requirements. Verify that the data adheres to your expected format and constraints before using it.