locked
Export CRM data to Excel, international characters getting jumbled RRS feed

  • Question

  • Hi all:

    I have an ASP.NET application that writes out data to an Excel spreadsheet.
    This works fine unless there are international characters in the data, such
    as:

    AgCert do Brasil Soluções Ambientais Ltda.

    and then the characters get messed up and are replaced by things like:

    AgCert do Brasil Soluções Ambientais Ltda.

    It seems to be an Excel issue, when I open the CSV file in an editor the
    international characters show up fine, but in Excel they show up jumbled. To
    further confuse the issue, I tried copying and pasting the special
    characters directly to Excel from CRM, and that worked fine.

    Here is the code, the datatable is built from a Query from Dynamics CRM

      //===============================================================================================
      //===============================================================================================
      public void ExportToSpreadsheet(DataTable table, string name)
      {
        Encoding unicode = Encoding.Unicode;

        HttpContext context = HttpContext.Current;
        context.Response.Clear();
        foreach (DataColumn column in table.Columns)
        {
          context.Response.Write(column.ColumnName + Chr(9));
        }
        context.Response.Write(Environment.NewLine);
        foreach (DataRow row in table.Rows)
        {
         for (int i = 0; i < table.Columns.Count; i++)
         {
          context.Response.Write(row[i] + Chr(9));
         }
         context.Response.Write(Environment.NewLine);
        }
        byte[] rgByteLeader = new byte[2];
        rgByteLeader[0] = 0xff;
        rgByteLeader[1] = 0xfe;
        context.Response.BinaryWrite(rgByteLeader);
        context.Response.ContentType = "application/vnd.ms-excel";
        context.Response.ContentEncoding = System.Text.Encoding.Unicode;
        context.Response.AppendHeader("Content-Disposition",
    "attachment;filename=" + name + ".xls");

        context.Response.End();
     }

    Can anyone make any suggestions?

    John.
    Thursday, December 17, 2009 1:25 AM
    Moderator

All replies