The example for writing the data to an array and sending it to a spreadsheet at http://support.microsoft.com/default.aspx?scid=kb;EN-US;306022 is great and where my thinking on this began. It was inadequate however to deal with datasets and the problems encountered in dynamic data.
I would love to hear from anyone this may help or otherwise so feel free to leave a comment.
This code assumes you have a populated dataset.
‘Import statements – make sure you got the appropriate reference to the Excel library.
Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
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
we want to see what is happening
Excel.Visible = True
rows of dataset variable
Dim rows As Integer = dstest.Tables(0).Rows.Count
columns of dataset variable
Dim columns As Integer = dstest.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) = dstest.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) = dstest.Tables(0).Rows(r).Item(c)
will loop on rows here until all of that columns(c) values are collected
conclude the activity for that column – moving on until all columns are collected
now we write the data to the sheet – use A2 as we need to leave room for the header row
WSheet.Range("A2").Resize(rows, columns).Value = DataArray
we write the header row – we do this cell by cell to allow for special formatting I could have combined it but it was easier this way
count the columns involved
Dim columns2 as Integer
the movement of the loop – necessary due to rows in excel not being zero based.
Dim columns3 as Integer = 1
count the columns in the dataset(columns2)
For columns2 = 0 To dstest.Tables(0).Columns.Count – 1
assigning the values of the dataset column(columns3) in row 1 of spreadsheet (notice not zero based). We do not have to do anything with the row value as we know this is the only row we are interested in.
WSheet.Cells(1, columns3).Value = dstest.Tables(0).Columns(columns2).ColumnName
next excel column we are going to write to – incrementing it upward
columns3 = columns3 + 1
moving on to next dataset column
Please don’t forget to wipe out your Excel instance – This code does a good job of cleanup when exiting program.
Private Function closeExcel() As Short
Dim count As Short = 0
Dim excelInstance As System.Diagnostics.Process
Dim excelInstances() As Process = System.Diagnostics.Process.GetProcessesByName("Excel")
For Each excelInstance In excelInstances
excelInstance.Close() ‘<– you can use close or kill .. up to you
count += 1
Catch ex As Exception
Technorati Tags: vb.net,.NET Framework,csharp,Write,Data,Excel,Array,spreadsheet,Imports,reference,library,Office,Microsoft,Interop,instance,Application,workbook,worksheet,Workbooks,Worksheets,Object,column,element,Item,sheet,header,Range,Resize,Value,cell,cleanup,System,Diagnostics,GetProcessesByName,Kill,columns,Cells,dataset
Windows Live Tags: vb.net,.NET Framework,csharp,Write,Data,Excel,Array,spreadsheet,Imports,reference,library,Office,Microsoft,Interop,instance,Application,workbook,worksheet,Workbooks,Worksheets,Object,column,element,Item,sheet,header,Range,Resize,Value,cell,cleanup,System,Diagnostics,GetProcessesByName,Kill,columns,Cells,dataset