本文共 4508 字,大约阅读时间需要 15 分钟。
将Excel文件数据库导入SQL Server的三种方案//方案一: 通过OleDB方式获取Excel文件的数据,然后通过DataSet中转到SQL Server
openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel files(*.xls)|*.xls";if(openFileDialog.ShowDialog()==DialogResult.OK)
{ FileInfo fileInfo = new FileInfo(openFileDialog.FileName); string filePath = fileInfo.FullName; string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0"; try { OleDbConnection oleDbConnection = new OleDbConnection(connExcel); oleDbConnection.Open(); //获取excel表 DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//获取sheet名,其中[0][1]...[N]: 按名称排列的表单元素
string tableName = dataTable.Rows[0][2].ToString().Trim(); tableName = "[" + tableName.Replace("'","") + "]";//利用SQL语句从Excel文件里获取数据
//string query = "SELECT classDate,classPlace,classTeacher,classTitle,classID FROM " + tableName; string query = "SELECT 日期,开课城市,讲师,课程名称,持续时间 FROM " + tableName; dataSet = new DataSet();//OleDbCommand oleCommand = new OleDbCommand(query, oleDbConnection);
//OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand); OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query,connExcel); oleAdapter.Fill(dataSet,"gch_Class_Info");//dataGrid1.DataSource = dataSet;
//dataGrid1.DataMember = tableName; dataGrid1.SetDataBinding(dataSet,"gch_Class_Info");//从excel文件获得数据后,插入记录到SQL Server的数据表
DataTable dataTable1 = new DataTable(); SqlDataAdapter sqlDA1 = new SqlDataAdapter(@"SELECT classID, classDate,classPlace, classTeacher, classTitle, durativeDate FROM gch_Class_Info",sqlConnection1); SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1); sqlDA1.Fill(dataTable1);foreach(DataRow dataRow in dataSet.Tables["gch_Class_Info"].Rows)
{ DataRow dataRow1 = dataTable1.NewRow(); dataRow1["classDate"] = dataRow["日期"]; dataRow1["classPlace"] = dataRow["开课城市"]; dataRow1["classTeacher"] = dataRow["讲师"]; dataRow1["classTitle"] = dataRow["课程名称"]; dataRow1["durativeDate"] = dataRow["持续时间"];dataTable1.Rows.Add(dataRow1);
}Console.WriteLine("新插入 " + dataTable1.Rows.Count.ToString() + " 条记录");
sqlDA1.Update(dataTable1); oleDbConnection.Close();}
catch(Exception ex) { Console.WriteLine(ex.ToString()); }}
//方案二: 直接通过SQL语句执行SQL Server的功能函数将Excel文件转换到SQL Server数据库
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel files(*.xls)|*.xls";SqlConnection sqlConnection1 = null;
if(openFileDialog.ShowDialog()==DialogResult.OK)
{ string filePath = openFileDialog.FileName;sqlConnection1 = new SqlConnection();
sqlConnection1.ConnectionString = "server=(local);integrated security=SSPI;initial catalog=Library";//import excel into SQL Server 2000
/*string importSQL = "SELECT * into live41 FROM OpenDataSource" + "('Microsoft.Jet.OLEDB.4.0','Data Source=" + "/"" + "E://022n.xls" + "/"" + "; User ID=;Password=; Extended properties=Excel 5.0')...[Sheet1$]";*///export SQL Server 2000 into excel
string exportSQL = @"EXEC master..xp_cmdshell'bcp Library.dbo.live41 out " + filePath + "-c -q -S" + "/"" + "/"" + " -U" + "/"" + "/"" + " -P" + "/"" + "/"" + "/'"; try { sqlConnection1.Open(); //SqlCommand sqlCommand1 = new SqlCommand(); //sqlCommand1.Connection = sqlConnection1; //sqlCommand1.CommandText = importSQL; //sqlCommand1.ExecuteNonQuery(); //MessageBox.Show("import finish!"); SqlCommand sqlCommand2 = new SqlCommand(); sqlCommand2.Connection = sqlConnection1; sqlCommand2.CommandText = exportSQL; sqlCommand2.ExecuteNonQuery(); MessageBox.Show("export finish!"); } catch(Exception ex) { MessageBox.Show(ex.ToString()); }}if(sqlConnection1!=null)
{ sqlConnection1.Close(); sqlConnection1 = null;}
//方案三: 通过到入Excel的VBA dll,通过VBA接口获取Excel数据到DataSet
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "Excel files(*.xls)|*.xls";ExcelIO excelio = new ExcelIO();
if(openFile.ShowDialog()==DialogResult.OK)
{ if(excelio!=null) excelio.Close();excelio = new ExcelIO(openFile.FileName);
object[,] range = excelio.GetRange(); excelio.Close();DataSet ds = new DataSet("xlsRange");
int x = range.GetLength(0);
int y = range.GetLength(1);DataTable dt = new DataTable("xlsTable");
DataRow dr; DataColumn dc; ds.Tables.Add(dt);for(int c=1; c<=y; c++)
{ dc = new DataColumn(); dt.Columns.Add(dc); } object[] temp = new object[y]; for(int i=1; i<=x; i++) { dr = dt.NewRow();for(int j=1; j<=y; j++)
{ temp[j-1] = range[i,j]; } dr.ItemArray = temp; ds.Tables[0].Rows.Add(dr); }dataGrid1.SetDataBinding(ds,"xlsTable");
if(excelio!=null) excelio.Close();}
转载地址:https://linuxstyle.blog.csdn.net/article/details/1536901 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!