2024年3月9日发(作者：)

//方案一： 通过OleDB方式获取Excel文件的数据，然后通过DataSet中转到SQL Server

openFileDialog = new OpenFileDialog();

= "Excel files(*.xls)|*.xls";

if(alog()==)

{

FileInfo fileInfo = new FileInfo(me);

string filePath = me;

string connExcel = "Provider=.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";

try

{

OleDbConnection oleDbConnection = new OleDbConnection(connExcel);

();

//获取excel表

DataTable dataTable = DbSchemaTable(, null);

//获取sheet名，其中[0][1]...[N]: 按名称排列的表单元素

string tableName = [0][2].ToString().Trim();

tableName = "[" + e("'","") + "]";

//利用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);

(dataSet,"gch_Class_Info");

//urce = dataSet;

//mber = tableName;

aBinding(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);

(dataTable1);

foreach(DataRow dataRow in ["gch_Class_Info"].Rows)

{

DataRow dataRow1 = ();

dataRow1["classDate"] = dataRow["日期"];

dataRow1["classPlace"] = dataRow["开课城市"];

dataRow1["classTeacher"] = dataRow["讲师"];

dataRow1["classTitle"] = dataRow["课程名称"];

dataRow1["durativeDate"] = dataRow["持续时间"];

(dataRow1);

}

ine("新插入 " + ng() + " 条记录");

(dataTable1);

();

}

catch(Exception ex)

{

ine(ng());

}

}

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

OpenFileDialog openFileDialog = new OpenFileDialog();

= "Excel files(*.xls)|*.xls";

SqlConnection sqlConnection1 = null;

if(alog()==)

{

string filePath = me;

sqlConnection1 = new SqlConnection();

tionString = "server=(local);integrated security=SSPI;initial catalog=Library";

//import excel into SQL Server 2000

/*string importSQL = "SELECT * into live41 FROM OpenDataSource" +

"('.4.0','Data Source=" + """ + "E:" + """ +

"; User ID=;Password=; Extended properties=Excel 5.0')...[Sheet1$]";*/

//export SQL Server 2000 into excel

string exportSQL = @"p_cmdshell

'bcp 41 out " + filePath + "-c -q -S" + """ + """ +

" -U" + """ + """ + " -P" + """ + """ + "'";

try

{

();

//SqlCommand sqlCommand1 = new SqlCommand();

//tion = sqlConnection1;

//dText = importSQL;

//eNonQuery();

//("import finish!");

SqlCommand sqlCommand2 = new SqlCommand();

tion = sqlConnection1;

dText = exportSQL;

eNonQuery();

("export finish!");

}

catch(Exception ex)

{

(ng());

}

}

if(sqlConnection1!=null)

{

();

sqlConnection1 = null;

}

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

OpenFileDialog openFile = new OpenFileDialog();

= "Excel files(*.xls)|*.xls";

ExcelIO excelio = new ExcelIO();

if(alog()==)

{

if(excelio!=null)

();

excelio = new ExcelIO(me);

object[,] range = ge();

();

DataSet ds = new DataSet("xlsRange");

int x = gth(0);

int y = gth(1);

DataTable dt = new DataTable("xlsTable");

DataRow dr;

DataColumn dc;

(dt);

for(int c=1; c<=y; c++)

{

dc = new DataColumn();

(dc);

}

object[] temp = new object[y];

for(int i=1; i<=x; i++)

{

dr = ();

for(int j=1; j<=y; j++)

{

temp[j-1] = range[i,j];

}

ray = temp;

[0].(dr);

}

aBinding(ds,"xlsTable");

if(excelio!=null)

();

}