C#通过ACE OLEDB驱动程序访问 Access和 Excel

ACE 代表 Access Connectivity Engine。它是 Microsoft 提供的一组组件，用于访问和操作 Microsoft Access 数据库以及其他类似的文件格式，如 Excel 工作簿。ACE 主要包括以下几部分：

ACE OLEDB 驱动程序：用于通过 OLE DB 提供程序访问 Access 数据库和 Excel 文件。例如，Microsoft.ACE.OLEDB.12.0 是一个常见的提供程序版本，用于访问 Access 2007 及以上版本的数据库以及 Excel 2007 及以上版本的工作簿。
Access Database Engine：这是一个包含 ACE OLEDB 驱动程序的组件。它允许应用程序读取和写入 Access 数据库文件（.accdb 或 .mdb 文件）以及 Excel 文件（.xls 和 .xlsx 文件）。
数据源提供程序：ACE 提供了支持 Access 数据库和 Excel 文件的 OLE DB 和 ODBC 数据源提供程序，允许使用标准的 SQL 查询来操作这些文件中的数据。

主要用途

数据库连接：ACE 允许应用程序连接到 Access 数据库文件（.accdb 或 .mdb）并执行 SQL 查询。
Excel 数据访问：ACE 允许应用程序通过 OLE DB 或 ODBC 读取和写入 Excel 工作簿中的数据。

安装和版本

ACE 数据库引擎的版本与 Microsoft Access 的版本相关。较新的版本（如 Microsoft.ACE.OLEDB.12.0）支持较新的 Access 数据库格式和 Excel 文件格式。你可以从 Microsoft 的官方下载页面下载和安装适用于你系统的 Access Database Engine。

示例连接字符串

Access 数据库：
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\your\database.accdb;";
Excel 文件：
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\your\file.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;'";

解释连接字符串参数
Provider=Microsoft.ACE.OLEDB.12.0：指定使用 ACE 12.0 驱动程序。
Data Source={filePath}：指定 Excel 文件的路径。
Extended Properties='Excel 12.0 Xml;HDR=YES;'：
- Excel 12.0 Xml：指定 Excel 文件的格式是 2007 或更高版本。
- HDR=YES：指定第一行包含列头。

ACE 驱动程序是处理 Microsoft Access 和 Excel 文件数据的强大工具，广泛用于各种应用程序中以实现数据管理和分析功能。

确保表格名称和列名正确

检查 Excel 工作表名称：工作表的名称在 SQL 查询中要加上 $ 符号，比如 Sheet1$。
检查列名：列名在 SQL 查询中必须与工作表中的列标题完全匹配，包括大小写和任何可能的额外空格。

using System;
using System.Data;
using System.Data.OleDb;


class xlsxOLEDBOleDbDataAdapter
{
    static void Main()
    {
    // Excel 文件路径
    string filePath = @"C:\Users\Administrator\Documents\Employees.xlsx";
    string connectionString = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0 Xml;HDR=YES;'";

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        connection.Open();
        string query = "SELECT * FROM [Sheet1$]"; // 修改为实际的工作表名称
        OleDbCommand command = new OleDbCommand(query, connection);
        OleDbDataAdapter adapter = new OleDbDataAdapter(command);
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        foreach (DataRow row in dataTable.Rows)
        {
            foreach (var cell in row.ItemArray)
            {
                Console.Write(cell.ToString() + "\t");
            }
            Console.WriteLine();
        }
    }
    Console.ReadKey();
    }
}

using System;
using System.Data;
using System.Data.OleDb;

class xlsxOLEDBOleDbDataAdapter
{
    static void Main()
    {
        // Excel 文件路径
        string filePath = @"C:\Users\Administrator\Documents\Employees.xlsx";
        string connectionString = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0 Xml;HDR=YES;'";

        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            connection.Open();

            // 先检索表格结构，确保列名正确
            string schemaQuery = "SELECT * FROM [Sheet1$] WHERE 1 = 0"; // 仅获取列名
            OleDbCommand schemaCommand = new OleDbCommand(schemaQuery, connection);
            OleDbDataAdapter schemaAdapter = new OleDbDataAdapter(schemaCommand);
            DataTable schemaTable = new DataTable();
            schemaAdapter.FillSchema(schemaTable, SchemaType.Source);

            Console.WriteLine("列名列表：");
            foreach (DataColumn column in schemaTable.Columns)
            {
                Console.WriteLine(column.ColumnName);
            }

            // 插入数据的 SQL 语句
            string insertQuery = "INSERT INTO [Sheet1$] ([CustomerId], [CustomerName], [Email]) VALUES (?, ?, ?)";

            // 创建命令对象
            OleDbCommand command = new OleDbCommand(insertQuery, connection);

            // 添加参数
            command.Parameters.AddWithValue("?", "88881"); // CustomerId
            command.Parameters.AddWithValue("?", "John Doe"); // CustomerName
            command.Parameters.AddWithValue("?", "john.doe@example.com"); // Email

            try
            {
                // 执行插入命令
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} rows inserted.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"插入数据时出错: {ex.Message}");
            }

            // 检索并显示数据以验证插入
            string selectQuery = "SELECT * FROM [Sheet1$]";
            OleDbDataAdapter adapter = new OleDbDataAdapter(selectQuery, connection);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);

            foreach (DataRow row in dataTable.Rows)
            {
                foreach (var cell in row.ItemArray)
                {
                    Console.Write(cell.ToString() + "\t");
                }
                Console.WriteLine();
            }
        }
        Console.ReadKey();
    }
}

System.Data.OleDb 命名空间提供了一组类，用于通过 OLE DB 提供程序与数据库进行交互。主要涉及的类和它们的关键方法和属性如下：

1. `OleDbConnection`

作用: 用于与数据源建立连接。
主要属性:
- ConnectionString: 获取或设置连接字符串。
- State: 获取当前连接状态。
主要方法:
- Open(): 打开与数据源的连接。
- Close(): 关闭与数据源的连接。

2. `OleDbCommand`

作用: 表示要发送到数据源的 SQL 语句或存储过程。
主要属性:
- CommandText: 获取或设置要执行的 SQL 语句或存储过程的名称。
- CommandType: 获取或设置命令的类型（如 Text 或 StoredProcedure）。
- Connection: 获取或设置与 OleDbCommand 关联的 OleDbConnection 对象。
主要方法:
- ExecuteReader(): 执行命令并返回 OleDbDataReader。
- ExecuteNonQuery(): 执行命令而不返回结果（如 INSERT、UPDATE、DELETE）。
- ExecuteScalar(): 执行命令并返回单一值（通常是聚合函数结果）。

3. `OleDbDataReader`

作用: 以只读、向前的方式读取数据。
主要属性:
- FieldCount: 获取结果集中列的数量。
- HasRows: 指示是否存在行。
主要方法:
- Read(): 读取下一行数据。
- GetString(int ordinal): 获取指定列的字符串值（可以用 GetInt32、GetDouble 等方法读取其他数据类型）。
- GetValue(int ordinal): 获取指定列的值（返回 object 类型）。

4. `OleDbDataAdapter`

作用: 用于填充 DataSet 并更新数据源。
主要属性:
- SelectCommand: 获取或设置用于填充 DataSet 的 OleDbCommand。
- InsertCommand, UpdateCommand, DeleteCommand: 分别用于插入、更新和删除数据的 OleDbCommand。
主要方法:
- Fill(DataSet dataSet): 填充 DataSet。
- Update(DataSet dataSet): 更新数据源中的数据。

5. `OleDbParameter`

作用: 表示要添加到 OleDbCommand 中的参数。
主要属性:
- ParameterName: 获取或设置参数的名称。
- Value: 获取或设置参数的值。
- DbType: 获取或设置参数的数据类型。
- Size: 获取或设置参数的大小（适用于字符串和二进制数据）。

这些类和方法提供了通过 OLE DB 访问和操作数据的基本功能。

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace ExcelToDataGridView
{
    public partial class Form1 : Form
    {
        private DataGridView dataGridView1;

        public Form1()
        {
            // InitializeComponent();
            InitializeDataGridView();
            LoadExcelData();
        }

        private void InitializeDataGridView()
        {
            dataGridView1 = new DataGridView
            {
                Dock = DockStyle.Fill,
                AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill,
                AllowUserToAddRows = false,
                AllowUserToDeleteRows = false
            };

            this.Controls.Add(dataGridView1);
        }

        private void LoadExcelData()
        {
            string filePath = @"C:\Users\Administrator\Documents\Employees.xlsx";
            string connectionString = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0 Xml;HDR=YES;'";

            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString();

                    string query = $"SELECT * FROM [{sheetName}]";
                    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connection);

                    DataTable dataTable = new DataTable();
                    dataAdapter.Fill(dataTable);

                    dataGridView1.DataSource = dataTable;
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"Error: {ex.Message}");
                }
            }
        }
    }
    static class Program
    {
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}