Friday, June 26, 2009

Export the Grid data to Excel

Public Sub Export2Excel(ByVal grdView As DataGridView, ByVal fileName As String)
Dim myFile As String = fileName
Dim subtractBy As Integer, cellText As String
Dim fs As IO.StreamWriter = Nothing
Try
' Open the file and write the headers
fs = New IO.StreamWriter(myFile, False)
fs.WriteLine("")
fs.WriteLine("")
fs.WriteLine("")
' Create the styles for the worksheet
fs.WriteLine(" ")
' Style for the column headers
fs.WriteLine(" ")
fs.WriteLine(" ")
fs.WriteLine(" "ss:WrapText=""1""/>")
fs.WriteLine(" ")
fs.WriteLine("
")
' Style for the column information
fs.WriteLine(" ")
fs.WriteLine(" ")
fs.WriteLine("
")
fs.WriteLine("
")
' Write the worksheet contents
fs.WriteLine("")
fs.WriteLine(" ")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format(" ", _
grdView.Columns.Item(i).Width))
Next
fs.WriteLine(" ")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format(" " & _
"{0}
", _
grdView.Columns.Item(i).HeaderText))
Next
fs.WriteLine("
")
' Check for an empty row at the end due to Adding allowed on the DataGridView
If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1
' Write contents for each cell
For i As Integer = 0 To grdView.RowCount - subtractBy
fs.WriteLine(String.Format(" ", _
grdView.Rows(i).Height))
For intCol As Integer = 0 To grdView.Columns.Count - 1
cellText = ""
If grdView.Item(intCol, i).Value IsNot DBNull.Value Then cellText = grdView.Item(intCol, i).Value
' Check for null cell and change it to empty to avoid error
If cellText = vbNullString Then cellText = ""
fs.WriteLine(String.Format(" " & _
"{0}
", _
cellText.ToString))
Next
fs.WriteLine("
")
Next
' Close up the document
fs.WriteLine("
")
fs.WriteLine("
")
fs.WriteLine("
")
Catch ex As Exception
Throw ex
Finally
If fs IsNot Nothing Then fs.Close()
End Try
End Sub

No comments:

Post a Comment