Solved

How can I send an excel file using response object?

Posted on 2006-11-15
7
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

622 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