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#程序设计有一定的借鉴价值。