InsertDataTable not handling data type correctly

[dorcutt]

Hello,

I am using the Spire.XLS SDK. One of the uses is to export data from a database to an xlsx file. I pass the dataset into my function and iterate over the tables calling InsertDataTable on each one. This has worked well for me...however I have one table that is not exporting properly. In the failing case one of my columns is a byte[].

No errors are thrown however when I open the exported file I see that the column that should have a byte in it has System.Byte[]

My code looks like this:
Dim book As Workbook = New Workbook()
book.Worksheets.Clear()
book.Version = ExcelVersion.Version2010
Dim counter As Integer = 0
For Each table As Data.DataTable In ds.Tables
Dim sheet As Worksheet
sheet = book.Worksheets.Add(table.TableName)
sheet.InsertDataTable(table, True, 1, 1)
Next
book.SaveToFile(myFilePath, ExcelVersion.Version2007)

Any suggestions?

[Simon.yang]

Hello,

Thanks for your post. What's the real data in the byte[] column? If it's string value, please refer to the below code snippet to accomplish your task.

Code: Select all

         Dim book As New Workbook()
         book.Worksheets.Clear()
         Dim sheet As Worksheet = book.Worksheets.Add("my sheet")
         Dim dt As DataTable = GetData()

         sheet.InsertDataTable(dt, True, 1, 1)

         For i As Integer = 0 To dt.Rows.Count - 1
            For j As Integer = 0 To dt.Columns.Count - 1
               If dt.Columns(j).DataType.Name = "Byte[]" AndAlso TypeOf dt.Rows(i).ItemArray(j) Is Byte() Then
                  'convert byte[] to string
                  sheet.Rows(i + 1).Cells(j).Text = System.Text.Encoding.Default.GetString(TryCast(dt.Rows(i).ItemArray(j), Byte()))
               End If
            Next j
         Next i

         sheet.AllocatedRange.AutoFitColumns()
         book.SaveToFile("result.xlsx", Spire.Xls.FileFormat.Version2010)


Best regards,
Simon
E-iceblue support team

[dorcutt]

Simon,

Thank you so much for the quick response. Clearly my SetCellValue was not doing the trick. I took your code and tweaked it to Base64 encode the byte array and it worked well. Thank you so much! End result was this:

Code: Select all

    For i As Integer = 0 To table.Rows.Count - 1
        For j As Integer = 0 To table.Columns.Count - 1
            If table.Columns(j).DataType.Name = "Byte[]" AndAlso TypeOf table.Rows(i).ItemArray(j) Is Byte() Then
                sheet.Rows(i + 1).Cells(j).Text = Convert.ToBase64String(table.Rows(i).ItemArray(j))
            End If
        Next j
    Next i


[Gary.zhang]

Hello,

Glad to hear that and Thanks for your sharing. Please contact us if you have any queries.

Sincerely,
Gary
E-iceblue support team