Replicating table rows in a datatable causes the datatype in an Excel output report to be different?

Question

I pull a report from SQL Server being not a fan of cursors I process that table server side in my code behind file. So I pull this report that is an address label report and my client wants there to be X number of labels per person. So I coded this function:

 Private Function ProcessX(ByVal dt As DataTable, ByVal X As Integer) As DataTable
    Dim dtProcessed As DataTable = dt.Copy

    dtProcessed.Clear()

    For Each dr As DataRow In dt.Rows
        For i As Integer = 0 To X - 1
            Dim drHolder As DataRow = dtProcessed.NewRow
            drHolder.ItemArray = dr.ItemArray
            dtProcessed.Rows.Add(drHolder)
        Next
    Next

    Return dtProcessed
End Function

Which the function works beautifully and it processes much faster than a cursor in a sproc. The problem with this particular report is that when I export it to excel it chops off the leading 0s of zip codes that start with 0. I also have zip+4 and international zip codes that are returned in the same pull. Normally I would just chalk this up to a standard excel glitch on how it handles leading 0s, but when I don't process the report through the above function Excel does not chop off my leading 0s.

I stepped through the function and the datatype for the column in question is the same before and after the process and matches the datatype of the original table. The only thing I can think of is maybe because I am passing the datatable object ByVal instead of ByRef? I don't know I have tried almost everything else including copying each row item by value and recreating the datatable by hand. Nothing seems to change the outcome.

Answer 1

I found a solution and I am not happy with it, but it works.

SELECT [ZipCode] = ' ' + ISNULL(TABLE.ZipCode,'')
FROM   TABLE
...

Both the online report and excel interpret it as an empty space and it doesn't mess with my formatting.