C # Implementing methods for Excel to import SQLite

This article describes the C # implementation of Excel Import SQLite method, is a very practical technique. Share to everyone for your reference. Here's how:

First you need to refer to System.date.sqlite

The specific implementation code is as follows:

system.date.sqlite
system.date.sqlite.linq
// import--Excel imports 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 ("Failed to delete the original, please contact the 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 successfully");
   // bdData ("");
 }
 else
 {
   MessageBox.Show ("Import failed");
 }
  }
}
/// <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>
/// execute sql statement
/// </ summary>
/// <param name = "strSql"> sql statement </ param>
/// <returns> whether execution was 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 operation 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>
/// Get the excel table data
/// </ summary>
/// <param name = "excelFile"> excel file path </ param>
/// <param name = "sheetName"> excel sheet name </ param>
/// <returns> </ returns>
public static DataSet TransferData (string excelFile, string sheetName)
{
  DataSet ds = new DataSet ();
  // Get all 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;
} 

It is believed that this article has certain reference value for the C # program design of everybody.

In addition to the Declaration, Running GuestArticles are original, reproduced please link to the form of the address of this article
C # Implementing methods for Excel to import SQLite

This address: http://www.paobuke.com/develop/c-develop/pbk23485.html

Related Content C # a Web page fetching all the information in a Web page based on a regular expression implementation the suspend and resume of process in C # how to read C # datetime date format C # Reading and writing config profile
C # Double-buffering implementation method (prevents splash screen) C # generate Code39 barcodes instead of barcode fonts C # Generate Word Document code example C # implement custom FTP actions encapsulate class instances

C # Implementing methods for Excel to import SQLite