How to read Excel file in C#, VB.NET?

13 mins read

The Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents without the dependency of interop Excel library. Also, converts Excel documents to PDF files and much more. Read Excel file and write into Excel file are the common features that are very essential for almost every business application.

This article demonstrates how to read Excel file and Excel data in C# and VB.NET by using Syncfusion Excel (XlsIO) library. The code examples for the following operations are also shown.

Read/Write an Excel file and read different types of Excel data
Iterate the cells in the used range of the Excel file to read Excel data
Read an Excel file and export to DataTable

Steps to read Excel file and Excel data programmatically:

Step 1: Create a new C# console application project.

Create a new C# console application

Step 2: Install Syncfusion.XlsIO.WinForms NuGet package as a reference to your .NET Framework applications from the NuGet.org.

Install XlsIO NuGet package

Step 3: Include the following namespace in the Program.cs file.

using Syncfusion.XlsIO;

VB.NET

Imports Syncfusion.XlsIO

Step 4: Add the below code snippets to read Excel file and its data. The description of some important class members used in the code examples are as follows:

ExcelEngine should be initialized to access the Excel object.
The Open() method of IWorkbooks interface opens the existing Excel workbook from the specified location. Moreover, you can also open the specified stream.
You can access any of the Worksheets with its zero-based index for further manipulations.
String values can be read from a cell range using the Text property.
Likewise, number values can be read from a cell range using the Number property.
Date and time values can be read from a cell using the DateTime property.
Excel formulas can be read using the Formula property.
The EnableSheetCalculations() method of IWorksheet interface should be invoked to perform calculation in an Excel workbook, and to evaluate formula.
Excel formulas can be evaluated using the CalculatedValue property.
The DisplayText property can be used to retrieve the resultant value of a cell with its number format applied.
Using the UsedRange property, you can get the used range on the specified worksheet.
Finally, the SaveAs() method can be used to save the Excel file to the disk.
The ExportDataTable() method can be used to export worksheet data in the specified range into a DataTable.

Read Excel file and the different types of Excel data

The following code snippet in C# and VB.NET reads an Excel file, prints the different types of data present in the Excel file, and writes the loaded Excel file.

//Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Initialize application
    IApplication app = excelEngine.Excel;
 
    //Set default application version as Xlsx
    app.DefaultVersion = ExcelVersion.Xlsx;
 
    //Open existing Excel workbook from the specified location
    string inputFileName = "Sample.xlsx";
    IWorkbook workbook = app.Workbooks.Open(inputFileName, ExcelOpenType.Automatic);
 
    //Access the first worksheet
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //Read number value
    int id = (int)worksheet["A2"].Number;
 
    //Read text value
    string name = worksheet["B2"].Text;
 
    //Read date value
    DateTime dob = worksheet["C2"].DateTime;
 
    //Read formula
    string ageFormula = worksheet["D2"].Formula;
 
    //Enable calculations
    worksheet.EnableSheetCalculations();
 
    //Read calculated value
    string formulaValue = worksheet["D2"].CalculatedValue;
 
    //Get display text
    string displayText = worksheet["C2"].DisplayText;
 
    //Save the Excel workbook to disk in xlsx format
    workbook.SaveAs("Output.xlsx");
 
    //Print the values that are read from the Excel file
    Console.WriteLine("Number in cell A2: {0}", id);
    Console.WriteLine("Text in cell B2: {0}", name);
    Console.WriteLine("DateTime in cell C2: {0}", dob);
    Console.WriteLine("Formula in cell D2: {0}", ageFormula);
    Console.WriteLine("Calculated formula value in cell D2: {0}", formulaValue);
    Console.WriteLine("Display text in cell C2: {0}", displayText);
    Console.Read();
}

VB.NET

'Instantiate the spreadsheet creation engine
Using excelEngine As ExcelEngine = New ExcelEngine
 
    'Initialize application
    Dim app As IApplication = excelEngine.Excel
 
    'Set default application version as Xlsx
    app.DefaultVersion = ExcelVersion.Xlsx
 
    'Open existing Excel workbook from the specified location
    Dim inputFileName As String = "Sample.xlsx"
    Dim workbook As IWorkbook = app.Workbooks.Open(inputFileName, ExcelOpenType.Automatic)
 
    'Access the first worksheet
    Dim worksheet As IWorksheet = workbook.Worksheets(0)
 
    'Read number value
    Dim id As Integer = CInt(worksheet("A2").Number)
 
    'Read text value
    Dim name As String = worksheet("B2").Text
 
    'Read date value
    Dim dob As DateTime = worksheet("C2").DateTime
 
    'Read formula
    Dim ageFormula As String = worksheet("D2").Formula
 
    'Enable calculations
    worksheet.EnableSheetCalculations()
 
    'Read calculated value
    Dim formulaValue As String = worksheet("D2").CalculatedValue
 
    'Get display text
    Dim displayText As String = worksheet("C2").DisplayText
 
    'Save the Excel workbook to disk in xlsx format
    workbook.SaveAs("Output.xlsx")
 
    'Print the values that are read from the Excel file
    Console.WriteLine("Number in cell A2: {0}", id)
    Console.WriteLine("Text in cell B2: {0}", name)
    Console.WriteLine("DateTime in cell C2: {0}", dob)
    Console.WriteLine("Formula in cell D2: {0}", ageFormula)
    Console.WriteLine("Calculated formula value in cell D2: {0}", formulaValue)
    Console.WriteLine("Display text in cell C2: {0}", displayText)
    Console.Read()
End Using

A complete working example to read Excel file and its data from code along with input Excel file used can be downloaded from Read Excel File.zip.

By executing the program, you will get the loaded and saved Excel file as shown below.

Loaded and saved Excel document

Also, the values are read from the Excel file and printed in the output console window as follows.

Read Excel data in console output window

Read Excel data within the used range – cell by cell iteration

The following code snippet reads data within the used range of the Excel file by iterating rows and columns.

//Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Initialize application
    IApplication app = excelEngine.Excel;
 
    //Set default application version as Xlsx
    app.DefaultVersion = ExcelVersion.Xlsx;
 
    //Open existing Excel workbook from the specified location
    string inputFileName = "Sample.xlsx";
    IWorkbook workbook = app.Workbooks.Open(inputFileName, ExcelOpenType.Automatic);
 
    //Access the first worksheet
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //Access the used range of the Excel file
    IRange usedRange = worksheet.UsedRange;                
    int lastRow = usedRange.LastRow;
    int lastColumn = usedRange.LastColumn;
    //Iterate the cells in the used range and print the cell values
    for (int row = 1; row <= lastRow; row++)
    {
        for (int col = 1; col <= lastColumn; col++)
        {
            Console.Write(worksheet[row, col].Value);
            Console.Write("\t\t");
        }
        Console.WriteLine("\n");
    }
    Console.WriteLine("\n\n");
    //Iterate the cells in the used range and print the display text
    for (int row = 1; row <= lastRow; row++)
    {
        for (int col = 1; col <= lastColumn; col++)
        {
            Console.Write(worksheet[row, col].DisplayText);
            Console.Write("\t\t");
        }
        Console.WriteLine("\n");
    }
    Console.Read();
}

VB.NET

'Instantiate the spreadsheet creation engine
Using excelEngine As ExcelEngine = New ExcelEngine
 
    'Initialize application
    Dim app As IApplication = excelEngine.Excel
 
    'Set default application version as Xlsx
    app.DefaultVersion = ExcelVersion.Xlsx
 
    'Open existing Excel workbook from the specified location
    Dim inputFileName As String = "Sample.xlsx"
    Dim workbook As IWorkbook = app.Workbooks.Open(inputFileName, ExcelOpenType.Automatic)
 
    'Access the first worksheet
    Dim worksheet As IWorksheet = workbook.Worksheets(0)
 
    'Access the used range of the Excel file
    Dim usedRange As IRange = worksheet.UsedRange
    Dim lastRow As Integer = usedRange.LastRow
    Dim lastColumn As Integer = usedRange.LastColumn
    'Iterate the cells in the used range and print the cell values
    For row As Integer = 1 To lastRow
        For col As Integer = 1 To lastColumn
            Console.Write(worksheet(row, col).Value)
            Console.Write(vbTab & vbTab)
        Next
        Console.WriteLine(vbLf)
    Next
    Console.WriteLine(vbLf & vbLf)
    'Iterate the cells in the used range and print the display text
    For row As Integer = 1 To lastRow
        For col As Integer = 1 To lastColumn
            Console.Write(worksheet(row, col).DisplayText)
            Console.Write(vbTab & vbTab)
        Next
        Console.WriteLine(vbLf)
    Next
    Console.Read()
End Using

A complete working example to iterate each cell in the used range and read data from an Excel file in C# and VB.NET can be downloaded from Read Excel data in used range.zip.

The following is the screenshot of the sample Excel file.

Sample input Excel document

By executing the program, you will get the following output.

Cell values and display text in the used range

Read Excel file and export to DataTable in C# and VB.NET

To use the DataTable object, the following namespace must be included in addition to `Syncfusion.XlsIO`.

using System.Data;

VB.NET

Imports System.Data

Now, the following code example helps you to read an Excel file and load it to DataTable.

//Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Initialize application
    IApplication app = excelEngine.Excel;
 
    //Set default application version as Xlsx
    app.DefaultVersion = ExcelVersion.Xlsx;
 
    //Open existing Excel workbook from the specified location
    string inputFileName = "Sample.xlsx";
    IWorkbook workbook = app.Workbooks.Open(inputFileName, ExcelOpenType.Automatic);
 
    //Access the first worksheet
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //Export Excel to DataTable
    DataTable dataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
}

VB.NET

'Instantiate the spreadsheet creation engine
Using excelEngine As ExcelEngine = New ExcelEngine
 
    'Initialize application
    Dim app As IApplication = excelEngine.Excel
 
    'Set default application version as Xlsx
    app.DefaultVersion = ExcelVersion.Xlsx
 
    'Open existing Excel workbook from the specified location
    Dim inputFileName As String = "Sample.xlsx"
    Dim workbook As IWorkbook = app.Workbooks.Open(inputFileName, ExcelOpenType.Automatic)
 
    'Access the first worksheet
    Dim worksheet As IWorksheet = workbook.Worksheets(0)
 
    'Export Excel to DataTable
    Dim dataTable As DataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)
End Using

A complete working example to read an Excel file and export it to DataTable in C# and VB.NET can be downloaded from Excel to DataTable.zip.

By executing the program, you will get the DataTable as follows.

DataTable exported from Excel file

Take a moment to explore the rich set of Syncfusion Excel (XlsIO) library features.

Besides, here is an online sample link to read Excel file.

To learn more about the Syncfusion Excel (XlsIO) library, refer to the documentation where you will find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheet or workbook, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheet using Template Markers, and most importantly PDF and Image conversions with code examples.

How to read Excel file in C#, VB.NET?

Steps to read Excel file and Excel data programmatically:

Read Excel file and the different types of Excel data

Read Excel data within the used range – cell by cell iteration

Read Excel file and export to DataTable in C# and VB.NET

Access denied