Can't open .xlsx files created by ASP.NET page - error “Cannot open the file abc.xlsx

[jasonatstackoverflow]

Hello Good folks,

I have a simple asp.net page (code in C#). I have a gridview control. When the page is running the gridview control has data in it.

There is a button to export the data from gridview control to Excel spreadsheet (.xlsx). Following code does that. .xlsx file gets created fine. But we can't open it with Excel 2016. It says "Cannot open the file abc.xlsx because the file format or file extension is not valid". I have attached the error message.

Any suggestions on why I can't open the .xls file that gets created by ASP.net page?

If I have to avoid exporting data from GridviewControl and instead get the data from source and export to Excel, I won't get the column headings. Thats why I tried to export data from Gridview control into Excel.

          string ExportFileName = "FileName"+ DateTime.Now.Day + DateTime.Now.Month + DateTime.Now.Year + DateTime.Now.Hour + DateTime.Now.Minute      + ".xlsx";

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader(
                "content-disposition", string.Format("attachment; filename={0}", ExportFileName));
            HttpContext.Current.Response.ContentType = "application/ms-excel";

            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    //  Create a form to contain the grid
                    Table table = new Table();

                    //  add the header row to the table
                    if (GridView_ExportCapStock.HeaderRow != null)
                    {
                        PrepareControlForExport(this.GridView_ExportCapStock.HeaderRow);
                        table.Rows.Add(GridView_ExportCapStock.HeaderRow);
                    }

                    //  add each of the data rows to the table
                    foreach (GridViewRow row in GridView_ExportCapStock.Rows)
                    {
                        PrepareControlForExport(row);
                        table.Rows.Add(row);
                    }

                    //  add the footer row to the table
                    if (GridView_ExportCapStock.FooterRow != null)
                    {
                        PrepareControlForExport(GridView_ExportCapStock.FooterRow);
                        table.Rows.Add(GridView_ExportCapStock.FooterRow);
                    }

                    //  render the table into the htmlwriter
                    table.RenderControl(htw);

                    //  render the htmlwriter into the response
                    HttpContext.Current.Response.Write(sw.ToString());
                    HttpContext.Current.Response.End();

                    Loggers.AssemblyLogger.Debug("Export to Excel done" + DateTime.Now);
                }
            }
        }
        catch (Exception ex)
        {
            Loggers.AssemblyLogger.Error(ex);
            throw;
        }

[NeedSomeAnswers]

can you post your - PrepareControlForExport procedure?

[techgnome]

HTMLTextWriter suggests you're writing out HTML text... but you're not... you should be streaming binary data for an Excel file. Also, at the end you .Write(sw.tostring()) ... which doesn't feel right to me either. That's going to write out the string version of the streamwriter, which is going to be something along the lines of a literal string "System.StreamWriter...blah blah blah" or something to that effect. All in all, your entire routine is suspect in my opinion.

-tg

[jasonatstackoverflow]

So what is you suggestion techgnome?

[techgnome]

Don't write out text... that's not what an xls/xlsx file is... you'll need to build up the appropriate object, convert it to a byte stream and send that over. Unfortunately I don't have any examples because the examples I do have are internal objects we use and I can't go about sharing entire projects... but the short of it is that a spreadsheet object is created, then the rows are added to it, then the whole thing is streamed to the response object and transmitted to the client.

-tg