如何使用 C# 將 Excel 匯入 SQL Server

主要特點

使用 IronXL 從 Excel 讀取數據

使用 IronXL 可以更輕鬆地從 Excel 檔案中讀取資料。 以下範例顯示如何使用 IronXL 從 Excel 檔案中讀取資料。這種方法會將資料讀取並儲存到一個字典清單中，每個字典對應於 Excel 工作表中的一行。

using IronXL;
using System;
using System.Collections.Generic;

public class ExcelReader
{
    public static List<Dictionary<string, object>> ReadExcelFile(string filePath)
    {
        // Initialize a list to store data from Excel
        var data = new List<Dictionary<string, object>>();

        // Load the workbook from the file path provided
        WorkBook workbook = WorkBook.Load(filePath);

        // Access the first worksheet in the workbook
        WorkSheet sheet = workbook.WorkSheets[0];

        // Retrieve column headers from the first row
        var headers = new List<string>();
        foreach (var header in sheet.Rows[0].Columns)
        {
            headers.Add(header.ToString());
        }

        // Loop through each row starting from the second row
        for (int i = 1; i < sheet.Rows.Count; i++)
        {
            // Create a dictionary to store the row data associated with column headers
            var rowData = new Dictionary<string, object>();
            for (int j = 0; j < headers.Count; j++)
            {
                rowData[headers[j]] = sheet.Rows[i][j].Value;
            }
            data.Add(rowData);
        }

        return data;
    }
}

using IronXL;
using System;
using System.Collections.Generic;

public class ExcelReader
{
    public static List<Dictionary<string, object>> ReadExcelFile(string filePath)
    {
        // Initialize a list to store data from Excel
        var data = new List<Dictionary<string, object>>();

        // Load the workbook from the file path provided
        WorkBook workbook = WorkBook.Load(filePath);

        // Access the first worksheet in the workbook
        WorkSheet sheet = workbook.WorkSheets[0];

        // Retrieve column headers from the first row
        var headers = new List<string>();
        foreach (var header in sheet.Rows[0].Columns)
        {
            headers.Add(header.ToString());
        }

        // Loop through each row starting from the second row
        for (int i = 1; i < sheet.Rows.Count; i++)
        {
            // Create a dictionary to store the row data associated with column headers
            var rowData = new Dictionary<string, object>();
            for (int j = 0; j < headers.Count; j++)
            {
                rowData[headers[j]] = sheet.Rows[i][j].Value;
            }
            data.Add(rowData);
        }

        return data;
    }
}

正在連線到 SQL Server

使用System.Data.SqlClient命名空間中的SqlConnection類別建立與 SQL Server 的連線。 請確保您擁有正確的連接字串，該字串通常由資料庫名稱、伺服器名稱和身份驗證資訊組成。 以下範例介紹如何連線到 SQL Server 資料庫並新增資料。

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

public class SqlServerConnector
{
    private string connectionString;

    // Constructor accepts a connection string
    public SqlServerConnector(string connectionString)
    {
        this.connectionString = connectionString;
    }

    // Inserts data into the specified table
    public void InsertData(Dictionary<string, object> data, string tableName)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Construct an SQL INSERT command with parameterized values to prevent SQL injection
            var columns = string.Join(",", data.Keys);
            var parameters = string.Join(",", data.Keys.Select(key => "@" + key));
            string query = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                // Add parameters to the command
                foreach (var kvp in data)
                {
                    command.Parameters.AddWithValue("@" + kvp.Key, kvp.Value ?? DBNull.Value);
                }

                // Execute the command
                command.ExecuteNonQuery();
            }
        }
    }
}

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

public class SqlServerConnector
{
    private string connectionString;

    // Constructor accepts a connection string
    public SqlServerConnector(string connectionString)
    {
        this.connectionString = connectionString;
    }

    // Inserts data into the specified table
    public void InsertData(Dictionary<string, object> data, string tableName)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Construct an SQL INSERT command with parameterized values to prevent SQL injection
            var columns = string.Join(",", data.Keys);
            var parameters = string.Join(",", data.Keys.Select(key => "@" + key));
            string query = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                // Add parameters to the command
                foreach (var kvp in data)
                {
                    command.Parameters.AddWithValue("@" + kvp.Key, kvp.Value ?? DBNull.Value);
                }

                // Execute the command
                command.ExecuteNonQuery();
            }
        }
    }
}

將 IronXL 與 SQL Server 結合使用

一旦建立了讀取 Excel 檔案並將資料插入 SQL 資料庫的邏輯，就將這些功能整合起來以完成匯入過程。 接下來的應用程式會從 Excel 檔案接收訊息，並將其新增至 Microsoft SQL Server 資料庫。

using System;
using System.Collections.Generic;

class Program
{
    static void Main(string[] args)
    {
        // Define the path to the Excel file, SQL connection string, and target table name
        string excelFilePath = "path_to_your_excel_file.xlsx";
        string connectionString = "your_sql_server_connection_string";
        string tableName = "your_table_name";

        // Read data from Excel
        List<Dictionary<string, object>> excelData = ExcelReader.ReadExcelFile(excelFilePath);

        // Create an instance of the SQL connector and insert data
        SqlServerConnector sqlConnector = new SqlServerConnector(connectionString);
        foreach (var row in excelData)
        {
            sqlConnector.InsertData(row, tableName);
        }

        Console.WriteLine("Data import completed successfully.");
    }
}

using System;
using System.Collections.Generic;

class Program
{
    static void Main(string[] args)
    {
        // Define the path to the Excel file, SQL connection string, and target table name
        string excelFilePath = "path_to_your_excel_file.xlsx";
        string connectionString = "your_sql_server_connection_string";
        string tableName = "your_table_name";

        // Read data from Excel
        List<Dictionary<string, object>> excelData = ExcelReader.ReadExcelFile(excelFilePath);

        // Create an instance of the SQL connector and insert data
        SqlServerConnector sqlConnector = new SqlServerConnector(connectionString);
        foreach (var row in excelData)
        {
            sqlConnector.InsertData(row, tableName);
        }

        Console.WriteLine("Data import completed successfully.");
    }
}

該類別負責使用 IronXL 從指定的 Excel 檔案中讀取資料。 ReadExcelFile ReadExcelFile會載入 Excel 工作簿，開啟第一個工作表，並透過遍歷資料工作表的每一行來收集資料。 為了方便處理表格，資訊保存在字典清單中。

如何在 C# 中將 Excel 檔案匯入 SQL Server：圖 7 - 範例輸入 Excel 文件

此類會將資料插入指定的資料庫表中，同時也會管理與 SQL Server 資料庫的連線。 InsertData方法採用參數化查詢來防止 SQL 注入，並根據字典的鍵（代表列名）動態建立 SQL INSERT 查詢。

Main函數使用ExcelReader類別從 Excel 檔案讀取資料到 SQL 表中，並使用SqlServerConnector類別將每一行插入到 SQL Server 表中，從而管理整個過程。

如何在 C# 中將 Excel 匯入 SQL Server：圖 8 - 輸出結果展示了 SQL Server 上的成功查詢

錯誤處理和最佳化對於確保穩健且高效的導入過程至關重要。 實作健全的錯誤處理機制可以管理諸如檔案遺失、資料格式無效和 SQL 異常等潛在問題。 以下是加入錯誤處理的範例。

try
{
    // Insert the importing logic here
}
catch (Exception ex)
{
    Console.WriteLine("An error occurred: " + ex.Message);
}

try
{
    // Insert the importing logic here
}
catch (Exception ex)
{
    Console.WriteLine("An error occurred: " + ex.Message);
}