如何在 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 函數載入 Excel 工作簿，開啟第一個工作表，並透過循環遍歷資料工作表的行來收集資料。 為了方便處理表格，資訊保存在字典清單中。

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

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

錯誤處理和最佳化對於確保穩健且高效的導入過程至關重要。 實作健全的錯誤處理機制可以管理諸如檔案遺失、資料格式無效和 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);
}