how to import Excel file data into SQLite database in c# windows form?

user_14454875 0
Viewed 1498

I am trying to import excel file data into SQLite database. I used 2 buttons, import and save button.

Here is the code of import button, in which i tried to get file from given path and loading file data into datagridview.

    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();
        }

And here i am importing data into SQLite database table.

      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();
        }

PROBLEM:

Its working properly, but i want to read data from excel and directly import it in database table, instead of using datagridview. If anyone know how to do it, kindly let me know, Thank you

excel sqlite c#
2 Answers

You could open a direct connection to the Excel file using OleDbConnection, open an OleDbDataReader on that connection, and then read row by row into the SQLite connection.

user_1117940

Not tested, since you didnt tell us, what kind of Excel intergration you are using(interop, ...). Basically you store the result in a variable or property, here I used "data", and access it directly, later.

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();
}
user_10378410
Related