problem formatting excel data

[radiance]

Hello.

I need to write data to excel. The problem is not writing the data to excel, but how to render the data in the format that my client seeks.

The data is currently output as:

select * from fullViewPrg, where the test fields are as follows: program, platformType, directRights, cleared, totalCosts

so, from the table view, the data appears as:

Program Platform Rights Cleared Costs Summary
Toon Comedy Free TV Program Rights pending 1200 lorem ipsom
Toon Comedy Cable TV SAG Residuals Triggered yes 1800 lorem ipsom
Toon Comedy Internet Broadband 3rd Party Music Cleared no 6300 lorem ipsom
Toon Comedy Wireless Download Program Rights yes 2200 lorem ipsom

Toon Comedy Free TV SAG Residuals Triggered no
Toon Comedy Cable TV 3rd Party Music Cleared no lorem ipsom
Toon Comedy Wireless Download 3rd Party Music Cleared yes lorem ipsom
Toon Comedy Internet Broadband Program Rights yes lorem ipsom
Toon Comedy Cable TV Program Rights yes lorem ipsom

Toon Comedy Internet Broadband SAG Residuals Triggered no lorem ipsom
Toon Comedy Free TV 3rd Party Music Cleared no lorem ipsom
Toon Comedy Wireless Download SAG Residuals Triggered yes lorem ipsom


it's currently writing as:

Toon Comedy Free TV Cable TV Internet Broadband Wireless Download
3rd Party Footage Cleared
3rd Party Music Cleared Yes Yes Yes Pending
Program Rights
SAG Residuals Triggered


Costs 1200 1800 6300 2200


But I need for the data to appear as (rights appearing once):


Free TV Cable TV Internet Broadband Wireless Download Summary
Toon Comedy

Program Rights pending yes yes yes
SAG Residuals Triggered no yes no yes
3rd Party Music Cleared no no no yes

Costs 1200 1800 6300 2200



In my code, I created 2 datasets, one to ensure that 'Program' was displayed once and written once in the Excel Worksheet. The second dataset contained the full view's data. What can i do in my code to get the formatted output above? I can't get the field 'cleared' to display properly.

Here's a snippet of the code, for how I am writing the data to excel as well as the datasets:

oCmd = New SqlCommand("select * from fullViewPrg where program LIKE @program;", mySqlConn)
oCmd.Parameters.AddWithValue("@program", SqlDbType.VarChar).Value = programList.SelectedValue.ToString

'snippet for excel (not the entire code)

>
oCmd.Connection.Open()

            Using da As New SqlDataAdapter(oCmd)

                'begin to fill the dataset
                myDataSet = New DataSet()
                da.Fill(myDataSet, "fullViewPrg")

                'fill datatable and start datareader               
                DataReader = oCmd.ExecuteReader()

		iRow = 1
                iCol = 1

                'Display //Program Name in COLUMN A1
                xlWs.Cells(iRow, iCol).value = dt.Rows(iRow).Item("Program").ToString

		'Display platformType across cells
                iRow = 1
                iCol = 2

                For iCol = 0 To dt.Rows.Count - 1
                    xlWs.Cells(iRow, iCol + 2).Value = dt.Rows(iCol).Item("platformType").ToString
                Next


   		'DISPLAY //Values of 'cleared'
                iRow = 2
                iCol += 2


                For iCol = 0 To dt.Rows.Count - 1
                    xlWs.Cells(iRow + 1, iCol + 2).Value = dt.Rows(iCol).Item("cleared").ToString
                Next

'i close that connection and open up another connection as well the 2nd dataset


'select distinct directRights

'DISPLAY //Values of Rights across cells from 
                    iRow = 2
                    iCol = 1

                    For iRow = 0 To ab.Rows.Count - 1
                        xlWs.Cells(iRow + 2, iCol).Value = ab.Rows(iRow).Item("directRights").ToString
                    Next