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

Question

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?

helrich · Accepted Answer · 2014-09-11 11:36:48Z

1

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""")

answered Sep 11, 2014 at 11:36

helrich

1,3101 gold badge15 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user30643 Over a year ago

Hi helrich, thanks a lot for the answer. In fact the value cause problem really a string. I have fixed it by convert it (add single quote (') in front of the variable) before save to excel. In excel a value start with single quote is defined as string.

user30643 Over a year ago

Hi helrich, forgot to tell you that the conversion code is useful.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related