datagridview导入excel到数据库

可以按照以下步骤将DataGridView中的数据导入到数据库中：

1. 将DataGridView中的数据保存为Excel文件

private void SaveToExcel(DataGridView dgv)
{
    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    excel.Application.Workbooks.Add(true);

    int columnIndex = 0;
    foreach (DataGridViewColumn col in dgv.Columns)
    {
        columnIndex++;
        excel.Cells[1, columnIndex] = col.HeaderText;
    }

    int rowIndex = 0;
    foreach (DataGridViewRow row in dgv.Rows)
    {
        rowIndex++;
        columnIndex = 0;
        foreach (DataGridViewColumn col in dgv.Columns)
        {
            columnIndex++;
            excel.Cells[rowIndex + 1, columnIndex] = row.Cells[col.Name].Value;
        }
    }

    excel.Visible = true;
}

2. 使用OLEDB连接器读取Excel文件并将数据加载到DataTable中

private DataTable LoadFromExcel(string filePath)
{
    using (OleDbConnection conn = new OleDbConnection())
    {
        string query = "SELECT * FROM [Sheet1$]";
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
        {
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            return dataTable;
        }
    }
}

3. 将DataTable中的数据插入到数据库中

private void InsertToDatabase(DataTable dataTable)
{
    using (SqlConnection conn = new SqlConnection("connection string"))
    {
        conn.Open();
        foreach (DataRow row in dataTable.Rows)
        {
            SqlCommand cmd = new SqlCommand("INSERT INTO [Table] ([Column1], [Column2], [Column3]) VALUES (@Column1, @Column2, @Column3)", conn);
            cmd.Parameters.AddWithValue("@Column1", row["Column1"]);
            cmd.Parameters.AddWithValue("@Column2", row["Column2"]);
            cmd.Parameters.AddWithValue("@Column3", row["Column3"]);
            cmd.ExecuteNonQuery();
        }
    }
}

完整代码如下：

private void btnImport_Click(object sender, EventArgs e)
{
    OpenFileDialog openFileDialog1 = new OpenFileDialog();
    openFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
    openFileDialog1.Title = "Select an Excel File";
    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
        string filePath = openFileDialog1.FileName;

        // Load data from Excel to DataTable
        DataTable dataTable = LoadFromExcel(filePath);

        // Insert data from DataTable to database
        InsertToDatabase(dataTable);
    }
}

private void SaveToExcel(DataGridView dgv)
{
    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    excel.Application.Workbooks.Add(true);

    int columnIndex = 0;
    foreach (DataGridViewColumn col in dgv.Columns)
    {
        columnIndex++;
        excel.Cells[1, columnIndex] = col.HeaderText;
    }

    int rowIndex = 0;
    foreach (DataGridViewRow row in dgv.Rows)
    {
        rowIndex++;
        columnIndex = 0;
        foreach (DataGridViewColumn col in dgv.Columns)
        {
            columnIndex++;
            excel.Cells[rowIndex + 1, columnIndex] = row.Cells[col.Name].Value;
        }
    }

    excel.Visible = true;
}

private DataTable LoadFromExcel(string filePath)
{
    using (OleDbConnection conn = new OleDbConnection())
    {
        string query = "SELECT * FROM [Sheet1$]";
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
        {
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            return dataTable;
        }
    }
}

private void InsertToDatabase(DataTable dataTable)
{
    using (SqlConnection conn = new SqlConnection("connection string"))
    {
        conn.Open();
        foreach (DataRow row in dataTable.Rows)
        {
            SqlCommand cmd = new SqlCommand("INSERT INTO [Table] ([Column1], [Column2], [Column3]) VALUES (@Column1, @Column2, @Column3)", conn);
            cmd.Parameters.AddWithValue("@Column1", row["Column1"]);
            cmd.Parameters.AddWithValue("@Column2", row["Column2"]);
            cmd.Parameters.AddWithValue("@Column3", row["Column3"]);
            cmd.ExecuteNonQuery();
        }
    }
}