.Net DataGridView 導出到Execl

将DataGridView 中的資料導出到Execl，可根據DataGridVeiw 的可見的列動态顯示．

代碼如下：

Public Sub PrintOutStoreNumDetail(ByVal Grid As DataGridView )

        Try

            Dim xlApp As New Excel.Application

            Dim xlWorkbook As Excel.Workbook

            Dim xlWorksheet As Excel.Worksheet

            Dim Rowindex As Int16, Colindex As Integer  '' 擷取datagridview的所有行和列

            Dim Arrary As New ArrayList   ''' 取得可見列的索引值

            Dim ColCount As Integer  =   0

            Dim ColNum As Integer  =   0

            xlWorkbook  =  xlApp.Workbooks.Add()

            xlWorksheet  =  xlWorkbook.Worksheets( " sheet1 " )

            Rowindex  =  Grid.Rows.Count

            For i As Integer  =   0  To Grid.Columns.Count  -   1

                If Grid.Columns(i).Visible  =  True Then

                    Arrary.Add(i)

                    ColCount  +=   1

                    Colindex  +=   1

                End If

            Next

            For i As Integer  =   0  To Arrary.Count  -   1

                xlApp.Cells( 1 , i  +   1 )  =  Grid.Columns(Arrary(i)).HeaderText

            Next

            For i As Int16  =   0  To Grid.Rows.Count  -   1

                For j As Integer  =   0  To Arrary.Count  -   1

                    If Grid.Columns(Arrary(j)).ValueType.Name Is System.Type.GetType( " System.String " ).Name Then

                        xlApp.Cells( 2   +  i, j  +   1 )  =   " ' "   &  Grid.Item(Arrary(j), i).Value.ToString

                    ElseIf Grid.Columns(Arrary(j)).ValueType.Name Is System.Type.GetType( " System.Decimal " ).Name Then

                        xlWorksheet.Columns(j  +   1 ).NumberFormatLocal  =   " 0.00_  "

                        xlApp.Cells( 2   +  i, j  +   1 )  =  Convert.ToDecimal(Grid.Item(Arrary(j), i).Value.ToString)

                        ColNum  =  Arrary(j)

                    ElseIf Grid.Columns(Arrary(j)).ValueType.Name Is System.Type.GetType( " System.Int32 " ).Name Then

                        xlApp.Cells( 2   +  i, j  +   1 )  =  Grid.Item(Arrary(j), i).Value.ToString

                    Else

                        xlApp.Cells( 2   +  i, j  +   1 )  =   " ' "   &  Grid.Item(Arrary(j), i).Value.ToString

                    End If

                Next

            Next

            Dim mTable As DataTable  =  CType(Grid.DataSource, DataTable)

            Dim SumCount As Double  =  mTable.Compute( " sum( "   &  Grid.Columns(ColNum).Name  &   " ) " ,  "" )  '' 此處加合計

            ''  showProgressInfo(Grid) 

            With xlWorksheet

                .PageSetup.TopMargin  =   120   '' 距頂部的距離

                .Range(.Cells( 1 ,  1 ), .Cells(Rowindex  +   1 , Colindex)).Font.Size  =   13    '' 設定填充資料的字型大小

                .Range(.Cells( 1 ,  1 ), .Cells(Rowindex  +   1 , Colindex)).RowHeight  =   25    '' 設定行高

                .Range(.Cells( 1 ,  1 ), .Cells( 1 , Colindex)).HorizontalAlignment  =  Excel.XlVAlign.xlVAlignCenter  '' 設定标題對齊方式

                .Range(.Cells( 1 ,  1 ), .Cells( 1 , Colindex)).Select()

                .Range(.Cells( 1 ,  1 ), .Cells( 1 , Colindex)).Font.Name  =   " 黑體 "

                .Range(.Cells( 1 ,  1 ), .Cells( 1 , Colindex)).Font.Bold  =  True

                .Range(.Cells( 1 ,  1 ), .Cells(Rowindex  +   1 , Colindex)).Borders.LineStyle  =   1

                .Columns.EntireColumn.AutoFit()

                 ' .Range(.Cells(Rowindex + 7, 2), .Cells(Rowindex + 7, Colindex)).Merge(False)   '' 合并單元格

            End With

            With xlWorksheet.PageSetup

                .CenterHeader  =   " & "" 宋體,Bold "" &22 "   &   " 公司名稱 "   &  Chr( 10 )  &   " & "" 宋體,Bold "" &16 "   &   Chr( 10 ) 

                .LeftFooter  =   " 制表人： "   &   " _________________ "

                .CenterFooter  =   " 制表日期： "

                .RightFooter  =   " 第&P頁 共&N頁 "

            End With

            xlApp.Visible  =  True  

           '' If index  =   0  Then

            ''Else

            ''    xlWorksheet.PageSetup.Orientation  =  Excel.XlPageOrientation.xlLandscape  

            ''    xlApp.Worksheets.PrintPreview()    '' false  表示可直接顯示列印界面

            '' End If

        Catch ex As Exception

            MsgBox( " PrintOutStoreNumDetail: "   +  ex.ToString, MsgBoxStyle.Exclamation  +  MsgBoxStyle.OkOnly, Msg.CompanyName)

        End Try

    End Sub

 其中,Grid參數可換成其他類型，index=1可直接顯示Excel的列印界面．