C#根据excel文件中的表头创建数据库表

C#根据excel文件中的表头创建数据库表

private void button1_Click(object sender, EventArgs e){string tableName = tableNameTextBox.Text;string connectionString = "";using (OpenFileDialog openFileDialog = new OpenFileDialog()){openFileDialog.Filter = "Excel Files|*.xlsx;*.xls";openFileDialog.Title = "Select an Excel file";if (openFileDialog.ShowDialog() == DialogResult.OK){string filePath = openFileDialog.FileName;tableName = tableNameTextBox.Text;connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath}; Extended Properties='Excel 12.0;HDR=YES;'";}}// SQL Server connection stringstring sqlConnectionString = "server=127.0.0.1;uid=sa;pwd=xyz@1230;database="+ datebasetext.Text;try{using (OleDbConnection excelConnection = new OleDbConnection(connectionString)){excelConnection.Open();DataTable schemaTable = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);if (schemaTable.Rows.Count == 0){MessageBox.Show("Excel文件列名为空！");return;}DataView sortedView = schemaTable.DefaultView;sortedView.Sort = "ORDINAL_POSITION ASC";DataTable sortedSchemaTable = sortedView.ToTable();// Create the SQL CREATE TABLE statementstring createTableQuery = $"CREATE TABLE {tableName} (";List<string> existingColumnNames = new List<string>();foreach (DataRow row in sortedSchemaTable.Rows){string columnName = row["COLUMN_NAME"].ToString();if (!existingColumnNames.Contains(columnName)){createTableQuery += $"[{columnName}] NVARCHAR(200), ";existingColumnNames.Add(columnName);}}createTableQuery = createTableQuery.TrimEnd(',', ' ');createTableQuery += ")";using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionString)){sqlConnection.Open();using (SqlCommand sqlCommand = new SqlCommand(createTableQuery, sqlConnection)){sqlCommand.ExecuteNonQuery();}}MessageBox.Show("创建表成功！");}}catch (Exception ex){MessageBox.Show("报错信息： " + ex.Message);}}