C# Export to Excel giving '', hexadecimal value 0x02, is an invalid character error

Question

giving '', hexadecimal value 0x02, is an invalid character error

foreach (DataRow dataRow in data.Rows)
    {
         row = new DocumentFormat.OpenXml.Spreadsheet.Row { RowIndex =+rowcount};
         for (int i = 0; i < fieldsToExpose.Length; i++)
            {
               row.Append(CreateTextCell(ColumnLetter(i), rowcount,dataRow[fieldsToExpose[i]].ToString()));
            }
             sheetData.AppendChild(row);
     }
 worksheetPart.Worksheet.Save();

Getting error when saving the worksheetPart.Worksheet.Save() because 'row' contains the hexadecimal value

Answer 1

There must be some invalid XML text. you can check it by using below statement:

System.Xml.XmlConvert.VerifyXmlChars(dataRow[fieldsToExpose[i]].ToString())

Use the below code:

row.Append(
  CreateTextCell(
    ColumnLetter(i), 
    rowcount, 
    Regex.Replace(
      dataRow[fieldsToExpose[i]].ToString(), 
      @"[\u0000-\u0008\u000A-\u001F\u0100-\uFFFF]", 
      ""
    )
  )
);