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?
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