Friday, January 7, 2011

Export DataGridView data in Excel using Vb.Net

' creating Excel Application

Dim app As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()





' creating new WorkBook within Excel application

Dim workbook As Microsoft.Office.Interop.Excel._Workbook = app.Workbooks.Add(Type.Missing)





' creating new Excelsheet in workbook

Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing



' see the excel sheet behind the program

app.Visible = True



' get the reference of first sheet. By default its name is Sheet1.

' store its reference to worksheet

worksheet = workbook.Sheets("Sheet1")

worksheet = workbook.ActiveSheet



' changing the name of active sheet

worksheet.Name = "Exported from gridview"





' storing header part in Excel

For i As Integer = 1 To dataGridView1.Columns.Count



  worksheet.Cells(1, i) = dataGridView1.Columns(i - 1).HeaderText
Next







' storing Each row and column value to excel sheet

For i As Integer = 0 To dataGridView1.Rows.Count - 2


  For j As Integer = 0 To dataGridView1.Columns.Count - 1



    worksheet.Cells(i + 2, j + 1) = dataGridView1.Rows(i).Cells(j).Value.ToString()

  Next
Next





' save the application

workbook.SaveAs("c:\output.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
  Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing)



' Exit from the application

app.Quit()

No comments:

Post a Comment