By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,049 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

Exporting to Excel in a VB Windows App

P: 2
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:



Expand|Select|Wrap|Line Numbers
  1. Private Sub ExportToExcelToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExportToExcelToolStripMenuItem.Click
  2.         Dim rowsTotal, colsTotal As Short
  3.         Dim I, j, iC As Short
  4.  
  5.         System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
  6.         Dim xlApp As New Excel.Application
  7.  
  8.         Try
  9.             Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
  10.             Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
  11.             'here
  12.             Dim sY As String
  13.             Dim iX As Integer
  14.             Dim iY As Integer
  15.             'here
  16.             'more here
  17.             Dim TheRg As Excel.Range
  18.             Dim oRow As Excel.Range
  19.  
  20.             'more here
  21.  
  22.             xlApp.Visible = True
  23.             rowsTotal = DataGridView1.RowCount - 0
  24.             colsTotal = DataGridView1.Columns.Count - 1
  25.             For iX = 0 To DataGridView1.Rows.Count - 2
  26.  
  27.             Next
  28.  
  29.             For iY = 0 To DataGridView1.Columns.Count - 2
  30.  
  31.             Next
  32.             With excelWorksheet
  33.                 .Cells.Select()
  34.                 .Cells.Delete()
  35.                 For iC = 0 To colsTotal
  36.                     .Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText
  37.                 Next
  38.                 For I = 0 To rowsTotal - 1
  39.                     For j = 0 To colsTotal - 0
  40.                         .Cells(I + 2, j + 1).value = DataGridView1.Rows(I).Cells(j).Value
  41.                     Next j
  42.                 Next I
  43.                 .Rows("1:1").Font.FontStyle = "Bold"
  44.                 .Rows("1:1").Interior.colorindex = 37
  45.                 .Rows("1:1").Font.Size = 11
  46.  
  47.                 'test
  48.                 '.Rows("2:100").Interior.colorindex = 40
  49.  
  50.                 TheRg = .Rows("2:500")
  51.                 For Each oRow In TheRg
  52.                     If (oRow.Row / 2) = Int(oRow.Row / 2) Then
  53.                         With .Rows(oRow.Row).Interior
  54.                             .ColorIndex = 40
  55.  
  56.                         End With
  57.                     End If
  58.                 Next
  59.  
  60.  
  61.  
  62.  
  63.  
  64.                 'test
  65.  
  66.                 .Cells.Columns.AutoFit()
  67.                 .Cells.Select()
  68.                 .Cells.EntireColumn.AutoFit()
  69.                 .Cells(1, 1).Select()
  70.                 sY = DataGridView1(iY, iX).Value.ToString & ","
  71.             End With
  72.  
  73.         Catch ex As Exception
  74.             MsgBox("Export Excel Error " & ex.Message)
  75.         Finally
  76.             'RELEASE ALLOACTED RESOURCES
  77.             System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
  78.             xlApp = Nothing
  79.  
  80.             'codes starts here
  81.         End Try
  82.     End Sub
Oct 20 '09 #1
Share this Question
Share on Google+
1 Reply


tlhintoq
Expert 2.5K+
P: 3,525
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.
Oct 20 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.