如何将excel数据导入sqlite数据库 [英] how to import excel data to sqlite database

问题描述

如何将excel数据导入SQLite数据库。

我可以导入数据表但无法为sqlite执行



代码ADDED

how can we import excel data to SQLite database.
I can import to data table but unable to do for sqlite

CODE ADDED

if (browsefile == true)
 {
 txtFilePath.Text = openfile.FileName;

 Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
 Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(txtFilePath.Text.ToString(), 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
 Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.get_Item(1); ;
 Microsoft.Office.Interop.Excel.Range excelRange = excelSheet.UsedRange;

 string strCellData = "";
 double douCellData;
 int rowCnt = 0;
 int colCnt = 0;


 DataTable dt = new DataTable();

 for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
 {
 string strColumn = "";
 strColumn = (string)(excelRange.Cells[1, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
 dt.Columns.Add(strColumn, typeof(string));
 }

 for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)
 {
 string strData = "";
 for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
 {
 try
 {
 strCellData = (string)(excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
 strData += strCellData + "|";
 }
 catch (Exception ex)
 {
 douCellData = (excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
 strData += douCellData.ToString() + "|";
 }
 }
 strData = strData.Remove(strData.Length - 1, 1);
 dt.Rows.Add(strData.Split('|'));
 }
 SQLiteConnection.CreateFile("MyDatabase.sqlite");
 SQLiteConnection m_dbConnection;
 m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
 m_dbConnection.Open();
 string sql = "create table signals(name varchar(20), date datetime)";
 SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
 command.ExecuteNonQuery();
 
//I wast stuck here to insert data pls help me

string sql = "insert into signals (name, date) values

 excelBook.Close(true, null, null);
 excelApp.Quit();
 }

推荐答案

如果您已经可以将Excel文件中的数据导入到程序中的数据表中，那么就在那里。



下一步是你：

- 创建与SQLite的连接

- 创建一个用于插入的命令数据进入数据库

- 创建一个循环，循环遍历数据表中的行

- 为每一行设置insert语句的参数并执行它。



要了解实际编码，请查看使用C＃开始使用SQLite [ ^ ]



添加了伪示例

If you can already import the data from an Excel file to a data table in your program, you're half way there.

The next step is that you:
- create a connection to SQLite
- create a command which will be used for inserting the data into the database
- create a loop which will loop through the rows in your data table
- for each row you set the parameters for the insert statement and execute it.

To get to the actual coding, have a look at Getting started with SQLite in C#[^]

Pseudo example added

string sql = "insert into signals (name, date) values (:name, :date)"
SQLiteCommand insertCommand = new SQLiteCommand(sql, m_dbConnection);
insertCommand.Parameters.Add(new SQLiteParameter("name"));
insertCommand.Parameters.Add(new SQLiteParameter("date"));
foreach(DataRow originalRow in dt.Rows) {
   insertCommand.Parameters["name"].Value = originalRow[0].ToString();
   insertCommand.Parameters["date"].Value = originalRow[1].ToString();
   try {
      insertCommand.ExecuteNonQuery();
   } catch {
      ...
   }
}

这篇关于如何将excel数据导入sqlite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！