Wednesday, February 13, 2013

Upload and Read the Excel file using C#

This example will show you how to Upload the excel file and then read the excel file data using C# and display it on Gridview.

Drag and drop the FileUpload control and Button control from toolbox. Also drag and drop the Gridview  control for display the excel file data. ASPX page will look as following.


<div>
 <asp:FileUpload ID="fileupload" runat="server" />
  <asp:Button ID="btnUpload" runat="server" onclick="btnUpload_Click"
    Text="Upload" />
 <asp:GridView ID="grdExcelData" runat="server">
 </asp:GridView>
</div>

Now write the following code on Click event of Button control.

protected void btnUpload_Click(object sender, EventArgs e)
{
  try
  {
     string connectionString = "";
     if (fileupload.HasFile)
     {
        string fileName = Path.GetFileName(fileupload.PostedFile.FileName);
        string fileExtension = Path.GetExtension(fileupload.PostedFile.FileName);
        string fileLocation = Server.MapPath("~/App_Data/" + fileName);
        fileupload.SaveAs(fileLocation);
        //Check whether file extension is xls or xslx
        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\"";
        }

        //Create OleDB Connection and OleDb Command
        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);
        con.Close();
        grdExcelData.DataSource = dtExcelRecords;
        grdExcelData.DataBind();
   }
 }
 catch (Exception ex)
 {
 }
}


Build and run the application.
Output:-
upload file
uploaded data
Happy coding!! :)
kick it on DotNetKicks.com

No comments:

Post a Comment

^ Scroll to Top