Exporting Related Data From a Dataset

Posted on 2006-05-16
Last Modified: 2010-04-17

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
Question by:pgilfeather
    LVL 65

    Expert Comment

    what code u using for exporting?

    in that code, check to see if u can specify a sheetname
    LVL 65

    Expert Comment

    sorry, what code = what language and what method

    Author Comment

    VB.NET or C# would be nice.

    LVL 65

    Accepted Solution

    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()
    LVL 65

    Expert Comment

    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

    Author Comment


    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

    LVL 65

    Expert Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Suggested Solutions

    Title # Comments Views Activity
    withoutString  challenge 40 119
    mirrorEnds challenge 6 64
    Visual xHarbour 1 47
    Batch file output 20 71
    Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
    If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now