使用c#读取excel文件中的特定列并添加到查询中

using System;
using System.Data;
using System.Data.SqlClient;
using OfficeOpenXml;

public class ExcelReader
{
    public DataTable ReadExcelData(string filePath, string sheetName, string columnName)
    {
        using (var package = new ExcelPackage(new System.IO.FileInfo(filePath)))
        {
            var worksheet = package.Workbook.Worksheets[sheetName];
            var columnData = new DataTable();
            columnData.Columns.Add(columnName);

            for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
            {
                var cellValue = worksheet.Cells[row, GetColumnIndex(worksheet, columnName)].Value;
                if (cellValue != null)
                {
                    columnData.Rows.Add(cellValue.ToString());
                }
            }

            return columnData;
        }
    }

    private int GetColumnIndex(ExcelWorksheet worksheet, string columnName)
    {
        for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
        {
            var cellValue = worksheet.Cells[1, col].Value;
            if (cellValue != null && cellValue.ToString().Equals(columnName, StringComparison.OrdinalIgnoreCase))
            {
                return col;
            }
        }

        throw new ArgumentException($"Column '{columnName}' not found in the Excel sheet.");
    }
}

public class DatabaseQuery
{
    public void AddDataFromExcelToQuery(DataTable excelData)
    {
        // 假设你已经有一个数据库连接字符串
        string connectionString = "your_connection_string";

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();

            foreach (DataRow row in excelData.Rows)
            {
                string value = row[0].ToString(); // 假设只有一列数据

                // 构建查询语句，将value添加到查询中
                string query = $"INSERT INTO YourTable (ColumnName) VALUES ('{value}')";

                using (var command = new SqlCommand(query, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }
    }
}

public class Program
{
    public static void Main(string[] args)
    {
        string filePath = "your_excel_file_path";
        string sheetName = "your_sheet_name";
        string columnName = "your_column_name";

        var excelReader = new ExcelReader();
        var excelData = excelReader.ReadExcelData(filePath, sheetName, columnName);

        var databaseQuery = new DatabaseQuery();
        databaseQuery.AddDataFromExcelToQuery(excelData);

        Console.WriteLine("Data added to the query successfully.");
    }
}