datagridview中的数据导出到excel过程

这两天在研究怎样将DataGridView和listview 中的数据导出到Excel中

这两个控件基本方法是一样，只需要将 DGV(iY, iX)　改成listview.item即可

 '把datagridview中的数据导出到excel

 Public Sub dgExportToExcel()Sub dgExportToExcel(ByVal DGV As DataGridView)
        Dim wapp As New Microsoft.Office.Interop.Excel.Application
        Dim wsheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim wbook As Microsoft.Office.Interop.Excel.Workbook

        'Dim wapp As New Excel.Application
        'Dim wsheet As Excel.Worksheet
        'Dim wbook As Excel.Workbook

        On Error Resume Next

        wapp.Visible = True
       '打已有的excel模板文件
        wbook = wapp.Workbooks.Open(System.IO.Directory.GetCurrentDirectory().ToString() & "HTXX.xlt")
        '方式一：
        'wsheet = wbook.ActiveSheet
        '方式二：
        wsheet = wbook.Worksheets("Sheet1")

        Dim iX As Integer
        Dim iY As Integer
        Dim iC As Integer

        '写DV控件头名称
    'For iC = 0 To DGV.Columns.Count - 1

        '    wsheet.Cells(1, iC + 1).Value = DGV.Columns(iC).HeaderText
        '    wsheet.Cells(1, iC + 1).Font.Bold = True
        'Next

        '选择表格第三行
    wsheet.Rows(3).select()
        For iX = 0 To DGV.Rows.Count - 1            'DataGridView 总行数
            wsheet.Cells(iX + 3, 1).value = (iX + 1).ToString
            For iY = 0 To DGV.Columns.Count - 1
                wsheet.Cells(iX + 3, iY + 2).value = DGV(iY, iX).Value.ToString
                'wsheet.Cells(iX + 3, iY + 2).Borders(xlEdgeTop).
                '.LineStyle = xlContinuous
                '.Weight = xlThin
                '.ColorIndex = xlAutomatic
            Next
        Next

        '画表格线
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlDiagonalDown).LineStyle = XlLineStyle.xlLineStyleNone
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlDiagonalUp).LineStyle = XlLineStyle.xlLineStyleNone

        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlEdgeLeft).Weight = XlBorderWeight.xlThin
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlEdgeLeft).ColorIndex = XlColorIndex.xlColorIndexAutomatic

        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlEdgeTop).Weight = XlBorderWeight.xlThin
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlEdgeTop).ColorIndex = XlColorIndex.xlColorIndexAutomatic

        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlThin
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlEdgeBottom).ColorIndex = XlColorIndex.xlColorIndexAutomatic

        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlEdgeRight).Weight = XlBorderWeight.xlThin
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlEdgeRight).ColorIndex = XlColorIndex.xlColorIndexAutomatic

        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlInsideVertical).Weight = XlBorderWeight.xlThin
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlInsideVertical).ColorIndex = XlColorIndex.xlColorIndexAutomatic

        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlInsideHorizontal).Weight = XlBorderWeight.xlThin
        wsheet.Range(wsheet.Cells(3, 1), wsheet.Cells(DGV.RowCount - 1 + 3, DGV.ColumnCount + 1)).Borders(XlBordersIndex.xlInsideHorizontal).ColorIndex = XlColorIndex.xlColorIndexAutomatic

'页脚处理
        With wsheet.PageSetup
            '.PaperSize = XlPaperSize.xlPaperA3
            '.Orientation = XlPageOrientation.xlLandscape
            '.LeftHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10公司名称：" '& Gsmc 
            '.CenterHeader = "&""楷体_GB2312,常规""宋体,常规""" & strBT & Chr(10) & "&""楷体_GB2312,常规"""
            '.RightHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10单位："
            .LeftFooter = "&""楷体_GB2312,常规""&10制表人："
            .CenterFooter = "&""楷体_GB2312,常规""&10制表日期：" & Now.Date.ToString
            .RightFooter = "&""楷体_GB2312,常规""&10第&P页 共&N页"

        End With

    End Sub