Reader Level:
ARTICLE

Uploading and Downloading Excel Files From Database Using ASP.NET C#

Posted by Vithal Wadje Articles | ASP.NET Programming October 23, 2012
In this article i will explain how to upload excel files to database and display in gridview and Download the excel files from database on gridview selected event.
  • 4
  • 0
  • 58860
Download Files:
 

Background

When a beginner joins a company or if there is a final round of interviews, known as a machine round, then most of the time the team leader gives the Candidate the first assignment to create an application which allows the end user to upload only Excel files and display it in a grid view and download it. When I joined a company the same task was given to me by my team leader; instead I was expecting him to give me the task of inserting, updating and deleting in a grid view.

So by considering the above requirement I decided to write this article specially focusing on beginners and those who want to learn how to upload Excel files and display in a grid view and download files in a gridview selected event which is displayed in the grid view.

Now before creating the application, let us create a table named Excelfiledemo in a database to store the downloaded Excel files in a database table having the following fields (shown in the following image):

Excelfiledemotbl.png

In the above table I have created four columns, they are id for the unique identity, Name for the Excel file name, type for file type and data to store the actual content of the files with binary datatype because the content of the files stored in bytes.

I hope you have created the same type of table.

Now  let us start to create an application to upload and download Excel files step-by-step.

Create a web site as:

  1. Start-All Programs-Microsoft Visual Studio 2010
     
  2. File-New Website-C#-Empty website (to avoid adding master page)
  3. Give the web site name as ExcelFileUploadDownload and specify the location
  4. Then right-click on Solution Explorer - Add New Item-Default.aspx page
  5. Open source view and simply drag one File upload control, two Buttons, one label and a grid view
     
  6. The source code <body> tag should be as follows:

<body bgcolor="Silver">

    <form id="form1" runat="server">

    <div>  

   <table>

    <tr>

    <td> 

        Select File

        </td>

        <td>

        <asp:FileUpload ID="FileUpload1" runat="server" ToolTip="Select Only Excel File" />

        </td>

        <td> 

        <asp:Button ID="Button1" runat="server" Text="Upload" onclick="Button1_Click" />

        </td>

        <td>
 

            <asp:Button ID="Button2" runat="server" Text="View Files" 

                onclick="Button2_Click" />

               </td>

        </tr>
 

</table>

<table><tr><td><p><asp:Label ID="Label2" runat="server" Text="label"></asp:Label>  </p></td></tr></table>

 

<asp:GridView ID="GridView1" runat="server" Caption="Excel Files " 

        CaptionAlign="Top" HorizontalAlign="Justify" 

         DataKeyNames="id" onselectedindexchanged="GridView1_SelectedIndexChanged" 

        ToolTip="Excel FIle DownLoad Tool" CellPadding="4" ForeColor="#333333" 

        GridLines="None">

        <RowStyle BackColor="#E3EAEB" />

        <Columns>

            <asp:CommandField ShowSelectButton="True" SelectText="Download" ControlStyle-ForeColor="Blue"/>

        </Columns>

        <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />

        <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />

        <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />

        <HeaderStyle BackColor="Gray" Font-Bold="True" ForeColor="White" />

        <EditRowStyle BackColor="#7C6F57" />

        <AlternatingRowStyle BackColor="White" />

    </asp:GridView> 

    </div>

 </form>

  </body>


Then run the page which will look as in the following:

demoscreen.png

From the above view I am using two buttons to do the upload; one to upload the selected files to the database and view files which shows the files in a grid view which is stored in database table.

Now switch to design mode and double click on upload button and put the following code to validate the Only Excel files to be allowed to upload.

protected void Button1_Click(object sender, EventArgs e)

    {

        Label2.Visible = true;

        string filePath = FileUpload1.PostedFile.FileName; // getting the file path of uploaded file

        string filename1 = Path.GetFileName(filePath);     // getting the file name of uploaded file

        string ext = Path.GetExtension(filename1);          // getting the file extension of uploaded file

        string type = String.Empty;

 

 if (!FileUpload1.HasFile)

        {

            Label2.Text = "Please Select File"//if file uploader has no file selected

        }

        else

        if (FileUpload1.HasFile)

        {

            try

            {

                                                    
                switch (ext)   
// this switch code validate the files which allow to upload only excel file you can change it for any file

                {

                    case ".xls"

                        type = "application/vnd.ms-excel"

                        break;                 


                   case ".xlsx":

                         type = "application/vnd.ms-excel";
 

                        break;

                }
 

                if (type != String.Empty)

                { 

                   connection();

                    Stream fs = FileUpload1.PostedFile.InputStream;

                    BinaryReader br = new BinaryReader(fs);  //reads the   binary files

                    Byte[] bytes = br.ReadBytes((Int32)fs.Length);  //counting the file length into bytes

                    query = "insert into Excelfiledemo(Name,type,data)" + " values (@Name, @type, @Data)"; //insert query

                    com = new SqlCommand(query, con);

                    com.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename1;

                    com.Parameters.Add("@type", SqlDbType.VarChar).Value = type;

                    com.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;

                    com.ExecuteNonQuery();

                    Label2.ForeColor = System.Drawing.Color.Green;

                    Label2.Text = "File Uploaded Successfully"

                }

                else

                {

                    Label2.ForeColor = System.Drawing.Color.Red; 

                    Label2.Text = "Select Only Excel File having extension .xlsx or .xls "; // if file is other than speified extension 

                }

            }

            catch (Exception ex)

            {

                Label2.Text = "Error: " + ex.Message.ToString(); 

            } 

        }

    }


Add the following code in the view file button click:
 

protected void Button2_Click(object sender, EventArgs e)
    {

 
        GridView1.Visible =true;
        connection();
        query = "Select *from Excelfiledemo";
        SqlDataAdapter da = new SqlDataAdapter(query, con);
        DataSet ds = new DataSet();
        da.Fill(ds, "Excelfiledemo");
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
        con.Close();

    }


Add the following code to the Gridview selected index changed event to download the files:
 

protectedvoid GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {
   using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["excelconn"].ToString()))
        {
            con.Open();
            SqlCommand cmd =new SqlCommand("select Name,type,data from Excelfiledemo where id=@id", con);
            cmd.Parameters.AddWithValue("id", GridView1.SelectedRow.Cells[1].Text);
            SqlDataReader dr = cmd.ExecuteReader();

 
            if (dr.Read())
            {
                Response.Clear();
                Response.Buffer =true;
                Response.ContentType = dr["type"].ToString();
 // to open file prompt Box open or Save file
                Response.AddHeader("content-disposition", "attachment;filename=" + dr["Name"].ToString()); 
                   
                Response.Charset = "";
                Response.Cache.SetCacheability(HttpCacheability.NoCache);
                Response.BinaryWrite((byte[])dr["data"]);
                Response.End();
            }


For more code please download the zip file attachment of this article.


After downloading the zip file, extract the files and open it into the Visual Studio and make whatever changes in the connection string to your web.config file as per your server location.

Now run the application and select the file other than Excel which shows the following error as shown in the following:

otherfileerror.png

Now select the Excel file, which shows the following message after Suceessfully Uploaded:

fileuploaded.png

Now click on view files details. The gridview is shows uploaded files with details as shown below. 

viewfilesingrid.png

then Click on the download button of gridview, the following prompt message is displayed as shown in below image 


openfile.png



Then choose browse with Excel and click on the ok button. The file will be opened in Excel as follows: 


openexcel.png


Summary


I hope this article is useful for all readers, if you have any suggestion then please contact me including beginners also.

Note

Download the zip file from the attachment for the full source code of an application.
 

COMMENT USING

Trending up