none
Unable to Read File When Exporting To Excel In C# RRS feed

  • Question

  • I am using C# exporting data to excel. This works fine at first. The export is triggered via a button event, if the user request several exports from the same page in a row I start to receive the error "Unable to Read File". If I select "SAVE" file instead of open, then it writes the file just fine. It just will not open the file in the browser.

    I am fairly certain that the error is coming from Excel, since Excel does open up but will not load my file.

    I read about Response.End and Response.Close ... Some say never ever use them some say to use them. I have tried every combination of End and Close.

    My gut feeling that that something is being cached in memory, because of I close my browser (or wait for a long time) it seems to clear up the problem.

    Does anyone have an idea of what could be wrong, or at least how do I tell why excel is unable to read my file when I can write it and open it from my hard drive without an issue?

    protected void btnSaveToXls_Click(object sender, EventArgs e)
    {
    
        Response.ClearHeaders();
        Response.Cache.SetCacheability(HttpCacheability.Private);
        Response.Buffer = true;
        Response.AddHeader("content-transfer-encoding", "binary");
        Response.Clear();           
        Response.AddHeader("Content-Disposition", "attachment; filename=myFileName.xls");
        Response.AddHeader("pragma", "private");
        Response.ContentType = "application/vnd.ms-excel";
    
        using (StringWriter sw = new StringWriter())
        {
            // The main purpose of these next two writes are to name the worksheet and add the gridlines to the excel sheet
            sw.Write(@"<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">");
            sw.Write(@"<head>
                    <xml>
                    <x:ExcelWorkbook> 
                        <x:ExcelWorksheets>
                            <x:ExcelWorksheet>
                                <x:Name>Clients Contacted</x:Name>
                                <x:WorksheetOptions>
                                    <x:Panes></x:Panes>
                                    <x:Print><x:Gridlines /></x:Print>
                                </x:WorksheetOptions>
                            </x:ExcelWorksheet>
                        </x:ExcelWorksheets>
                    </x:ExcelWorkbook>
                    </xml>
                </head>");
    
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                Label newLine = new Label();
                newLine.Text = "<br/>";
                GridView gv = new GridView();
                gv.GridLines = GridLines.Both;
                gv.HeaderStyle.Font.Bold = true;
    
                //. . .
                // a bunch of code here to populate my gridview
                //. . .
    
                Panel p = new Panel();
                p.Controls.Add(lblTitle);
                p.Controls.Add(new Label { Text = "<BR/>" });
                p.Controls.Add(lblReportTitle);
                p.Controls.Add(new Label { Text = "<BR/>" });
                p.Controls.Add(new Label { Text = string.Format("Run Date {0}", DateTime.Now.ToString("MM/dd/yyyy")) });
                p.Controls.Add(new Label { Text = "<BR/>" });
                p.Controls.Add(new Label { Text = "<BR/>" });
                p.Controls.Add(gv);
    
                p.RenderControl(htw);
                try
                {
                    //  Response.Clear();
                    sw.Write("</html>");
                    Response.Buffer = true;
                    Response.Output.Write(sw.ToString());
                    // Response.Output.Flush();
                    Response.Flush();
                    // Response.End();
                }
                catch (Exception ex)
                {
                   //... Logging the error ...
                }
                finally
                {
                    Response.Close();
                }
            }
        }
    }

    • Moved by CoolDadTx Saturday, September 30, 2017 4:40 PM ASP.NET related
    Friday, September 29, 2017 9:45 PM

All replies