Import and Export Data From Excel to Database

Import and Export of data from excel to database is always a hot issues for developers. There are many libraries using that we can implement these task. Today I will explain about “Spire.xls” libraries, using this we can easily import data from excel to database and export data from database to excel. Now I will explain using some simple steps.

Step 1: Go to File option and select a project. Here I selected a “Window Form Application”. Now right click on project and select the “Manage NuGet Packages” option. Now a window will popup, write “spire” in search box and select “Spire.Office. for .NET” package and install this package.



Now go to Reference Folder and you will find following “dll” file has been added.



Step 2: We create a window form as below. In this form we take a Datagridview in which we will show the data. We divided this form mainly in two part. In first part we will export the data from database to excel and using second section we will import the data from excel to database.



Step 3: Export Data from Database to Excel.

We will export the data from below table to excel sheet using the C# code.



Export Data From Database

privatevoid btnExportData_Click(object sender, EventArgs e)
{
    SqlConnection Con = newSqlConnection("Data Source=DataSource Name;Initial Catalog=Dataabse-Name;Integrated Security=true");
    Con.Open();
    SqlDataAdapter Da = newSqlDataAdapter(txtQuery.Text, Con);
    DataTable Tab = newDataTable();
    Da.Fill(Tab);
    dataGridView1.DataSource = Tab;
    Con.Close();
}

 

Using above code we only retrieved the data from data table and show this data into datagridview.

Save Data Into Excel:

privatevoid 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

privatevoid 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

privatevoid 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.