Solved

How can I send an excel file using response object?

Posted on 2006-11-15
7
259 Views
Last Modified: 2009-12-16
Hi Experts,

I have stuck on something. In some part of my application when user clicks a button and excel file is created on the fly and I want it to be downloaded or opened by the user. The function below works for CSV file but when I try to convert it for excel it didn't work. Any idea how I can do the same thing with excel files?


  Public Sub redirectToText(ByVal fileToShow As String, ByVal fileName As String)
        Dim req As System.Net.WebRequest = System.Net.WebRequest.Create(fileToShow)
        Dim result As System.Net.WebResponse = req.GetResponse()
        Dim ReceiveStream As Stream = result.GetResponseStream
        Dim sreader As New StreamReader(ReceiveStream)
        Dim sbPage As String
        sbPage = sreader.ReadToEnd
        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.AddHeader("Content-Type", "multipart/form-data")
        HttpContext.Current.Response.ContentType = "multipart/form-data"

        ' "attachment;" will cause the open action to prompt for download twice.
        ' Remove to only have prompt occur once.
        'Response.AddHeader("Content-Disposition", "attachment; FileName=" & "agingexport.csv")
        HttpContext.Current.Response.AddHeader("Content-Disposition", "FileName=" & fileName)
        HttpContext.Current.Response.Write(sbPage)
        HttpContext.Current.Response.End()
    End Sub
0
Comment
Question by:sunny012097
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 28

Expert Comment

by:strickdd
ID: 17949499
here is a class i wrote that will help you:

      /// <summary>
      /// Summary description for Download.
      /// </summary>
      public class Download
      {
            private Download()
            {
                  // There is no contructor for this class
            }

            public enum Extension
            {
                  CSV,
                  XLS,
                  TXT,
                  DOC,
                  HTML,
                  JPEG,
                  GIF
            }

            /// <summary>
            /// Displays a download dialogue
            /// </summary>
            /// <param name="FileName">Name the file should be downloaded as</param>
            /// <param name="TextForDownload">Data to download</param>
            /// <param name="FileExtension">Extension of Download</param>
            public static void FileDownloadPrompt(string FileName, string TextForDownload, Extension FileExtension)
            {
                  FileDownloadPrompt(FileName, TextForDownload, GetContentTypeFromExtension(FileExtension), GetExtensionFromFileExtension(FileExtension));
            }

            /// <summary>
            /// This will turn a reader into a CSV with column headers and data
            /// </summary>
            /// <param name="FileName">Name the file should be downloaded as</param>
            /// <param name="reader">SQL or ODBC datareader to download</param>
            /// <param name="FileExtension">Extension of download</param>
            public static void FileDownloadPrompt(string FileName, IDataReader reader, Extension FileExtension)
            {
                  FileDownloadPrompt(FileName, ToCSV(reader), FileExtension);
            }

            /// <summary>
            /// This will prompt the user to download the BinaryFile.
            ///            Used commonly with downloading a file saved to a database.
            /// </summary>
            /// <param name="FileName">Name the file should be downloaded as</param>
            /// <param name="BinaryFile">Byte Array Representation of Data</param>
            /// <param name="FileExtension">Extension of Download</param>
            public static void FileDownloadPrompt(string FileName, byte[] BinaryFile, Extension FileExtension)
            {
                  FileDownloadPrompt(FileName, BinaryFile, GetContentTypeFromExtension(FileExtension), GetExtensionFromFileExtension(FileExtension));
            }

            public static void FileDownloadPrompt(string FileName, byte[] BinaryFile, string ContentType)
            {
                  FileDownloadPrompt(FileName, BinaryFile, ContentType, GetExtensionFromContentType(ContentType));
            }

            #region Private Functions
            private static void FileDownloadPrompt(string FileName, byte[] BinaryFile, string ContentType, string FileExtension)
            {
                  //set the conttent type of the file to be downloaded
                  System.Web.HttpContext.Current.Response.ContentType = ContentType;
                  //add the response headers
                  System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=\"" + FileName + FileExtension + "\"");
                  //add the contents of the result set to the response stream
                  System.Web.HttpContext.Current.Response.BinaryWrite(BinaryFile);

                  //end the http response
                  System.Web.HttpContext.Current.Response.End();
            }

            private static void FileDownloadPrompt(string FileName, string TextForDownload, string ContentType, string FileExtension)
            {
                  //set the conttent type of the file to be downloaded
                  System.Web.HttpContext.Current.Response.ContentType = ContentType;
                  //add the response headers
                  System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=\"" + FileName + FileExtension + "\"");
                  //add the contents of the result set to the response stream
                  System.Web.HttpContext.Current.Response.Write(TextForDownload);

                  //end the http response
                  System.Web.HttpContext.Current.Response.End();
            }

            private static string GetExtensionFromContentType(string ContentType)
            {
                  switch(ContentType.ToLower())
                  {
                        case "application/x-msexcel":
                        case "application/vnd.ms-excel":
                              return ".csv";
                        case "text/plain":
                              return ".txt";
                        case "application/x-msword":
                              return ".doc";
                        case "text/html":
                              return ".html";
                        case "image/jpeg":
                              return ".jpg";
                        case "image/gif":
                              return ".gif";
                        default:
                              return ".txt";
                  }
            }

            private static string GetExtensionFromFileExtension(Extension FileExtension)
            {
                  switch(FileExtension)
                  {
                        case Extension.CSV:
                              return ".csv";
                        case Extension.XLS:
                              return ".xls";
                        case Extension.DOC:
                              return ".doc";
                        case Extension.TXT:
                              return ".txt";
                        case Extension.HTML:
                              return ".html";
                        case Extension.JPEG:
                              return ".jpg";
                        case Extension.GIF:
                              return ".gif";
                        default:
                              return ".txt";
                  }
            }

            private static string GetContentTypeFromExtension(Extension FileExtension)
            {
                  switch(FileExtension)
                  {
                        case Extension.CSV:
                              return "application/x-msexcel";
                        case Extension.XLS:
                              return "application/vnd.ms-excel";
                        case Extension.DOC:
                              return "application/x-msword";
                        case Extension.TXT:
                              return "text/plain";
                        case Extension.HTML:
                              return "text/html";
                        case Extension.JPEG:
                              return "image/jpeg";
                        case Extension.GIF:
                              return "image/gif";
                        default:
                              return "text/plain";
                  }
            }

            private static string ToCSV(IDataReader iDr)
            {
                  string sb = "";

                  //check to see if there the reader has columns
                  if(iDr.FieldCount != 0)
                  {
                        //loop thru each column
                        for(int i = 0; i <= iDr.FieldCount -1; i++)
                        {
                              //check to see if we are on the last column
                              // this is necessary so that we could append the nextline symbol
                              if(i<iDr.FieldCount -1)
                              {
                                    //if not the last column, append the column name and a ','
                                    sb += "\"" + Utilities.CommonFunctions.ConvertToString(iDr.GetName(i)) + "\",";
                              }
                              else
                              {
                                    //append the last column name then append a carriage return and line feed
                                    sb += "\"" + Utilities.CommonFunctions.ConvertToString(iDr.GetName(i)) + "\"";
                                    sb += "\r\n";
                              }
                        }
               
                        //loop until the record reaches eol(end of line)
                        while(iDr.Read())
                        {
                              //loop thru each column, this is necessesary so that we could map
                              //the columns to their proper place
                              for(int i = 0; i <= iDr.FieldCount -1; i++)
                              {
                                    //check to see if we are on the last column
                                    //this is necessary so that we could append the nextline symbol
                                    if(i<iDr.FieldCount -1)
                                    {
                                          //if not the last column, append the column value and a ','
                                          sb += "\"" + Utilities.CommonFunctions.ConvertToString(iDr.GetValue(i)) + "\",";
                                    }
                                    else
                                    {
                                          //append the last column value then append a carriage return and line feed
                                          sb += "\"" + Utilities.CommonFunctions.ConvertToString(iDr.GetValue(i)) + "\"";
                                          sb += "\n";
                                    }
                              }
                        }
                  }
                  //return our stringbuilder
                  return sb.ToString();
            }
            #endregion
      }
0
 

Author Comment

by:sunny012097
ID: 17949756
thanks for the replay.

I can't see if it works coz file's locations is http://localhost/text/31911343543556E.xls

how can I do a binary read from there?
0
 
LVL 28

Expert Comment

by:strickdd
ID: 17949946
If you open the file and do a binary read on it, then pass that value in, it should work for you.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 5

Expert Comment

by:rajups
ID: 17953591
Hi,
Use below code. nd can add as many cases u can depending on all allowed / possible file extensions


strFileExtension = System.IO.Path.GetExtension(strDocumentName).Trim()
                'Based on the extension, it will add MimeType
                Select Case strFileExtension
                    Case ".xls"
                        strMimeType = "application/vnd.ms-excel"
                End Select
                'check if the file exists, if file exists, it will force do force download
                If (System.IO.File.Exists(strFullpath)) Then
                    Response.AppendHeader("content-disposition", "attachment; filename=" + strDocumentName)
                    Response.ContentType = strMimeType
                    Response.WriteFile(strFullpath)
                    Response.Flush()
                    Response.Close()
                End If


All the Best.

Raju P S
0
 
LVL 6

Expert Comment

by:badalpatel
ID: 17953906
u just have to modify ur code..i have written modified code below

Public Sub redirectToText(ByVal fileToShow As String, ByVal fileName As String)
        Dim req As System.Net.WebRequest = System.Net.WebRequest.Create(fileToShow)
        Dim result As System.Net.WebResponse = req.GetResponse()
        Dim ReceiveStream As Stream = result.GetResponseStream
        Dim sreader As New StreamReader(ReceiveStream)
        Dim sbPage As String
        sbPage = sreader.ReadToEnd
       
          HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" & FileName)
            HttpContext.Current.Response.Flush()
            HttpContext.Current.Response.Clear()
            HttpContext.Current.Response.Write(sbPage)

            HttpContext.Current.Response.End()      
    End Sub

here just remember one thing..ur FileName variable should contains filename with .xls extension like abc.xls
0
 

Author Comment

by:sunny012097
ID: 17959046
When I point to a disk location like c:\ccc.xls it works fine but when I point to URL it does not. any idea?
0
 
LVL 5

Accepted Solution

by:
rajups earned 500 total points
ID: 17962421
Hi,
U can not refer the file using URL, u have to give the Physical path only, Virtual pathis only to open directly not to stream.

If you are using any DFS  then instead of using c:\.., u can use \\servername\... also, but u have to provide the Physical path only.
If its in the application root folder u can even use Server.MapPath("./").

All d Best,

Raju P S
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now