C# to Read Excel Files and Export Data to DataTable & Database

Excel files remain a ubiquitous format for storing tabular data, making the ability to efficiently read and extract information from them crucial. C#, benefiting from the extensive functionality of the .NET Framework, is capable of efficient data manipulation. Utilizing C# to read Excel files and write their data to databases and data tables, or use the data for other purposes, is an excellent solution for developers to manipulate Excel data.

This article focuses on how to use C# to read Excel files. It includes the following parts:

• Advantages and API for Reading Excel Files in C#
• Getting Started with Excel File Reading Using C#
• Read Excel Files into Text with C# Code
• Script in C# for Reading Excel Files into DataTable
• C# Code to Read Excel Files and Insert into Databases
• Get a Free License for the API

Advantages and API for Reading Excel Files in C#

Leveraging C# for reading Excel files brings the following advantages:

• Automation and scalability: C#, with .NET Framework and APIs, allows automating Excel file reading tasks, making it possible to process large volumes of data efficiently. Developers can create scripts or applications that can handle batch processing, scheduled tasks, or data import/export operations, saving time and effort.
• Flexibility and customization: The .NET Framework along with the APIs provides a high level of flexibility and customization when working with Excel files. Developers can define custom logic to extract and transform data, apply specific formatting, or perform calculations. This allows tailoring the reading process to specific requirements or business rules.

However, reading Excel files with only C# and the .NET Framework can be a challenging task. The native library provided by the .NET Framework for handling Excel files is Microsoft.Office.Interop.Excel. However, the usage of this library requires the installation of MS Excel, and the code logic for processing Excel files with it is extremely complex, making it difficult to efficiently read Excel files. As a result, developers prefer third-party libraries to work with Excel files efficiently and conveniently.

Spire.XLS for .NET is a professional C# API for Excel file processing. It features in extensive format support and efficient Excel data manipulation, including reading Excel files and writing the data to database, datatable, etc. Users can download Spire.XLS for .NET from the official site or install it through NuGet.

PM> Install-Package Spire.XLS

Getting Started with Excel File Reading in C#

Spire.XLS for .NET allows developers to create, read, write, and manipulate Excel files in their .NET applications with simple code.

To effectively work with Spire.XLS, you'll interact with various classes, properties, and methods. Here's a table summarizing some of the main ones along with their descriptions:

The typical workflow of using Spire.XLS for .NET  to read Excel files involves these steps:

1. Load the Excel file using Workbook.LoadFromFile() method.
2. Access worksheets through the Workbook.Worksheets[] property.
3. Access cells through the Worksheet.Range[] property.
4. Access displayed text in cells through the CellRange.Text property or access the value of cells (text, numbers, formulas, etc.) through the CellRange.Value property
5. Retrieve the data, write the data to text files, databases, etc., or perform other desired operations.
6. To export cell values to a data table, developers can directly use the Worksheet.ExportDataTable() method and it will return a DataTable object.
7. Alternatively, developers can also directly convert workbook using Workbook.SaveToFile() method to any supported format.

In short, this API provides classes and properties that allow developers to load Excel files and read their contents using straightforward code, making reading Excel files with C# efficient and simple. Additionally, it enables direct conversion of workbooks and worksheets to PDF, HTML, XML, and other file formats.

C# to Read Excel Files into Text

Steps for reading Excel files and write the data to text files with C#:

1. Import the needed namespaces.
2. Create an object of Workbook class and load an Excel  file using the Workbook.LoadFromFile() method.
3. Get the first worksheet through the Workbook.Worksheets[] property.
4. Create a text file to write the data in the worksheet.
5. Iterate through the allocated rows and columns, access cells through the Worksheet.Range[] property, and get the displayed text of each cell through the CellRange.Text property and write it to the text file.
6. Release resources.

Code Example:

using Spire.Xls;
using System.IO;

class Program
{
    static void Main(string[] args)
    {
        // Load the Excel file
        Workbook workbook = new Workbook();
        workbook.LoadFromFile("Sample.xlsx");

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

        // Create an output text file
        string outputFile = "Output.txt";
        StreamWriter writer = new StreamWriter(outputFile);

        // Iterate through the rows and columns of the worksheet and write the data to the text file
        for (int row = 1; row <= worksheet.LastRow; row++)
        {
            for (int col = 1; col <= worksheet.LastColumn; col++)
            {
                CellRange range = worksheet.Range[row, col];
                string cellValue = range.Text == null ? string.Empty : range.Text.ToString();
                writer.Write(cellValue + "\t"); // Separate cell data using a tab character
            }
            writer.WriteLine(); // Write a line break
        }

        // Close the writer and save the text file
        writer.Close();

        // Release resources
        workbook.Dispose();
    }
}

The reading result:

Code in C Sharp for Reading Excel Files into DataTables

DataTable is an object that represents a data table in the .NET Framework. It is used to store and manipulate data in memory and can perform operations such as sorting, filtering, modifying, and exporting. Developers can use C# to read Excel file data and write it to the DataTable object, thus facilitating further processing of the data. The following are the steps:

1. Import the needed namespaces.
2. Create an object of Workbook class and load an Excel file using the Workbook.LoadFromFile() method.
3. Get the first worksheet through the Workbook.Worksheets[] property.
4. Export the data from the worksheet to a DataTable object using the Worksheet.ExportDataTable() method.
5. Release resources.

Code Example:

using Spire.Xls;
using System.Data;

namespace ExcelToAccess
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create an object of Workbook class
            Workbook workbook = new Workbook();

            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

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

            // Export the data from the worksheet to a DataTable object
            DataTable dataTable = worksheet.ExportDataTable();

            workbook.Dispose();
        }
    }
}

C# Code to Read Excel Files and Insert into Databases

This example shows how to use C# code to write Excel worksheet data to a database using an Access database. This method also requires the System.Data.OleDB namespace. If the .NET Framework you are using does not have it, you can install it by entering the following code in the Package Management Console:

Install-Package System.Data.OleDb

The steps for reading an Excel file and inserting into a database are as follows:

1. Import the needed namespaces.
2. Create an object of Workbook class and load an Excel  file using the Workbook.LoadFromFile() method.
3. Retrieve the first worksheet with the Workbook.Worksheets[] property.
4. Get the worksheet name as the table name by accessing the Worksheet.Name property.
5. Retrieve the first row as column names by accessing the CellRange object of the first row with Worksheet.Rows[] property and storing the value of each column in a string array.
6. Connect to the Access database using the OleDbConnection by specifying the connection string and the file path of the database.
7. Create the data table by dynamically generating an SQL query string for creating the table, including the table name, column names, and data types.
8. Execute the create table query using an OleDbCommand object and the ExecuteNonQuery method.
9. Insert the data by iterating through each row of the Excel worksheet (starting from the second row) and constructing an insert statement with parameterized queries. Insert each row of data into the Access database table.
10. Close the database connection and release resources.

Code Example:

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

class Program
{
    static void Main(string[] args)
    {
        // Set the Excel file path
        string excelFilePath = "Sample.xlsx";
        // Set the Access database file path
        string accessDbFilePath = "Sample.accdb";

        // Load the Excel file
        Workbook workbook = new Workbook();
        workbook.LoadFromFile(excelFilePath);

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

        // Use the worksheet name as the table name
        string tableName = worksheet.Name;

        // Get the first row as column names
        CellRange headerRange = worksheet.Rows[0];
        string[] columnNames = new string[headerRange.Columns.Length];
        for (int i = 0; i < headerRange.Columns.Length; i++)
        {
            columnNames[i] = headerRange.Columns[i].Value.Replace(" ", "_");
        }

        // Connect to the Access database
        string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={accessDbFilePath};Persist Security Info=False;";
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            connection.Open();

            // Create the table
            string createTableQuery = $"CREATE TABLE [{tableName}] ({string.Join(", ", columnNames.Select(c => $"[{c}] Memo"))})";
            using (OleDbCommand createTableCommand = new OleDbCommand(createTableQuery, connection))
            {
                createTableCommand.ExecuteNonQuery();
            }

            // Insert data
            string insertQuery = $"INSERT INTO [{tableName}] ({string.Join(", ", columnNames.Select(c => $"[{c}]"))}) VALUES ({string.Join(", ", columnNames.Select(c => $"@{c}"))})";
            using (OleDbCommand insertCommand = new OleDbCommand(insertQuery, connection))
            {
                foreach (CellRange row in worksheet.Rows.Cast().Skip(1))
                {
                    for (int i = 0; i < row.Columns.Length; i++)
                    {
                        insertCommand.Parameters.AddWithValue($"@{columnNames[i]}", row.Columns[i].Value);
                    }

                    insertCommand.ExecuteNonQuery();
                    insertCommand.Parameters.Clear();
                }
            }

            connection.Close();
            workbook.Dispose();
        }
    }
}

The reading and inserting result:

Get a Free License for the API

Spire.XLS for .NET provides a free trial license, allowing developers, businesses, and individual users to explore its powerful Excel file processing capabilities without any usage restrictions or watermarks. By obtaining a free license, users can maximize the potential of Spire.XLS for .NET, expand their file handling capabilities, and significantly improve their efficiency in processing Excel files.

Conclusion

In this article, we explore how to use C# to read Excel files and extract data for various purposes. By leveraging the powerful features of Spire.XLS for .NET, developers can efficiently manipulate Excel data, export it to different formats, and enhance data-driven applications. With step-by-step instructions, code examples, and graphical presentations of processing results, this guide helps optimize file processing efficiency and unlock the full potential of data analysis in C# projects.

• Extract Text from Images or Scanned PDF with C#
• Convert PDF to HTML with Python Code
• Apply Conditional Formatting in Excel with Python
• Convert PDF to Text Effortlessly with Python Code