Load GridView from Excel

Introduction 

Hi everyone, here I am going to explain how to load data to a gridview or to a database from an Excel sheet. We need to load the data to the web application from external resources. 

• Download Excel_Import.zip

Background 

I had seen many articles and searched google a lot of times. And i got the solution at last. What i am going to do is load the data to the gridview from Excel file. In many cases we need to load the data from external resources, so lets see that. 

My Excel file contains data like below. 

ID    FirstName    LastName
1      AAA          AAA
2      BBB          BBB
3      CCC          CCC  

Using the code 

First design the Excel sheet. For example i laod the excel sheet with columns ID, FirstName, LastName.  

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" 
            Height="21px" Text="Upload" 
            Width="92px" onclick="btnUpload_Click"/>
</div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>  

The excel file can be uploaded using file upload control. Here I bind the Excel sheet data to a gridview. The code for the upload button will be, 

protected void btnUpload_Click(object sender, EventArgs e)
{
    String strConnection = "ConnectionString";
    string connectionString ="";
    if (FileUpload1.HasFile)
    {
        string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
        string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        string fileLocation = Server.MapPath("~/App_Data/" + fileName);
        FileUpload1.SaveAs(fileLocation); 
        if (fileExtension == ".xls")
        {
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
              fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; 
        }
        else if (fileExtension == ".xlsx")
        {
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
              fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        } 
        OleDbConnection con = new OleDbConnection(connectionString);
        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Connection = con;
        OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
        DataTable dtExcelRecords = new DataTable();
        con.Open();
        DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
        cmd.CommandText = "SELECT * FROM [" + getExcelSheetName +"]";
        dAdapter.SelectCommand = cmd;
        dAdapter.Fill(dtExcelRecords); 
        GridView1.DataSource = dtExcelRecords;
        GridView1.DataBind(); 
    }

Points of Interest

If you want to store the data to the database. then use this code to copy the excel data to the database.  

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strConnection))
{
  bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
  bulkCopy.ColumnMappings.Add("LastName", "LastName"); 
  bulkCopy.DestinationTableName = "Excel_table";
  bulkCopy.WriteToServer(dtExcelRecords);
}

but make sure that the database contains table name  Excel_table with fields FirstName and LastName  

Conclusion

In many cases we need to load the data from external resources. In such cases we may use this. Hope its useful