Test in production without watermarks.
Works wherever you need it to.
Get 30 days of fully functional product.
Have it up and running in minutes.
Full access to our support engineering team during your product trial
This article will explore how to use IronXL, a C# Excel Library, to read data from an Excel file and insert it into a database table.
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.
Before you can use the IronXL library to write to a database from an Excel file, you must fulfill certain prerequisites. These include:
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.
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.
Project configuration
After the new project is created, you can access the program.cs file where you can write code and execute the application.
Project with code open
Now the Visual Studio project is created, let's install IronXL.
The IronXL library can be downloaded and installed in different ways, but for this article, two simplest methods are covered:
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.
NuGet Package Manager
The following screenshot shows IronXL in the search results:
IronXL search result
Many developers prefer to install packages using a command line interface. To install IronXL using the command line, follow these steps:
Enter the following line in the Package Manager Console tab:
Install-Package IronXL
Install-Package IronXL
The package will be downloaded and installed into the current project.
Installing via command line
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.
Search and install SqlClient in NuGet Package Manager UI
Once installed, go to the project menu and click on "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.
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.
Right-click and select Open
In the new sidebar, click on your database and go to its properties. From there, copy the connection string.
Right-click and select Properties
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".
Add New Table
To create a new table in the database, follow these steps:
Id
, Name
, and Number
.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.
Excel file data
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
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.
Database data
This is how you can write data imported from a Microsoft Excel file to a database.
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.
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.
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.
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.
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'.
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.
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'.
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.
Run a SQL query like 'SELECT * FROM [dbo].[Table]' in the SQL Server to retrieve and verify the data.
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.
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.