我在将选定的Excel表上传到MySQL数据库时遇到问题。我试图找到解决方案，在尝试时，我会得到此异常错误"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Sheet1$]' at line 1'"

我使用OpenFileDialog将Excel文件导入到DataGridView中，现在我想将该表上传到MySQL数据库中。

private void SelectExcel_Click(object sender, EventArgs e)
{

    using (OpenFileDialog openFileDialog1 = new OpenFileDialog())
    { 
    openFileDialog1.InitialDirectory = "C:\\";
    openFileDialog1.RestoreDirectory = true;
    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
            FileStream fileStream = File.Open(openFileDialog1.FileName, FileMode.Open, FileAccess.Read);
            IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
            DataSet ds = reader.AsDataSet();
            dataGridView1.DataSource = ds.Tables[0];
    }
    }
}



private void Uploadbutton_Click(object sender, EventArgs e)
{
    

    string connectionString = "server=localhost; database = Excel; uid=root; pwd=ednadarling@5";
    MySqlConnection conn = new MySqlConnection(connectionString);
    

    string export = "select * from [Sheet1$]";
    MySqlDataAdapter adapter = new MySqlDataAdapter(export, conn);
        DataSet ds = new DataSet();
    DataTable table = new DataTable();
        adapter.Fill(table);
        this.dataGridView1.DataSource = table.DefaultView;

    conn.Close();
}
分析解答

您不需要SQL查询到select data from the Excel file。相反，您已经将数据从Excel文件加载到dataGridView1中，并且应该将其直接插入MySQL数据库中。

using MySql.Data.MySqlClient;

// ...

private void Uploadbutton_Click(object sender, EventArgs e)
{
    try
    {
        string connectionString = "server=localhost; database=YourDatabaseName; uid=root; pwd=YourPassword";
        MySqlConnection conn = new MySqlConnection(connectionString);

        conn.Open();

        // Assuming you have a DataTable with data from Excel (you can reuse the one you created earlier)
        DataTable excelData = (DataTable)dataGridView1.DataSource;

        foreach (DataRow row in excelData.Rows)
        {
            // Assuming your MySQL table has columns named Col1, Col2, Col3, etc.
            string insertQuery = "INSERT INTO YourTableName (Col1, Col2, Col3) VALUES (@Col1, @Col2, @Col3)";
            MySqlCommand cmd = new MySqlCommand(insertQuery, conn);

            // Map the DataTable columns to MySQL parameters
            cmd.Parameters.AddWithValue("@Col1", row["ExcelColumn1"]);
            cmd.Parameters.AddWithValue("@Col2", row["ExcelColumn2"]);
            cmd.Parameters.AddWithValue("@Col3", row["ExcelColumn3"]);

            cmd.ExecuteNonQuery();
        }

        conn.Close();
        MessageBox.Show("Data imported successfully.");
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error: " + ex.Message);
    }
}