title: Vb.net export data to excel
body: <p>I have a problem with the following code to export the data to excel with following button click Event:</p>
  
  <pre><code>Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
      ExportToExcel()
  End Sub
  </code></pre>
  
  <p><strong>here is the code</strong> </p>
  
  <pre><code>Private Sub ExportToExcel()
      Dim ExcelApp As Object, ExcelBook As Object
      Dim ExcelSheet As Object
      Dim i As Integer
      Dim j As Integer
      Try
          Me.DgView.DefaultCellStyle.ForeColor = Color.Navy
          Me.DgView.RowsDefaultCellStyle.BackColor = Color.AliceBlue
          Me.DgView.GridColor = Color.Blue
          Me.DgView.BorderStyle = BorderStyle.Fixed3D
          Me.DgView.RowHeadersBorderStyle = BorderStyle.Fixed3D
  
  
          DgView.AutoGenerateColumns = False
  
          Dim ReferColumn As New DataGridViewTextBoxColumn()
          ReferColumn.DataPropertyName = "Refer_no"
          ReferColumn.HeaderText = "ካሴት መ/ቁ"
          ReferColumn.Width = 80
  
          Dim TitleColumn As New DataGridViewTextBoxColumn()
          TitleColumn.DataPropertyName = "Title"
          TitleColumn.HeaderText = "ካሴት ርእስ"
          TitleColumn.Width = 150
  
          Dim GnrNmColumn As New DataGridViewTextBoxColumn()
          GnrNmColumn.DataPropertyName = "Genre_Name"
          GnrNmColumn.HeaderText = "የካሴት ዓይነት"
          GnrNmColumn.Width = 100
  
          Dim GrpNmColumn As New DataGridViewTextBoxColumn()
          GrpNmColumn.DataPropertyName = "Group_Name"
          GrpNmColumn.HeaderText = "የምድብ ስም"
          GrpNmColumn.Width = 100
  
          Dim ActColumn As New DataGridViewTextBoxColumn()
          ActColumn.DataPropertyName = "Actors"
          ActColumn.HeaderText = "ሪፖርተር"
          ActColumn.Width = 150
  
          Dim DirecColumn As New DataGridViewTextBoxColumn()
          DirecColumn.DataPropertyName = "Director"
          DirecColumn.HeaderText = "ኃላፊ"
          DirecColumn.Width = 150
  
          Dim LangColumn As New DataGridViewTextBoxColumn()
          LangColumn.DataPropertyName = "Language"
          LangColumn.HeaderText = "ቋንቋ"
          LangColumn.Width = 150
          Dim releasedateColumn As New DataGridViewTextBoxColumn()
          LangColumn.DataPropertyName = "release_date"
          LangColumn.HeaderText = "የተቀረፀበተት ቀነን"
          LangColumn.Width = 150
          Dim StatusColumn As New DataGridViewTextBoxColumn()
          StatusColumn.DataPropertyName = "status"
          StatusColumn.HeaderText = "ሁኔታ"
          StatusColumn.Width = 80
  
          Dim synopsisColumn As New DataGridViewTextBoxColumn()
          synopsisColumn.DataPropertyName = "synopsis"
          synopsisColumn.HeaderText = "የተቀረፀበተት ጉዳይ"
          synopsisColumn.Width = 180
  
          DgView.Columns.Add(ReferColumn)
          DgView.Columns.Add(TitleColumn)
          DgView.Columns.Add(GnrNmColumn)
          DgView.Columns.Add(GrpNmColumn)
          DgView.Columns.Add(ActColumn)
          DgView.Columns.Add(DirecColumn)
          DgView.Columns.Add(LangColumn)
          DgView.Columns.Add(releasedateColumn)
          DgView.Columns.Add(StatusColumn)
          DgView.Columns.Add(synopsisColumn)
  
          Dim columns As String() = {"Refer_No", "Title", "genre_name", "group_name", "Actors", "Director", "Language", "release_date", "Status", "synopsis"}
  
  
          DVDModule.FillListWithoutParam(DVDList, columns, "usp_SelectDVDList", _
                                                           GetType(Entity.DVD))
  
          FormatGridWithBothTableAndColumnStyles()
          DgView.DataSource = DVDList
  
  
          'create object of excel
          ExcelApp = CreateObject("Excel.Application")
          ExcelBook = ExcelApp.WorkBooks.Add
          ExcelSheet = ExcelBook.WorkSheets(1)
  
          With ExcelSheet
              For Each column As DataGridViewColumn In DgView.Columns
                  .cells(1, column.Index + 1) = column.HeaderText
              Next
              For i = 1 To Me.DgView.RowCount
                  .cells(i + 1, 1) = Me.DgView.Rows(i - 1).Cells("Refer_no").Value
                  For j = 1 To DgView.Columns.Count - 1
                      .cells(i + 1, j + 1) = DgView.Rows(i - 1).Cells(j).Value
                  Next
              Next
          End With
  
          ExcelApp.Visible = True
          '
          ExcelSheet = Nothing
          ExcelBook = Nothing
          ExcelApp = Nothing
  </code></pre>