need to display data correctly in excel

[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)

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

 

[dglienna]

Nested Loops is the answer.
Something like this:

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

-David
2006 & 2007 Microsoft Most Valuable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[radiance]

i am getting a comexception error once I add this statement...not sure why..