Efficiently integrating data between systems is vital for boosting productivity and informed decision-making. A common task in this area is transferring data between Excel and databases. Importing Excel files into a database enables businesses to utilize powerful features like efficient queries, transaction support, and concurrency control, which Excel lacks. Conversely, exporting database data to Excel allows for detailed analysis, reporting, and sharing in a widely used and familiar format. In this article, we will explore how to import Excel data into databases and export data from databases into Excel files using Spire.XLS for .NET with C#.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Import Excel Data into Databases with C#
With the help of Spire.XLS for .NET, we can use the Workbook.LoadFromFile() method to load an Excel file and then access the cell data using CellRange.Value property. Subsequently, we can utilize the relevant database operation modules, such as the System.Data.SQLite module for SQLite, to write the data into the database. This approach enables the seamless import of data from an Excel file into a database.
The following steps and code use SQLite as an example to demonstrate how to import Excel data into a database using C#:
- Define the path for the Excel file and the output database.
- Create an instance of Workbook class and load an Excel file using Workbook.LoadFromFile() method.
- Create a new SQLite database or connect to an existing database.
- Iterate through each worksheet in the workbook and create a table in the database for each worksheet.
- Get the cells in the first row through Worksheet.Rows.CellList property.
- Iterate through the cells to get their values through CellRange.Value property, and use these values as the column names of the database table.
- Iterate through the rest rows and cells, and insert them as values into the database.
- Close the database connection and release resources.
- C#
using System.Data.SQLite;
using Spire.Xls;
namespace ExcelToSQLite
{
class Program
{
static void Main(string[] args)
{
// Excel file path
string excelFilePath = "Sample.xlsx";
// SQLite database path
string sqliteFilePath = "output/Database.db";
// Open the Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile(excelFilePath);
// If the database file doesn't exist, create it
if (!File.Exists(sqliteFilePath))
{
SQLiteConnection.CreateFile(sqliteFilePath);
Console.WriteLine("A new SQLite database file has been created: output.db");
}
// Create SQLite connection
using (SQLiteConnection connection = new SQLiteConnection($"Data Source={sqliteFilePath};Version=3;"))
{
connection.Open();
// Iterate through each worksheet
foreach (Worksheet sheet in workbook.Worksheets)
{
string tableName = sheet.Name;
// Get the first row as column names
var columns = sheet.Rows[0].CellList;
string createTableQuery = $"CREATE TABLE IF NOT EXISTS [{tableName}] (";
foreach (var column in columns)
{
createTableQuery += $"[{column.Value}] TEXT,";
}
createTableQuery = createTableQuery.TrimEnd(',') + ");";
// Create table
using (SQLiteCommand createTableCommand = new SQLiteCommand(createTableQuery, connection))
{
createTableCommand.ExecuteNonQuery();
}
// Insert data
for (int i = 1; i < sheet.Rows.Length; i++) // Skip the first row
{
var row = sheet.Rows[i];
string insertQuery = $"INSERT INTO [{tableName}] VALUES (";
foreach (var cell in row.CellList)
{
insertQuery += $"'{cell.Value?.Replace("'", "''")}',"; // Prevent SQL injection
}
insertQuery = insertQuery.TrimEnd(',') + ");";
using (SQLiteCommand insertCommand = new SQLiteCommand(insertQuery, connection))
{
insertCommand.ExecuteNonQuery();
}
}
}
connection.Close();
workbook.Dispose();
}
Console.WriteLine("Excel data has been successfully written to the new SQLite database!");
}
}
}
Export Data from Databases into Excel Files with C#
Similarly, we can use the database handling module to read data from the database. Then, by creating a Workbook object, we can generate an Excel file and use the CellRange.Value property to write the data into the Excel file. This allows us to export data from the database to an Excel file.
The following steps and code use an SQLite database as an example to demonstrate how to export data from a database to an Excel file.
- Define the path for the database and the output Excel file.
- Create a Workbook instance to create a new Excel workbook and clear the default worksheets using Workbook.Worksheets.Clear() method.
- Connect to the database and get all the table names.
- Create a worksheet for each table with the table names as sheet names using Workbook.Worksheets.Add() method.
- Get the column names in the tables and write them to the first row of the worksheet through Worksheet.Range[].Value property.
- Get the data in the table and write it to the worksheet sequentially through Worksheet.Range[].Value property.
- Format the worksheet through CellRange.Style property if needed.
- Close the database connection and save the workbook using Workbook.SaveToFile() method.
- C#
using System.Data;
using System.Data.SQLite;
using Spire.Xls;
namespace SQLiteToExcel
{
class Program
{
static void Main(string[] args)
{
// SQLite database path
string sqliteFilePath = "Sample.db";
// Excel file path
string excelFilePath = "output/DatabaseToExcel.xlsx";
// Create a new Workbook instance
Workbook workbook = new Workbook();
// Clear the default worksheet
workbook.Worksheets.Clear();
// Create SQLite connection
using (SQLiteConnection connection = new SQLiteConnection($"Data Source={sqliteFilePath};Version=3;"))
{
connection.Open();
// Get all table names
DataTable tables = connection.GetSchema("Tables");
// Iterate through each table
foreach (DataRow tableRow in tables.Rows)
{
string tableName = tableRow["TABLE_NAME"].ToString();
// Create a new worksheet
Worksheet sheet = workbook.Worksheets.Add(tableName);
// Get table data
string selectQuery = $"SELECT * FROM [{tableName}]";
using (SQLiteCommand command = new SQLiteCommand(selectQuery, connection))
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
// Get column names and write them in the first row
for (int col = 0; col < reader.FieldCount; col++)
{
sheet.Range[1, col + 1].Value = reader.GetName(col);
}
// Set the font style for the header
sheet.Rows[0].Style.Font.IsBold = true;
sheet.Rows[0].Style.Font.Size = 12;
// Write data rows
int rowIndex = 2;
while (reader.Read())
{
for (int col = 0; col < reader.FieldCount; col++)
{
sheet.Range[rowIndex, col + 1].Value = reader.GetValue(col).ToString();
// Auto-fit column width
sheet.AutoFitColumn(col + 1);
}
// Set the font style for data rows
sheet.Rows[rowIndex - 1].Style.Font.Size = 11;
rowIndex++;
}
}
}
}
connection.Close();
}
// Save the Excel file
workbook.SaveToFile(excelFilePath);
workbook.Dispose();
Console.WriteLine("Data has been successfully exported to the Excel file!");
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
