Import and Export Data From Excel to Database

private void btnSaveFile_Click(object sender, EventArgs e)    
{    
    string fileName;    
    Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();    
    Spire.DataExport.XLS.WorkSheet worksheet1 = new Spire.DataExport.XLS.WorkSheet();    
    worksheet1.DataSource = Spire.DataExport.Common.ExportSource.DataTable;    
    worksheet1.DataTable = this.dataGridView1.DataSource asDataTable;    
    worksheet1.StartDataCol = ((System.Byte)(0));    
    cellExport.Sheets.Add(worksheet1);    
    cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;    
    fileName = txtFileName.Text.ToString() + ".xls";    
    cellExport.SaveToFile(fileName);    
}

Using above code we read the data from Datagridview and save into excel sheet. We can see in below image that complete data is inserted into excel sheet.

Step 4. Import Data From Excel to Database:

Now I will explain how to import data from excel sheet to database. I will use below “Employee.xls” file and import the data of this file into data table.

We have already a table in database in database that is empty in current.

Import Data From Excel To DataGridview

private void btnImport_Click(object sender, EventArgs e)    
{    
    string fileName;    
    fileName = txtExcelToDatabase.Text.ToString();    
    Workbook workbook = newWorkbook();    
    workbook.LoadFromFile(fileName);    
    Worksheet sheet = workbook.Worksheets[0];    
    this.dataGridView1.DataSource = sheet.ExportDataTable();    
}

Using above code we retrieved the data from excel sheet and display into DataGridView.

Save Data Into Database

private void btnsaveTodatabase_Click(object sender, EventArgs e)    
{    
    SqlConnection Con = newSqlConnection("Data Source=Data-Source;Initial Catalog=Database-Name;Integrated Security=true");    
    SqlCommand com;    
    string str;    
    Con.Open();    
    for (int index = 0; index < dataGridView1.Rows.Count - 1; index++)    
    {    
        str = @ "Insert Into Employee(Emp_Id,Emp_Name,Manager_Id,Project_Id) Values(" + dataGridView1.Rows[index].Cells[0].Value.ToString() + ", '" + dataGridView1.Rows[index].Cells[1].Value.ToString() + "'," + dataGridView1.Rows[index].Cells[2].Value.ToString() + "," + dataGridView1.Rows[index].Cells[3].Value.ToString() + ")";    
        com = newSqlCommand(str, Con);    
        com.ExecuteNonQuery();    
    }    
    Con.Close();    
}

Using above code we retrieved data from Datagridview as row by row and inserted this data into “Employee” table. Now if we examine the “Employee” table , we will find that data from excel has been inserted into table.

Using Spire.Office library we can easily export data from database to excel sheet and also import data from excel sheet to database just using some line of code.