Format Excel Column in vb.net

[ArizonaRedneck]

I need to transfer a dataset into excel for work. I can do that, but I don't know how to format the column through code. for example...

in excel, you would highlight the column,click format,cells, and under category I would choose text.

I need to do this because some of the fields in my dataset are numbers stored as text (zip code and others). When excel opens the dataset, it converts these columns to numbers and any leading zeros are cut off.

here is the sub I'm using.

Friend Sub ExportToExcel(ByVal dstDataSet As DataSet, ByVal TableName As String)
        Dim excelApp As New Excel.Application()
        Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
        Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
        excelApp.Visible = True
        Dim x As Integer = 1
        Dim dcColumn As DataColumn
        For Each dcColumn In dstDataSet.Tables(TableName).Columns
            excelApp.ActiveSheet.Cells(1, x) = dcColumn.ColumnName
            x += 1
        Next
        Dim drDataRow As DataRow
        Dim ColumnCount, RowCount As Integer
        For Each dcColumn In dstDataSet.Tables(TableName).Columns
            ColumnCount += 1
            RowCount = 1
            For Each drDataRow In dstDataSet.Tables(TableName).Rows
                RowCount += 1
                Try
                    excelApp.ActiveSheet.Cells(RowCount, ColumnCount) = CType(drDataRow(dcColumn), String)
                Catch
                    excelApp.ActiveSheet.Cells(RowCount, ColumnCount) = drDataRow(dcColumn)
                End Try
            Next
        Next
        excelApp.Columns.AutoFit()
    End Sub

 

[ArizonaRedneck]

I got it to work. Just in case anyone else out there would find this useful, here's the updated sub.

 Friend Sub ExportToExcel(ByVal dstDataSet As DataSet, ByVal TableName As String)
        Dim excelApp As New Excel.Application()
        Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
        Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
        excelApp.Visible = True
        [b]excelApp.Columns.Cells.NumberFormat = "@"[/b]
        Dim x As Integer = 1
        Dim dcColumn As DataColumn
        For Each dcColumn In dstDataSet.Tables(TableName).Columns
            excelApp.ActiveSheet.Cells(1, x) = dcColumn.ColumnName
            x += 1
        Next
        Dim drDataRow As DataRow
        Dim ColumnCount, RowCount As Integer
        For Each dcColumn In dstDataSet.Tables(TableName).Columns
            ColumnCount += 1
            RowCount = 1
            For Each drDataRow In dstDataSet.Tables(TableName).Rows
                RowCount += 1
                excelApp.ActiveSheet.Cells(RowCount, ColumnCount) = drDataRow(dcColumn)
            Next
        Next
        excelApp.Columns.AutoFit()
    End Sub

-
"Do your duty in all things. You cannot do more, you should never wish to do less." Robert E. Lee