将Excel文件数据库导入SQL Server

将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());
}
}

if(sqlConnection1!=null)
{
sqlConnection1.Close();
sqlConnection1=null;
}

//方案三：通过到入Excel的VBAdll，通过VBA接口获取Excel数据到DataSet

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

ExcelIOexcelio=newExcelIO();

if(openFile.ShowDialog()==DialogResult.OK)
{
if(excelio!=null)
excelio.Close();

excelio=newExcelIO(openFile.FileName);
object[,]range=excelio.GetRange();
excelio.Close();

DataSetds=newDataSet("xlsRange");

intx=range.GetLength(0);
inty=range.GetLength(1);

DataTabledt=newDataTable("xlsTable");
DataRowdr;
DataColumndc;

ds.Tables.Add(dt);

for(intc=1;c<=y;c++)
{
dc=newDataColumn();
dt.Columns.Add(dc);
}

object[]temp=newobject[y];

for(inti=1;i<=x;i++)
{
dr=dt.NewRow();

for(intj=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();
}