excel

[fmardani]

Hi,
Is there a functionality in VB.NET to copy a whole data (retrieved from a sql query) into the sheet of an excel file?
Currently I do a loop using the sqldatareader.
I am aware of a dataset but just do not know how to just put the whole data in one go into the sheet.
Thanks

 

[chrissie1]

Tip.

excell can use ODBC.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[fmardani]

What good does this tip do for my purpose please?
Thanks

 

[JignasuK]

What you can do is, instead of having to export as excel file, export as xml file and then you can open it in excel. I also know there is a way to export as excel, but need never done that. I hope this will help. Below is the code I used (three lines).

SqlDataAdapter1.Fill(DataSet1)
Dim xmlStream As New Xml.XmlTextWriter("C:\Test.xls", Nothing)
DataSet1.WriteXml(xmlStream)

Jignasu

 

[fmardani]

So, you get the data into a xml file, and then how do you write to excel, the other question is, is this just a straight dump of the xml into the excel sheet in the correct columns, rows?
Thanks

 

[JignasuK]

Yes, It is straight dump to excel and it will be nicely formatted. Below is an example of what I dumped to xml file...You also have option to create schema file.

ns1:CourseID ns1:CourseName ns1:CourseDesc
28 Test 1 Test 1
32 tEST 2 tEST 2
34 Test X Test X
25 Test 4 Test 4
27 Test Test
29 Test 9 Test 9
31 Test 5 Test 5
33 Test Test

Jignasu

 

[chrissie1]

if excell can use odbc then you can make a direct connection to the sql query without going via a datset or datreader or extra xml file.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[fmardani]

JignasuK ,
So, you get the data into a xml file, and then how do you write to excel in one go rather than loop?
Thanks

 

[JignasuK]

You don't have to write to excel, you can open xml file using excel! In a sense, you are creating presentation layer using excel. So in future all you have to do is replace underlaying xml file and excel will update automatically.

 

[fmardani]

Is it only a manual job to open xml file using excel or can the code do this?
Thanks

 

[JignasuK]

You certainly can also open using code. I am not sure what exacetly you are trying to do. But if you could explain what is that you need, I might be able to offer other solution/scenario.

 

[fmardani]

So let me get this correct.
I get the data in a select query which can be then saved as an xml file, then it is just a matter of going to the file on the network and change the .xml to .xls and then open it up. This will have the columns, etc in the correct order.
Is this right?
Thanks

 

[JignasuK]

No...once you have xml file, you open the file in excel. It will ask you few question first time you will open. After you do that save the excel file and you are all set.

Jignasu

 

[ThatRickGuy]

I think what Christiaan was suggesting was to just make an ODBC entry for your database, and then create the query in Excel. Then you won't need to code anything, just work it with it in Excel.

-Rick