Link to home
Start Free TrialLog in
Avatar of Member_2_7717371
Member_2_7717371

asked on

Change Format on Data when exporting to Excel

Visual Studio Professional 2013/Microsoft Excel 2013

I have a Datagridview in which I am exporting the contents to excel.  There are may rows in the datatable so I am using an array to export them.  However, I have a column that is stored as text, but gets exported in a number format eliminating the preceding zeros.  Does anyone know how to format this column/field so that it does not change to a number when exported?  Thanks in advance.

Code:
Private Sub Export_PartsRemovedFromTOs()
        Try
            'First add Header Row
            With excelWorksheet
               
                .Range("A5").Value = "TO Number"
                .Range("A5").Font.Bold = True
                .Range("A5").ColumnWidth = 15
                .Range("A5").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver)
                .Range("B5").Value = "TO Date"
                .Range("B5").Font.Bold = True
                .Range("B5").ColumnWidth = 25
                .Range("B5").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver)
                .Range("C5").Value = "Linked Transaction Number"
                .Range("C5").Font.Bold = True
                .Range("C5").ColumnWidth = 30
                .Range("C5").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver)
                .Range("D5").Value = "Item"
                .Range("D5").Font.Bold = True
                .Range("D5").ColumnWidth = 25
                .Range("D5").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver)
                .Range("E5").Value = "Avg Cost"
                .Range("E5").Font.Bold = True
                .Range("E5").ColumnWidth = 15
                .Range("E5").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver)
                .Range("F5").Value = "Warehouse"
                .Range("F5").Font.Bold = True
                .Range("F5").ColumnWidth = 35
                .Range("F5").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver)
                .Range("G5").Value = "Technician"
                .Range("G5").Font.Bold = True
                .Range("G5").ColumnWidth = 25
                .Range("G5").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver)
                .Range("H5").Value = "Period"
                .Range("H5").Font.Bold = True
                .Range("H5").ColumnWidth = 30
                .Range("H5").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver)
               
            End With

            '**This is the code for putting the data table into an array and exporting to excel -
            Dim arr As Object(,) = New Object(dt_Data.Rows.Count - 1, dt_Data.Columns.Count - 1) {}
            For r As Integer = 0 To dt_Data.Rows.Count - 1
                Dim dr As DataRow = dt_Data.Rows(r)
                For c As Integer = 0 To dt_Data.Columns.Count - 1
                    arr(r, c) = dr(c)
                Next
            Next
            Dim topRow As Integer = 6
            Dim cell1 As Microsoft.Office.Interop.Excel.Range = DirectCast(excelWorksheet.Cells(topRow, 1), Microsoft.Office.Interop.Excel.Range)
            Dim cell2 As Microsoft.Office.Interop.Excel.Range = DirectCast(excelWorksheet.Cells(topRow + dt_Data.Rows.Count - 1, dt_Data.Columns.Count), Microsoft.Office.Interop.Excel.Range)
            Dim range As Microsoft.Office.Interop.Excel.Range = excelWorksheet.Range(cell1, cell2)
            range.Value = arr
            '**End Code for Array

            excelWorksheet.Range("E:E").NumberFormat = "#,###,###.00"

            Dim fileReport As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\ServiceReports_PartsRemovedFromTOs.xlsx"
            Dim Report As String = "ServiceReports_PartsRemovedFromTOs.xlsx"
            Dim openfileReport As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\~$ServiceReports_PartsRemovedFromTOs.xlsx"
           
            If File.Exists(openfileReport) Then
                MessageBox.Show("You have the file open.  Please close the ServiceReports_PartsRemovedFromTOs.xlsx file and try again!", "File Open Error")

               
                excelBook = Nothing
                excelApp.Quit()
                excelApp = Nothing
                Me.Cursor = Cursors.Default

                Exit Sub
            End If

            If File.Exists(fileReport) Then
                File.Delete(fileReport)

            End If

            excelBook.SaveAs(fileReport)

            'Switch to Excel.
            With excelApp
                .Visible = True
                .UserControl = True
            End With

            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.ActiveWorkbook)
            'xlApp.ActiveWorkbook.Close()

            'Tell the Garbage Collector that these objects are ready to be destroyed.
            excelWorksheet = Nothing
            'xlWBook.Close(SaveChanges:=False)
            excelBook = Nothing
            'xlApp.Quit()
            excelApp = Nothing




            GC.Collect()
            GC.WaitForPendingFinalizers()

            Me.Cursor = Cursors.Default
        Catch ex As System.Exception
            Me.Cursor = Cursors.Default
            MessageBox.Show(ex.Message)
        End Try
    End Sub
Avatar of Brian Benson
Brian Benson

I don't think you need to convert to date, just display as a date using  excelWorksheet.Range("A:B").NumberFormat = "yyyy-mm-dd" on the cells you wish to display as a date.  The number is how time is tracked internally if I am reading your correctly.

If you wish to convert prior you can by converting epoch to date string.

This might help here:
http://windevblog.blogspot.com/2008/09/epoch-time-in-vbnet.html
Avatar of Member_2_7717371

ASKER

I'm not converting to date.  I have a text value in the datagridview but when it exports to excel it gets changed to a number which removes the preceding zeros.  I want it to export and stay in the text format.  Column "item" in the code.
ASKER CERTIFIED SOLUTION
Avatar of Brian Benson
Brian Benson

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I figure out the issue and your comment triggered a thought so I will mark it as the solution.

The problem is that excel uses the first few rows of data to auto format the data.  I tested this by sorting in descending order and when I did none of the values were converted to numbers because the first few rows started with a "Z".  So I added the line of code below to change the worksheet cells to text before inserting any data and that did the trick.  Thanks

excelWorksheet.Cells.NumberFormat = "@"