Solved

How can I send an excel file using response object?

Posted on 2006-11-15
7
258 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
Comment Utility
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
Comment Utility
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
Comment Utility
If you open the file and do a binary read on it, then pass that value in, it should work for you.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 5

Expert Comment

by:rajups
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 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

12 Experts available now in Live!

Get 1:1 Help Now