mso-data-placement:same-cell not working

Question

I am exporting data into Excel from a web page. This should be a no brainer, but there are <p> tags in the data. This causes Excel to create new rows when the data should all be in the same cell. After some research I found that mso-data-placement should do the trick, but it's not working. Excel opens, the data is displayed, but extra uncessary rows are created. Here is the code I use to export the data:

protected void doexcel()
  {
      string style =  @"<style type='text/css'>P {mso-data-placement:same-cell; font-weight:bold;}</style>";


    HttpResponse response = HttpContext.Current.Response;

    // first let's clean up the response.object
    response.Clear();
    response.Charset = "";

    //set the response mime type for excel
    response.ContentType = "application/vnd.ms-excel";
    Random RandomClass = new Random();
    int RandomNumber = RandomClass.Next();
    String filename = "a" + RandomNumber + DateTime.Now + ".xls";
    response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"" );

    // create a string writer
    using (StringWriter sw = new StringWriter())
    {
        using (HtmlTextWriter htw = new HtmlTextWriter(sw))
        {

      HttpContext.Current.Response.Write(style);
            SqlDataSourceEmployeeAssets.ConnectionString =   MyObjects.Application.CurrentContext.ConnectionString;
            String sql = (string)Session["sql"];
            SqlDataSourceEmployeeAssets.SelectCommand = sql;
            // lCount.Text = "Query returned " + getCount(query) + " rows.";
            DataGrid dge = new DataGrid();
            dge.DataSource = SqlDataSourceEmployeeAssets;
            dge.DataBind();
            dge.RenderControl(htw);
            response.Write(sw.ToString());
            response.End();
        }
    }
}

This is an example of the raw data in the database that is giving me grief:

<P>4/13/2011 : Cheng  "Jonathan" Vaing is with BSES Graffiti Unit.</P><P>4/13/2011 : Cheng  "Jonathan" Vaing is with</P>

Suggestions?

---

I tried a couple of other things

1. I went straight to the data and added the mso-data-placement attribute to the paragraph tag inline. Still didn't work. The data looked like this

<P style="mso-data-placement:same-cell> my data </p>

1. I tried other mso-* attributes, that didn't work either. For example, I changed my stylesheet to look like this

<style type='text/css'>P {mso-highlight:yellow}</style>";

Why oh why doesn't Excel recognize my mso-* attributes?!?!

Answer 1

There is a solution but it is not clean.

After the dge.DataBind, place the following code. This will encode the text of each cell

 foreach (DataGridItem dgi in dge.Items)
 {                
      foreach (TableCell cell in dgi.Cells)
      {
           cell.Text = WebUtility.HtmlEncode(cell.Text);;
      }
 }        

The Excel file, when opened, should show the raw data with the markup, all in one cell.

I found that this works because Excel actually encodes the text, as well. To see what Excel does in action, do the following:

1. Create a new workbook in Excel (I am using Office 2013).
2. In the first cell, paste the raw data (as you have it displayed). Do this by first pressing F2 (insert into cell), then paste the text.
3. Save the workbook as an HTML file (or web page).
4. Using windows explorer, go to the folder location of where you saved the file. There should be a hidden folder (i think it is hidden) with the same name as your file. For example, if your workbook is Book1.htm, there should be a folder labeled Book1_files.
5. In this folder, there should be an HTM file with the name sheet001.htm. Open this file in notepad (or any text editor...not excel or word)
6. Locate your raw data. You will see that the text is not showing the HTML markup, rather it is showing the encoded version.

Hope this helps.