USING IRONXL

1. IronXL

IronXL is a powerful software library designed for .NET developers, providing them with an easy-to-use API to read, write, and manipulate Excel files in their .NET applications. It offers a comprehensive set of features for creating, editing, and exporting Excel spreadsheets, all without requiring Microsoft Office or Excel to be installed on the target machine. With support for a wide range of file formats, including XLS, XLSX, CSV, TSV, and more, this library makes it easy for developers to create Excel-based applications that can be deployed anywhere. IronXL also offers advanced features like chart creation, data visualization, and data analysis to streamline the development process for small to large-scale projects.

2. Prerequisites

Before you can use the IronXL library to write to a database from an Excel file, you must fulfill certain prerequisites. These include:

  • Having Visual Studio installed on your computer to create a C# project.
  • Ensuring that ASP.NET is also installed on your system before creating a C# project.
  • Installing the IronXL library on your system to export data. You can obtain it by downloading the IronXL NuGet package from the NuGet Package Manager in Visual Studio.
  • Having SQL installed in Visual Studio.

3. Creating a New Project in Visual Studio

Before you can utilize the IronXL library to perform Excel-related operations, you need to create a .NET project in Visual Studio. While any version of Visual Studio is compatible, it is recommended to use the latest version available. You can choose from various project templates such as Windows Forms and ASP.NET, based on your project requirements. For this tutorial, the Console Application project template is recommended to demonstrate how to work with IronXL.

How to Read Excel Data and Insert to Database Table in C#, Figure 1: Create a new project window Create a new project

Once you have chosen the project type, you need to name the project and select its location. You can also specify the preferred framework, such as .NET Core 6, for the project.

How to Read Excel Data and Insert to Database Table in C#, Figure 2: Project configuration Project configuration

After the new project is created, you can access the program.cs file where you can write code and execute the application.

How to Read Excel Data and Insert to Database Table in C#, Figure 3: Project with code open Project with code open

Now the Visual Studio project is created, let's install IronXL.

4. Install IronXL

The IronXL library can be downloaded and installed in different ways, but for this article, two simplest methods are covered:

  • Using NuGet packages in Visual Studio.
  • Using the Visual Studio Command-Line.

4.1 Using Visual Studio

To install the IronXL library, the first approach is to use the NuGet Package Manager in Visual Studio. Simply open the NuGet Package Manager and search for IronXL in the Browse tab. Once you have located IronXL in the search results, select it and proceed with the installation. Once the installation is complete, you can start using the IronXL library in your project.

The following screenshot shows how to open the NuGet Package Manager in Visual Studio.

How to Read Excel Data and Insert to Database Table in C#, Figure 4: NuGet Package Manager NuGet Package Manager

The following screenshot shows IronXL in the search results:

How to Read Excel Data and Insert to Database Table in C#, Figure 5: IronXL search result IronXL search result

4.2 Using the Visual Studio Command-Line

Many developers prefer to install packages using a command line interface. To install IronXL using the command line, follow these steps:

  • In Visual Studio, go to Tools > NuGet Package Manager > Package Manager Console.
  • Enter the following line in the Package Manager Console tab:

    Install-Package IronXL
    Install-Package IronXL
    SHELL

The package will be downloaded and installed into the current project.

How to Read Excel Data and Insert to Database Table in C#, Figure 6: Installing via command line Installing via command line

5. Installing and Configuring the SQL Server Database

To install and integrate the SQL server database table with your C# project, first, go to the NuGet Package Manager, search for System.Data.SqlClient, and install it.

How to Read Excel Data and Insert to Database Table in C#, Figure 7: Search and install SqlClient in NuGet Package Manager UI Search and install SqlClient in NuGet Package Manager UI

Once installed, go to the project menu and click on "Add New Item".

How to Read Excel Data and Insert to Database Table in C#, Figure 8: Add New Item Add New Item

A new window will appear. Select data from the side menu and then click on Service-Based Database from the list. Write an appropriate name for the database and click the Add button.

How to Read Excel Data and Insert to Database Table in C#, Figure 9: Select Service-based Database Select Service-based Database

Then, in the Solution Explorer, right-click on the newly created database and select "Open". This will open a new sidebar.

How to Read Excel Data and Insert to Database Table in C#, Figure 10: Right-click and select Open Right-click and select Open

In the new sidebar, click on your database and go to its properties. From there, copy the connection string.

How to Read Excel Data and Insert to Database Table in C#, Figure 11: Right-click and select Properties Right-click and select Properties

How to Read Excel Data and Insert to Database Table in C#, Figure 12: Handle Connection String Handle Connection String

After copying the connection preferences, click on your database instance to open a new list. Right-click on the table folder and select "Add New Table".

How to Read Excel Data and Insert to Database Table in C#, Figure 13: Add New Table Add New Table

To create a new table in the database, follow these steps:

  1. Open a new data table design page.
  2. Add the following SQL Query, which will create a new table with three columns: Id, Name, and Number.
  3. Click on the "Update" button at the top of the page.
  4. The newly generated table will be added to the database.
CREATE TABLE [dbo].[Table]
(
  [Id] INT NOT NULL PRIMARY KEY,
  [Name] VARCHAR(100) NOT NULL,
  [Number] INT
)

Now that the SQL environment is set up, let's create some sample data to fill this database from Excel data.

How to Read Excel Data and Insert to Database Table in C#, Figure 14: Excel file data Excel file data

6. Import Excel File Data and Export to a Database using IronXL

With IronXL, developers can automate the process of transferring data between Excel files and databases, which can save a significant amount of time and effort. By utilizing IronXL, developers can streamline their workflow and eliminate the need for manual data entry, ensuring that the data is accurate and up-to-date.

Once the SQL server is set up and the connection string is copied, just paste the connection string into the code below, link your Excel file with the code, and change the SQL query if needed. Then simply run the code and the data will be exported to the database table.

using IronXL; // Import the IronXL library
using System.Data; // Import data handling library
using System.Data.SqlClient; // Import SQL Client library for SQL database operations

// Load the Excel workbook
WorkBook workBook = WorkBook.Load("book.xlsx");

// Convert the workbook into a DataSet
DataSet dataSet = workBook.ToDataSet();

// Define the SQL query to select data from the table
string sql = "SELECT * FROM [dbo].[Table]";

// Connection string for the SQL Server
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\BUTTW\SOURCE\REPOS\CREATE PDF\CREATE PDF\DATABASE1.MDF;Integrated Security=True"; 

// Open a connection to the SQL Server using the connection string
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open(); // Open connection
    // Use SqlDataAdapter to update SQL table with DataSet from Excel file
    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
    adapter.Update(dataSet);
}
using IronXL; // Import the IronXL library
using System.Data; // Import data handling library
using System.Data.SqlClient; // Import SQL Client library for SQL database operations

// Load the Excel workbook
WorkBook workBook = WorkBook.Load("book.xlsx");

// Convert the workbook into a DataSet
DataSet dataSet = workBook.ToDataSet();

// Define the SQL query to select data from the table
string sql = "SELECT * FROM [dbo].[Table]";

// Connection string for the SQL Server
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\BUTTW\SOURCE\REPOS\CREATE PDF\CREATE PDF\DATABASE1.MDF;Integrated Security=True"; 

// Open a connection to the SQL Server using the connection string
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open(); // Open connection
    // Use SqlDataAdapter to update SQL table with DataSet from Excel file
    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
    adapter.Update(dataSet);
}
Imports IronXL ' Import the IronXL library

Imports System.Data ' Import data handling library

Imports System.Data.SqlClient ' Import SQL Client library for SQL database operations



' Load the Excel workbook

Private workBook As WorkBook = WorkBook.Load("book.xlsx")



' Convert the workbook into a DataSet

Private dataSet As DataSet = workBook.ToDataSet()



' Define the SQL query to select data from the table

Private sql As String = "SELECT * FROM [dbo].[Table]"



' Connection string for the SQL Server

Private connectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\BUTTW\SOURCE\REPOS\CREATE PDF\CREATE PDF\DATABASE1.MDF;Integrated Security=True"



' Open a connection to the SQL Server using the connection string

Using connection As New SqlConnection(connectionString)

	connection.Open() ' Open connection

	' Use SqlDataAdapter to update SQL table with DataSet from Excel file

	Dim adapter As New SqlDataAdapter(sql, connection)

	adapter.Update(dataSet)

End Using
$vbLabelText   $csharpLabel

To check if the data has been successfully exported to the database, right-click on the table directory again and click on "New Query." A new page will open, select your database from the top bar and run the appropriate SQL query to retrieve the data.

SELECT * FROM [dbo].[Table]

Press the green button, and the result will be displayed in a second.

How to Read Excel Data and Insert to Database Table in C#, Figure 15: Database data Database data

This is how you can write data imported from a Microsoft Excel file to a database.

7. Conclusion

Working with Excel spreadsheets is a common task in many applications, and inserting data from an Excel sheet into a database table can streamline data management processes. One way to achieve this task in C# is by using libraries that allow reading and manipulating Excel files, such as IronXL. By utilizing this library, developers can easily extract data from an Excel sheet and insert it into a database table, simplifying the data management process and reducing the chances of errors. This article covers the steps involved in adding data from an Excel file to a SQL Server table in a SQL Server database using the IronXL library. It also provides a brief introduction to the IronXL library, discusses the prerequisites needed to insert data, and describes how to create a new project in Visual Studio, install IronXL, and configure an SQL Server database. Please visit the following tutorial to learn how to read Excel files in C#.

Additionally, IronXL also offers advanced features including support for cell formatting such as text alignment, font size, color, freeze panel, adding formulas, applying conditional formatting, and encryption with a password.

Users can also benefit from Iron Suite, a collection of software development tools that includes IronPDF, IronOCR, IronXL, IronBarcode, and IronWebscraper.

Frequently Asked Questions

What is this library for handling Excel files in .NET applications?

IronXL is a powerful software library designed for .NET developers to read, write, and manipulate Excel files in .NET applications without requiring Microsoft Office or Excel to be installed.

What are the prerequisites for using this library to insert Excel data into a database?

To use IronXL for inserting Excel data into a database, you need Visual Studio, ASP.NET, the IronXL library from NuGet Package Manager, and SQL installed in Visual Studio.

How do I create a new project in Visual Studio for using this Excel handling library?

Create a new .NET project in Visual Studio, choosing a project template like Windows Forms or ASP.NET. For this tutorial, a Console Application project template is recommended.

How can I install the Excel handling library?

You can install IronXL using the NuGet Package Manager in Visual Studio by searching for IronXL and proceeding with the installation, or by using the Visual Studio Command-Line to execute 'Install-Package IronXL'.

How do I set up a SQL Server database for use with this Excel handling library?

Install 'System.Data.SqlClient' via NuGet, add a new Service-Based Database in Visual Studio, and configure it by setting up tables and generating a connection string.

What SQL query is used to create a new table in the database?

Use the SQL query: CREATE TABLE [dbo].[Table] ( [Id] INT NOT NULL PRIMARY KEY, [Name] VARCHAR(100) NOT NULL, [Number] INT ) to create a table with columns 'Id', 'Name', and 'Number'.

How can I import Excel data and export it to a database using this library?

Load the Excel workbook using IronXL, convert it into a DataSet, and use SqlDataAdapter to update the SQL table with the DataSet from the Excel file.

How can I verify if the Excel data has been exported to the database?

Run a SQL query like 'SELECT * FROM [dbo].[Table]' in the SQL Server to retrieve and verify the data.

What are the advanced features of this Excel handling library?

IronXL offers advanced features such as chart creation, data visualization, support for cell formatting, freeze panels, adding formulas, conditional formatting, and encryption with a password.

What is the benefit of using this library for data transfer between Excel and databases?

IronXL automates the process of transferring data between Excel files and databases, saving time and effort while ensuring data accuracy and up-to-date information.

Regan Pun
Software Engineer
Regan graduated from the University of Reading, with a BA in Electronic Engineering. Before joining Iron Software, his previous job roles had him laser-focused on single tasks; and what he most enjoys at Iron Software is the spectrum of work he gets to undertake, whether it’s adding value to sales, technical support, product development or marketing. He enjoys understanding the way developers are using the Iron Software library, and using that knowledge to continually improve documentation and develop the products.
< PREVIOUS
How to Export Datatable to Excel in ASP.NET Core
NEXT >
How to Convert Dataset to Excel in C#