How to Import, Read, and Manipulate Excel Data in 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. Modern XLSX files follow the Office Open XML standard, while older XLS files use a proprietary binary format.

static void Main(string[] args)
{
 // This code will NOT work - demonstrates the problem
 using (StreamReader reader = new StreamReader("ProductData.xlsx"))
 {
    string content = reader.ReadLine(); // Attempts to read Excel as text
    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(); // Attempts to read Excel as text
    Console.WriteLine(content); // Outputs garbled binary data
 }
}

When you run this code snippet, instead of seeing your spreadsheet data, you'll encounter binary data, such as "PK♥♦" or similar 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. For containerized applications, attempting to use native Excel libraries or COM Interop would require installing Microsoft Office in your container, dramatically increasing image size and complexity.

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 perfect for modern, cross-platform applications. For DevOps teams, IronXL's zero-dependency architecture means no native libraries or COM components to manage during deployment.

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}");
}

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.

For containerized deployments, you can easily include health check endpoints that verify Excel processing capabilities:

// Health check endpoint for containerized apps
public async Task<IActionResult> HealthCheck()
{
    try
    {
        // Test Excel functionality
        using var workbook = WorkBook.Create(ExcelFileFormat.XLSX);
        var sheet = workbook.CreateWorkSheet("HealthCheck");
        sheet["A1"].Value = DateTime.UtcNow;

        // Convert to byte array for validation
        var bytes = workbook.ToByteArray();
        return Ok(new { 
            status = "healthy", 
            excelSupport = true,
            timestamp = DateTime.UtcNow 
        });
    }
    catch (Exception ex)
    {
        return StatusCode(503, new { 
            status = "unhealthy", 
            error = ex.Message 
        });
    }
}

// Health check endpoint for containerized apps
public async Task<IActionResult> HealthCheck()
{
    try
    {
        // Test Excel functionality
        using var workbook = WorkBook.Create(ExcelFileFormat.XLSX);
        var sheet = workbook.CreateWorkSheet("HealthCheck");
        sheet["A1"].Value = DateTime.UtcNow;

        // Convert to byte array for validation
        var bytes = workbook.ToByteArray();
        return Ok(new { 
            status = "healthy", 
            excelSupport = true,
            timestamp = DateTime.UtcNow 
        });
    }
    catch (Exception ex)
    {
        return StatusCode(503, new { 
            status = "unhealthy", 
            error = ex.Message 
        });
    }
}

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 like AWS S3 or Azure Blob Storage. IronXL handles these situations seamlessly:

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 DataTable
    var dataTable = worksheet.ToDataTable(false);
    // Display 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 DataTable
    var dataTable = worksheet.ToDataTable(false);
    // Display DataTable row count 
    Console.WriteLine($"Loaded {dataTable.Rows.Count} data rows");
}

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. For microservices architectures, this stream-based approach minimizes disk I/O and improves performance.

[HttpPost("upload")]
public async Task<IActionResult> ProcessExcelUpload(IFormFile file)
{
    if (file == null || file.Length == 0)
        return BadRequest("No file uploaded");

    using var stream = new MemoryStream();
    await file.CopyToAsync(stream);
    stream.Position = 0;

    var workbook = WorkBook.FromStream(stream);
    var worksheet = workbook.DefaultWorkSheet;

    // Process and return results
    var data = worksheet.ToDataSet();
    return Ok(new { 
        sheets = workbook.WorkSheets.Count,
        rows = worksheet.RowCount,
        processed = DateTime.UtcNow
    });
}

[HttpPost("upload")]
public async Task<IActionResult> ProcessExcelUpload(IFormFile file)
{
    if (file == null || file.Length == 0)
        return BadRequest("No file uploaded");

    using var stream = new MemoryStream();
    await file.CopyToAsync(stream);
    stream.Position = 0;

    var workbook = WorkBook.FromStream(stream);
    var worksheet = workbook.DefaultWorkSheet;

    // Process and return results
    var data = worksheet.ToDataSet();
    return Ok(new { 
        sheets = workbook.WorkSheets.Count,
        rows = worksheet.RowCount,
        processed = DateTime.UtcNow
    });
}

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, which is particularly useful for ETL pipelines and data integration scenarios common in DevOps workflows:

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");

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.

For automated data pipelines, you can also export to JSON or XML formats:

// Export Excel to multiple formats for data pipelines
var workbook = WorkBook.Load("report.xlsx");

// Export to JSON for API responses
string jsonData = workbook.ToJson();

// Export to HTML for web display
workbook.SaveAsHtml("report.html");

// Export to XML for integration systems
workbook.SaveAsXml("report.xml");

// Export specific range to DataTable for database insertion
var dataTable = workbook.DefaultWorkSheet["A1:D10"].ToDataTable();

// Export Excel to multiple formats for data pipelines
var workbook = WorkBook.Load("report.xlsx");

// Export to JSON for API responses
string jsonData = workbook.ToJson();

// Export to HTML for web display
workbook.SaveAsHtml("report.html");

// Export to XML for integration systems
workbook.SaveAsXml("report.xml");

// Export specific range to DataTable for database insertion
var dataTable = workbook.DefaultWorkSheet["A1:D10"].ToDataTable();

Container Deployment Best Practices

For DevOps engineers deploying Excel processing applications, IronXL offers several advantages. Here's a production-ready Dockerfile optimized for Excel processing:

FROM mcr.microsoft.com/dotnet/aspnet:8.0 AS base
WORKDIR /app
EXPOSE 80
EXPOSE 443

# Install any required system fonts for Excel rendering
RUN apt-get update && apt-get install -y \
    fontconfig \
    libfreetype6 \
    && rm -rf /var/lib/apt/lists/*

FROM mcr.microsoft.com/dotnet/sdk:8.0 AS build
WORKDIR /src
COPY ["YourApp.csproj", "."]
RUN dotnet restore
COPY . .
RUN dotnet build -c Release -o /app/build

FROM build AS publish
RUN dotnet publish -c Release -o /app/publish

FROM base AS final
WORKDIR /app
COPY --from=publish /app/publish .

# Set environment variables for IronXL
ENV IRONXL_LICENSE_KEY=${IRONXL_LICENSE_KEY}
ENV DOTNET_SYSTEM_GLOBALIZATION_INVARIANT=false

ENTRYPOINT ["dotnet", "YourApp.dll"]

This Dockerfile ensures your Excel processing application runs smoothly in containers with minimal dependencies. The licensing is handled through environment variables, making it easy to manage across different deployment environments.

What Are the Next Steps for Excel Processing in C#?

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 a comprehensive solution with its intuitive API, extensive 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. The library's support for conditional formatting, charts, formulas, and advanced Excel features makes it a complete solution for enterprise applications.

For DevOps teams, IronXL's container-friendly architecture, minimal system dependencies, and solid performance characteristics make it an ideal choice for modern cloud-native applications. The library supports horizontal scaling, works seamlessly in Kubernetes pods, and integrates well with CI/CD pipelines.

Ready to start working with Excel files properly? Download IronXL's free trial that best suits your project's needs. The library offers flexible licensing options including development, staging, and production deployments, with options for containerized environments and cloud-native applications.