CODEWITHSUNDEEP
vb.netexceldatagridexport
user30643
user30643

Reputation: 207

vb.net export datagrid as excel some column data format lost

I am using vb.net to generate a datagrid, and based on the datagrid I have a function allow user to export the datagrid value into excel. The strange thing is one of my string column value is very strange:

(1) datagrid result:

enter image description here

(2) only after the export - the BQ SEGMENT data is strange and different from what in the datagrid:

enter image description here

(3) I change the cell format of "BQ SEGMENT" to general or text - result also strange and wrong

enter image description here

My code for export as excel:

Dim xlApp As Excel.Application
    Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
    Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim i As Integer
    Dim j As Integer

    xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass
    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = xlWorkBook.Sheets("sheet1")


    For i = 0 To DataGridView1.RowCount - 2
        For j = 0 To DataGridView1.ColumnCount - 1
            For k As Integer = 1 To DataGridView1.Columns.Count
                xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
            Next
        Next
    Next

    Dim saveFileDialog1 As New SaveFileDialog
    saveFileDialog1.Filter = "Excel File|*.xlsx"
    saveFileDialog1.Title = "Save an Excel File"
    saveFileDialog1.ShowDialog()

    xlApp.ScreenUpdating = True
    xlApp.Visible = False

    If Not saveFileDialog1.FileName.Equals("") Then
        xlApp.DisplayAlerts = False
        xlWorkSheet.SaveAs(saveFileDialog1.FileName)
        MsgBox("The data has been successfully export as excel file.")
    End If

    xlWorkBook.Close()
    xlApp.Quit()
    xlApp = Nothing

    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkSheet)

Is the error caused by the code?

Upvotes: 0

Views: 3817

Answers (1)

helrich
helrich

Reputation: 1310

Your original date format is pretty weird. I'm guessing 1988/03-1 is January 3, 1988? So basically your issue isn't with the data itself but the formatting in Excel. Your third example shows how Excel internally represents dates, Decimal value where 1.0 is January 1, 1900, adding 1 for each day since then. The fractional part of the number (after the decimal) holds the time information (of which your data does not have).

Anyways, if you were to right click that date column and choose "Format Cells...", you would be able to specify the format you would like your dates to use. This will differ from one user/machine to the next. Another option would be, in your export for loop, to parse your date column's value as a string using the format you specify, and dressing the value in such a way that makes Excel treat the value as a string, not a DateTime (adding = and surrounding the value in double-quotes):

xlWorkSheet.Cells(i + 2, j + 1) = _
   DateTime.Parse(DataGridView1(j, i).Value.ToString()) _
   .ToString("=""yyyy/dd-M""")

Upvotes: 1

Related Questions