Stealing people's code, and doing a terrible job of it
Check out this link http://www.kjmsolutions.com/datasetarray.htm which was posted at KJM Solutions about a year ago. Now, check out this code posted by “Vinod” claiming to be his. You'll notice that he didn't even change the frigging comments - just stone cold stole old boy's code. He changes a cell reference from A2 to A20 which shows that not only is he a thief, but he's a Moron. Since he didn't change the author's comments, chaning the cell reference to A20 to allow for the 'header row' makes no sense and proves that the guy is a scumbag. Some people have no shame! Then, just to add insult to injury, someone else cites the code and credits Vinod. At least Homer tried to do the right thing, but Vinod is a BUTTMUNCH http://vbcity.com/forums/topic.asp?tid=67309 . And just for the record, I'm using Buttmunch as a synonym for goddamned a-hole.
HERE IS A SAMPLE TO CODE TO AUTOMATE EXCEL FROM VB.NET. NEED TO INSTALL THE OFFICEXP INTEROP ASSEMBLIES BEFORE IT WILL WORK AND SET REFERENCE TO THE EXCEL OBJECT LIBRARY. MAIL ME IF U NEED ANY FURTHER INFO.
'Import statements to set reference to Office
Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
'Open excel and populate the cells
Sub CreateSpreadSheetFromDataSet(ByVal mydataset As DataSet)
'test for zero rows OR more than excel limit
If (mydataset.Tables(0).Rows.Count > 0) Then
If (mydataset.Tables(0).Rows.Count > 65536) Then
MsgBox("Query results greater than 65,536 rows")
Exit Sub
End If
Else
MsgBox("Empty Dataset")
Exit Sub
End If
'create new instance of application
Dim Excel As New Excel.Application()
' create(New workbook())
Dim oBook As Excel.Workbook
' create(New worksheet())
Dim WSheet As New Excel.Worksheet()
'Adding new worksheet to excel workbooks
WSheet = Excel.Workbooks.Add.Worksheets.Add()
'Name the worksheet
WSheet.Name = "test"
'we want to see what is happening
Excel.Visible = True
'rows of dataset variable
Dim rows As Integer = mydataset.Tables(0).Rows.Count
'columns of dataset variable
Dim columns As Integer = mydataset.Tables(0).Columns.Count
'used to count rows in loop
Dim r As Integer
'used to count columns in loop
Dim c As Integer
'array to hold data - the size is set by the dataset 'rows' number and 'columns' data
Dim DataArray(rows, columns) As Object
'first we deal with the column in the loop - assigning the element in the dataarray of c. Dont worry about 'r' at this point. We are only 'filling the 'c' of the array
For c = 0 To columns - 1
DataArray(r, c) = mydataset.Tables(0).Columns.Item(c).ColumnName
'next we deal with the row (r of the array) entries for the 'c' column - for that column we just created in the array - that is the part which threw me because I was expecting it to write the whole row. Notice we are still using the same value for c. It has not changed from the first loop.
For r = 0 To rows - 1
DataArray(r, c) = mydataset.Tables(0).Rows(r).Item(c)
'will loop on rows here until all of that columns(c) values are collected
Next
'conclude the activity for that column - moving on until all columns are collected
Next
'now we write the data to the sheet - use A20 as we need to leave room for the header row
WSheet.Range("A20").Resize(rows, columns).Value = DataArray
'we write the header row - we do this cell by cell to a