Read Excel Files in C# Without Installing Excel

2024-01-19 05:36:49 Allen Yang
AI Summarize:
ChatGPT
ChatGPT ✓
Claude ✓
Grok ✓
Perplexity ✓
Quick
Quick
Concise overview
Highlights
Key takeaways
Detailed
Structured explanation
Brief
One sentence summary
Summarize |

Reading an Excel file in C# is a common task for reports, data imports, and automation. With Free Spire.XLS for .NET, a free community edition, you can load an Excel workbook and read its data without installing Microsoft Excel on the machine and without using Interop. This article shows how to read cells, worksheets, and rows, export a worksheet to a DataTable, and handle formula results.

Quick Start: Read an Excel File in C#

Install Free Spire.XLS for .NET from NuGet. Run this command in a terminal:

dotnet add package FreeSpire.XLS

Or run this command in the NuGet Package Manager Console:

Install-Package FreeSpire.XLS

You can also download Free Spire.XLS for .NET and reference the DLL manually, or find the package on NuGet.

The following example loads an Excel file, gets the first worksheet, and prints the value of cell A1. Replace input.xlsx with the full path to your file, or make sure the file is in the program's current working directory.

using System;
using Spire.Xls;

class Program
{
    static void Main()
    {
        Workbook workbook = new Workbook();

        try
        {
            // Load an existing Excel file
            workbook.LoadFromFile("input.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Read the value of cell A1 (row 1, column 1)
            string value = sheet.Range[1, 1].Value;

            Console.WriteLine("Cell A1: " + value);
        }
        finally
        {
            workbook.Dispose();
        }
    }
}

Workbook.LoadFromFile() reads the file into memory. Worksheets[0] returns the first worksheet, and Range[1, 1].Value returns the text value of the cell in the first row and first column. The finally block releases the workbook's resources when you are done.

Read Excel cell A1 value in C# with Free Spire.XLS — console output

Read a Specific Cell or Worksheet

The following examples assume that workbook has already been loaded with LoadFromFile(). They show how to access a worksheet by index or name.

An Excel file can contain several worksheets. You can get a worksheet by its position or by its name:

// By index (zero-based): the first sheet is 0, the second is 1
Worksheet first = workbook.Worksheets[0];

// By name
Worksheet sales = workbook.Worksheets["Sales"];

You can also read a cell by its row and column numbers, or by its Excel address:

// By row and column numbers (one-based): row 2, column 3
string byIndex = sales.Range[2, 3].Value;

// By Excel address
string byAddress = sales.Range["C2"].Value;

Note the two different starting points. The worksheet collection is zero-based, so the first worksheet is Worksheets[0]. Cell row and column numbers are one-based, so Range[1, 1] is cell A1.

To read a block of cells, pass a range address and loop through it:

CellRange range = sales.Range["A1:C3"];

foreach (CellRange cell in range.CellList)
{
    Console.WriteLine({BODY_CONTENT}quot;{cell.RangeAddress}: {cell.Value}");
}

Read Excel Data Row by Row in C#

To read all the data in a worksheet, loop through the rows with an outer loop and through the columns with an inner loop. Write a line break after each row so the console output keeps the structure of the worksheet:

using System;
using Spire.Xls;

class Program
{
    static void Main()
    {
        Workbook workbook = new Workbook();

        try
        {
            workbook.LoadFromFile("input.xlsx");
            Worksheet sheet = workbook.Worksheets[0];

            for (int row = 1; row <= sheet.LastDataRow; row++)
            {
                for (int col = 1; col <= sheet.LastDataColumn; col++)
                {
                    Console.Write({BODY_CONTENT}quot;{sheet.Range[row, col].Value}\t");
                }

                Console.WriteLine();
            }
        }
        finally
        {
            workbook.Dispose();
        }
    }
}

The loops run from row 1 and column 1 to LastDataRow and LastDataColumn, which return the last row and column that contain data. This avoids including formatted but empty cells that LastRow and LastColumn would cover. If your worksheet has an unusual layout, verify the actual range on your test file before processing the output.

For a worksheet that contains the sample data below:

Product Category Price
Keyboard Accessories 25
Monitor Displays 180

the console shows each worksheet row on its own line, with the cell values separated by tabs:

Product    Category    Price
Keyboard   Accessories 25
Monitor    Displays    180

Read Excel data row by row in C# with Free Spire.XLS — console output

Read Excel Data into a DataTable

If you want to process worksheet data with ADO.NET, bind it to a control, or insert it into a database, export the worksheet to a DataTable with ExportDataTable():

using System;
using System.Data;
using Spire.Xls;

class Program
{
    static void Main()
    {
        Workbook workbook = new Workbook();

        try
        {
            workbook.LoadFromFile("input.xlsx");

            // Recalculate formulas before exporting computed values
            workbook.CalculateAllValue();

            Worksheet sheet = workbook.Worksheets[0];

            DataTable dataTable = sheet.ExportDataTable(
                sheet.AllocatedRange,
                true,   // exportColumnNames
                true    // computedFormulaValue
            );

            // Print each row to verify the exported data
            foreach (DataRow row in dataTable.Rows)
            {
                Console.WriteLine(string.Join("\t", row.ItemArray));
            }
        }
        finally
        {
            workbook.Dispose();
        }
    }
}

This overload takes three parameters:

  • range: the cell range to export. sheet.AllocatedRange returns the worksheet's used range. Depending on the workbook's formatting and used-range settings, it may include empty cells. If you only need a specific data region, use an explicit range and verify its boundaries.
  • exportColumnNames: when true, the first row of the range becomes the column names of the DataTable. When false, the first row is exported as ordinary data.
  • computedFormulaValue: when true, the exporter uses calculated formula values instead of formula text. When false, formula cells are exported as their formula text. If your data contains formulas, verify the exported values on your own test file — formula calculation may need to be triggered before export for the results to populate correctly.

To show the result in a Windows Forms application, assign the table to a DataGridView:

dataGridView1.DataSource = dataTable;

Check the column names and data types of the exported table against your own Excel file before relying on them in later processing. For more options, such as exporting a specific range, see the detailed tutorial on converting Excel to DataTable in C#.

Export Excel data to DataTable and display in DataGridView — C# Free Spire.XLS

Read Formula Results and Handle Cell Values

The snippets in this section assume that sheet refers to a worksheet from a loaded workbook, as in the previous examples.

A cell that contains a formula has two things you may want to read: the formula itself and the result it produces. Use HasFormula to check whether a cell contains a formula, Formula to get the formula text, and FormulaValue to get the calculated result. If you need up-to-date formula results, call workbook.CalculateAllValue() after loading the workbook and before reading FormulaValue.

CellRange cell = sheet.Range["D2"];

if (cell.HasFormula)
{
    Console.WriteLine("Formula: " + cell.Formula);
    Console.WriteLine("Result: " + cell.FormulaValue);
}
else
{
    Console.WriteLine("Value: " + cell.Value);
}

For more on working with formulas, see Add or Read Formulas and Functions in Excel.

Read Excel formula results in C# with Free Spire.XLS — console output

Cells with different content types can be read through typed properties:

CellRange priceCell = sheet.Range["C2"];
CellRange dateCell = sheet.Range["E2"];

double price = priceCell.NumberValue;       // numeric value
DateTime date = dateCell.DateTimeValue;     // date value
string text = sheet.Range["A2"].Text;       // text as displayed

Excel stores numbers and dates in different ways than plain text, so choose the property that matches the type of data in the cell.

Read Number Formats and Displayed Values

For cells with number formats — percentages, currency, dates — the stored value, the format code, and the formatted text can all differ. A cell that stores 0.15 with a percent format displays as 15%. Use Value for the raw value, NumberFormat for the format code, and NumberText for the text as it appears in Excel:

Property What it returns
Value The raw cell value
NumberFormat The number format code (e.g., 0.00%)
NumberText The text after applying the format (e.g., 15%)
CellRange cell = sheet.Range["C2"];

Console.WriteLine("Value: " + cell.Value);
Console.WriteLine("Number format: " + cell.NumberFormat);
Console.WriteLine("Formatted text: " + cell.NumberText);

Choose the property that matches what your downstream logic needs: the numeric value for calculations, or the formatted text for display. For more on number formats, see Set Number Format in Excel.

Before reading a cell, you can check whether it is empty:

if (string.IsNullOrEmpty(sheet.Range["B5"].Value))
{
    Console.WriteLine("B5 is empty.");
}

Frequently Asked Questions

Can I read Excel files in C# without installing Microsoft Excel?

Yes. Free Spire.XLS reads Excel files directly and does not require Microsoft Excel or Interop on the computer or server that runs your code.

How do I read formula results instead of formulas?

For a single cell, check CellRange.HasFormula and read CellRange.FormulaValue. To read a whole worksheet, call ExportDataTable() with computedFormulaValue set to true. See Read Formula Results and Handle Cell Values.

Can I read password-protected Excel files?

Yes. Set the open password before loading the file:

Workbook workbook = new Workbook();
workbook.OpenPassword = "your-password";
workbook.LoadFromFile("protected.xlsx");

Which Excel file formats does Free Spire.XLS support?

Free Spire.XLS supports the .xls, .xlsx, and .xlsm formats.

What are the limitations of the free edition?

Free Spire.XLS for .NET is a community edition for personal developers and small projects. When working with the .xls format, each workbook is limited to 5 worksheets and 200 rows per sheet. Loading and saving .xlsx files are not subject to these limits. For higher capacity or advanced features, Spire.XLS for .NET is available as a commercial edition.

If you need higher capacity or advanced features, Spire.XLS for .NET is the commercial edition — you can request a free 30-day trial license to evaluate it.

See Also