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.Colo r = System.Drawing.ColorTransl ator.ToOle (System.Dr awing.Colo r.Silver)
.Range("B5").Value = "TO Date"
.Range("B5").Font.Bold = True
.Range("B5").ColumnWidth = 25
.Range("B5").Interior.Colo r = System.Drawing.ColorTransl ator.ToOle (System.Dr awing.Colo r.Silver)
.Range("C5").Value = "Linked Transaction Number"
.Range("C5").Font.Bold = True
.Range("C5").ColumnWidth = 30
.Range("C5").Interior.Colo r = System.Drawing.ColorTransl ator.ToOle (System.Dr awing.Colo r.Silver)
.Range("D5").Value = "Item"
.Range("D5").Font.Bold = True
.Range("D5").ColumnWidth = 25
.Range("D5").Interior.Colo r = System.Drawing.ColorTransl ator.ToOle (System.Dr awing.Colo r.Silver)
.Range("E5").Value = "Avg Cost"
.Range("E5").Font.Bold = True
.Range("E5").ColumnWidth = 15
.Range("E5").Interior.Colo r = System.Drawing.ColorTransl ator.ToOle (System.Dr awing.Colo r.Silver)
.Range("F5").Value = "Warehouse"
.Range("F5").Font.Bold = True
.Range("F5").ColumnWidth = 35
.Range("F5").Interior.Colo r = System.Drawing.ColorTransl ator.ToOle (System.Dr awing.Colo r.Silver)
.Range("G5").Value = "Technician"
.Range("G5").Font.Bold = True
.Range("G5").ColumnWidth = 25
.Range("G5").Interior.Colo r = System.Drawing.ColorTransl ator.ToOle (System.Dr awing.Colo r.Silver)
.Range("H5").Value = "Period"
.Range("H5").Font.Bold = True
.Range("H5").ColumnWidth = 30
.Range("H5").Interior.Colo r = System.Drawing.ColorTransl ator.ToOle (System.Dr awing.Colo r.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.E xcel.Range = DirectCast(excelWorksheet. Cells(topR ow, 1), Microsoft.Office.Interop.E xcel.Range )
Dim cell2 As Microsoft.Office.Interop.E xcel.Range = DirectCast(excelWorksheet. Cells(topR ow + dt_Data.Rows.Count - 1, dt_Data.Columns.Count), Microsoft.Office.Interop.E xcel.Range )
Dim range As Microsoft.Office.Interop.E xcel.Range = excelWorksheet.Range(cell1 , cell2)
range.Value = arr
'**End Code for Array
excelWorksheet.Range("E:E" ).NumberFo rmat = "#,###,###.00"
Dim fileReport As String = Environment.GetFolderPath( Environmen t.SpecialF older.Desk top) & "\ServiceReports_PartsRemo vedFromTOs .xlsx"
Dim Report As String = "ServiceReports_PartsRemov edFromTOs. xlsx"
Dim openfileReport As String = Environment.GetFolderPath( Environmen t.SpecialF older.Desk top) & "\~$ServiceReports_PartsRe movedFromT Os.xlsx"
If File.Exists(openfileReport ) Then
MessageBox.Show("You have the file open. Please close the ServiceReports_PartsRemove dFromTOs.x lsx 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(fileRepor t)
'Switch to Excel.
With excelApp
.Visible = True
.UserControl = True
End With
System.Runtime.InteropServ ices.Marsh al.Release ComObject( excelApp.A ctiveWorkb ook)
'xlApp.ActiveWorkbook.Clos e()
'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.WaitForPendingFinalizer s()
Me.Cursor = Cursors.Default
Catch ex As System.Exception
Me.Cursor = Cursors.Default
MessageBox.Show(ex.Message )
End Try
End Sub
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.Colo
.Range("B5").Value = "TO Date"
.Range("B5").Font.Bold = True
.Range("B5").ColumnWidth = 25
.Range("B5").Interior.Colo
.Range("C5").Value = "Linked Transaction Number"
.Range("C5").Font.Bold = True
.Range("C5").ColumnWidth = 30
.Range("C5").Interior.Colo
.Range("D5").Value = "Item"
.Range("D5").Font.Bold = True
.Range("D5").ColumnWidth = 25
.Range("D5").Interior.Colo
.Range("E5").Value = "Avg Cost"
.Range("E5").Font.Bold = True
.Range("E5").ColumnWidth = 15
.Range("E5").Interior.Colo
.Range("F5").Value = "Warehouse"
.Range("F5").Font.Bold = True
.Range("F5").ColumnWidth = 35
.Range("F5").Interior.Colo
.Range("G5").Value = "Technician"
.Range("G5").Font.Bold = True
.Range("G5").ColumnWidth = 25
.Range("G5").Interior.Colo
.Range("H5").Value = "Period"
.Range("H5").Font.Bold = True
.Range("H5").ColumnWidth = 30
.Range("H5").Interior.Colo
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.E
Dim cell2 As Microsoft.Office.Interop.E
Dim range As Microsoft.Office.Interop.E
range.Value = arr
'**End Code for Array
excelWorksheet.Range("E:E"
Dim fileReport As String = Environment.GetFolderPath(
Dim Report As String = "ServiceReports_PartsRemov
Dim openfileReport As String = Environment.GetFolderPath(
If File.Exists(openfileReport
MessageBox.Show("You have the file open. Please close the ServiceReports_PartsRemove
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(fileRepor
'Switch to Excel.
With excelApp
.Visible = True
.UserControl = True
End With
System.Runtime.InteropServ
'xlApp.ActiveWorkbook.Clos
'Tell the Garbage Collector that these objects are ready to be destroyed.
excelWorksheet = Nothing
'xlWBook.Close(SaveChanges
excelBook = Nothing
'xlApp.Quit()
excelApp = Nothing
GC.Collect()
GC.WaitForPendingFinalizer
Me.Cursor = Cursors.Default
Catch ex As System.Exception
Me.Cursor = Cursors.Default
MessageBox.Show(ex.Message
End Try
End Sub
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.Numbe rFormat = "@"
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.Numbe
If you wish to convert prior you can by converting epoch to date string.
This might help here:
http://windevblog.blogspot