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, Jun 21 '18 at 12:54
@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, Jun 21 '18 at 13:09
@AndrewMorton You Sir, are a Genius! Please post this as an answer and I will gladly accept! — Wowdude, Jun 22 '18 at 07:00
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, Jun 22 '18 at 07:50
For anyone interested, you can utilize LINQ as per the [response](https://github.com/ClosedXML/ClosedXML/issues/918) on ClosedXML Git page. — Wowdude, Jun 22 '18 at 11:17

score 1 · Accepted Answer · answered Jun 22 '18 at 07:48

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 Answers1