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;'" ;
}
}
string sqlConnectionString = "server=127.0.0.1;uid=sa;pwd=xyz@1230;database=" + datebasetext. Text;
try
{
using ( OleDbConnection excel Connection = new OleDbConnection ( connectionString) )
{
excel Connection. Open ( ) ;
DataTable schemaTable = excel Connection. 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 ( ) ;
string 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) ;
}
}