How to Import Excel File in C#

What Key Features Make IronXL Essential for Excel Import?

• Open, read Excel files and search data from different spreadsheet formats like XLS/CSV/TSV/XLSX files without requiring Excel installation
• Export Excel worksheets to multiple formats including XLS/XLSX/CSV/TSV/JSON and even HTML
• Encrypting and decrypting XLSM/XLTX/XLSX files with passwords for enhanced security
• Import Excel sheet as System.Data.DataSet and System.Data.DataTable objects for seamless database integration
• Excel file formulas are recalculated automatically every time a sheet is edited
• Intuitive cell range settings with a WorkSheet["A1:B10"] easy syntax for selecting ranges
• Sort cell ranges, columns, and rows with built-in methods
• Comprehensive cell styling including font customization, background colors, border and alignment, and numbering formats

What Do I Need Before Starting Excel Import?

To use IronXL in C# to read Excel files, make sure the following components are installed on your computer:

1. Visual Studio - This is the official IDE for developing C# .NET applications. Visual Studio provides excellent IntelliSense support and debugging tools that make working with Excel files easier. You can download and install Visual Studio from the Microsoft website. The Community Edition is free and sufficient for most development needs.

2. IronXL - This is the library that helps you work with Excel sheets at a given path in C#. It must be installed in your C# program before using it. IronXL can be easily downloaded from the NuGet website or installed directly through the Manage NuGet packages interface in Visual Studio tools. You can also download the .NET Excel DLL file directly if you prefer manual installation.

For production deployments, you'll need to apply a license key to remove watermarks and enable full functionality. Development and testing can be done with the trial version.

Which Namespaces Should I Add to My Code?

Once Visual Studio and IronXL are installed, reference the IronXL assembly to use IronXL in your source code. Add the following line of code at the top of the file within the new project where IronXL functions will be used:

using IronXL;

using IronXL;

This single namespace import provides access to all the core functionality needed for Excel operations. If you're working with specific Excel features like charts or conditional formatting, you may need to import additional namespaces from the IronXL library.

How Do I Load and Open an Excel File?

// Supported Excel spreadsheet formats for reading include: XLSX, XLS, CSV, and TSV
WorkBook workbook = WorkBook.Load("test.xlsx");

// You can also load from a stream
using (var stream = File.OpenRead("test.xlsx"))
{
    WorkBook streamWorkbook = WorkBook.Load(stream);
}

// Or load CSV files with custom delimiters
WorkBook csvWorkbook = WorkBook.LoadCSV("data.csv", delimiter: ",");

// Supported Excel spreadsheet formats for reading include: XLSX, XLS, CSV, and TSV
WorkBook workbook = WorkBook.Load("test.xlsx");

// You can also load from a stream
using (var stream = File.OpenRead("test.xlsx"))
{
    WorkBook streamWorkbook = WorkBook.Load(stream);
}

// Or load CSV files with custom delimiters
WorkBook csvWorkbook = WorkBook.LoadCSV("data.csv", delimiter: ",");

// Get the first worksheet
WorkSheet sheet = workbook.WorkSheets.First();

// Or access by worksheet name
WorkSheet namedSheet = workbook.GetWorkSheet("Sheet1");

// Or by index (zero-based)
WorkSheet indexedSheet = workbook.WorkSheets[0];

// Get the first worksheet
WorkSheet sheet = workbook.WorkSheets.First();

// Or access by worksheet name
WorkSheet namedSheet = workbook.GetWorkSheet("Sheet1");

// Or by index (zero-based)
WorkSheet indexedSheet = workbook.WorkSheets[0];

How Can I Read Specific Cell Values After Import?

Once the Excel file is imported, it's ready for reading data. Reading Excel file data in C# using IronXL is very simple and easy. You can read Excel cell values by simply mentioning the cell reference number in standard Excel notation (like "A1", "B2", etc.). The library provides multiple ways to access cell values depending on the expected data type.

The code below retrieves the value of a cell with reference number "C2":

// Select cells easily in Excel-notation and return the value
int cellValue = sheet["C2"].IntValue;

// Display the value
Console.WriteLine(cellValue);

// You can also access different data types
string textValue = sheet["A2"].StringValue;
decimal decimalValue = sheet["C2"].DecimalValue;
double doubleValue = sheet["C2"].DoubleValue;
DateTime dateValue = sheet["D2"].DateTimeValue;
bool boolValue = sheet["E2"].BoolValue;

// Check if cell is empty
bool isEmpty = sheet["F2"].IsEmpty;

// Select cells easily in Excel-notation and return the value
int cellValue = sheet["C2"].IntValue;

// Display the value
Console.WriteLine(cellValue);

// You can also access different data types
string textValue = sheet["A2"].StringValue;
decimal decimalValue = sheet["C2"].DecimalValue;
double doubleValue = sheet["C2"].DoubleValue;
DateTime dateValue = sheet["D2"].DateTimeValue;
bool boolValue = sheet["E2"].BoolValue;

// Check if cell is empty
bool isEmpty = sheet["F2"].IsEmpty;

The output is as follows:

The debug console output after successfully reading an Excel file in C#. The program has completed execution and is waiting for user input to close.

Now, let's read data from a range of cells in the opened Excel file. IronXL makes it easy to work with cell ranges using familiar Excel notation. The code goes as follows:

// Read from a range of cells elegantly.
foreach (var cell in sheet["A2:A6"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// You can also work with entire rows or columns
var row = sheet.GetRow(2); // Get row 3 (0-indexed)
foreach (var cell in row)
{
    Console.WriteLine($"Row cell value: {cell.Value}");
}

// Or get a column
var column = sheet.GetColumn(0); // Get column A
foreach (var cell in column)
{
    Console.WriteLine($"Column A value: {cell.Value}");
}

// Read from a range of cells elegantly.
foreach (var cell in sheet["A2:A6"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// You can also work with entire rows or columns
var row = sheet.GetRow(2); // Get row 3 (0-indexed)
foreach (var cell in row)
{
    Console.WriteLine($"Row cell value: {cell.Value}");
}

// Or get a column
var column = sheet.GetColumn(0); // Get column A
foreach (var cell in column)
{
    Console.WriteLine($"Column A value: {cell.Value}");
}

The code is very simple, clean, and clear. The range of cells can be referenced with simple syntax as shown in a foreach loop: sheet["A2:A6"] and each cell can be iterated to get its value. Here, you'll see the names in column A from row 2 to row 6 on the console output:

Console output demonstrating successful reading of a range of Excel cells (A2:A6) containing names using C#

For more details on reading and writing to cell values, check out this tutorial on reading Excel files in C# example. You can also explore advanced features like applying formulas or working with named ranges.

How Do I Import Entire Worksheets at Once?

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.WorkSheets.First();

// Traverse all rows of Excel WorkSheet
for (int i = 0; i < sheet.Rows.Count(); i++)
{
    // Traverse all columns of specific Row
    for (int j = 0; j < sheet.Columns.Count(); j++)
    {
        // Get the values
        string val = sheet.Rows[i].Columns[j].Value.ToString();
        Console.Write("{0}\t", val);
    }
    Console.WriteLine();
}

// Alternative method using LINQ for more efficient processing
var allData = sheet.Rows.SelectMany(row => 
    row.Columns.Select(col => col.Value?.ToString() ?? "")).ToList();

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.WorkSheets.First();

// Traverse all rows of Excel WorkSheet
for (int i = 0; i < sheet.Rows.Count(); i++)
{
    // Traverse all columns of specific Row
    for (int j = 0; j < sheet.Columns.Count(); j++)
    {
        // Get the values
        string val = sheet.Rows[i].Columns[j].Value.ToString();
        Console.Write("{0}\t", val);
    }
    Console.WriteLine();
}

// Alternative method using LINQ for more efficient processing
var allData = sheet.Rows.SelectMany(row => 
    row.Columns.Select(col => col.Value?.ToString() ?? "")).ToList();

// Convert worksheet to DataTable for easier manipulation
DataTable dataTable = sheet.ToDataTable(true); // true = first row contains headers

// Process data using DataTable methods
foreach (DataRow row in dataTable.Rows)
{
    foreach (var item in row.ItemArray)
    {
        Console.Write($"{item}\t");
    }
    Console.WriteLine();
}

// Convert worksheet to DataTable for easier manipulation
DataTable dataTable = sheet.ToDataTable(true); // true = first row contains headers

// Process data using DataTable methods
foreach (DataRow row in dataTable.Rows)
{
    foreach (var item in row.ItemArray)
    {
        Console.Write($"{item}\t");
    }
    Console.WriteLine();
}

What Are the Next Steps After Importing Excel Files?

This article demonstrated how to import and read an Excel file in C# without Microsoft Excel installed. It covered multiple ways to read data from an Excel spreadsheet, from individual cells to entire worksheets. IronXL also helps you create Excel files in C# without any Excel installation required.

After successfully importing Excel data, you might want to explore additional features:

• Data Processing: Sort cell ranges, apply filters, or group rows and columns
• Data Validation: Implement data validation rules to ensure data integrity
• Formatting: Apply cell styling, conditional formatting, or number formats
• Charts and Visuals: Create charts or add images to enhance data presentation
• Security: Password protect workbooks or individual worksheets

IronXL provides an all-in-one solution for all Microsoft Excel document-related tasks to be implemented programmatically. You can perform formula calculations, string or number sorting, trimming and appending, find and replace, merge and unmerge cells, save files, and much more. You can edit cell values and also set cell data formats along with validating spreadsheet data. It also supports CSV files and helps you work with Excel data seamlessly.

For enterprise applications, consider exploring deployment options for various platforms including Docker containers, Azure Functions, and AWS Lambda.

IronXL is available for a free trial and can be licensed for commercial use with its Lite package starting from $799 only. The library offers excellent documentation, tutorials, and code examples to help you get started quickly with Excel file operations in your C# applications.