欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C#实现Excel导入sqlite的方法

程序员文章站 2023-12-16 22:58:34
本文实例讲述了c#实现excel导入sqlite的方法,是非常实用的技巧。分享给大家供大家参考。具体方法如下: 首先需要引用system.date.sqlite 具体实...

本文实例讲述了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#程序设计有一定的借鉴价值。

上一篇:

下一篇: