重生陆如萍,in189,学习联盟
最近在做这个如何把excel导入到数据库中,经过多方查找,终于找到一个适合的,并且经过自己的完善可以正常使用(忘记原作者博客的链接地址了,敬请见谅)
代码如下:可根据自己的需求进行修改,我是要导入之后就对我的另一窗体进行刷新,定义了委托,你们可以忽略。
//定义委托
public delegate void refresh();
//定义事件
public event refresh myrefresh;
public excelroprtform()
{
initializecomponent();
}
private void button1_click(object sender, eventargs e)
{
//选中导入的文件
try
{
//openfiledialog1.filter = "excel 文件|*.xls";//指定存放文件格式类型
openfiledialog fd = new openfiledialog();
fd.filter = "excel文件(*.xls,xlsx)|*.xls;*.xlsx";
if (fd.showdialog() == dialogresult.ok)
{
string filename = fd.filename.tostring();
this.textbox1.text = filename;
}
}
catch (exception ee)
{
messagebox.show("打开文件出错!" + ee.message.tostring());
}
}
private dataset xsldata(string filepath)
{
string strcon = "provider=microsoft.jet.oledb.4.0;data source=" + filepath + ";extended properties='excel 8.0;imex=1'";
//string strcon = "provider=microsoft.jet.oledb.4.0;data source= " + filepath + ";extended properties='excel 12.0; hdr=yes; imex=1'";
system.data.oledb.oledbconnection conn = new system.data.oledb.oledbconnection(strcon);
string strcom = "select * from [sheet1$]";
conn.open();
system.data.oledb.oledbdataadapter mycommand = new system.data.oledb.oledbdataadapter(strcom, conn);
dataset ds = new dataset();
mycommand.fill(ds, "[sheet1$]");
datagridview1.datasource = ds.tables[0];
conn.close();
return ds;
}
private void button2_click(object sender, eventargs e)
{
if (textbox1.text == "")
{
messagebox.show("请选择要导入的excel文档!", "系统提示", messageboxbuttons.ok, messageboxicon.information);
return;
}
string filepath = textbox1.text;
string strcon1 = configurationmanager.connectionstrings["connstring"].tostring();
sqlconnection conn = new sqlconnection(strcon1);//链接数据库
conn.open();
try
{
dataset ds = new dataset();
//取得数据集
//调用上面的函数
ds = xsldata(filepath);
int errorcount = 0;//记录错误信息条数
int insertcount = 0;//记录插入成功条数
int updatecount = 0;//记录更新信息条数
for (int i = 0; i < ds.tables[0].rows.count; i++)
{
string carnumber = ds.tables[0].rows[i][0].tostring();
int carstatus = convert.toint32(ds.tables[0].rows[i][1].tostring());
int cartype = convert.toint32(ds.tables[0].rows[i][2].tostring());
string carbrand = ds.tables[0].rows[i][3].tostring();
if (carnumber != "" && carstatus != 0 && cartype != 0)
{
sqlcommand selectcmd = new sqlcommand("select count(*) from carinfo where carnumber='" + carnumber + "'", conn);
int count = convert.toint32(selectcmd.executescalar());
if (count > 0)
{
updatecount++;
}
else
{
sqlcommand insertcmd = new sqlcommand("insert into carinfo(carnumber,carstatusid,cartypeid,carbrand) values(" + "'" + carnumber + "'," + carstatus + "," + cartype + ",'" + carbrand + "'" + ")", conn);
insertcmd.executenonquery();
insertcount++;
}
}
else
{
//messagebox.show("电子表格信息有错!");
errorcount++;
}
}
myrefresh();
messagebox.show(insertcount + "条数据导入成功!" + updatecount + "条数据重复!" + errorcount + "条数据部分信息为空没有导入!");
}
catch (exception ex)
{
messagebox.show(ex.message);
}
finally
{
conn.close();
}
}
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
Blazor server side 自家的一些开源的, 实用型项目的进度之 CEF客户端
.NET IoC模式依赖反转(DIP)、控制反转(Ioc)、依赖注入(DI)
vue+.netcore可支持业务代码扩展的开发框架 VOL.Vue 2.0版本发布
网友评论