如何以C#WINDOWS的形式将Excel文件数据导入SQLite数据库?

ca1c2owp  于 2022-11-15  发布在  SQLite
关注(0)|答案(2)|浏览(327)

我正在尝试将EXCEL文件数据导入到SQLite数据库。我用了两个按钮,导入和保存按钮。
下面是IMPORT按钮的代码,其中我试图从给定的路径获取文件并将文件数据加载到datagridview中。

using Microsoft.Office.Interop.Excel;
...

private void import_btn_Click(object sender, EventArgs e)
{
    string fileName= @"C:\Users\***\Documents\Customers.xlsx";
    Workbook workbook = new Workbook();
    workbook.LoadFromFile(fileName);
    Worksheet sheet = workbook.Worksheets[0];
    this.dataGridView1.DataSource = sheet.ExportDataTable();
}

在这里,我将数据导入到SQLite数据库表中。

using System.Data.SQLite;
...

private void save_btn_Click(object sender, EventArgs e)
{
    string code = "C-0002";
    SQLiteConnection Con = new SQLiteConnection("Data Source=|DataDirectory|JDS_DB.sqlite");
    SQLiteCommand com;
    string str;
    Con.Open();
    for (int index = 0; index < dataGridView1.Rows.Count - 1; index++)
    {
        str = "INSERT INTO Customers(Name,Code,Group_,Address,Phone,Cell,Email_Address,Date,Opening_balance) VALUES(@Name,@Code,@Group_,@Address,@Phone,@Cell,@Email_Address,@Date,@Opening_balance)";
        com = new SQLiteCommand(str, Con);
        com.Parameters.AddWithValue("@Name", dataGridView1.Rows[index].Cells[0].Value.ToString());
        com.Parameters.AddWithValue("@Code",code);
        com.Parameters.AddWithValue("@Group_", dataGridView1.Rows[index].Cells[1].Value.ToString());
        com.Parameters.AddWithValue("@Address", dataGridView1.Rows[index].Cells[2].Value.ToString());
        com.Parameters.AddWithValue("@Phone", dataGridView1.Rows[index].Cells[3].Value.ToString());
        com.Parameters.AddWithValue("@Cell", dataGridView1.Rows[index].Cells[4].Value.ToString());
        com.Parameters.AddWithValue("@Email_Address", dataGridView1.Rows[index].Cells[5].Value.ToString());
        com.Parameters.AddWithValue("@Date", dataGridView1.Rows[index].Cells[6].Value.ToString());
        com.Parameters.AddWithValue("@Opening_balance", dataGridView1.Rows[index].Cells[7].Value.ToString());
        com.ExecuteNonQuery();
        MessageBox.Show("added");
    }
    Con.Close();
}

问题:

它工作正常,但我想从EXCEL中读取数据,并直接将其导入数据库表中,而不是使用datagridview。如果有人知道怎么做,请让我知道,谢谢

fgw7neuy

fgw7neuy1#

您可以使用OleDbConnection打开到Excel文件的直接连接,在该连接上打开OleDbDataReader,然后逐行读取SQLite连接。

7cwmlq89

7cwmlq892#

没有经过测试,因为您没有告诉我们,您使用的是哪种Excel集成(互操作,...)。基本上,您将结果存储在变量或属性中,这里我使用了“data”,并在以后直接访问它。

private void save_btn_Click(object sender, EventArgs e)
{
    // Load the data
    string fileName = @"C:\Users\***\Documents\Customers.xlsx";
    Workbook workbook = new Workbook();
    workbook.LoadFromFile(fileName);
    Worksheet sheet = workbook.Worksheets[0];
    DataTable data = sheet.ExportDataTable();

    // Write the data
    string code = "C-0002";
    SQLiteConnection Con = new SQLiteConnection("Data Source=|DataDirectory|JDS_DB.sqlite");
    SQLiteCommand com;
    string str;
    Con.Open();
    foreach (DataRow row in data.Rows) //iterate over all rows
    {
        str = "insert into Customers(Name,Code,Group_,Address,Phone,Cell,Email_Address,Date,Opening_balance)values(@Name,@Code,@Group_,@Address,@Phone,@Cell,@Email_Address,@Date,@Opening_balance)";
        com = new SQLiteCommand(str, Con);
        com.Parameters.AddWithValue("@Name", row.Field<string>(0));
        com.Parameters.AddWithValue("@Code", row.Field<string>(1));
        //... you get the point :)
        com.ExecuteNonQuery();
        MessageBox.Show("added");
    }
    Con.Close();
}

相关问题