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

Vithal Wadje
12y
196.5k
0
8

Article

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

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:

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

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

<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">

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

</Columns>

</asp:GridView>

</div>

</form>

</body>

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

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:

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

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

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

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

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.

Up Next

Ebook Download

View all

Getting Started with ASP.NET 4.5 Web Forms and Visual Studio 2013

Read by 7.6k people

Download Now!

Learn

View all