ClosedXML Export to xlsx error: 'Unable to set cell value to ' + uniqueidentifier

Question

I'm looking to create a simple windows service to export data from SQL tables, through a .NET datatable, directly to an Excel xlsx file.

Whilst testing ClosedXML, I found that when the 1st column is a uniqueidentifier the ClosedXML.dll errors

"An unhandled exception of type 'System.ArgumentException' occurred in ClosedXML.dll

Additional information: Unable to set cell value to [insert uniqueidentifier here]"

Is there any easy way around this?

Sample Code:

Private Sub ExportToxlsxUsingClosedXML

    Dim appPath As String = Directory.GetCurrentDirectory()
    Dim filename As String = appPath & "\mytest_" & Format(Now, "yyyyMMddHHmmss") & ".xlsx"

    Dim SQL_Query As String = "select * FROM [mydb].[dbo].[mytable] where myDate >= '2017-01-01' "

    Dim myTable As DataTable = CLS_SQL.SQL_Retrieve(SQL_Query)

    Dim wb As XLWorkbook = New XLWorkbook
    wb.Worksheets.Add(myTable, "MyData")
    wb.SaveAs(filename)

End Sub

@AndrewMorton I've tried it with removing the uniqueidentifier and it works seamlessly (as a Winforms app for now), but I was hoping to keep it. my try changing the column order or adding in a dummy/blank column beforehand. — Wowdude
@AndrewMorton I've just tried adding in a blank column at ordinal position 0, but it still doesn't allow the uniqueidentifier to be a cell values. Looks like it can't handle uniqueidentifiers at all. — Wowdude
@AndrewMorton You Sir, are a Genius! Please post this as an answer and I will gladly accept! — Wowdude
Aw, shucks! I've posted it as an answer. You might want to contact the author of ClosedXML and let them know it doesn't work with the uniqueidentifier type. — Andrew Morton

Andrew Morton Andrew Morton · Accepted Answer · 2018-06-22T07:48:18

It appears from your findings that ClosedXML does not work with the uniqueidentifier type.

However, as it definitely works with string types, you can cast the appropriate column to a VARCHAR(36) in the SQL query.

It would then be possible to parse it to a Guid in .NET if it was needed as such.

ClosedXML Export to xlsx error: 'Unable to set cell value to ' + uniqueidentifier

1 Answers