Export To Excel

[rkingmdu]

Hi,

I have two Tables, OrderHeader and OrderDetail, with a One-Many relationship between Header and Detail.

On Button_Click(), I need to export the data to Excel. What is the best way to export them?.

I know I can store the table data in a datatable and use StreamWriter and to
write to a CSV file. But I am not sure about the part where I have to loop through the detail table (not sure how to search for the Master key in the detail table). Is this the right approach?


I would appreciate any help.

Thanks
rkingmdu

 

[cjard]

Only you know how you want your data to appear. I'd write a sql query to do it and select it intoa single datatable, then write the table to an XML file that will open in Excel. I've posted examples before that used XML transforms. Search for ExcelableData and read the earliest thread.

I also made a subclass of dataset that can write itself to excel xml. See the attachment.
NOTE: This attachment deliberately does contain the BIN folder containing the compiled code because the project is in C# and a person with VB express has no way to use the code if they do not wish to convert it. Normally I remove the compiled binaries, but leaving this one in was a conscious decision. If you want to integrate the code into your VB project, you'll need to convert it.

 

[rkingmdu]

Export to Excel

Thank you very much. My idea was also to write the query into a single datatable and proceed from there.

I will check out your project.

rkingmdu

 

[cjard]

It does depend on how you want the data to be written

If you want:

Parent1Values
Child1Values
Child1Values
Child1Values
Child1Values
Parent2Values
Child2Values
Child2Values
Child2Values
Child2Values

Then you will need 2 datatables and write the excel file yourself (see my C# code for how to write xml that excel can read).

If you want:

Parent1Values Child1.1Values
Parent1Values Child1.2Values
Parent1Values Child1.3Values
Parent1Values Child1.4Values
Parent2Values Child2.1Values
Parent2Values Child2.2Values
Parent2Values Child2.3Values
Parent2Values Child2.4Values

Then one db query can work easily. Actually, if youre an SQL genius, one query can work for the top too, but it would have to look something like

SELECT * FROM
(
SELECT relevant parent values, 1 as type
UNION ALL
SELECT relevant child values, 2 as type
)
ORDER BY id field, type


This way your parent values get a type of 1, children a type of 2, sorting by ID, then type would give the parent IDs at the top, children underneath


Naturally the number of cols would need to be the same, or nulls used if they werent..

 

[rkingmdu]

Export to Excel

I will be using the second format.

If the user insists on the first format, then I would use the UNION ALL query.

Thanks,

rkingmdu