浮生记

//从excel导入到数据库。

oledbconnection conexcel = new oledbconnection();
try
{
openfiledialog openfile = new openfiledialog();//打开文件对话框。
openfile.filter = ("excel 文件(*.xls)|*.xls");//后缀名。
if (openfile.showdialog() == dialogresult.ok)
{
string filename = openfile.filename;
int index = filename.lastindexof("\");//截取文件的名字
filename = filename.substring(index + 1);
conexcel.connectionstring = "provider=microsoft.jet.oledb.4.0;data source=" + application.startuppath + "\appdata.mdb";
//将excel导入access
//distinct :删除excel重复的行.
//[excel名].[sheet名] 已有的excel的表要加$
//where not in : 插入不重复的记录。
string sql = "insert into 用户表 select distinct * from [excel 8.0;database=" + filename + "].[用户表$] where 记录编号 not in (select 记录编号 from 用户表)";
oledbcommand com = new oledbcommand(sql, conexcel);
conexcel.open();
com.executenonquery();
messagebox.show("导入数据成功","导入数据", messageboxbuttons.ok, messageboxicon.information );
}
}
catch (exception ex)
{
messagebox.show(ex.tostring());
}
finally
{
conexcel.close();
}

//导出到excel操作。
oledbconnection conexcel = new oledbconnection();
try
{
savefiledialog savefile = new savefiledialog();
savefile.filter = ("excel 文件(*.xls)|*.xls");//指定文件后缀名为excel 文件。
if (savefile.showdialog() == dialogresult.ok)
{
string filename = savefile.filename;
if (system.io.file.exists(filename))
{
system.io.file.delete(filename);//如果文件存在删除文件。
}
int index = filename.lastindexof("\");//获取最后一个的索引
filename = filename.substring(index + 1);//获取excel名称(新建表的路径相对于savefiledialog的路径)
//select * into 建立 新的表。
//[[excel 8.0;database= excel名].[sheet名] 如果是新建sheet表不能加$,如果向sheet里插入数据要加$.
//sheet最多存储65535条数据。
string sql = "select top 65535 * into [excel 8.0;database=" + filename + "].[用户表] from 用户表";
conexcel.connectionstring = "provider=microsoft.jet.oledb.4.0;data source=" + application.startuppath + "\appdata.mdb";//将数据库放到debug目录下。
oledbcommand com = new oledbcommand(sql, conexcel);
conexcel.open();
com.executenonquery();
messagebox.show("导出数据成功","导出数据", messageboxbuttons.ok, messageboxicon.information );
}
}
catch (exception ex)
{
messagebox.show(ex.tostring());
}
finally
{
conexcel.close();
}

//读取excel

//excel 的连接串
string sconnectionstring = "provider=microsoft.jet.oledb.4.0;data source=" + server.mappath("mgadin/excelroom/2007-8-10.xls") + "; extended properties=excel 8.0;";

//建立excel的连接
oledbconnection objconn = new oledbconnection(sconnectionstring);

objconn.open();

oledbcommand objcmdselect = new oledbcommand("select * from [sheet1$]", objconn);
oledbdataadapter objadapter1 = new oledbdataadapter();
objadapter1.selectcommand = objcmdselect;

dataset objdataset1 = new dataset();

objadapter1.fill(objdataset1, "xldata");

gridview1.datasource = objadapter1;
gridview1.databind();

相关日志

• 遭遇“AV终结者”
• 计算机等级考试一级考试大纲（07级参考）
• 计算机应用基础2习题1
• 网管员必知：常用电脑密码破解
• 纯灌水，婚礼可否商业开发?