C implements Excel's method of importing sqlite
- 2020-10-31 21:57:31
- OfStack
The example in this article shows how C# implements Excel import sqlite, which is a very practical technique. Share to everybody for everybody reference. The specific methods are as follows:
First you need to refer to system.date.sqlite
The specific implementation code is as follows:
system.date.sqlite
system.date.sqlite.linq
// The import --Excel The import sqlite
private void button2_Click(object sender, EventArgs e)
{
DAL.Sqlite da = new DAL.Sqlite("DataByExcel.db");
if (chk_sfzj.Checked==false)
{
// Delete all data
if (da.SqlExSQLiteCommand("delete from sqllitebyexcel"))
{
}
else
{
MessageBox.Show(" Delete original failure, please contact administrator! ");
}
}
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(" Import success ");
//bdData("");
}
else
{
MessageBox.Show(" Import failure ");
}
}
}
/// <summary>
/// Initialize the database
/// </summary>
/// <param name="strSqlitePath"> Database file path </param>
SQLiteConnection SQLCon;
public Sqlite(string dataName)
{
SQLCon = new SQLiteConnection(string.Format("Data Source={0}{1}", System.AppDomain.CurrentDomain.BaseDirectory, dataName));
}
/// <summary>
/// perform sql statements
/// </summary>
/// <param name="strSql">sql statements </param>
/// <returns> Whether the execution is successful </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>
/// Import data into the database
/// </summary>
/// <param name="outFile"> file </param>
/// <param name="sql"> Database manipulation object </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>
/// To obtain excel Table data
/// </summary>
/// <param name="excelFile">excel The file path </param>
/// <param name="sheetName">excel Work table name </param>
/// <returns></returns>
public static DataSet TransferData(string excelFile, string sheetName)
{
DataSet ds = new DataSet();
// Get all the data
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;
}
I believe that this article has a certain reference value for your C# programming.