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

Vithal Wadje

5y384.9k41100Article

Convert GridView to ExcelDownloading Excel Files in ASP.NETUpload Excel to database in C#

41 Comments

Join the conversation! Your thoughts help the community grow.

sagar shinde3y ago
The path is not of a legal form error showing FileUpload control after button click'
indradeo kumar7y ago
Can you confirm id details which are help to download uploads file
indradeo kumar7y ago
Sir plz rply tomorrow my project submission date.
indradeo kumar7y ago
Error: Cannot insert the value NULL into column 'id', table 'Emp.dbo.Excelfiledemo'; column does not allow nulls. INSERT fails. The statement has been terminated. share exl file details
- Vithal Wadje7y ago
  id is primary key thats why
indradeo kumar7y ago
I have getting mention error.
JATIN NAGPAL7y ago
Sir Please help in providing the concept for Deleting the File from Database
- Vithal Wadje7y ago
  Its similar to deleting any other records from database , you can refer my article which explained about the CRUD operation
Pranil Bhosale8y ago
Hello Vithal sir I am getting {"Object reference not set to an instance of an object."} exception Please help
Vithal Wadje13y ago
no problem Louie Ignacio sir,you are welcome n thanks
Louie Ignacio13y ago
Error: Cannot insert the value NULL into column 'id', table 'NAS.dbo.ExcelFile'; column does not allow nulls. INSERT fails. The statement has been terminated.
- Louie Ignacio13y ago
  Pls disregard my previous post. I just missed out the isIdentity property. Thanks Sir for this tutorial. Big help for newbies like me!.. cheers!
- Vithal Wadje13y ago
  no problem Louie Ignacio sir,you are welcome n thanks
Louie Ignacio13y ago
Hi, im getting this error please help.
Vithal Wadje13y ago
thanks ketan sir
ketan italiya13y ago
sir,thanks it is so useful for me.
VIDYUT SINGHANIA13y ago
Sir, I am extremely thankful to you or making Excel upload and download so easy for noob's like me! :) however, I am facing a small issue - the line : Response.ContentType = dr["type"].ToString(); it gives me an IndexOutOfRangeExpression! Please help me out sir! And thanks once again for all the help you have provided! :)
VIDYUT SINGHANIA13y ago
Sir, I am extremely thankful to you or making Excel upload and download so easy for noob's like me! :) however, I am facing a small issue - the line : Response.ContentType = dr["type"].ToString(); it gives me an IndexOutOfRangeExpression! Please help me out sir! And thanks once again for all the help you have provided! :)
Vithal Wadje13y agoedited
no,you can take connection string name same as it is in article but you need to perform some changes in web.config file connection string that is your database name,your server name,password etc
Spencer Lin13y ago
the files i downloaded = "unknown file type". please help! :)
Spencer Lin13y ago
i see from your codes that you are using "excelconn". So it's just the connection string to connect to the database?
Vithal Wadje13y ago
Spencer Lin sir,your connection with sql serevr in not created thats why you got error, check the connection string in web.config file
Spencer Lin13y ago
I keep getting this error 'Object reference not set to an instance of an object' when I click on view files. Anyone of you all experience this as well?
Vithal Wadje13y ago
anil sir,you can add the extension for this article
Vithal Wadje13y ago
thanks Sivuyile Ntushelo sir
Sivuyile Ntushelo13y ago
Ok nevermind guys, I got the Zip file. its downloaded fine now. Thanks
Sivuyile Ntushelo13y ago
Ok nevermind guys, I got the Zip file. its downloaded fine now. Thanks
Sivuyile Ntushelo13y ago
I cant Download the Zip guys. an erros says its been moved Somewhere. And i need this Code. Can you please advise. Thanks
Sivuyile Ntushelo13y ago
I cant Download the Zip guys. an erros says its been moved Somewhere. And i need this Code. Can you please advise. Thanks
Anil Kumar13y ago
Nothing is wrong in your code. I just added a concern that File upload should be care in terms of content spoof. This is done by altering the extension name of file ex. from filename.html to filename.html.xls. :)
Vithal Wadje13y ago
thanks you anil sir,if anything is wrong i will check it
Anil Kumar13y ago
Welcome Vithal, but Path.GetExtension Method do not care about double extensions like filename.txt.xls Refer MSDN's point- "The extension of path is obtained by searching path for a period (.), starting with the last character in path and continuing toward the start of path. If a period is found before a DirectorySeparatorChar or AltDirectorySeparatorChar character, the returned string contains the period and the characters after it; otherwise, Empty is returned." http://msdn.microsoft.com/en-us/library/system.io.path.getextension.aspx
Vithal Wadje13y ago
thanks anil sir,its works just check it by downloading above zip file
Anil Kumar13y ago
file content spoof validation is the vital part of uploading files, that one is missing. It can't be validated using GetExtension. Also, I am not sure if GetExtension take cares of double extension in file name.
Vithal Wadje14y ago
yes inba i have idea about it but its very broad concept it can not be explained in comment, so refer the link as mahesh sir suggested below
Mahesh Chand14y ago
Yogesh has written many blogs on Arcobject here: http://www.c-sharpcorner.com/1/290/arcobject.aspx
inba Kalathy14y ago
hi vital, u know about an Arcobjets developing with c# or any idea
Vithal Wadje14y ago
thank you Inba,its my pleasure
inba Kalathy14y ago
very usefull for me
Vithal Wadje14y ago
thank you anurag sir its my pleasure
Anurag Sarkar14y ago
Nice One:)

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.

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,

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

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,

protected void 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,

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.