将Excel文件数据库导入SQL Server

最新推荐文章于 2025-07-23 14:14:36 发布

最新推荐文章于 2025-07-23 14:14:36 发布 · 118 阅读

文章标签：

#SQL Server #Excel #SQL #VBA #XP

本文介绍了三种将Excel数据导入SQLServer的方法：一是使用OleDB连接并转换数据；二是直接利用SQLServer的开放数据源功能；三是通过VBA接口读取Excel数据。

将Excel文件数据库导入SQLServer的三种方案//方案一：通过OleDB方式获取Excel文件的数据，然后通过DataSet中转到SQLServer

openFileDialog=newOpenFileDialog();
openFileDialog.Filter="Excelfiles(*.xls)|*.xls";

if(openFileDialog.ShowDialog()==DialogResult.OK)
{
FileInfofileInfo=newFileInfo(openFileDialog.FileName);
stringfilePath=fileInfo.FullName;
stringconnExcel="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+filePath+";ExtendedProperties=Excel8.0";

try
{
OleDbConnectionoleDbConnection=newOleDbConnection(connExcel);
oleDbConnection.Open();

//获取excel表
DataTabledataTable=oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);

//获取sheet名，其中[0][1]...[N]:按名称排列的表单元素
stringtableName=dataTable.Rows[0][2].ToString().Trim();
tableName="["+tableName.Replace("'","")+"]";

//利用SQL语句从Excel文件里获取数据
//stringquery="SELECTclassDate,classPlace,classTeacher,classTitle,classIDFROM"+tableName;
stringquery="SELECT日期,开课城市,讲师,课程名称,持续时间FROM"+tableName;
dataSet=newDataSet();

//OleDbCommandoleCommand=newOleDbCommand(query,oleDbConnection);
//OleDbDataAdapteroleAdapter=newOleDbDataAdapter(oleCommand);
OleDbDataAdapteroleAdapter=newOleDbDataAdapter(query,connExcel);

oleAdapter.Fill(dataSet,"gch_Class_Info");

//dataGrid1.DataSource=dataSet;
//dataGrid1.DataMember=tableName;
dataGrid1.SetDataBinding(dataSet,"gch_Class_Info");

//从excel文件获得数据后，插入记录到SQLServer的数据表
DataTabledataTable1=newDataTable();

SqlDataAdaptersqlDA1=newSqlDataAdapter(@"SELECTclassID,classDate,
classPlace,classTeacher,classTitle,durativeDateFROMgch_Class_Info",sqlConnection1);

SqlCommandBuildersqlCB1=newSqlCommandBuilder(sqlDA1);

sqlDA1.Fill(dataTable1);

foreach(DataRowdataRowindataSet.Tables["gch_Class_Info"].Rows)
{
DataRowdataRow1=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(Exceptionex)
{
Console.WriteLine(ex.ToString());
}
}

//方案二：直接通过SQL语句执行SQLServer的功能函数将Excel文件转换到SQLServer数据库

OpenFileDialogopenFileDialog=newOpenFileDialog();
openFileDialog.Filter="Excelfiles(*.xls)|*.xls";

SqlConnectionsqlConnection1=null;

if(openFileDialog.ShowDialog()==DialogResult.OK)
{
stringfilePath=openFileDialog.FileName;

sqlConnection1=newSqlConnection();
sqlConnection1.ConnectionString="server=(local);integratedsecurity=SSPI;initialcatalog=Library";

//importexcelintoSQLServer2000
/*stringimportSQL="SELECT*intolive41FROMOpenDataSource"+
"('Microsoft.Jet.OLEDB.4.0','DataSource="+"\""+"E:\\022n.xls"+"\""+
";UserID=;Password=;Extendedproperties=Excel5.0')...[Sheet1$]";*/

//exportSQLServer2000intoexcel
stringexportSQL=@"EXECmaster..xp_cmdshell
'bcpLibrary.dbo.live41out"+filePath+"-c-q-S"+"\""+"\""+
"-U"+"\""+"\""+"-P"+"\""+"\""+"\'";

try
{
sqlConnection1.Open();

//SqlCommandsqlCommand1=newSqlCommand();
//sqlCommand1.Connection=sqlConnection1;
//sqlCommand1.CommandText=importSQL;
//sqlCommand1.ExecuteNonQuery();
//MessageBox.Show("importfinish!");

SqlCommandsqlCommand2=newSqlCommand();
sqlCommand2.Connection=sqlConnection1;
sqlCommand2.CommandText=exportSQL;
sqlCommand2.ExecuteNonQuery();
MessageBox.Show("exportfinish!");
}
catch(Exceptionex)
{
MessageBox.Show(ex.ToString());
}
}