Using closedXML C# library, How can I figure out what data is causing this error when trying to save

Question

I have a C# app that exports to Excel using ClosedXML. It works fine but just ran into an issue where when i hit the :

  var ms = new MemoryStream();
  workbook.SaveAs(ms);

I get an exception:

' ', hexadecimal value 0x0B, is an invalid character

Its definitely data related because it I look at certain data it works fine but other data it causes this issue.

how can i figure out which character is causing the issue? Also, once I figure that out, what is the best way of finding where this character is within my data?

Answer 1

Since you have invalid characters in the data / strings you put into the ClosedXML sheet, you have to find them and get them out.

The simplest solution is to add

.Replace((0x0B).ToString(), " ")

to all your strings to get rid of the vertical tabs and replace them with spaces.