Exporting DataTable to CSV file using Binary order markup
Exporting Data from DataTable object to .CSV is discussed here.
The Issue
While working with european characters, exporting data into .csv file (Excel 2003) replacing eastern european characters with funny characters. Due to lack of support in Excel 2003.
Resolution
In order to resolve the issue, it is required to use BOM(Binary Order markup). The BOM gives the producer of the text a way to describe the text stream's endianness to the consumer of the text without requiring some contract or metadata outside of the text stream itself.
Excel 2003 cannot recognize the encoding correctly, BOM can help it recognize.
1: System.Text.StringBuilder sb = new System.Text.StringBuilder();
2: foreach (DataColumn col in dtResults.Columns)
3: {
4: sb.Append(col.ColumnName.ToUpper() + ",");
5: }
6:
7: sb.Remove(sb.Length - 1, 1);
8: sb.Append(Environment.NewLine);
9:
10: foreach (DataRow row in dtResults.Rows)
11: {
12: for (int i = 0; i < dtResults.Columns.Count; i++)
13: {
14: sb.Append(row[i].ToString().Replace("\n", " ")
15: .Replace("\n\r", ";").Replace("\r", " ").Replace(",", ";").Replace("0", "0") + ",");
16: }
17:
18: sb.Append(Environment.NewLine);
19: }
20: Response.Clear();
21: Response.ContentType = "text/csv";
22: Response.AppendHeader("Content-Disposition", "attachment; filename="
23: + fileName + ".csv");
24:
25: byte[] BOM = { 0xEF, 0xBB, 0xBF };
26: Response.BinaryWrite(BOM);
27:
28: Response.Write(sb.ToString());
29: Response.End();
30: