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; } |