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

Posted on by Merry Christina

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),

Uploading And Downloading Excel Files From Database Using ASP.NET C#
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,
    1. <body bgcolor="Silver">
    2.     <form id="form1" runat="server">
    3.         <div>
    4.             <table>
    5.                 <tr>
    6.                     <td>
    8.         Select File
    10.         </td>
    11.                     <td>
    12.                         <asp:FileUpload ID="FileUpload1" runat="server" ToolTip="Select Only Excel File" />
    13.                     </td>
    14.                     <td>
    15.                         <asp:Button ID="Button1" runat="server" Text="Upload" onclick="Button1_Click" />
    16.                     </td>
    17.                     <td>
    18.                         <asp:Button ID="Button2" runat="server" Text="View Files"
    20.                 onclick="Button2_Click" />
    21.                     </td>
    22.                 </tr>
    23.             </table>
    24.             <table>
    25.                 <tr>
    26.                     <td>
    27.                         <p>
    28.                             <asp:Label ID="Label2" runat="server" Text="label"></asp:Label>
    29.                         </p>
    30.                     </td>
    31.                 </tr>
    32.             </table>
    33.             <asp:GridView ID="GridView1" runat="server" Caption="Excel Files "
    35.         CaptionAlign="Top" HorizontalAlign="Justify"
    37.          DataKeyNames="id" onselectedindexchanged="GridView1_SelectedIndexChanged"
    39.         ToolTip="Excel FIle DownLoad Tool" CellPadding="4" ForeColor="#333333"
    41.         GridLines="None">
    42.                 <RowStyle BackColor="#E3EAEB" />
    43.                 <Columns>
    44.                     <asp:CommandField ShowSelectButton="True" SelectText="Download" ControlStyle-ForeColor="Blue"/>
    45.                 </Columns>
    46.                 <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
    47.                 <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
    48.                 <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
    49.                 <HeaderStyle BackColor="Gray" Font-Bold="True" ForeColor="White" />
    50.                 <EditRowStyle BackColor="#7C6F57" />
    51.                 <AlternatingRowStyle BackColor="White" />
    52.             </asp:GridView>
    53.         </div>
    54.     </form>
    55. </body>
Then run the page which will look as in the following,
Uploading And Downloading Excel Files From Database Using ASP.NET C#

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.

  1. protected void Button1_Click(object sender, EventArgs e) {
  2.     Label2.Visible = true;
  3.     string filePath = FileUpload1.PostedFile.FileName; // getting the file path of uploaded file
  4.     string filename1 = Path.GetFileName(filePath); // getting the file name of uploaded file
  5.     string ext = Path.GetExtension(filename1); // getting the file extension of uploaded file
  6.     string type = String.Empty;
  7.     if (!FileUpload1.HasFile) {
  8.         Label2.Text = "Please Select File"; //if file uploader has no file selected
  9.     } else
  10.     if (FileUpload1.HasFile) {
  11.         try {
  12.             switch (ext) // this switch code validate the files which allow to upload only excel file you can change it for any file
  13.             {
  14.                 case ".xls":
  15.                     type = "application/vnd.ms-excel";
  16.                     break;
  17.                 case ".xlsx":
  18.                     type = "application/vnd.ms-excel";
  19.                     break;
  20.             }
  21.             if (type != String.Empty) {
  22.                 connection();
  23.                 Stream fs = FileUpload1.PostedFile.InputStream;
  24.                 BinaryReader br = new BinaryReader(fs); //reads the   binary files
  25.                 Byte[] bytes = br.ReadBytes((Int32) fs.Length); //counting the file length into bytes
  26.                 query = "insert into Excelfiledemo(Name,type,data)" + " values (@Name, @type, @Data)"; //insert query
  27.                 com = new SqlCommand(query, con);
  28.                 com.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename1;
  29.                 com.Parameters.Add("@type", SqlDbType.VarChar).Value = type;
  30.                 com.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
  31.                 com.ExecuteNonQuery();
  32.                 Label2.ForeColor = System.Drawing.Color.Green;
  33.                 Label2.Text = "File Uploaded Successfully";
  34.             } else {
  35.                 Label2.ForeColor = System.Drawing.Color.Red;
  36.                 Label2.Text = "Select Only Excel File having extension .xlsx or .xls "; // if file is other than speified extension 
  37.             }
  38.         } catch (Exception ex) {
  39.             Label2.Text = "Error: " + ex.Message.ToString();
  40.         }
  41.     }
  42. }
Add the following code in the view file button click,
  1. protected void Button2_Click(object sender, EventArgs e)
  2. {
  3.     GridView1.Visible =true;
  4.     connection();
  5.     query = "Select *from Excelfiledemo";
  6.     SqlDataAdapter da = new SqlDataAdapter(query, con);
  7.     DataSet ds = new DataSet();
  8.     da.Fill(ds, "Excelfiledemo");
  9.     GridView1.DataSource = ds.Tables[0];
  10.     GridView1.DataBind();
  11.     con.Close();
  12. }
Add the following code to the Gridview selected index changed event to download the files,
  1. protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
  2. {
  3.     using(SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["excelconn"].ToString()))
  4.     {
  5.         con.Open();
  6.         SqlCommand cmd = new SqlCommand("select Name,type,data from Excelfiledemo where id=@id", con);
  7.         cmd.Parameters.AddWithValue("id", GridView1.SelectedRow.Cells[1].Text);
  8.         SqlDataReader dr = cmd.ExecuteReader();
  9.         if (dr.Read())
  10.         {
  11.             Response.Clear();
  12.             Response.Buffer = true;
  13.             Response.ContentType = dr["type"].ToString();
  14.             // to open file prompt Box open or Save file  
  15.             Response.AddHeader("content-disposition", "attachment;filename=" + dr["Name"].ToString());
  16.             Response.Charset = "";
  17.             Response.Cache.SetCacheability(HttpCacheability.NoCache);
  18.             Response.BinaryWrite((byte[]) dr["data"]);
  19.             Response.End();
  20.         }
  21.     }
  22. }
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,

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

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

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

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

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

then Click on the download button of gridview, the following prompt message is displayed as shown in below image,
Uploading And Downloading Excel Files From Database Using ASP.NET C#
Then choose browse with Excel and click on the ok button. The file will be opened in Excel as follows,

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

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.
Rate this post
error: Content is protected !!