C#批量导入Excel数据到SQLite数据库的流程步骤实用指南

/// <summary>
/// 批量导入excel
/// </summary>
private void btnImport_Click(object sender, EventArgs e)
{
    using (OpenFileDialog ofd = new OpenFileDialog()) {
        ofd.Title = "选择要导入的Excel文件";
        ofd.Filter = "Excel文件|*.xls;*.xlsx";

        if (ofd.ShowDialog() != DialogResult.OK)
            return;
        string filePath = ofd.FileName;
        try
        {
            // 1.读取Excel
            importStatus.Text = "正在读取Excel...";
            Application.DoEvents();

            DataTable dt = ReadExcel(filePath);

            // 判断读取的excel里是否有数据
            if (dt == null || dt.Rows.Count == 0) {
                importStatus.Text = "Excel里没有数据";
                MessageBox.Show("Excel里没有数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            // 2.批量导入
            importStatus.Text = $"共读取{dt.Rows.Count}行，正在导入...";
            Application.DoEvents();

            int success = ImportToDatabase(dt);

            // 3.显示结果
            importStatus.Text = $"导入完成：成功{success}条，失败{dt.Rows.Count - success}条";
            MessageBox.Show($"导入成功！n成功：{success}条n失败：{dt.Rows.Count - success}条", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            
            // 重新加载数据
            LoadUserData();
        }
        catch (Exception ex)
        {
            importStatus.Text = "导入失败";
            MessageBox.Show($"导入失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
}

/// <summary>
/// 读取 Excel 文件到 DataTable
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private DataTable ReadExcel(string filePath) {

    // .NET Framework 读取 .xls 需要注册编码提供程序
    System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);

    using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
    {
        using (var reader = ExcelReaderFactory.CreateReader(stream)) 
        {
            var result = reader.AsDataSet(new ExcelDataSetConfiguration()
            {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                {
                    UseHeaderRow = true     // 第一行作为标题
                }
            });

            if(result.Tables.Count > 0)
                return result.Tables[0];

            return null;
        }
    }
}

/// <summary>
/// 批量导入数据库（用事务提高性能）
/// </summary>
/// <param name="dt"></param>
private int ImportToDatabase(DataTable dt) {

    int successCount = 0;
    int totalCount = dt.Rows.Count;

    using (var conn = SqliteHelper.GetConnection()) {

        // 打开连接
        conn.Open();

        // 启动事务
        using (var transaction = conn.BeginTransaction())
        {
            using (var cmd = new SQLiteCommand(conn)) { 
                cmd.Transaction = transaction;
                cmd.CommandText = "INSERT INTO user (username, password, status) VALUES (@username, @password, @status)";

                var pUsername = new SQLiteParameter("@username", DbType.String);
                var pPassword = new SQLiteParameter("@password", DbType.String);
                var pStatus = new SQLiteParameter("@status", DbType.Int32);
                cmd.Parameters.AddRange(new[] { pUsername, pPassword, pStatus });

                foreach (DataRow row in dt.Rows)
                {
                    try
                    {
                        string username = row["用户名"]?.ToString().Trim();
                        string password = row["密码"]?.ToString().Trim();
                        string statusStr = row["状态"]?.ToString().Trim();

                        int status = 0;     //未知
                        if (statusStr == "开启") status = 1;
                        else if (statusStr == "关闭") status = 2;
                        else status = 0;

                        if (string.IsNullOrEmpty(username))
                            continue;

                        string passwordHash = BCrypt.Net.BCrypt.HashPassword(password ?? "123456");

                        pUsername.Value = username;
                        pPassword.Value = passwordHash;
                        pStatus.Value = status;

                        cmd.ExecuteNonQuery();
                        successCount++;
                    }
                    catch (Exception ex)
                    {

                        continue;
                    }

                    //每100条更新一次进度
                    if (successCount % 100 == 0)
                    {
                        importStatus.Text = $"正在导入：{successCount}/{totalCount}条...";
                        Application.DoEvents();
                    }
                }
            }
            transaction.Commit();
        }
    }
    return successCount;
}

/// <summary>
/// 加载用户数据到 DataGridView
/// </summary>
private void LoadUserData() {

    try
    {
        string sql = "SELECT id, username, CASE status WHEN 1 THEN '开启'  WHEN 2 THEN '关闭' ELSE '未知' END AS status FROM user ORDER BY ID ASC";
        DataTable dt = SqliteHelper.ExecuteDataTable(sql);
        dgvUsers.DataSource = dt;

        // 设置标题
        if (dgvUsers.Columns.Contains("id")) 
            dgvUsers.Columns["id"].HeaderText = "编号";
        if (dgvUsers.Columns.Contains("username"))
            dgvUsers.Columns["username"].HeaderText = "用户名";
        if (dgvUsers.Columns.Contains("status"))
            dgvUsers.Columns["status"].HeaderText = "状态";

        // 添加修改/删除列
        // 先移除旧的按钮列，避免重复添加
        if (dgvUsers.Columns.Contains("colEdit"))
            dgvUsers.Columns.Remove("colEdit");
        if (dgvUsers.Columns.Contains("colDelete"))
            dgvUsers.Columns.Remove("colDelete");

        // 修改按钮列
        DataGridViewButtonColumn btnEdit = new DataGridViewButtonColumn();
        btnEdit.Name = "colEdit";
        btnEdit.HeaderText = "操作";
        btnEdit.Text = "修改";
        btnEdit.UseColumnTextForButtonValue = true;
        btnEdit.Width = 60;
        dgvUsers.Columns.Add(btnEdit);

        // 删除按钮列
        DataGridViewButtonColumn btnDelete = new DataGridViewButtonColumn();
        btnDelete.Name = "colDelete";
        btnDelete.HeaderText = "操作";
        btnDelete.Text = "删除";
        btnDelete.UseColumnTextForButtonValue = true;
        btnDelete.Width = 60;
        dgvUsers.Columns.Add(btnDelete);

    }
    catch (Exception ex)
    {
        MessageBox.Show("加载数据失败：" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }

}