数据库应用与开发实验报告

四、实验过程、步骤及内容

在之前连接好学生数据库的窗体中加入“Excel导入”按钮

写入代码实现Excel导入数据库

usingSystem;

usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;

usingSystem.Drawing;usingSystem.Linq;usingSystem.Text;

usingSystem.Windows.Forms;usingSystem.Data.OleDb;

namespaceMDI窗体

{

publicpartialclassForm4:Form

{

publicForm4()

{

InitializeComponent();

}

OleDbConnectionmycnn=newOleDbConnection();OleDbCommandCMD=newOleDbCommand();OleDbDataReaderReader;

DataSetdt=newDataSet();

OleDbDataAdapteradapter=newOleDbDataAdapter();

privatevoidbutton5_Click(objectsender,EventArgse)

{

OpenFileDialogopenfileDialog=newOpenFileDialog();openfileDialog.DefaultExt="xlsx";

openfileDialog.Filter="xlsx文件(*.xlsx)|*.xlsx|xls文件(*.xls)|*.xls|所有文件(*.*)|*.*";

if(openfileDialog.ShowDialog()!=DialogResult.OK)

{

return;

}

try

{

stringFilePath="",File_Name="";

intpos=openfileDialog.FileName.LastIndexOf("\\");FilePath=openfileDialog.FileName.Substring(0,pos);File_Name=openfileDialog.FileName.Substring(pos+1);

OleDbConnectioncon=newOleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;DataSource="+openfileDialog.FileName+@";ExtendedProperties='Excel12.0;HDR=YES'");

con.Open();

DataTabledtSheetName=con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,newobject[]{null,null,null,"Table"});

stringstrTableNames=dtSheetName.Rows[0]["TABLE_NAME"].ToString();stringdatainput="select*from["+strTableNames+"]";OleDbDataAdapteradapter=newOleDbDataAdapter(datainput,con);DataTabledt=newDataTable();

dt.Clear();adapter.Fill(dt);con.Close();con.Dispose();

dataGridView1.DataSource=dt;dataGridView1.DataSource=dt;

}

catch

{

stringsuffix="";

intpos=openfileDialog.FileName.LastIndexOf(".");suffix=openfileDialog.FileName.Substring(pos+1);suffix=suffix.ToUpper();

if(openfileDialog.FileName.IndexOf("")>=0)MessageBox.Show("文件名中不能出现空格，请修改");

elseif(suffix!="xlsx"&&suffix!="xls")MessageBox.Show("请确定是xls或xlsx文件，请重试");

else

MessageBox.Show("请关闭当前文件："+openfileDialog.FileName+"，

再试");

}

}

}

}

（2）运行结果

可以导入Excel文件，运行成功

注：报告内页的项目或内容设置，根据实际情况可加以调整和补充，写不下可附一页。

五、实验结论：

六、指导教师批阅意见：

成绩评定：

指导教师签