Exporting to Excel in a VB Windows App

[jgabrielson]

Hello All,

I have a windows application that exports data from a datagridview into excel. Now this data can change based on what a user does in the datagridview itself. An example would be out of 1,000 files the user can sort through using DataGridViewAutoFilter to search for a handful of rows, say 50 or 143 or whatever. The user then clicks on the button which exports to the excel worksheet. I'm looking for some help on getting a subtotal count at the bottom of the export. I would like to be able to have some additional code in place that would subtotal the records that the user exports into the worksheet. Let me show you what I have so far:

Private Sub ExportToExcelToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExportToExcelToolStripMenuItem.Click
        Dim rowsTotal, colsTotal As Short
        Dim I, j, iC As Short
 
        System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
        Dim xlApp As New Excel.Application
 
        Try
            Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
            Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
            'here
            Dim sY As String
            Dim iX As Integer
            Dim iY As Integer
            'here
            'more here
            Dim TheRg As Excel.Range
            Dim oRow As Excel.Range
 
            'more here
 
            xlApp.Visible = True
            rowsTotal = DataGridView1.RowCount - 0
            colsTotal = DataGridView1.Columns.Count - 1
            For iX = 0 To DataGridView1.Rows.Count - 2
 
            Next
 
            For iY = 0 To DataGridView1.Columns.Count - 2
 
            Next
            With excelWorksheet
                .Cells.Select()
                .Cells.Delete()
                For iC = 0 To colsTotal
                    .Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText
                Next
                For I = 0 To rowsTotal - 1
                    For j = 0 To colsTotal - 0
                        .Cells(I + 2, j + 1).value = DataGridView1.Rows(I).Cells(j).Value
                    Next j
                Next I
                .Rows("1:1").Font.FontStyle = "Bold"
                .Rows("1:1").Interior.colorindex = 37
                .Rows("1:1").Font.Size = 11
 
                'test
                '.Rows("2:100").Interior.colorindex = 40
 
                TheRg = .Rows("2:500")
                For Each oRow In TheRg
                    If (oRow.Row / 2) = Int(oRow.Row / 2) Then
                        With .Rows(oRow.Row).Interior
                            .ColorIndex = 40
 
                        End With
                    End If
                Next
 
 
 
 
 
                'test
 
                .Cells.Columns.AutoFit()
                .Cells.Select()
                .Cells.EntireColumn.AutoFit()
                .Cells(1, 1).Select()
                sY = DataGridView1(iY, iX).Value.ToString & ","
            End With
 
        Catch ex As Exception
            MsgBox("Export Excel Error " & ex.Message)
        Finally
            'RELEASE ALLOACTED RESOURCES
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
            xlApp = Nothing
 
            'codes starts here
        End Try
    End Sub

[tlhintoq]

TIP: When you are writing your question, there is a button on the tool bar that wraps the [code] tags around your copy/pasted code. It helps a bunch. Its the button with a '#' on it. More on tags. They're cool. Check'em out.