Spire.XLS for .NET is a professional Excel .NET library that can be used to any type of .NET( C#, VB.NET, ASP.NET, .NET Core) application and Java (J2SE and J2EE) application.

Thu Dec 28, 2017 8:00 pm

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?

dorcutt
 
Posts: 2
Joined: Thu Dec 28, 2017 7:42 pm

Fri Dec 29, 2017 3:49 am

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
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Fri Dec 29, 2017 4:25 pm

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

dorcutt
 
Posts: 2
Joined: Thu Dec 28, 2017 7:42 pm

Mon Jan 01, 2018 2:12 am

Hello,

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

Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Return to Spire.XLS