Exporting DataTable to Excel file showing ? on mathematics symbols

Question

I am developing a WPF application and in that I need to export my data into excel. I have data of mathematics query with fractions, degrees, power symbols and so on. I am able to see my data correctly but when I export data into excel it changes those symbols to ?

Previously I was using Encoding.ASCII for data then I got to know that we need to change it something else. I have tried UTF7, UTF8, Unicode and Default but nothing works. Although I was able to get some data correctly using Encoding.Default While searching, I got to know about Office Interlop but to run it properly you need to have MS Office excel installed and I don't want to have that dependency with my project.

  int rowIndex = 1;
        colIndex = 0;
  foreach (DataRow row in dvRecords.Table.Rows)
        {
            foreach (DataColumn col in dvRecords.Table.Columns)
            {
                if (row[col] != null)
                {

                    if (col.DataType == typeof(int))
                        this.WriteCell(rowIndex, colIndex, Convert.ToInt32(row[col]));
                    else if (col.DataType == typeof(double))
                        this.WriteCell(rowIndex, colIndex, Convert.ToDouble(row[col]));
                    else if (col.DataType == typeof(bool))
                        this.WriteCell(rowIndex, colIndex, (Convert.ToBoolean(row[col]) ? "Yes" : "No"));
                    else
                        this.WriteCell(rowIndex, colIndex, Convert.ToString(row[col]));

                    colIndex++;
                }
            }
            rowIndex++;
            colIndex = 0;
        }

WriteCell method

 private BinaryWriter _writer;

 private void WriteCell(int row, int col, string value)
    {

        ushort[] clData = { 0x0204, 0, 0, 0, 0, 0 };
        byte[] plainText = Encoding.Default.GetBytes(value);
        int iLen = plainText.Length;
        clData[1] = (ushort)(8 + iLen);
        clData[2] = (ushort)row;
        clData[3] = (ushort)col;
        clData[5] = (ushort)iLen;
        WriteUshortArray(clData);
        _writer.Write(plainText);
    }

 private void WriteUshortArray(ushort[] value)
    {
        for (int i = 0; i < value.Length; i++)
            _writer.Write(value[i]);
    }

I expect to get the symbols correctly imported to excel from my DataTable data, but the data I am getting in its place is "?" symbol

Answer 1

I have searched and finally got the better approach then before. I got to know the library SpreadSheetLight. It is very simple to use and absolutely free. Here is my code for reference which I have changed.

        SLDocument sl = new SLDocument();
        List<string> columnName = new List<string>();
        int rowNumber = 1;
        int currentColumnNumber = 1;
        //set headers
        foreach (DataColumn dataColumn in dvRecords.Table.Columns)
        {
            columnName.Add(dataColumn.ColumnName);
            sl.SetCellValue(rowNumber, currentColumnNumber, dataColumn.ColumnName.Replace("_", " "));
            currentColumnNumber++;
        }
        rowNumber++;
        currentColumnNumber = 1;

        foreach (DataRow dataRow in dvRecords.Table.Rows)
        {
            foreach (var column in columnName)
            {
                sl.SetCellValue(rowNumber, currentColumnNumber, dataRow[column].ToString());
                currentColumnNumber++;
            }
            rowNumber++;
            currentColumnNumber = 1;
        }
        sl.SaveAs(filename);