Comment importer, lire et manipuler des données Excel en C#

Why Can't StreamReader Read Excel Files?

StreamReader is designed for plain text files, reading character data line by line using a specified encoding. Excel files, despite their spreadsheet appearance, are actually complex binary or ZIP-compressed XML structures that StreamReader cannot interpret.

static void Main(string[] args)
{
 // This code will NOT work - demonstrates the problem
 using (StreamReader reader = new StreamReader("ProductData.xlsx"))
 {
    string content = reader.ReadLine(); // read data
    Console.WriteLine(content); // Outputs garbled binary data
 }
}

static void Main(string[] args)
{
 // This code will NOT work - demonstrates the problem
 using (StreamReader reader = new StreamReader("ProductData.xlsx"))
 {
    string content = reader.ReadLine(); // read data
    Console.WriteLine(content); // Outputs garbled binary data
 }
}

IRON VB CONVERTER ERROR developers@ironsoftware.com

When you run this class Program code snippet, instead of seeing your spreadsheet data, you'll encounter binary unknown data, such as "PK♥♦" or similar system characters. This happens because XLSX files are ZIP archives containing multiple XML files, while XLS files use a proprietary binary format. StreamReader expects plain text and tries to interpret these complex structures as characters, resulting in meaningless output.

How to Read Excel Files with IronXL?

IronXL provides a straightforward solution for reading Excel files in C#. Unlike StreamReader, IronXL understands Excel's internal structure and provides intuitive methods to access your data. The library supports Windows, Linux, macOS, and Docker containers, making it ideal for modern, cross-platform applications.

First, install IronXL via NuGet Package Manager:

Install-Package IronXL.Excel

Here's how to read an Excel file properly:

using IronXL;
// Load the Excel file
WorkBook workbook = WorkBook.Load("sample.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;
// Read specific cell values
string cellValue = worksheet["A1"].StringValue;
Console.WriteLine($"Cell A1 contains: {cellValue}");
// Read a range of cells
foreach (var cell in worksheet["A1:C5"])
{
    Console.WriteLine($"{cell.AddressString}: {cell.Text}");
}

using IronXL;
// Load the Excel file
WorkBook workbook = WorkBook.Load("sample.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;
// Read specific cell values
string cellValue = worksheet["A1"].StringValue;
Console.WriteLine($"Cell A1 contains: {cellValue}");
// Read a range of cells
foreach (var cell in worksheet["A1:C5"])
{
    Console.WriteLine($"{cell.AddressString}: {cell.Text}");
}

IRON VB CONVERTER ERROR developers@ironsoftware.com

This code successfully loads your Excel file and provides clean access to cell values. The WorkBook.Load method automatically detects the file format (XLSX, XLS, XLSM, CSV) and handles all the complex parsing internally. You can access cells using familiar Excel notation like "A1" or ranges like "A1:C5", making the code intuitive for anyone familiar with Excel.

How to Read Excel from Memory Streams?

Real-world applications often need to process Excel files from streams rather than disk files. Common scenarios include handling web uploads, retrieving files from databases, or processing data from cloud storage. IronXL handles these situations elegantly:

using IronXL;
using System.IO;
// Read Excel from a memory stream
byte[] fileBytes = File.ReadAllBytes("ProductData.xlsx");
using (MemoryStream stream = new MemoryStream(fileBytes))
{
    WorkBook workbook = WorkBook.FromStream(stream);
    WorkSheet worksheet = workbook.DefaultWorkSheet;
    // Process the data
    int rowCount = worksheet.RowCount;
    Console.WriteLine($"The worksheet has {rowCount} rows");
    // Read all data into a new DataTable, return dt
    var dataTable = worksheet.ToDataTable(false);
    // Return DataTable row count 
    Console.WriteLine($"Loaded {dataTable.Rows.Count} data rows");
}

using IronXL;
using System.IO;
// Read Excel from a memory stream
byte[] fileBytes = File.ReadAllBytes("ProductData.xlsx");
using (MemoryStream stream = new MemoryStream(fileBytes))
{
    WorkBook workbook = WorkBook.FromStream(stream);
    WorkSheet worksheet = workbook.DefaultWorkSheet;
    // Process the data
    int rowCount = worksheet.RowCount;
    Console.WriteLine($"The worksheet has {rowCount} rows");
    // Read all data into a new DataTable, return dt
    var dataTable = worksheet.ToDataTable(false);
    // Return DataTable row count 
    Console.WriteLine($"Loaded {dataTable.Rows.Count} data rows");
}

IRON VB CONVERTER ERROR developers@ironsoftware.com

The WorkBook.FromStream method accepts any stream type, whether it's a MemoryStream, FileStream, or network stream. This flexibility allows you to process Excel files from various sources without saving them to disk first. The example also demonstrates converting worksheet data to a DataTable, which integrates seamlessly with databases and data-binding scenarios.

How to Convert Between Excel and CSV?

While StreamReader can handle CSV files, you often need to convert between Excel and CSV formats. IronXL makes this conversion straightforward:

using IronXL;
// Load an Excel file and save as CSV
WorkBook workbook = WorkBook.Load("data.xlsx");
workbook.SaveAsCsv("output.csv");
// Load a CSV file and save as Excel
WorkBook csvWorkbook = WorkBook.LoadCSV("input.csv");
csvWorkbook.SaveAs("output.xlsx");
// Export specific worksheet to CSV
WorkSheet worksheet = workbook.WorkSheets[0];
worksheet.SaveAsCsv("worksheet1.csv");

using IronXL;
// Load an Excel file and save as CSV
WorkBook workbook = WorkBook.Load("data.xlsx");
workbook.SaveAsCsv("output.csv");
// Load a CSV file and save as Excel
WorkBook csvWorkbook = WorkBook.LoadCSV("input.csv");
csvWorkbook.SaveAs("output.xlsx");
// Export specific worksheet to CSV
WorkSheet worksheet = workbook.WorkSheets[0];
worksheet.SaveAsCsv("worksheet1.csv");

IRON VB CONVERTER ERROR developers@ironsoftware.com

These conversions preserve your data while changing the file format. When converting Excel to CSV, IronXL flattens the first worksheet by default, but you can specify which worksheet to export. Converting from CSV to Excel creates a properly formatted spreadsheet that preserves data types and enables future formatting and formula additions.

Conclusion

StreamReader's inability to process Excel files stems from the fundamental difference between plain text and Excel's complex file structure. While StreamReader works perfectly for CSV and other text formats, true Excel files require a specialized library like IronXL that understands the binary and XML structures within.

IronXL provides an elegant solution with its intuitive API, comprehensive format support, and seamless stream processing capabilities. Whether you're building web applications, desktop software, or cloud services, IronXL handles Excel files reliably across all platforms.

Ready to start working with Excel files properly? Download IronXL's free trial that best suit your project's needs.