在Visual Basic 2012中将datagridview导出为ex​​cel [英] export datagridview to excel in visual basic 2012

问题描述

你好我正在使用visual studio 2012和sql server 2012制作一个表格，将datagridview导出到microsoft excel 2010.



我的按钮输出编码就像这个：

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

我想问的是：



- 我如何制作在microsoft excel中设置单元格大小的编码会自动调整到我的datagridview中列的大小吗？



示例：在我的datagridview中有一个名为date的列有价值的诞生：12/09 / 1994.但是，当我将数据导出到excel时，值变为#####，并且excel中的单元格不会调整到datagridview中的列大小。



- 如何在visual studio中进行编码以调整microsoft excel单元格中的文本位置（中心，左侧或右侧）？

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?

推荐答案

当您在源代码的第4行设置Autofit（）时，指定的日期为co lumn由系统调整。要覆盖，您可以使用属性设置特定单元格

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

更多详细信息，请访问 https：// msdn。 microsoft.com/en-us/library/microsoft.office.interop.excel.range.columnwidth.aspx [ ^ ]

这篇关于在Visual Basic 2012中将datagridview导出为ex​​cel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！