Write data with International characters to Excel

[John Straumann]

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, 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.

[Rod Gill]

Hi,

Have you tried applying a different font to the spreadsheet? It may show
correctly.
--
Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

Author of the only book on Project VBA, see: http://www.projectvbabook.com

Post by John Straumann
I have an ASP.NET application that writes out data to an Excel
spreadsheet. This works fine unless there are international characters in
AgCert do Brasil Soluções Ambientais Ltda.
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, 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.
__________ Information from ESET Smart Security, version of virus
signature database 4720 (20091227) __________
The message was checked by ESET Smart Security.
http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4720 (20091227) __________

The message was checked by ESET Smart Security.

http://www.eset.com