C#实现Excel导入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#程序设计有一定的借鉴价值。