Remove special character from DataTable

Question

I've a DataTable which I use to write it to Excel using below code. I use using ClosedXML.Excel to export it to Excel

var worksheet = workbook.Worksheets.Add(dataTable, "Report");

Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;  filename="Report.xlsx");

using (MemoryStream MyMemoryStream = new MemoryStream())
            {
                workbook.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);
                MyMemoryStream.Close();
            }

Response.Flush();
Response.End();

The above code throws error for the special character: '', hexadecimal value 0x1A, is an invalid character. I'm unable to copy the special character here. So please apologize.

I saw below post to replace the special character in the post C# hexadecimal value 0x12, is an invalid character

static string ReplaceHexadecimalSymbols(string txt)
{
string r = "[\x00-\x08\x0B\x0C\x0E-\x1F\x26]";
return Regex.Replace(txt, r,"",RegexOptions.Compiled);
}

Could anyone guide me on how I can convert DataTable value to string and back again from String to DataTable.

Or please let me know if there is any other approach to tackle this special character issue?

On which row is the exception thrown? `workbook.Worksheets.Add(dataTable, "Report");` ? — Magnus, Apr 03 '14 at 09:19
@Raj, If possible I would like to export with Special Character, else, I'm ok to remove it since Export is priority & not special character. @Magnus, I get error at line `workbook.SaveAs(MyMemoryStream);` — Interstellar, Apr 03 '14 at 09:21
I've seen that version of the ReplaceHexadecimalSymbols() function quoted in several articles. Note that if you data contains an & character, this function will just drop it. So your Excel will now something like "Barnes Noble". This might not be what your users are expecting.....! — Mike Gledhill, Jul 11 '17 at 08:21

score 1 · Accepted Answer · answered Apr 03 '14 at 09:46

If you need to replace the invalid string values in the DataTable you can do something like the following:

foreach (DataRow row in dt.Rows)
{
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        if(dt.Columns[i].DataType == typeof(string))
            row[i] = ReplaceHexadecimalSymbols((string)row[i]);
    }
}

Do it before you add it to the workbook

Remove special character from DataTable

1 Answers1