0

When trying to export data from a C# GridView to Excel, all newline ("\n") and return carriage ("\r") are being disregarded and removed on the export.

For example, when exporting this text "testing1\r\n\r\ntesting 2\r\n\r\ntesting 3", it exports like this:

enter image description here

I'd like it to be this

enter image description here

Here is the code I am using, I don't know what else to try. I've been looking for answers for some time now with no solution other than possibly switching to export to CSV instead although that is not ideally what I want.

   var gridView = new GridView { DataSource = content };
   Response.ClearContent();
   gridView.DataBind();
   Response.Buffer = true;
   Response.AddHeader("content-disposition", $"attachment; filename={fileName}.xls");
   Response.ContentType = "application/ms-excel";
   Response.Charset = "";            
   StringWriter objStringWriter = new StringWriter();
   HtmlTextWriter objHtmlTextWriter = new HtmlTextWriter(objStringWriter);
   gridView.RenderControl(objHtmlTextWriter);
   Response.Output.Write(objStringWriter.ToString());
   Response.Flush();
   Response.End();
TechnoKid
  • 23
  • 6
  • Your code doesn't create an Excel file at all. It creates an HTML file with a fake extension. Don't do that. This doesn't fool Excel – Panagiotis Kanavos Jan 07 '21 at 17:18
  • The duplicate shows how to create a *real* Excel file with only a few lines of code using a library like EPPlus – Panagiotis Kanavos Jan 07 '21 at 17:20
  • BTW you didn't specify the web app framework you use. Is it WebForms? MVC? Web Pages? This affects how the final file is sent to the client. Only Webforms requires writing to the response object directly, all other stacks have a built-in way to return streams – Panagiotis Kanavos Jan 07 '21 at 17:23
  • Does that library have the capability to fix my original problem? Where new lines and return carriages are not removed from strings on the export, and line breaks are formatted correctly in cells? I am using MVC, so I'd like the file to be downloaded in the browser when the user reaches an action in a controller – TechnoKid Jan 07 '21 at 17:26
  • Yes - because the original problem is that you don't create an Excel file at all. I repeat, this is just an HTML file with a fake extension. And `\n` is meaningless in HTML, it's just whitespace. Excel isn't fooled anyway. It will detect this immediately and try to *import the HTML table* using default settings. This can fail for any number of reasons. Eg if `<` or `>` appears in the table text, it will result in invalid HTML. You have to create a valid Excel file before you export newlines – Panagiotis Kanavos Jan 07 '21 at 17:27

0 Answers0