• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

How can I send an excel file using response object?

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
sunny012097
Asked:
sunny012097
  • 2
  • 2
  • 2
  • +1
1 Solution
 
strickddCommented:
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
 
sunny012097Author Commented:
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
 
strickddCommented:
If you open the file and do a binary read on it, then pass that value in, it should work for you.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rajupsCommented:
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
 
badalpatelCommented:
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
 
sunny012097Author Commented:
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
 
rajupsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now