CODEWITHSUNDEEP
vb.netclosedxml
Simon Price
Simon Price

Reputation: 3271

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

I am using closedXML to take a datatable and place this into an Excel file.

The code I am working with works, with 99% of the files I put through the application, but I get an error with a file every now and then. (no this is not a debugging issue)

The problem must originate from the data, however I am at a loss on how to resolve this.

The code I'm using is 

Public Sub WriteToExcel(dt As DataTable, filePath As String)

    Dim workbook = New XLWorkbook()
    Dim worksheet = workbook.Worksheets.Add(dt, "Call Validate Export")
    Try
        workbook.SaveAs(filePath)
        Process.Start(filePath)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

When saving the file I get the error

'', hexadecimal value 0x1A, is an invalid character.

between the '' there is a little arrow pointing to the right.

When reading the file in to the datatable it reads in fine, looking at the file I see no hex characters.

The file being read in is a ^ delimited csv file.

So, my question is how to I check and repair\replace the bad characters in the output that will allow me to save 100% of the time.

Upvotes: 0

Views: 19632

Answers (3)

Francois Botha
Francois Botha

Reputation: 4849

https://github.com/ClosedXML/ClosedXML/pull/66 is a pull request which could solve your issue. Please try it.

Upvotes: 0

Francois Botha
Francois Botha

Reputation: 4849

From the XML specification ( https://www.w3.org/TR/xml/#charsets )

Char ::= #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF] /* any Unicode character, excluding the surrogate blocks, FFFE, and FFFF. */

Which implies that character #x1A is an invalid character. You should manually remove it.

Upvotes: 3

Bharat Mori
Bharat Mori

Reputation: 383

@Simon,

It looks like whatever you are trying to export to Excel, it contains some invalid characters (Arrow pointing to right side).

I was receiving the similar error and upon detailed look, I come to know that I am exporting DataTable into Excel and one of the cell value of DataTable was looking like:

Please Note -> Some comment (Here, -> is actually a single character : Arrow pointing to right side)

I have removed those characters and it is working fine now.

Hope this helps.

Upvotes: 0

Related Questions