Click here to Skip to main content
14,298,065 members
Rate this:
Please Sign up or sign in to vote.
See more:
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 the 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?
Posted
Updated 24-May-15 0:54am
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

As you set Autofit() in 4th line of your source code, the specified date column is adjusted by the system. To override, you can set the specific cell using the property
Range.ColumnWidth


More details are at https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.columnwidth.aspx[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100