posted on Monday, February 20, 2006 12:16 AM
by
Obi
Launching Excel file from VB.Net
In my current project I was required to read the entire data from a DataGridView (VS.Net 2005) and dump it onto an Excel spreadsheet. Here's how:
Before you start working with Excel, add an Excel reference to your project by selecting 'Microsoft Excel 9.0 Object Library' or later.
The following code simply iterates through the rows and columns of a DataGridView, adds the contents to a StringBuilder object and then writes to an excel file and finally opens the excel file.
Try
Dim iRow, iCol As Int16
Dim value As String
Dim col0 As String = String.Empty
Dim col1 As String = String.Empty
Dim sbHeader As New Text.StringBuilder
Dim sbMain As New Text.StringBuilder
sbHeader.Append("ID," & vbTab & "Expense")
sbHeader.Append(vbCrLf)
For iRow = 0 To grdCustomer.RowCount - 1
For iCol = 0 To grdCustomer.ColumnCount - 1
value = grdCustomer.Item(iCol, iRow).Value.ToString
Select Case iCol
Case 0
col0 = value
Case 1
col1 = value
sbMain.Append(col0 & vbTab & col1)
sbMain.Append(vbCrLf)
End Select
Next
Next
Print("test.xls",sbHeader, sbMain)
Catch ex As Exception
ex.Message.ToString()
End Try
Friend Sub Print(ByVal fileName As String, ByVal sbHeader As StringBuilder, ByVal sbMain As StringBuilder)
Try
Dim dirName As String = DIRECTORY_NAME
If Not Directory.Exists(dirName) Then
Directory.CreateDirectory(dirName)
End If
Dim filePath As String = dirName & fileName
System.IO.File.WriteAllText(filePath, sbHeader.Append(sbMain).ToString)
xlBook = xlApp.Workbooks.Open(filePath)
xlApp.Visible = True
Catch ex As Exception
Throw
End Try
Cheers,
Obi Oberoi