C#实现Excel导入sqlite的方法
本文实例讲述了C#实现Excel导入sqlite的方法,是非常实用的技巧。分享给大家供大家参考。具体方法如下:
首先需要引用system.date.sqlite
具体实现代码如下:
system.date.sqlite system.date.sqlite.linq //导入--Excel导入sqlite privatevoidbutton2_Click(objectsender,EventArgse) { DAL.Sqliteda=newDAL.Sqlite("DataByExcel.db"); if(chk_sfzj.Checked==false) { //删除全部数据 if(da.SqlExSQLiteCommand("deletefromsqllitebyexcel")) { } else { MessageBox.Show("删除原失败,请联系管理员!"); } } OpenFileDialogofg=newOpenFileDialog(); ofg.Filter="*.xls|*.xls"; if(ofg.ShowDialog()==System.Windows.Forms.DialogResult.OK) { stringsName=ofg.FileName; if(newBLL.Excelcs().OutExcel(sName,da)) { MessageBox.Show("导入成功"); //bdData(""); } else { MessageBox.Show("导入失败"); } } } ///<summary> ///初始化数据库 ///</summary> ///<paramname="strSqlitePath">数据库文件路径</param> SQLiteConnectionSQLCon; publicSqlite(stringdataName) { SQLCon=newSQLiteConnection(string.Format("DataSource={0}{1}",System.AppDomain.CurrentDomain.BaseDirectory,dataName)); } ///<summary> ///执行sql语句 ///</summary> ///<paramname="strSql">sql语句</param> ///<returns>是否执行成功</returns> publicboolSqlExSQLiteCommand(stringstrSql) { SqlOpen(); SQLiteCommandcmd=newSQLiteCommand(); cmd.Connection=SQLCon; cmd.CommandText=strSql; try { inti=cmd.ExecuteNonQuery(); returntrue; } catch(Exceptionex) { returnfalse; } } ///<summary> ///导入数据到数据库 ///</summary> ///<paramname="outFile">文件</param> ///<paramname="sql">数据库操作对象</param> ///<returns></returns> publicboolOutExcel(stringoutFile,DAL.Sqlitesql) { DataTabledt=DAL.Excel.TransferData(outFile,"Sheet1").Tables[0]; try { foreach(DataRowitemindt.Rows) { stringstrSql=@"insertintosqllitebyexcel (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}')"; stringstrEnd=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); } returntrue; } catch(Exceptionex) { //MessBox.Show(""); stringaa=ex.Message; returnfalse; } } publicstaticstringToDate(thisobjectobj) { //if(obj==null||string.IsNullOrEmpty(obj.ToString())) if(string.IsNullOrEmpty(obj.ToString().Trim())) { return"null"; } return((DateTime)obj).ToString("yyyy-MM-ddHH:mm:ss"); } ///<summary> ///获取excel表数据 ///</summary> ///<paramname="excelFile">excel文件路径</param> ///<paramname="sheetName">excel工作表名</param> ///<returns></returns> publicstaticDataSetTransferData(stringexcelFile,stringsheetName) { DataSetds=newDataSet(); //获取全部数据 stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;"+"DataSource="+excelFile+";"+"ExtendedProperties=Excel8.0;"; OleDbConnectionconn=newOleDbConnection(strConn); try { conn.Open(); stringstrExcel=""; OleDbDataAdaptermyCommand=null; strExcel=string.Format("select*from[{0}$]",sheetName); myCommand=newOleDbDataAdapter(strExcel,strConn); myCommand.Fill(ds); } catch(Exceptionex) { thrownewException(ex.Message); } finally { conn.Close(); } returnds; }
相信本文所述对大家的C#程序设计有一定的借鉴价值。