0

I'm trying to export Gridview to Excel file.

Example SQL Database Record

91091234 SAM PLE TEXT
89237416 SAMPLE TEXT

I've been able to bind it to Gridview including the multiple spaces. I've used this code to include those multiple spaces

protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
{
   foreach (TableCell cell in e.Row.Cells)
   {
      cell.Text = cell.Text.Replace(" ", "&nbsp"); // need to modify this part, i think
   }
}

My code for exporting Gridview to Excel

private void _exportGV()
{
Response.Clear();
Response.Buffer = true;
Response.ClearContent();
Response.ClearHeaders();
Response.Charset = "";
string FileName = "Instances" + ".xls";
StringWriter strwritter = new StringWriter();
HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
gv.GridLines = GridLines.Both;
gv.HeaderStyle.Font.Bold = true;
gv.RenderControl(htmltextwrtter);
Response.Write(strwritter.ToString());
Response.End();
}

So my problem is when I export the data shows like this

91091234 SAM&nbspPLE&nbspTEXT
89237416 SAMPLE&nbspTEXT

How do I retain the multiple spaces without replacing it to &nbsp?

cydi
  • 67
  • 6
  • Start using a specialized library for creating Excel files, like [EPPlus](https://github.com/JanKallman/EPPlus). [Example here](https://stackoverflow.com/a/47293207/5836671) and [here](https://stackoverflow.com/a/39513057/5836671). All you are doing now is creating a HTML page with an .xls extension. – VDWWD Dec 17 '19 at 08:29
  • **1.** Do not replace those spaces **2.** Use StringBuilder to write the data from the grid to a .Csv file. It's like writing to a text file. This file can be directly opened in Excel. – Siddharth Rout Dec 17 '19 at 08:36
  • OR you can use [THIS](https://stackoverflow.com/questions/9943787/exporting-datagridview-to-csv-file) method as well – Siddharth Rout Dec 17 '19 at 08:38
  • The &nbsp is not a character that Excel can understand. Try exporting data cell by cell, leaving spaces unconverted. Using Interop classes may be more effective rather than using HtmlTextWriter. – Sertan Pekel Dec 17 '19 at 08:39

0 Answers0