Writing to Browser Issues

Topics: Developer Forum, User Forum
Jul 26, 2011 at 7:56 PM

I've developed an ASP.NET VB application which writes Excel spreadsheets

using OfficeOpenXml. The initial version of the code called SaveAs to write

the XML to a file. This works. I'm able to open the file in Excel and view the

rows and cells.

 

I'd like the user to be able to select the folder where the file will be

stored. I updated the code to write the Xml to the browser using

the HTTP Response object Write method.


When I run the code IE displays the Download panel. I select Save As,

browse and find a folder, and then save the file. I run Excel and attempt

to open the file. Excel complains the file is "unreadable" or "corrupt".

 

I assumed the problem was the Response Header CharSet field.

I've set CharSet to:

us-ascii, utf-7, utf-8, utf-16, and utf-32.

 

In addition to setting the CharSet I've read the data

from the Stream object using System.Text.Encoding.Unicoding, Utf32Encoding,

Utf8Encoding, AsciiEncoding. Utf7Encoding, and Utf8Encoding.

 

If I used Utf32Encoding then I set CharSet to utf-32., etc.

 

Regardless of which CharSet I use Excel complains the contents

of the file are unreadable or corrupt.

I've set Response ContentType to:

application/ms-excel
application/vnd.ms-excel
application/vnd.openxmlformats

Still no luck.

I've written the data as a String or Char() using Response.Write.

Excel still complains the file is unreadable.

I did look at some other posts in Discussions List which

appeared to be related to writing the XML using the Http response

object but they did not give any answers to this specific problem.

My methods are below:

Steps:

1) I call the FileDataGetFromSaveAsAsChar method to get the

XML from the Package.

2) I call SaveAsDialog passing the file data from step #1

to be written out to the browser.

 

' This method writes the XML to a memory stream, gets the data from the

' memory stream into a Byte() buffer, Encodes the data.

' As noted above I've tried all System.Text Encodings.

    Public Function FileDataGetFromSaveAsAsChar(ByRef CharSet As String) As Char()
        Dim BufferByte As Byte()
        Dim BufferChar As Char()
        Dim EncodingObj As System.Text.UTF8Encoding

        Dim StreamObj As MemoryStream

        StreamObj = New MemoryStream()
        PackageObj.SaveAs(StreamObj)

        StreamObj.Flush()

        BufferByte = New Byte(StreamObj.Length) {}
        StreamObj.Seek(0, SeekOrigin.Begin)
        StreamObj.Read(BufferByte, 0, BufferByte.Length)

        EncodingObj = New System.Text.UTF8Encoding()
        BufferChar = EncodingObj.GetChars(BufferByte)

        CharSet = EncodingObj.HeaderName

        Return BufferChar

    End Function

 

    Public Sub SaveAsDialog(ByVal ResponseObj As HttpResponse, ByVal FileName As String, ByVal FileData As Char(), ByVal CharSet As String)
        Dim FileNameOnly As String
        Dim Header As String

        ResponseObj.Buffer = True
        ResponseObj.Charset = CharSet
        'ResponseObj.ContentType = "application/ms-excel"
        'ResponseObj.ContentType = "application/vnd.ms-excel"
        ResponseObj.ContentType = "application/vnd.openxmlformats"

        FileNameOnly = Path.GetFileName(FileName)

        Header = String.Format("attachment;filename={0}", FileNameOnly)
        ResponseObj.AppendHeader("content-disposition", Header)

        ResponseObj.Write(FileData, 0, FileData.Length)
        ResponseObj.Flush()
        ResponseObj.End()
    End Sub

 

Any suggestions would be helpful?

 

Ed

 

Aug 1, 2011 at 9:09 PM

I do need to get this working. I'll add $15 to your Starbucks card

if someone can give me working code or suggestions which lead

to working code.

Ed

Aug 4, 2011 at 5:05 PM

I have this and it works. However, when I have concurrent users. It cause issue at pack.Save();

Anyone has solution to this? Greatly appreciate it.

 

     static public MemoryStream GetEmployeeAvailability(string role, Boolean showAll)
        {
            string sqlQuery = "whatever"
            MemoryStream ms = new MemoryStream();
            using (ExcelPackage pack = new ExcelPackage(ms))
            {
                ExcelWorksheet ws = pack.Workbook.Worksheets.Add("test");

                SqlConnection connection = new SqlConnection(connectionString);
                using (connection)
                {
                    SqlCommand sqlCommand = new SqlCommand(sqlQuery, connection);
                    connection.Open();
                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    using (sqlCommand)
                    {
                        SqlDataReader reader = sqlCommand.ExecuteReader();
                        int rowID = 1;
                        while (reader.Read())
                        {
                            //Append the column names
                            if (rowID == 1)
                            {
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    ws.Cells[1, i + 1].Value = reader.GetName(i);
                                    ws.Column(i + 1).Width = ws.Cells[1, i + 1].Value.ToString().Length * 1.7;
                                    ws.Cells[1, i + 1].Style.Font.Bold = true;
                                }
                                ws.Row(1).Style.Font.Bold = true;

                            }
                            //Append the dara rows
                            rowID++;
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                if (reader[i] != DBNull.Value)
                                {
                                    ws.Cells[rowID, i + 1].Value = reader[i].ToString();
                                }
                            }
                        }
                    }
                }

               pack.Workbook.Properties.Title = "Export Employee Data";
                pack.Save();
            }
            return ms;
        }

 

 

 

 

 
        private void ExportEmployeeAvailabilityToExcel()
        {
            HttpContext context = HttpContext.Current;
            try
            {
                MemoryStream ms = GetEmployeeAvailability(role, chkIncludeConfidential.Checked);
                context.Response.Clear();
                context.Response.ContentType = "application/vnd.openxmlformats";
                context.Response.AddHeader("Content-Disposition", "attachment; filename=AllStaffData.xlsx");
                context.Response.OutputStream.Write(ms.GetBuffer(), 0, Convert.ToInt32(ms.Length));
                context.Response.Flush();
                context.Response.Close();
                context.Response.End();
            }
            catch (System.Threading.ThreadAbortException)
            {
                //context.Response.Redirect(context.Request.Url.ToString());
            }
            catch (Exception ex)
            {
                context.Response.Write(ex.ToString());
            }
        }