编程资料excel导入sql代码Word格式文档下载.docx
《编程资料excel导入sql代码Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《编程资料excel导入sql代码Word格式文档下载.docx(12页珍藏版)》请在冰豆网上搜索。
usingSystem.IO;
usingModel;
usingDAL;
namespaceExamSystwmServer
{
publicclassCommonExcel
{
#region获取Excel数据表列表
///<
summary>
///获取Excel数据表列表
/summary>
paramname="
FilePath"
>
<
/param>
returns>
/returns>
publicstaticArrayListGetExcelTables(stringFilePath)
//将Excel架构存入数据里
DataTabledt=newDataTable();
ArrayListTablesList=newArrayList();
if(File.Exists(FilePath))
using(OleDbConnectionconn=newOleDbConnection("
Provider=Microsoft.Ace."
+
"
OLEDB.12.0;
ExtendedProperties=\"
Excel12.0\"
;
DataSource="
+FilePath))
try
conn.Open();
dt=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,newobject[]{null,null,null,"
TABLE"
});
}
catch(Exceptionex)
MessageBox.Show(ex.Message);
//获取数据表个数
inttablecount=dt.Rows.Count;
for(inti=0;
i<
tablecount;
i=i+2)
stringtablename=dt.Rows[i][2].ToString().Trim().TrimEnd('
$'
);
if(TablesList.IndexOf(tablename)<
0)
TablesList.Add(tablename);
returnTablesList;
#endregion
#region导入Excel数据表至DataTable(第一行作为表头)
///导入Excel数据表至DataTabl
publicstaticDataSetFillDataSet(stringFilePath)
if(!
File.Exists(FilePath))
MessageBox.Show("
Excel文件不存在!
"
"
提示"
MessageBoxButtons.OK,MessageBoxIcon.Information);
returnnull;
ArrayListTableList=newArrayList();
TableList=GetExcelTables(FilePath);
if(TableList.Count<
=0)
DataTabledt;
DataSetds=newDataSet();
OleDbConnectiondbcon=newOleDbConnection(@"
Provider=Microsoft.Ace.OLEDB.12.0;
+FilePath+"
ExtendedProperties=Excel12.0"
if(dbcon.State==ConnectionState.Closed)
dbcon.Open();
TableList.Count;
i++)
stringdtname=TableList[i].ToString();
OleDbCommandcmd=newOleDbCommand("
select*from["
+dtname+"
$]"
dbcon);
OleDbDataAdapteradapter=newOleDbDataAdapter(cmd);
dt=newDataTable(dtname);
adapter.Fill(dt);
ds.Tables.Add(dt);
TurnToID(refds);
finally
if(dbcon.State==ConnectionState.Open)
dbcon.Close();
returnds;
#region把班级名称换为班级ID
///把班级名称换为班级ID
ds"
publicstaticvoidTurnToID(refDataSetds)
UserDALuserDAL=newUserDAL();
userDAL.SelectClassID(refds);
ds.Tables[0].Rows.Count;
for(intj=0;
j<
j++)
if(ds.Tables[0].Rows[i][0].Equals(ds.Tables[1].Rows[j][1]))
ds.Tables[0].Rows[i][0]=ds.Tables[1].Rows[j][0];
ds.Tables[0].Rows[i][0]=Convert.ToInt32(ds.Tables[0].Rows[i][0]);
#regionExcel导入数据库
///Excel导入数据库
publicstaticDataSetImportFromExcel(stringFilePath)
returnFillDataSet(FilePath);
}
4.在btnView的Click事件里面:
(注:
把Excel的路径加载到txtPath文本框里面)
using(OpenFileDialogdialog=newOpenFileDialog())
dialog.Multiselect=true;
if(dialog.ShowDialog()==DialogResult.OK)
txtPath.Text=dialog.FileName;
catch
文件路径错误!
错误信息"
MessageBoxButtons.OK,MessageBoxIcon.Error);
5.在btnSure的Click事件里面:
privatevoidbtnSure_Click(objectsender,EventArgse)
if(this.txtPath.Text.Trim()=="
)
路径不能为空!
提示:
return;
ds=CommonExcel.ImportFromExcel(this.txtPath.Text.Trim());
DataTabledt=ds.Tables[0];
stringstrInsertComm;
dt.Rows.Count;
intid=0;
stringsno="
stringpwd="
stringname="
dt.Columns.Count;
j++)
if(j==1)
sno=dt.Rows[i][j].ToString().Trim();
elseif(j==2)
pwd=dt.Rows[i][j].ToString().Trim();
elseif(j==3)
name=dt.Rows[i][j].ToString().Trim();
else
id=Convert.ToInt32(dt.Rows[i][j].ToString().Trim());
strInsertComm="
InsertintoStudent(CID,SNO,SPWD,SNAME)values("
+id+"
'
+sno+"
'
+pwd+"
+name+"
)"
userDAL.InsertStudent(strInsertComm);
导入成功!
提示信息"
catch(Exce