Write Data To Excel With An Array

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

Sub CreateSpreadSheetFromDataSet

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


End Sub

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
End Try
Return count
End Function

Technorati Tags: ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

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

  1. #1 by Stefan on March 16, 2011 - 7:39 pm

    Thank you. It is great.

  2. #2 by Robert on October 26, 2012 - 5:17 am

    Hello i got one question, what in case if i would like to define which items from the array should be filled in which column, where i should declare it and how?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: