locked
export datagridview to excel in visual basic 2012 RRS feed

  • Question

  • hello i'm using visual studio 2012 and sql server 2012 to make a form to export datagridview to microsoft excel 2010.

    my coding for button export is like this:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim xlApp As Microsoft.Office.Interop.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.Application
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")
            xlWorkSheet.Columns.AutoFit()
    
            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
    
            xlWorkSheet.SaveAs("D:\vbexcel.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()
    
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
    
            MsgBox("You can find the file D:\vbexcel.xlsx")
        End Sub
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    End Class

    what i want to ask is:

    - how do i make the coding to set sthe cell size in the microsoft excel will automatically adjust to the size of column in my datagridview?

    example: in my datagridview there is a column named date of birth with value:12/09/1994.but, when i export the data to excel the value became ##### and the cell in excel doesn't adjust to the column size in the datagridview.

    - how do i make the coding in the visual studio to adjust the position of the text(center,left,or right) in the microsoft excel cell?

    • Moved by Youjun Tang Monday, May 25, 2015 9:23 AM more related to the suggested forum
    Saturday, May 23, 2015 8:44 AM

Answers

  • Hi,
    >>how do i make the coding to set sthe cell size in the microsoft excel will automatically adjust to the size of column in my datagridview
    You could use Columnwidth property to set the size for the cell. The following code you could refer to. It has reset the size of column B

    xlWorkSheet.Columns("B").ColumnWidth = 100


    >>how do i make the coding in the visual studio to adjust the position of the text(center,left,or right) in the microsoft excel cell?
    You could set the HorizontalAlignment property to adjust the position for the cells.The following line code provide a example to adjust the position of the text to center.

    xlWorkSheet.Range("B:B").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

    Hope this could help you

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by cxsfgfhtutu Tuesday, May 26, 2015 11:20 AM
    Tuesday, May 26, 2015 5:04 AM

All replies

  • This one on our website is very much used for your problem.

    http://www.vb-tips.com/DataGridViewExport.aspx


    Success
    Cor

    Saturday, May 23, 2015 10:15 AM
  • cxs,

    you are asking questions about the Excel object model. You have no VB.Net implementation problem. I could give an answer by reading the documentation of the Excel object model. It's written there. A better forum is the forum for Excel developers: Excel for developers.

    The VB.Net part is that the column widht is returned from System.Windows.Forms.DataGridViewColumn.Width. However, your example description is referring to auto-fitting a column, which again is an Excel object model question.


    Armin

    Saturday, May 23, 2015 11:27 AM
  • Because of Armin's reply I now become in doubt of your question.

    However, the forum which fits the most is probably Visual Studio for Office

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=vsto

    I myself would certainly not ask this in an excel developers forum. That is VBA and a DataGridView is something mysterious for those who don't use .Net.


    Success
    Cor

    Saturday, May 23, 2015 11:41 AM
  • I myself would certainly not ask this in an excel developers forum.
    I suggested it because the answer can be language independent. But you can be right, too. I believe I've never been there. ;)

    Armin

    Saturday, May 23, 2015 11:48 AM
  • Hi,
    >>how do i make the coding to set sthe cell size in the microsoft excel will automatically adjust to the size of column in my datagridview
    You could use Columnwidth property to set the size for the cell. The following code you could refer to. It has reset the size of column B

    xlWorkSheet.Columns("B").ColumnWidth = 100


    >>how do i make the coding in the visual studio to adjust the position of the text(center,left,or right) in the microsoft excel cell?
    You could set the HorizontalAlignment property to adjust the position for the cells.The following line code provide a example to adjust the position of the text to center.

    xlWorkSheet.Range("B:B").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

    Hope this could help you

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by cxsfgfhtutu Tuesday, May 26, 2015 11:20 AM
    Tuesday, May 26, 2015 5:04 AM