本文实例讲述了c#实现excel导入sqlite的方法，是非常实用的技巧。分享给大家供大家参考。具体方法如下：
首先需要引用system.date.sqlite
具体实现代码如下：
我要评论
system.date.sqlite
system.date.sqlite.linq
//导入--excel导入sqlite
private void button2_click(object sender, eventargs e)
{
dal.sqlite da = new dal.sqlite("databyexcel.db");
if (chk_sfzj.checked==false)
{
//删除全部数据
if (da.sqlexsqlitecommand("delete from sqllitebyexcel"))
{
}
else
{
messagebox.show("删除原失败，请联系管理员！");
}
}
openfiledialog ofg = new openfiledialog();
ofg.filter = "*.xls|*.xls";
if (ofg.showdialog() == system.windows.forms.dialogresult.ok)
{
string sname = ofg.filename;
if (new bll.excelcs().outexcel(sname, da))
{
messagebox.show("导入成功");
//bddata("");
}
else
{
messagebox.show("导入失败");
}
}
}
/// <summary>
/// 初始化数据库
/// </summary>
/// <param name="strsqlitepath">数据库文件路径</param>
sqliteconnection sqlcon;
public sqlite(string dataname)
{
sqlcon = new sqliteconnection(string.format("data source={0}{1}", system.appdomain.currentdomain.basedirectory, dataname));
}
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="strsql">sql语句</param>
/// <returns>是否执行成功</returns>
public bool sqlexsqlitecommand(string strsql)
{
sqlopen();
sqlitecommand cmd = new sqlitecommand();
cmd.connection = sqlcon;
cmd.commandtext = strsql;
try
{
int i = cmd.executenonquery();
return true;
}
catch (exception ex)
{
return false;
}
}
/// <summary>
/// 导入数据到数据库
/// </summary>
/// <param name="outfile">文件</param>
/// <param name="sql">数据库操作对象</param>
/// <returns></returns>
public bool outexcel(string outfile,dal.sqlite sql)
{
datatable dt = dal.excel.transferdata(outfile, "sheet1").tables[0];
try
{
foreach (datarow item in dt.rows)
{
string strsql = @"insert into sqllitebyexcel
(no,business_no,business_type_name,vessel_name_c,voyage,bill_no,ctnw1,ctnw2,
ctnw3,txdd,xxdd,ctn_no,ctn_type,name1,name2,name3,in_date,jfjssj,jfsc,dypcd,txpcsj,
txpcsc,jcsj,txsc,h986jjycsj,yfyxsj,lxsj,lxsc,ccjfsj,txjcsj,txccsj,dctxsc,timenow,ddtxsc)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',
'{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',
'{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}')";
string strend = string.format(strsql, item[0], item[1], item[2], item[3], item[4], item[5],
item[6], item[7], item[8], item[9], item[10], item[11], item[12],
item[13], item[14], item[15], item[16].todate(), item[17].todate(), item[18], item[19].todate(),
item[20].todate(), item[21], item[22].todate(), item[23], item[24].todate(), item[25].todate(), item[26].todate(),
item[27], item[28].todate(), item[29].todate(), item[30].todate(), item[31], datetime.now.todate(), "");
sql.sqlexsqlitecommand(strend);
}
return true;
}
catch (exception ex)
{
// messbox.show("");
string aa = ex.message;
return false;
}
}
public static string todate(this object obj)
{
// if (obj == null || string.isnullorempty(obj.tostring()))
if(string.isnullorempty(obj.tostring().trim()))
{
return "null";
}
return ((datetime)obj).tostring("yyyy-mm-dd hh:mm:ss");
}
/// <summary>
/// 获取excel表数据
/// </summary>
/// <param name="excelfile">excel文件路径</param>
/// <param name="sheetname">excel工作表名</param>
/// <returns></returns>
public static dataset transferdata(string excelfile, string sheetname)
{
dataset ds = new dataset();
//获取全部数据
string strconn = "provider=microsoft.jet.oledb.4.0;" + "data source=" + excelfile + ";" + "extended properties=excel 8.0;";
oledbconnection conn = new oledbconnection(strconn);
try
{
conn.open();
string strexcel = "";
oledbdataadapter mycommand = null;
strexcel = string.format("select * from [{0}$]", sheetname);
mycommand = new oledbdataadapter(strexcel, strconn);
mycommand.fill(ds);
}
catch (exception ex)
{
throw new exception(ex.message);
}
finally
{
conn.close();
}
return ds;
}
相信本文所述对大家的c#程序设计有一定的借鉴价值。
如对本文有疑问， 点击进行留言回复！！
使用Visual Studio2019创建C#项目(窗体应用程序、控制台应用程序、Web应用程序)
C#实现获取本地内网(局域网)和外网(公网)IP地址的方法分析
网友评论