Exporting Related Data From a Dataset


I am exporting data from a dataset into excel (.xls) format. At the moment this is just a test extract from the Northwind test database consisting of Customer,Orders and Order_Details Tables. What happens when I open excel is that three spreadsheets are created one for each table. However when I export a report from MS Reporting services my data hierarchy is maintained within excel. Could someone advise me on what to do to maintain the data hierarchy when exporting data?


Paul G
Who is Participating?
rockiroadsConnect With a Mentor Commented:
here is an example


uses excel automation like old vb does

u should then be able to specify what sheet u want to write to


I think u can also do Worksheets("sheetname").Select()
what code u using for exporting?

in that code, check to see if u can specify a sheetname
sorry, what code = what language and what method
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

pgilfeatherAuthor Commented:
VB.NET or C# would be nice.

Im assuming this is what u mean, u want all exports in one spreadsheet. If not, could u expand a bit further on your requirements
pgilfeatherAuthor Commented:

What I am looking for is my .xls file to open with as many subdatasheets as necessary. So that it is possible for a user to drill down into the hierarchy levels of data.


You can have the following format in excel

 + RootTableRow1
 + RootTableRow2
 + RootTableRow3
 + RootTableRow4
 -  RootTableRow5
           +  ChildRowA1
           +  ChildRowA2
           -   ChildRowA3
                       +  ChildRowB1
                       -   ChildRowB2

Ive not seen an excel spreadsheet like that, are your +/- expandable/collapsable buttons?

The only way I could think of is if you write your code to get the data from 3 tables

these queries u run, can u run them as union statements?
if not, could u put the results of these 3 queries into a temp table
then write your code to query the temp table (or union if u can manage that)

then as you write the the excel object, if a child row with children, write to next column, if not, go back a column
Put the logic in the write to the excel object
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.